測試數(shù)據(jù)

-- 創(chuàng)建測試表1 CREATE TABLE `testtable1` ( `Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `UserId` INT(11) DEFAULT NULL, `UserName` VARCHAR(10) DEFAULT NULL, `UserType` INT(11) DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `IX_UserId` (`UserId`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- 創(chuàng)建測試表2 CREATE TABLE `testtable2` ( `Id` INT(11) NULL AUTO_INCREMENT, `UserId` INT(11) DEFAULT NULL, `UserName` VARCHAR(10) DEFAULT NULL, `UserType` INT(11) DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `IX_UserId` (`UserId`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入測試數(shù)據(jù)1 INSERT INTO testtable1(Id,UserId,UserName,UserType) VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3); -- 插入測試數(shù)據(jù)2 INSERT INTO testtable2(Id,UserId,UserName,UserType) VALUES(1,201,'aaa',1),(2,202,'bbb',2),(3,203,'ccc',3),(4,101,'xxxx',5);

可以看到上邊的數(shù)據(jù)中會有userid為重復的數(shù)據(jù) userid=101

mysql> show tables; +---------------+ | Tables_in_dev | +---------------+ | testtable1 | | testtable2 | +---------------+ mysql> select * from testtable1; +----+--------+----------+----------+ | Id | UserId | UserName | UserType | +----+--------+----------+----------+ | 1 | 101 | aa | 1 | | 2 | 102 | bbb | 2 | | 3 | 103 | ccc | 3 | +----+--------+----------+----------+ 3 rows in set (0.04 sec)mysql> select * from testtable2; +----+--------+----------+----------+ | Id | UserId | UserName | UserType | +----+--------+----------+----------+ | 1 | 201 | aaa | 1 | | 2 | 202 | bbb | 2 | | 3 | 203 | ccc | 3 | | 4 | 101 | xxxx | 5 | +----+--------+----------+----------+ 4 rows in set (0.04 sec)### 當執(zhí)行以下sql時，會報1062錯誤，提示有重復的key mysql> insert into testtable1 (userid,username,usertype)-> select userid,username,usertype from testtable2; 1062 - Duplicate entry '101' for key 'IX_UserId'

---

• 如果想讓上邊的sql執(zhí)行成功的話，可以使用IGNORE關鍵字

mysql> insert ignore into testtable1 (userid,username,usertype)-> select userid,username,usertype from testtable2; Query OK, 3 rows affected (0.12 sec) Records: 4 Duplicates: 1 Warnings:1 mysql> select * from testtable1; +----+--------+----------+----------+ | Id | UserId | UserName | UserType | +----+--------+----------+----------+ | 1 | 101 | aa | 1 | | 2 | 102 | bbb | 2 | | 3 | 103 | ccc | 3 | | 11 | 201 | aaa | 1 | | 12 | 202 | bbb | 2 | | 13 | 203 | ccc | 3 | +----+--------+----------+----------+ 6 rows in set (0.05 sec)

查詢sql，顯示testtable2表中的數(shù)據(jù)插入到了表1中（除了重復key的那條信息）

另外注意到主鍵id為11,12,13開始的，這個是因為之前insert的sql失敗導致的自增主鍵不連續(xù)

---

導入并覆蓋重復數(shù)據(jù)，REPLACE INTO

上邊那個是沒有插入重復key的數(shù)據(jù)

• 回滾之前testtable1表的數(shù)據(jù)

  mysql> truncate table testtable1; Query OK, 0 rows affected (0.62 sec)mysql> select * from testtable1; Empty set mysql> -- 插入測試數(shù)據(jù)1 INSERT INTO testtable1(Id,UserId,UserName,UserType) VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3); Query OK, 3 rows affected (0.09 sec) mysql> replace into testtable1 (userid,username,usertype)-> select userid,username,usertype from testtable2; Query OK, 5 rows affected (0.10 sec) Records: 4 Duplicates: 1 Warnings: 0 mysql> select * from testtable1; +----+--------+----------+----------+ | Id | UserId | UserName | UserType | +----+--------+----------+----------+ | 2 | 102 | bbb | 2 | | 3 | 103 | ccc | 3 | | 4 | 201 | aaa | 1 | | 5 | 202 | bbb | 2 | | 6 | 203 | ccc | 3 | | 7 | 101 | xxxx | 5 | +----+--------+----------+----------+

  可以看到表1中的101的username被覆蓋為表2中的數(shù)據(jù)，這個是因為replace是現(xiàn)將原來表一中重復的數(shù)據(jù)刪除掉，然后再執(zhí)行插入新的數(shù)據(jù)

  導入重復數(shù)據(jù)，保留未指定的值

  mysql> insert into testtable1 (userid,username,usertype) -> select userid,username,usertype from testtable2-> on duplicate key update-> testtable1.username = testtable2.username;

  以上sql對于重復的數(shù)據(jù)，只是將username進行了覆蓋，其他的值還是表一中的數(shù)據(jù)

總結

以上是生活随笔為你收集整理的Sql批量插入时如果遇到相同的数据怎么处理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。