MySQL实战中，Insert语句的使用心得总结，小白看完都学会了

您现在的位置：首页后端 MySQL实战中，Insert语句的使用心得总结，小白看完都学会了

MySQL实战中，Insert语句的使用心得总结，小白看完都学会了

码农世界 2024-06-20 后端 92 次浏览 0个评论

2-2、插入速度慢的其他几种优化途径
三、REPLACE INTO语法的“坑”

一、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 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})
  
  自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
  
  深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
  
  因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
  
  既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！
  
  由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新
  
  如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
  
  最后
  
  ActiveMQ消息中间件面试专题
  - 什么是ActiveMQ?
  - ActiveMQ服务器宕机怎么办？
  - 丢消息怎么办？
  - 持久化消息非常慢怎么办？
  - 消息的不均匀消费怎么办？
  - 死信队列怎么办？
  - ActiveMQ中的消息重发时间间隔和重发次数吗？
    ActiveMQ消息中间件面试专题解析拓展：
    
    redis面试专题及答案
    - 支持一致性哈希的客户端有哪些？
    - Redis与其他key-value存储有什么不同？
    - Redis的内存占用情况怎么样？
    - 都有哪些办法可以降低Redis的内存使用情况呢？
    - 查看Redis使用情况及状态信息用什么命令？
    - Redis的内存用完了会发生什么？
    - Redis是单线程的，如何提高多核CPU的利用率？
      
      Spring面试专题及答案
      - 谈谈你对 Spring 的理解
      - Spring 有哪些优点？
      - Spring 中的设计模式
      - 怎样开启注解装配以及常用注解
      - 简单介绍下 Spring bean 的生命周期
        Spring面试答案解析拓展
        
        高并发多线程面试专题
        
        现在有线程 T1、T2 和 T3。你如何确保 T2 线程在 T1 之后执行，并且 T3 线程在 T2 之后执行？
        Java 中新的 Lock 接口相对于同步代码块（synchronized block）有什么优势？如果让你实现一个高性能缓存，支持并发读取和单一写入，你如何保证数据完整性。
        Java 中 wait 和 sleep 方法有什么区别？
        如何在 Java 中实现一个阻塞队列？
        如何在 Java 中编写代码解决生产者消费者问题？
        写一段死锁代码。你在 Java 中如何解决死锁？
        高并发多线程面试解析与拓展
        
        jvm面试专题与解析
        
        JVM 由哪些部分组成？
        JVM 内存划分？
        Java 的内存模型?
        引用的分类？
        GC什么时候开始？
        JVM面试专题解析与拓展！
        
        在有线程 T1、T2 和 T3。你如何确保 T2 线程在 T1 之后执行，并且 T3 线程在 T2 之后执行？
        
        Java 中新的 Lock 接口相对于同步代码块（synchronized block）有什么优势？如果让你实现一个高性能缓存，支持并发读取和单一写入，你如何保证数据完整性。
        Java 中 wait 和 sleep 方法有什么区别？
        如何在 Java 中实现一个阻塞队列？
        如何在 Java 中编写代码解决生产者消费者问题？
        写一段死锁代码。你在 Java 中如何解决死锁？
        高并发多线程面试解析与拓展
        
        [外链图片转存中…(img-Zpdwxmn0-1712098609170)]
        
        jvm面试专题与解析
        
        JVM 由哪些部分组成？
        JVM 内存划分？
        Java 的内存模型?
        引用的分类？
        GC什么时候开始？
        JVM面试专题解析与拓展！
        
        [外链图片转存中…(img-ufEGOZnR-1712098609170)]

转载请注明来自码农世界，本文标题：《MySQL实战中，Insert语句的使用心得总结，小白看完都学会了》

码农世界 37815篇文章站点微博

每一天，每一秒，你所做的决定都会改变你的人生！

发表评论取消回复

评论列表（暂无评论，92人围观）参与讨论

还没有评论，来说两句吧...

Top