MySql快速插入与批量更新的方法

发布时间:2019-12-29编辑:脚本学堂
本文介绍下,在mysql数据库中,进行数据的快速插入与批量更新的具体方法,有需要的朋友参考学习下。

本节内容:
mysql快速插入与批量更新。

1,mysql数据的插入:
MySql一次插入多条数据的用法:
 

复制代码 代码示例:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12)...; 

可以通过循环,添加Values对应的列表,最后使用一次executeUpdate完成插入操作。
但是,MYsql语句长度有限制,可以查看mysql的配置文件my.in中max_allowed_packet属性,并进行相应设置。

2,mysql数据的更新:
Mysql中没有提供像Insert一样一次更新多条记录,需要逐条语句拼接。
 

复制代码 代码示例:
update weibo set userName = 'xyw' where id = '22';update weibo set userID = '143' where id = '35'; 

可以使用addBatch语句,将拼接起来的SQL语句进行一次性处理,但是效率并不高。
还需要处理resultset的释放问题,否则mysql会报错:"Commands out of sync; you can't run this command now"
针对update语句,虽然并没有resultset返回,但仍然需要释放。
而由于未知原因(可能是sql语句太长?),释放resultset非常耗时,最终算下来得不偿失。

使用另一种方法执行批量更新,语句形式:
 

INSERT INTO tbl_name [col_name1, col_name2,...)] VALUES(col_value1,col_value2,...), (col_value1,col_value2,...)ON DUPLICATE KEY UPDATE userName=VALUES(userName) 

以上方法,必须满足条件:col_name1, col_name2,...中必须有主键或者唯一键。
userName是要更新的列。
 
如果想一次更新多列,可以在userName=VALUES(userName)后面继续添加,例如:
 

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) 
 

如此,便可以同时更新userName和userID两个字段。

实现原理:首先,Mysql根据表名后面列出的主键,查找表(因为是主键,所以在表中唯一存在)。如果存在该行数据,则按照最后的col_name = values(col_name)列表对相应的字段,按照values列表中给出的值进行更新。

建议:表名后面的字段列表,除了主键之外,列出来的最好都作为更新的对象,即在语句最后都要有相应的col_name = values(col_name),否则,你在表名后罗列出来字段,在values中赋值了,但是不是更新的对象,显然是浪费。
 
如果不存在该行数据,则进行插入操作,没有作为更新对象的列按照默认值填充(前提是Mysql运行在非严格模式下。如果在严格模式下,没列都需要有默认值,否则运行出错)。
 
注意:
主键可以作为更新的对象 ,但是只是在表中不存在该记录时起作用,即执行了插入操作,如果表中已经存在了该主键对应的行数据,下次更新时不会再插入该行,而是执行除了主键之外的其他列的更新操作。

因此,最好不要将主键设置为更新的对象。
 
例子:
 

复制代码 代码示例:
INSERT INTO keywordtable(id, keyword, userName, userID) VALUES(1, '你好', 'Eliot', 22), (2, 'hello', 'Jhon', 23),  
(3, '嘻嘻', 'Jim', 24) ON DUPLICATE KEY UPDATE keyword=VALUES(keyword), userName=VALUES(userName),userID=VALUES(userID); 
 

说明:
除了id外,字段有keyword, userName, userID,它们是要更新的字段。