mysql数据库中批量更新

时间：2022-09-21 17:16:25

在项目中遇到向mysql数据库中批量插入数据的问题：
使用存储函数语法太过复杂，项目中舍弃使用临时表。于是：
查阅资料和借阅 http://blog.csdn.net/u011598529/article/details/21321919 博客。
对前辈的分享表示感谢。
经过实测。写出此博客。

建表语句

DROP TABLE IF EXISTS `keywordtable`;
CREATE TABLE `keywordtable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`keyword` varchar(32) DEFAULT NULL,
`userName` varchar(32) DEFAULT NULL,
`userID` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

方法一

使用：REPLACE INTO … VALUES：
语句如下：

REPLACE INTO keywordtable(id, keyword, userName, userID)
VALUES
(1, '你好1', 'donggua1', 11),(2, '你好2', 'donggua2', 22),
(3, '你好4', 'donggua4', 224),(4, '你好3', 'donggua3', 232),
(NULL,'你好5', 'donggua5', 252),(NULL,'你好5', 'donggua5', 252),
(NULL,'你好5', 'donggua5', 252),(NULL,'你好5', 'donggua5', 252)
[说明]
前四条为更新语句。 后面两条为新增语句。主键自增，这里设为null.
replace into　操作本质是对重复的记录先delete 后insert，
如果更新的字段不全会将缺失的字段置为缺省值

方法二

使用：INSERT INTO … ON DUPLICATE KEY UPDATE

INSERT INTO tbl_name [column1, column2,…)] VALUES(col_value1,col_value2,…), (col_value1,col_value2,…)ON DUPLICATE KEY UPDATE userID=VALUES(userID)

使用这种方法必须满足条件：column1, column2,…中必须有主键或者唯一键。

userID是要更新的列。

如果想一次更新多列，可以在userID=VALUES(userID)后面继续添加，例如：

INSERT INTO tbl_name [col_name1, col_name2,…)] VALUES(col_value1,col_value2,…), (col_value1,col_value2,…)ON DUPLICATE KEY UPDATE userName=VALUES(userName), userID = VALUES(userID)
 INSERT INTO keywordtable(id, keyword, userName, userID) 
VALUES(1, 'helloworkd', 'java', 22), (2, 'hellodonggua', 'mysql', 23),(3, 'hello china', 'china', 24) ,
 (4,'hello baima', 'baima', 2423),(NULL,'hello_wangzi', 'wangzi', 2424),(NULL,'hello_spring', 'spring', 25)
ON DUPLICATE KEY UPDATE keyword=VALUES(keyword), userName=VALUES(userName),userID=VALUES(userID);

这样就可以同时更新userName和userID两个字段。

它的实现原理是，首先MySQL根据表名后面列出的主键，查找表（因为是主键，所以在表中唯一存在）。
如果存在该行数据，则按照最后的column = values(col_name)列表对相应的字段，按照values列表中给出的值进行更新。
建议：表名后面的字段列表，除了主键之外，列出来的最好都作为更新的对象，即在语句最后都要有相应的column = values(column)，
否则，你在表名后罗列出来字段，在values中赋值了，但是不是更新的对象，显然是浪费。

如果不存在该行数据，则进行插入操作，没有作为更新对象的列按照默认值填充。

注意：

主键可以作为更新的对象 ，但是只是在表中不存在该记录时起作用，即执行了插入操作，
如果表中已经存在了该主键对应的行数据，下次更新时不会再插入该行，而是执行除了主键之外的其他列的更新操作。所以最好不要将主键设置为更新的对象。

实例：
 INSERT INTO keywordtable(id, keyword, userName, userID) 
VALUES(1, 'helloworkd', 'java', 22), (2, 'hellodonggua', 'mysql', 23),(3, 'hello china', 'china', 24) ,
 (4,'hello baima', 'baima', 2423),(NULL,'hello_wangzi', 'wangzi', 2424),(NULL,'hello_spring', 'spring', 25)
ON DUPLICATE KEY UPDATE 
 keyword=VALUES(keyword), userName=VALUES(userName),userID=VALUES(userID);
[说明]
 insert into 则是只update重复记录，不会改变其它字段。

[结论]：

两种方式在25000条数据的操作下。耗时是没有差别的。
一条条的插入式这两中方式的十倍时间。
临时表未测试。

参考：http://blog.csdn.net/u011598529/article/details/21321919
MySql5.1参考手册 第十三章：sql语句语法：insert 语法
标签：

相关文章