REPLACE與INSERT INTO ... ON DUPLICATE KEY總結

svoid發表於2015-01-12

INSERT … ON DUPLICATE KEY UPDATE 總結

  • 當主鍵或唯一鍵衝突時,執行更新操作,否則執行插入操作,auto_increment始終累加。
  • 更新操作受多個唯一鍵影響,在MySQL5.6.6之後的SBR中被標記為不安全的
  • UPDATE子句可使用VALUES(col_name)使用Insert語句中列的值
  • DELAYED選項將被忽略
  • MySQL5.6.6之前,對於表級鎖儲存引擎(MyISAM),該語句會鎖定分割槽表中所有分割槽。MySQL5.6.6之後,只會鎖定所更新的分割槽(InnoDB無影響)
  • INSERT … SELECT ON DUPLICATE KEY UPDATE可能導致主從資料不一致,因此該語句在MySQL5.6.4之後的SBR中被標記為不安全的。
  • 當沒有主鍵或唯一鍵衝突時,與replace相同相當於普通的insert.
mysql> create table test(a int primary key auto_increment,b varchar(10),c varchar(10),d int);
Query OK, 0 rows affected (0.03 sec)

mysql> alter table test add unique index uq_b(b);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test (a,b,c,d) values(1,'1','1',1) on duplicate key update c='2', d=2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 | 1    | 2    |    1 |
+---+------+------+------+
1 row in set (0.01 sec)

mysql> insert into test (a,b,c,d) values(2,'1','1',1) on duplicate key update d=2;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 | 1    | 2    |    2 |
+---+------+------+------+
1 row in set (0.00 sec)

mysql> insert into test (a,b,c,d) values(null,'2','2',1) on duplicate key update d= values(a);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 | 1    | 2    |    2 |
| 2 | 2    | 2    |    1 |
+---+------+------+------+
2 rows in set (0.01 sec)

mysql> insert into test (a,b,c,d) values(null,'2','2',1) on duplicate key update d= values(a);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 | 1    | 2    |    2 |
| 2 | 2    | 2    |    3 |
+---+------+------+------+
2 rows in set (0.00 sec)

mysql> insert into test (a,b,c,d) values(null,'2','2',1) on duplicate key update d= values(a);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 | 1    | 2    |    2 |
| 2 | 2    | 2    |    4 |
+---+------+------+------+
2 rows in set (0.00 sec)

mysql> insert into test (a,b,c,d) values(null,'3','3',3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 | 1    | 2    |    2 |
| 2 | 2    | 2    |    4 |
| 5 | 3    | 3    |    3 |
+---+------+------+------+
3 rows in set (0.01 sec)

REPLACE總結

  • 當主鍵或唯一鍵衝突時,執行刪除操作,然後執行插入操作,否則直接執行插入操作。
  • 使用REPLACE時,必須同時擁有INSERT及DELETE許可權
  • MySQL 5.6.2之後,REPLACE可使用PARTITION選項指明明確的分割槽或子分割槽
  • REPLACE不能使用衝突列的值,如不指定列值將被設定為預設值
  • 刪除操作受多個唯一鍵影響,受影響的行數是刪除和插入的行的總和
  • REPLACE表不支援子查詢中查詢同一個表
  • REPLACE … SELECT可能導致主從資料不一致,因此該語句在MySQL5.6.4之後的SBR中被標記為不安全的。
  • MySQL5.6.6之前,對於表級鎖儲存引擎(MyISAM),該語句會鎖定分割槽表中所有分割槽,即使使用REPLACE … PARTITION語句。MySQL5.6.6之後,如不更新分割槽列,只鎖定與WHERE子句匹配的分割槽,如更新分割槽列,整個表將被鎖定

REPLACE演算法(LOAD DATA… REPLACE):

  1. 嘗試向表中插入新記錄
  2. 因為主鍵或唯一索引中的重複鍵錯誤導致插入失敗
  3. 從表中刪除與重複鍵值衝突的記錄
  4. 再次向表中插入新記錄
mysql> select * from test;
+----+------+------+------+
| a  | b    | c    | d    |
+----+------+------+------+
|  1 | 1    | 2    |    2 |
|  2 | 2    | 2    |    2 |
|  4 | 4    | 4    |    4 |
|  5 | 3    | 3    |    7 |
|  8 | 12   | 8    |    8 |
| 13 | 14   | 10   |   14 |
| 14 | 10   | 10   |   10 |
+----+------+------+------+
7 rows in set (0.01 sec)

mysql> replace into test (a,b,c,d) values (14,'14',9,9);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from test;
+----+------+------+------+
| a  | b    | c    | d    |
+----+------+------+------+
|  1 | 1    | 2    |    2 |
|  2 | 2    | 2    |    2 |
|  4 | 4    | 4    |    4 |
|  5 | 3    | 3    |    7 |
|  8 | 12   | 8    |    8 |
| 14 | 14   | 9    |    9 |
+----+------+------+------+
6 rows in set (0.00 sec)

mysql> replace into test (a,b,c,d) values (14,NULL,'',14);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from test;
+----+------+------+------+
| a  | b    | c    | d    |
+----+------+------+------+
|  1 | 1    | 2    |    2 |
|  2 | 2    | 2    |    2 |
|  4 | 4    | 4    |    4 |
|  5 | 3    | 3    |    7 |
|  8 | 12   | 8    |    8 |
| 14 | NULL |      |   14 |
+----+------+------+------+
6 rows in set (0.00 sec)


mysql> REPLACE INTO test SET a = 14 ,b= (select b from test where b = 12) ,c='12',d=12;
ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM clause
mysql> select b from test where b = 12;
+------+
| b    |
+------+
| 12   |
+------+
1 row in set (0.02 sec)

mysql> REPLACE INTO test SET a = 14 ,b= (select '12') ,c='12',d=12;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from test;
+----+------+------+------+
| a  | b    | c    | d    |
+----+------+------+------+
|  1 | 1    | 2    |    2 |
|  2 | 2    | 2    |    2 |
|  4 | 4    | 4    |    4 |
|  5 | 3    | 3    |    7 |
| 14 | 12   | 12   |   12 |
+----+------+------+------+
5 rows in set (0.00 sec)

整理自網路

Svoid
2015-01-09

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29733787/viewspace-1399583/,如需轉載,請註明出處,否則將追究法律責任。

相關文章