mybatisinsert忽略联合唯⼀索引_MySQL实战中，Insert语句
的使⽤⼼得总结
提到MySQL的Insert语句，你肯定不陌⽣，或许已经张⼝就来：不就是insert into table values(xxx,xxx,xxx)嘛！没错，但在实战中，根据不同的需求场景，插⼊操作在语法、执⾏⽅式上的⽤法多种多样。今天，我来给⼩伙伴们从这两⽅⾯分享⼀下搬砖⼼得，如果你有疑问或好的想法，记得在评论区给我留⾔，我会在搬砖之余和⼤家⼀起吃⽠喔~
⼀、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；如果不会导致唯⼀索引 ⼩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的速度。可以说是很慢了
发现逐条插⼊优化成本太⾼。然后去查询优化⽅式。发现⽤批量插⼊的⽅法可以显著提⾼速度。
将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/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）
B、⼤批量导数据，也可以先关闭索引，数据导⼊完后再打开索引
关闭：ALTER TABLE user_info DISABLE KEYS;
开启：ALTER TABLE user_info ENABLE KEYS;
三、REPLACE INTO语法的“坑”
insert语句字段顺序上⾯曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过⼀个的唯⼀索引。在这种情况下，REPLACE将考虑每⼀个唯⼀索引，并对每⼀个索引对应的重复记录都删除，然后插⼊这条新记录。假设有⼀个table1表，有3个字段a, b, c。它们都有⼀个唯⼀索引，会怎么样呢？我们早⼀些数据测试⼀下。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。