mybatisinsert忽略联合唯⼀索引_MySQL实战中,Insert语句
的使⽤⼼得总结
提到MySQL的Insert语句,你肯定不陌⽣,或许已经张⼝就来:不就是insert into table values(xxx,xxx,xxx)嘛!没错,但在实战中,根据不同的需求场景,插⼊操作在语法、执⾏⽅式上的⽤法多种多样。今天,我来给⼩伙伴们从这两⽅⾯分享⼀下搬砖⼼得,如果你有疑问或好的想法,记得在评论区给我留⾔,我会在搬砖之余和⼤家⼀起吃⽠喔~
手动trim⼀、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)
mysql语句顺序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"语句,这样就不必先查询,再决定是否先删除再插⼊。
流程图switch框图怎么画"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;如果不会导致唯⼀索引 ⼩tips:
或者主键重复时,就直接添加新⾏。 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的速度。可以说是很慢了
foreign key references发现逐条插⼊优化成本太⾼。然后去查询优化⽅式。发现⽤批量插⼊的⽅法可以显著提⾼速度。
将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);
}
linux如何删除某个文件夹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/mysqlf(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)
s11全球总决赛虎牙B、⼤批量导数据,也可以先关闭索引,数据导⼊完后再打开索引
关闭:ALTER TABLE user_info DISABLE KEYS;
开启:ALTER TABLE user_info ENABLE KEYS;
三、REPLACE INTO语法的“坑”
上⾯曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过⼀个的唯⼀索引。在这种情况下,REPLACE将考虑每⼀个唯⼀索引,并对每⼀个索引对应的重复记录都删除,然后插⼊这条新记录。假设有⼀个table1表,有3个字段a, b, c。它们都有⼀个唯⼀索引,会怎么样呢?我们早⼀些数据测试⼀下。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论