MySQL实战之Insert语句的使用心得

一、Insert的几种语法

1-1.普通插入语句

INSERT INTO table (`a`, `b`, `c`, ……) VALUES ('a', 'b', 'c', ……);

这里不再赘述,注意顺序即可,不建议小伙伴们去掉前面括号的内容,别问为什么,容易被同事骂。

1-2.插入或更新

如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用"INSERT INTO …> -- 用户陈哈哈充值了30元买会员 INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员') > -- 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> -- 用户首次添加 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>

MySQL实战之Insert语句的使用心得

扫一扫手机访问