MySQL实战中,Insert语句的使用心得总结,字节跳动Java金三银四解析

– 用户陈哈哈充值了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 list= new ArrayList();

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);

mybatis Mapper中的sql写法:

INSERT INTO db.user_info

( id,

username,

password,

price,

hobby)

values

(null,

#{item.userName},

#{item.password},

#{item.price},

#{item.hobby})

这样就能进行批量插入操作:

注:但是当批量操作数据量很大的时候。例如我插入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 所以报错。

解决方法:

《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享

数据库是MySQL57,查了一下资料是MySQL的一个系统参数问题:

max_allowed_packet,其默认值为1048576(1M),

查询:

show VARIABLES like ‘%max_allowed_packet%’;

MySQL实战中,Insert语句的使用心得总结,字节跳动Java金三银四解析

修改此变量的值:MySQL安装目录下的my.ini(windows)/etc/mysql.cnf(linux) 文件中的[mysqld]段中的

max_allowed_packet = 1M,如更改为20M(或更大,如果没有这行内容,增加这一行),如下图

上一篇:每天学一点——基础数据类型内置


下一篇:DHTML练习题