MySQL批量插入数据时字段值从该字段最大值+1开始递增问题解决

最近遇到一个数据库相关的问题：批量插入数据时要求某个字段的值从该字段值得最大值+1开始递增，上网查了很多资料，有前人分享过问题的解决方法，这里总结下前人的解决方法：

需要使用到MySQL的prepare、execute、deallocate这三个函数，插入一条数据需要三步才能实现，

第一步：准备好一条SQL语句供prepare使用
第二步：execute执行这条SQL语句
第三步：execute执行后会生成一个进程，需要手动关闭，因此需要deallocate关闭进程，释放内存空间
具体的实现步骤自行百度，这三步能实现数据插入时字段值递增问题，还是不能批量插入数据，换言之，仍适用于插入一条数据的情况，但是插入一条数据，完全没有必要这么复杂，直接录入不就完事了嘛，确实，有最简单的方法为什么非要这么麻烦呢？？还给自己没事找事。
没办法，当初的我就是这么给自己找事的，觉得涉及到了新知识，就折腾了下，后来发现这瞎折腾嘛，完全没必要啊。（还挺能折腾）
那么，也是基于之前的折腾，多折腾了下，肯定会有批量插入数据的需求，而且插入数据中某个字段的值从该字段最大值+1开始递增，那么有没有一条SQL语句就能实现的，答案是肯定的，以MySQL为例，亲自实践，分享如下：

1.表及数据准备
假设存在这样一张表及表（tb_table）结构

数据项名称    类型    描述
id    int    主键，递增
stu_num    varchar    学号
name    varchar    姓名
course_id    varchar    课程id
course_name    varchar    课程名称
表中数据如下：

id    stu_num    name    course_id    course_name
1    001    name1    1001    语文
2    002    name2    1002    数学
3    003    name3    1001    语文
要求：插入数据时stu_num字段从最大值+1开始，要求插入100条数据，如第四条数据应该是：4 004 name4 1001 语文

2.步骤及生成SQL语句
思路：查询stu_num字段的最大值，需要使用max函数，然后使用插入数据即可

查询stu_num最大值：select max(stu_num) from tb_table

最大值加1：select max(stu_num)+1 from tb_table

完犊子了，结果不是004吗？？？前面的0又不见了，因为+1后是int类型，所以就是4，那有办法变成004吗，我想应该有啊，百度后发现需要使用lpad函数
lpad函数语法：LPAD(str,len,padstr)
解释：用字符串padstr对str进行左边填补直至它的长度达到len个字符长度，然后返回str，如果str的长度长于len，它将被截取len个字符。
应用lpad函数后，效果很理想，SQL语句如下：
select lpad((select max(stu_num)+1 from tb_table),3,‘0’)

插入一条数据：
insert into tb_table(stu_num,name,course_id,course_name) value(lpad((select max(stu_num)+1 from tb_table),3,‘0’),‘name4’,‘1001’,‘语文’)
这不很简单嘛，so easy嘛，运行结果如下：

结果就报错，逻辑没毛病啊，一顿网上百度，找到了原因。
报错信息：1093 - You can't specify target table 'tb_table' for update in FROM clause
解释：更新表时不能从from字句中指定目标表tb_table，意思是同一语句中不能对同一张表插入数据的时候又查询这个表
原因分析：上述SQL语句表示在插入数据时嵌套了一个查询，即查询字段的最大值后再插入，那么，能不能换个思路，先查询字段最大值然后插入数据时直接使用就可以，这里有点绕口，可以细细体会下，因此SQL语句如下：
insert into tb_table(stu_num,name,course_id,course_name) select lpad((select max(stu_num)+1 from tb_table),3,‘0’),‘name4’,‘1001’,‘语文’ from tb_table
结果如下：

这里解释下id为什么是1007 1008 1009，因为id是自增的，插入的数据没有指定id字段的值，因此是从数据库记录id值的最大值+1开始。
但是这里有个问题，明明是插入一条数据，为什么插入了三条数据呢？？？
原因分析：查询后结果是三条数据，只不过stu_num字段的值都是004，如下图

那么，有什么办法限制查询结构是一条呢，而且是stu_num为最大值的那一条呢？？
加个where就好了，让stu_num等于最大值即可，这里子查询需要用()括起来
where stu_num = (select max(stu_num) from tb_table)
因此，最终的SQL语句为：
insert into tb_table(stu_num,name,course_id,course_name) select lpad((select max(stu_num)+1 from tb_table),3,‘0’),‘name4’,‘1001’,‘语文’ from tb_table where stu_num = (select max(stu_num) from tb_table);

3.验证SQL并批量插入数据
为了验证能批量导入数据成功，决定本地试一下，在notepad++中编写100条数据，文件名为new.sql，如下结构

cmd进入Windows命令行窗口，执行如下语句（s数据库名为test），回车并输入密码：
mysql -uroot -p -D test < new.sql

4.SQL优化
上述SQL语句可以进一步优化，提高插入的效率。
知识点：需要使用数据库中的dual表和触发器

1.dual：dual表是数据库中实际存在的一个表，任何用户均可读取，常用在没有目标表的select中，像这样select 1，从dual表选择数据被用来通过select语句计算常数表达式。
select 1 from tb_table
select * from tb_table;
上述两条语句从作用但是上来看没有区别，都是查看是否有记录，但是select 1查到的所有行的值都是1，只有一列，值都是1，select *查到的是所有字段的实际的值，查询效率来说1>*，因为*需要查询字典表。
2. 触发器trigger：MySQL从5.0版本开始支持触发器功能了，简单理解触发器就是在满足条件时自动触发定义的语句集合，意义就是确保数据的完整性，这里引用前人的一个形象案例：

比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

而在这里正好可以用到触发器，即在插入数据前就查询到字段最大值+1，然后插入数据时直接使用就可以。
打开Navicat 15，打开表设计，切换到触发器面板，添加触发器max_stu_num，触发类型为BEFORE，触发操作是插入，并在下方定义窗口中编写语句：

begin
    set @max_stu_num=(select max(stu_num)+1 from tb_table);
    set NEW.stu_num = lpad(@max_stu_num,3,'0');
end
1
2
3
4

5.更改SQL语句：
5.1不使用触发器优化
insert into tb_table(stu_num,name,course_id,course_name) select lpad((select max(stu_num)+1 from tb_table),3,‘0’),‘name4’,‘1001’,‘语文’;
或者
insert into tb_table(stu_num,name,course_id,course_name) select lpad((select max(stu_num)+1 from tb_table),3,‘0’),‘name4’,‘1001’,‘语文’ from dual;

5.2 使用触发器
insert into tb_table(name,course_id,course_name) value(‘name4’,‘1001’,‘语文’);

6.总结
写到这里，终于搞定了，生成了一条满足需求的SQL语句，并通过SQL文件验证批量导入，导入让程序去做，能完成批量插入数据，你可以去安心的喝下午茶了，如果帮助到你，记得点赞收藏，并亲自动手试试，向着更好的自己努力呀！
————————————————
版权声明：本文为CSDN博主「旱鸭子不会游泳」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/LoFreedomAND/article/details/119516857