REPLACE與INSERT INTO ... ON DUPLICATE KEY總結
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):
- 嘗試向表中插入新記錄
- 因為主鍵或唯一索引中的重複鍵錯誤導致插入失敗
- 從表中刪除與重複鍵值衝突的記錄
- 再次向表中插入新記錄
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql INSERT ... ON DUPLICATE KEY UPDATEMySql
- MySQL insert on duplicate key update 死鎖MySql
- MySQL批量Insert應用ON DUPLICATE KEY UPDATEMySql
- MySQL 5.5 INSERT ... ON DUPLICATE KEY UPDATE語句說明MySql
- BUG: pymysql executemany不支援insert on duplicate key updateMySql
- MySQL 關於 INSERT INTO...ON DUPLICATE KEY UPDATE 的使用MySql
- SQLite INSERT OR REPLACE使用SQLite
- SQLite 之 INSERT OR REPLACE使用SQLite
- 翻譯:insert on duplicate key update(已提交到MariaDB官方手冊)
- Duplicate的一點總結
- RESTOREkeyttlserialized-value[REPLACE]RESTTLSZed
- SSH key使用總結
- MySQL中REPLACE INTO和INSERT INTO的區別分析MySql
- insert:key too large to index…Index
- MySQL_插入更新 ON DUPLICATE KEY UPDATEMySql
- JavaScript keydown事件總結JavaScript事件
- MySQL pt-duplicate-key-checker工具使用初探MySql
- Redshift關於SORTKEY排序鍵、DISTKEY分配鍵的總結排序
- Direct Path Insert與APPEND, PARALLEL的梳理與小結APPParallel
- MYSQL INNODB replace into 死鎖 及 next key lock 淺析MySql
- ReplaceableModel:為 Eloquent 模型新增 REPLACE 和 INSERT IGNORE 查詢功能模型
- Java中List集合轉Map集合報錯:Duplicate keyJava
- DUMP-CX_SY_OPEN_SQL_DB-DBSQL_DUPLICATE_KEY_ERRORSQLError
- 【Go】strings.Replace 與 bytes.Replace 調優Go
- foreign key的一些總結
- PHP字串替換substr_replace與str_replace函式PHP字串函式
- mysql實現merge功能之DUPLICATE key UPDATE語法MySql
- insert /*+ append */ into 與insert into 的區別APP
- keycloak~對框架中提供的Provider總結框架IDE
- Java中replace與replaceAll區別Java
- oracle中translate與replace的使用Oracle
- ERROR 1062 (23000): Duplicate entry for key 'PRIMARY'Error
- Q:[Vue warn]: Duplicate keys detected: ‘PAYACT‘. This may cause an update error.VueError
- MySQL使用pt-duplicate-key-checker找出冗餘、重複索引MySql索引
- MySQL 建立外來鍵報錯Can't write; duplicate key in tableMySql
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundIndex
- 筆記:React 中關於 key 的一點總結筆記React
- 代理與反射總結反射