ON DUPLICATE KEY UPDATE

事件背景

在閱讀公司原來代碼的過程中，我發現了這樣一段代碼:

$sql = "INSERT INTO {$table} ({$fields}) VALUES " . $values;
if (!empty($onDuplicate)) {$sql .= ' ON DUPLICATE KEY UPDATE '. $onDuplicate;
}

在語義的理解上，應當是索引沖突則更新原有索引數據。經過查閱資料，我總結如下：

假設業務上我們需要的就是如果存在則更新,如果不存在則新增. INSERT 中ON DUPLICATE KEY UPDATE（用redis的kv就可以很容易的實現.在MySQL中也有這樣的功能）

但是這個在在使用的時候需要把關鍵的字段（列）設置為key ,unique key。（也就是會發生沖突的索引）

INSERT 中ON DUPLICATE KEY UPDATE的使用：

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值，則執行舊行UPDATE。

栗子

CREATE TABLE `test_duplicate` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) NOT NULL,`b` int(11) NOT NULL,`c` int(11) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test_duplicate (a,b,c) values(1,2,3);

假設我們有表如上，SQL列a被定義為UNIQUE，并且包含值1，則以下兩段語句具有相同的效果：

mysql>INSERT INTO test_duplicate (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  
mysql> select * from test_duplicate;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | 1 | 2 | 4 |
+----+---+---+---+mysql>SELECT id,a,b,c from test_duplicate where a=1;
mysql>UPDATE table SET c=c+1 WHERE id=1; SELECT id,a,b,c from test_duplicate where a=1;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | 1 | 2 | 5 |
+----+---+---+---+

從結果可以看出來，2段SQL都都c進行了+1操作。但是insert實際上并沒有進行插入數據而是進行了更新數據。

那如果，我們表內有兩個可能會產生沖突的鍵時，又會如何呢？

mysql> ALTER TABLE `test_duplicate` ADD UNIQUE(`b`);
mysql> INSERT INTO test_duplicate (a,b,c) VALUES (2,3,4);mysql> INSERT INTO test_duplicate (a,b,c) VALUES (1,3,4) ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.00 sec)mysql> select * from test_duplicate;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | 1 | 2 | 6 |
|  3 | 2 | 3 | 4 |
+----+---+---+---+

可以看出來，同時更新了兩條數據 。

那假如同一行數據，我們有兩個沖突的值會產生怎么樣的結果呢？

mysql> INSERT INTO test_duplicate (a,b,c) VALUES (1,2,4) ON DUPLICATE KEY UPDATE c=c+1;
mysql> select * from test_duplicate;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | 1 | 2 | 7 |
|  3 | 2 | 3 | 4 |
+----+---+---+---+

因此，我們在設計表的時候，應該盡量避免多沖突值得存在，如果實在避免不了，我們可以使用values方法獲取本次提交的值。VALUES()函數只在INSERT...UPDATE語句中有意義，其它時候會返回NULL。

mysql> INSERT INTO test_duplicate (a,b,c) VALUES (1,2,4) ON DUPLICATE KEY UPDATE c=values(c);
mysql> select * from test_duplicate;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | 1 | 2 | 4 |
|  3 | 2 | 3 | 4 |
+----+---+---+---+

應該一般需求都是要達到這個結果

需要注意的是，在事務中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一筆數據時會等待其它事務結束后才執行，一般SELECT ... 則不受此影響。拿上面的實例來說，當我執行select status from t_goods where id=1 for update;后。我在另外的事務中如果再次執行select status from t_goods where id=1 for update;則第二個事務會一直等待第一個事務的提交，此時第二個查詢處于阻塞的狀態，但是如果我是在第二個事務中執行select status from t_goods where id=1;則能正常查詢出數據，不會受第一個事務的影響。

關于老的數據庫密碼設置

剛入職的時候，我編譯了自己的docker是php7環境的，然后無法支持mysql只支持mysqlnd作為pdo驅動。于是乎，錯誤來了

"SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

在google遨游了很久，也沒找到解決方式，然后，也嘗試著裝mysql然后編譯PHP的時候使用mysql的頭文件嘗試修改mysqlnd的方式也沒有成功。

網上大部分答案都需要登入mysql服務器去改my.cnf。

最后終于搞定了,將他記錄下來，原來可以臨時修改會話的密碼長度然后重設。

mysql> SELECT user, Length(`Password`) FROM  `mysql`.`user`;
+----------------+--------------------+
| user           | Length(`Password`) |
+----------------+--------------------+
| root           |                 16 |
| root           |                  0 |
| root           |                  0 |
|                |                  0 |
|                |                  0 |
| root           |                 16 |
| test           |                 16 |
| club_star_user |                 16 |
| club_star_user |                 16 |
| wenlong11      |                 16 |
+----------------+--------------------+

mysql> SET SESSION old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('abc'));

mysql> UPDATE mysql.user SET Password = PASSWORD('123456') WHERE user = 'xxxx';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT user, Length(`Password`) FROM  `mysql`.`user`;
+----------------+--------------------+
| user           | Length(`Password`) |
+----------------+--------------------+
| root           |                 16 |
| root           |                  0 |
| root           |                  0 |
|                |                  0 |
|                |                  0 |
| root           |                 16 |
| test           |                 16 |
| club_star_user |                 16 |
| club_star_user |                 16 |
| wenlong11      |                 41 |
+----------------+--------------------+

可以觀察到 密碼長度終于變成41的新版的長度了

order by 排序不準

mysql排序 假如對很多值相等的值進行order 分頁 會產生亂序 導致數據重復

select * from where a = 3 and b = 5 order by c descselect * from where a = 3 and b =5 order by c desc, d desc

解決方式： 1、盡量不要使用這種字段排序 2、如果業務需求，將其修改成多種混合 如 order field desc => order field desc, uniqueField desc 確保結果不會混亂

MySQL Explain

示例: explain select * from tablename;

將會得出結果查詢詳情，而不是結果集

expain出來的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

force index

在某些情況下，mysql推薦的索引并不是我們最想用的業務（根據業務需求）

這個時候，我們可以將自己確定的索引進行設置，保證本條SQL強制走索引

select * from $table_name force index(index_name) where condition  limit number

測試性能比較

8000W 數據，不用force index 200s都未查詢完畢

加了之后，1S左右完成

執行explain,發現這個sql掃描了8000W條記錄到磁盤上。然后再進行篩選。type=index說明整個索引樹都被掃描了，效果顯然不理想。

ignore index

對應的，在某些情況下我們確定了不需要某個索引

這個時候，我們可以將此索引忽略，保證本條SQL不遍歷這個索引