巡山小妖007

个人站

欢迎来到我的个人博客~


Sql批量插入时如果遇到相同的数据怎么处理

测试数据

-- 创建测试表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; 

-- 创建测试表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; 

-- 插入测试数据1 
INSERT INTO testtable1(Id,UserId,UserName,UserType) 
VALUES(1,101,'aa',1),(2,102,'bbb',2),(3,103,'ccc',3); 

-- 插入测试数据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);

可以看到上边的数据中会有userid为重复的数据 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)

### 当执行以下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执行成功的话,可以使用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表中的数据插入到了表1中(除了重复key的那条信息)

另外注意到主键id为11,12,13开始的,这个是因为之前insert的sql失败导致的自增主键不连续


导入并覆盖重复数据,REPLACE INTO

上边那个是没有插入重复key的数据

  • 回滚之前testtable1表的数据

    mysql> truncate table testtable1;
    Query OK, 0 rows affected (0.62 sec)
      
    mysql> select * from testtable1;
    Empty set
    mysql> -- 插入测试数据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中的数据,这个是因为replace是现将原来表一中重复的数据删除掉,然后再执行插入新的数据

    导入重复数据,保留未指定的值

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

    以上sql对于重复的数据,只是将username进行了覆盖,其他的值还是表一中的数据

打赏一个呗

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦