一、Insert的几种语法
1-1.普通插入语句
INSERT INTO table (`a`, `b`, `c`, ……) VALUES ('a', 'b', 'c', ……);
这里不再赘述,注意顺序即可,不建议小伙伴们去掉前面括号的内容,别问为什么,容易被同事骂。
1-2.插入或更新
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"语句:
情景示例:这张表存了用户历史充值金额,如果第一次充值就新增一条数据,如果该用户充值过就累加历史充值金额,需要保证单个用户数据不重复录入。
这时可以使用"INSERT INTO … ON DUPLICATE KEY UPDATE …"语句。
注意事项:"INSERT INTO … ON DUPLICATE KEY UPDATE …"语句是基于唯一索引或主键来判断唯一(是否存在)的。如下SQL所示,需要在username字段上建立唯一索引(Unique),transId设置自增即可。
-- 用户陈哈哈充值了30元买会员 INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员') ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员'; -- 用户陈哈哈充值了100元买瞎子至高之拳皮肤 INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '购买盲僧至高之拳皮肤') ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='购买盲僧至高之拳皮肤';
若username='chenhaha'的记录不存在,INSERT语句将插入新记录,否则,当前username='chenhaha'的记录将被更新,更新的字段由UPDATE指定。
对了,ON DUPLICATE KEY UPDATE为MySQL特有语法,比如在MySQL迁移Oracle或其他DB时,类似的语句要改为MERGE INTO语法,兼容性让人想骂街。但没办法,就像用WPS写的xlsx用Office无法打开一样。
1-3.插入或替换
如果我们想插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。
情景示例:这张表存的每个客户最近一次交易订单信息,要求保证单个用户数据不重复录入,且执行效率最高,与数据库交互最少,支撑数据库的高可用。
此时,可以使用"REPLACE INTO"语句,这样就不必先查询,再决定是否先删除再插入。
"REPLACE INTO"语句是基于唯一索引或主键来判断唯一(是否存在)的。
"REPLACE INTO"语句是基于唯一索引或主键来判断唯一(是否存在)的。
"REPLACE INTO"语句是基于唯一索引或主键来判断唯一(是否存在)的。
注意事项:如下SQL所示,需要在username字段上建立唯一索引(Unique),transId设置自增即可。
-- 20点充值 REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值'); -- 21点买皮肤 REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');
若username='chenhaha'的记录不存在,REPLACE语句将插入新记录(首次充值),否则,当前username='chenhaha'的记录将被删除,然后再插入新记录。
id不要给具体值,不然会影响SQL执行,业务有特殊需求除外。
小tips:
ON DUPLICATE KEY UPDATE:如果插入行出现唯一索引或者主键重复时,则执行旧的update;如果不会导致唯一索引或者主键重复时,就直接添加新行。
REPLACE INTO:如果插入行出现唯一索引或者主键重复时,则delete老记录,而录入新的记录;如果不会导致唯一索引或者主键重复时,就直接添加新行。
replace into 与 insert on deplicate udpate 比较:
1、在没有主键或者唯一索引重复时,replace into 与 insert on deplicate udpate 相同。
2、在主键或者唯一索引重复时,replace是delete老记录,而录入新的记录,所以原有的所有记录会被清除,这个时候,如果replace语句的字段不全的话,有些原有的比如c字段的值会被自动填充为默认值(如Null)。
3、细心地朋友们会发现,insert on deplicate udpate只是影响一行,而REPLACE INTO可能影响多行,为什么呢?写在文章最后一节咯~
1-4.插入或忽略
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO …语句:情景很多,不再举例赘述。
注意事项:同上,"INSERT IGNORE INTO …"语句是基于唯一索引或主键来判断唯一(是否存在)的,需要在username字段上建立唯一索引(Unique),transId设置自增即可。
-- 用户首次添加 INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 20:00:20'); -- 二次添加,直接忽略 INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 21:00:20');
二、大量数据插入
2-1、三种处理方式
2-1-1、单条循环插入
我们取10w条数据进行了一些测试,如果插入方式为程序遍历循环逐条插入。在mysql上检测插入一条的速度在0.01s到0.03s之间。
逐条插入的平均速度是0.02*100000,也就是33分钟左右。
下面代码是测试例子:
1普通循环插入100000条数据的时间测试
@Test public void insertUsers1() { User user = new User(); user.setUserName("提莫队长"); user.setPassword("正在送命"); user.setPrice(3150); user.setHobby("种蘑菇"); for (int i = 0; i < 100000; i++) { user.setUserName("提莫队长" + i); // 调用插入方法 userMapper.insertUser(user); } }
执行速度是30分钟也就是0.018*100000的速度。可以说是很慢了
发现逐条插入优化成本太高。然后去查询优化方式。发现用批量插入的方法可以显著提高速度。
将100000条数据的插入速度提升到1-2分钟左右↓
2-1-2、修改SQL语句批量插入
insert into user_info (user_id,username,password,price,hobby) values (null,'提莫队长1','123456',3150,'种蘑菇'),(null,'盖伦','123456',450,'踩蘑菇');
用批量插入插入100000条数据,测试代码如下:
@Test public void insertUsers2() { List<User> list= new ArrayList<User>(); User user = new User(); user.setPassword("正在送命"); user.setPrice(3150); user.setHobby("种蘑菇"); for (int i = 0; i < 100000; i++) { user.setUserName("提莫队长" + i); // 将单个对象放入参数list中 list.add(user); } userMapper.insertListUser(list); }
批量插入使用了0.046s 这相当于插入一两条数据的速度,所以用批量插入会大大提升数据插入速度,当有较大数据插入操作是用批量插入优化
批量插入的写法:
dao定义层方法:
Integer insertListUser(List<User> user);
mybatis Mapper中的sql写法:
<insert id="insertListUser" parameterType="java.util.List"> INSERT INTO `db`.`user_info` ( `id`, `username`, `password`, `price`, `hobby`) values <foreach collection="list" item="item" separator="," index="index"> (null, #{item.userName}, #{item.password}, #{item.price}, #{item.hobby}) </foreach> </insert>
这样就能进行批量插入操作:
注:但是当批量操作数据量很大的时候。例如我插入10w条数据的SQL语句要操作的数据包超过了1M,MySQL会报如下错:
报错信息:
Mysql You can change this value on the server by setting the max_allowed_packet' variable. Packet for query is too large (6832997 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
解释:
用于查询的数据包太大(6832997> 1048576)。 您可以通过设置max_allowed_packet的变量来更改服务器上的这个值。
通过解释可以看到用于操作的包太大。这里要插入的SQL内容数据大小为6M 所以报错。
解决方法:
数据库是MySQL57,查了一下资料是MySQL的一个系统参数问题:
max_allowed_packet,其默认值为1048576(1M),
查询:
show VARIABLES like '%max_allowed_packet%';
修改此变量的值:MySQL安装目录下的my.ini(windows)或/etc/mysql.cnf(linux) 文件中的[mysqld]段中的
max_allowed_packet = 1M,如更改为20M(或更大,如果没有这行内容,增加这一行),如下图
保存,重启MySQL服务。现在可以执行size大于1M小于20M的SQL语句了。
但是如果20M也不够呢?
2-1-3、分批量多次循环插入
如果不方便修改数据库配置或需要插入的内容太多时,也可以通过后端代码控制,比如插入10w条数据,分100批次每次插入1000条即可,也就是几秒钟而已;当然,如果每条的内容很多的话,另说。。
2-2、插入速度慢的其他几种优化途径
A、通过show processlist;命令,查询是否有其他长进程或大量短进程抢占线程池资源 ?看能否通过把部分进程分配到备库从而减轻主库压力;或者,先把没用的进程kill掉一些?(手动挠头o_O)
B、大批量导数据,也可以先关闭索引,数据导入完后再打开索引
关闭:ALTER TABLE user_info DISABLE KEYS;
开启:ALTER TABLE user_info ENABLE KEYS;
三、REPLACE INTO语法的“坑”
上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引,会怎么样呢?我们早一些数据测试一下。
-- 测试表创建,a,b,c三个字段均有唯一索引 CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE); -- 插入三条测试数据 INSERT into table1 VALUES(1,1,1); INSERT into table1 VALUES(2,2,2); INSERT into table1 VALUES(3,3,3);
此时table1中已经有了3条记录,a,b,c三个字段都是唯一(UNIQUE)索引
mysql> select * from table1; +---+---+---+ | a | b | c | +---+---+---+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +---+---+---+ 3 rows in set (0.00 sec)
下面我们使用REPLACE语句向table1中插入一条记录。
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
mysql> REPLACE INTO table1(a, b, c) VALUES(1,2,3); Query OK, 4 rows affected (0.04 sec)
此时查询table1中的记录如下,只剩一条数据了~
mysql> select * from table1; +---+---+---+ | a | b | c | +---+---+---+ | 1 | 2 | 3 | +---+---+---+ 1 row in set (0.00 sec)
(老板:插入前10w数据,插入5w数据后还剩8w数据??,咱们家数据让你喂狗了吗!!)
REPLACE INTO语法回顾:如果插入行出现唯一索引或者主键重复时,则delete老记录,而录入新的记录;如果不会导致唯一索引或者主键重复时,就直接添加新行。
我们可以看到,在用REPLACE INTO时每个唯一索引都会有影响的,可能会造成误删数据的情况,因此建议不要在多唯一索引的表中使用REPLACE INTO;
总结
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]