大量插入数据时关闭主键唯一性检测

最新推荐文章于 2025-05-17 00:43:16 发布

原创最新推荐文章于 2025-05-17 00:43:16 发布 · 5.4k 阅读

3 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

19 篇文章

订阅专栏

本文探讨了MySQL中批量导入数据的方法，特别是如何处理主键冲突的问题。介绍了MyISAM和InnoDB表类型的处理策略，包括禁用唯一性检查、使用IGNORE和REPLACE关键字等技巧。

部署运行你感兴趣的模型镜像

我们想实现的效果是：

合并数据库的说话，如果出现主键重复的情况，首先不报错。如果可以，则两条数据都保留，更新其中的一条数据，然后使数据不唯一。

思路一：mysql插入数据时可以关闭主键唯一检测，关闭检测，等数据库合并完成，用脚本或者手动修改主键重复的值（数据量比较小时）。（此方法测试了不成功，参照下文做的，mysql版本 5.7.0）

1) 对于Myisam类型的表，可以通过以下方式快速的导入大量的数据。
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

而对于Innodb类型的表，这种方式并不能提高导入数据的效率。

2) 对于Innodb类型的表，我们有以下几种方式可以提高导入的效率：

因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

测试了下，mysql（5.7.0），不论是 Innodb还是myisam引擎，参照下文连接中的方法进行唯一性检测关闭都不成功。

有大牛知道原因可以讨论下。。。。。

http://www.debugrun.com/a/1ZvDkzx.html

思路二：插入数据时，忽略掉重复的值，合并完成再将重复的数据二次插入。

创建两个索引不同的表

CREATE TABLE `myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(22) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(22) NOT NULL,
PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据

INSERT INTO myisam(id,name) VALUES ('1','NAME1'),('2','NAME2'),('3','NAME3');

INSERT INTO innodb(id,name) VALUES ('1','NAME1'),('2','NAME2'),('3','NAME3');

思路二：使用INGORE

INSERT IGNORE INTO `mysiam` (id, name)VALUES( '1', 'Thomas'),('4','aa');

使用INGORE 可以同时插入一条或多条，显示插入行数。不报错。当然也可以一条一条的插入，显示的为本条sql插入的情况。

[SQL] INSERT IGNORE INTO `myisam` (id, name)VALUES( '1', 'Thomas'),('4','aa');
受影响的行: 1
时间: 0.003s

扩展：

来源：http://www.cnblogs.com/chenmh/p/5392540.html

REPLACE：使用replace当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入。

REPLACE INTO mysiam() VALUES(1,1),(1,2),(2,2);    ====》它是先插入了（1,1）然后又删除了（1,1）

ON DUPLICATE KEY UPDATE

当插入的记录遇到主键或者唯一键重复时，会执行后面定义的UPDATE操作。相当于先执行Insert 操作，再根据主键或者唯一键执行update操作。

INSERT INTO mysiam() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;相当于：
INSERT INTO Tupdate() VALUES(1,1)
UPDATE Tupdate
SET NAME1=NAME1+1
WHERE ID=1;
INSERT INTO mysiam() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=VALUES(NAME1)+1;
相当于:INSERT INTO Tupdate() VALUES(1,1)
UPDATE Tupdate
SET NAME1=2+1
WHERE ID=1;

INSERT INTO mysiam() VALUES(1,1),(2,1) ON DUPLICATE KEY UPDATE NAME1=VALUES(ID)+1;
相当于：INSERT INTO Tupdate() VALUES(1,1)
UPDATE Tupdate
SET NAME1=2+1
WHERE ID=1;