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

ゝ一世哀愁。 2022-09-03 14:28 259阅读 0赞

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

> 需要使用到MySQL的`prepare、execute、deallocate`这三个函数，插入一条数据需要三步才能实现，
> 
> 1.  第一步：准备好一条SQL语句供prepare使用
> 2.  第二步：execute执行这条SQL语句
> 3.  第三步：execute执行后会生成一个进程，需要手动关闭，因此需要deallocate关闭进程，释放内存空间

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

# 1.表及数据准备 #

假设存在这样一张表及表（tb\_table）结构

<table> 
 <thead> 
  <tr> 
   <th>数据项名称</th> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
   <td>主键，递增</td> 
  </tr> 
  <tr> 
   <td>stu_num</td> 
   <td>varchar</td> 
   <td>学号</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
   <td>姓名</td> 
  </tr> 
  <tr> 
   <td>course_id</td> 
   <td>varchar</td> 
   <td>课程id</td> 
  </tr> 
  <tr> 
   <td>course_name</td> 
   <td>varchar</td> 
   <td>课程名称</td> 
  </tr> 
 </tbody> 
</table>

表中数据如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>stu_num</th> 
   <th>name</th> 
   <th>course_id</th> 
   <th>course_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>001</td> 
   <td>name1</td> 
   <td>1001</td> 
   <td>语文</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>002</td> 
   <td>name2</td> 
   <td>1002</td> 
   <td>数学</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>003</td> 
   <td>name3</td> 
   <td>1001</td> 
   <td>语文</td> 
  </tr> 
 </tbody> 
</table>

`要求：插入数据时stu_num字段从最大值+1开始，要求插入100条数据，如第四条数据应该是：4 004 name4 1001 语文`

# 2.步骤及生成SQL语句 #

**思路：查询stu\_num字段的最大值，需要使用max函数，然后使用插入数据即可**

1.  查询stu\_num最大值：select max(stu\_num) from tb\_table  
    ![在这里插入图片描述][754f1057d08f41f5ac480c5b63e8f6ae.png_pic_center]
2.  最大值加1：select max(stu\_num)+1 from tb\_table  
    ![在这里插入图片描述][407d238e75114708a80b9cd941270e09.png_pic_center]  
    完犊子了，结果不是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’)  
    ![在这里插入图片描述][3900753ed95345a3a2026f93b69f07f4.png_pic_center]
3.  插入一条数据：  
    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嘛，运行结果如下：  
    ![在这里插入图片描述][c59a63693d034dd980ee3a7fb285ffe0.png_pic_center]  
    结果就报错，逻辑没毛病啊，一顿网上百度，找到了原因。  
    报错信息：`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  
    结果如下：  
    ![在这里插入图片描述][88b58eaa744546c5b5f28a513a36f89d.png_pic_center]  
    ![在这里插入图片描述][f325e8a27a004cbe9c0cf2392d1b9250.png_pic_center]  
    这里解释下id为什么是`1007 1008 1009`，因为id是自增的，插入的数据没有指定id字段的值，因此是从数据库记录id值的最大值+1开始。  
    但是这里有个问题，明明是插入一条数据，为什么插入了三条数据呢？？？  
    原因分析：**查询后结果是三条数据，只不过stu\_num字段的值都是004**，如下图

![在这里插入图片描述][9980107ee41942f1be60e2c7215956cf.png_pic_center]  
那么，有什么办法限制查询结构是一条呢，而且是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);  
![在这里插入图片描述][1d8e08d88203408eba256c45ac3b4b89.png_pic_center]

# 3.验证SQL并批量插入数据 #

为了验证能批量导入数据成功，决定本地试一下，在notepad++中编写100条数据，文件名为new.sql，如下结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvRnJlZWRvbUFORA_size_16_color_FFFFFF_t_70_pic_center]  
cmd进入Windows命令行窗口，执行如下语句（s数据库名为test），回车并输入密码：  
mysql -uroot -p -D test < new.sql  
![在这里插入图片描述][6cb3f9009662462c9a6c84c307d1280b.png_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvRnJlZWRvbUFORA_size_16_color_FFFFFF_t_70_pic_center 1]

# 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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvRnJlZWRvbUFORA_size_16_color_FFFFFF_t_70_pic_center 2]

# 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文件验证批量导入，导入让程序去做，能完成批量插入数据，你可以去安心的喝下午茶了，如果帮助到你，记得点赞收藏，并亲自动手试试，向着更好的自己努力呀！

[754f1057d08f41f5ac480c5b63e8f6ae.png_pic_center]: /images/20220829/33e63749c73749b39faf8f620f1d7e2e.png
[407d238e75114708a80b9cd941270e09.png_pic_center]: /images/20220829/4a0cd0c7eeb544bea50eaa08faf3b0e5.png
[3900753ed95345a3a2026f93b69f07f4.png_pic_center]: /images/20220829/5ffd918f14444d7193a82764991294e7.png
[c59a63693d034dd980ee3a7fb285ffe0.png_pic_center]: /images/20220829/b48028c3f8ce4c3f8b2a280125ae3091.png
[88b58eaa744546c5b5f28a513a36f89d.png_pic_center]: /images/20220829/9de58569516d4a5aac6bbcee5863d71f.png
[f325e8a27a004cbe9c0cf2392d1b9250.png_pic_center]: /images/20220829/9c0ddf7dc22340b086c5e8181ae5edf5.png
[9980107ee41942f1be60e2c7215956cf.png_pic_center]: /images/20220829/0eb9f0266753463ea6d8631ca87b92c1.png
[1d8e08d88203408eba256c45ac3b4b89.png_pic_center]: /images/20220829/4dfe4064a23047f188555c645bbe8cff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvRnJlZWRvbUFORA_size_16_color_FFFFFF_t_70_pic_center]: /images/20220829/8d90284dd178436bae388a3b7523c93b.png
[6cb3f9009662462c9a6c84c307d1280b.png_pic_center]: /images/20220829/4a8efa5e0d8f44cda0e26a1a383fd337.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvRnJlZWRvbUFORA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220829/f846d9a8c01044dea3f4721fb4701dd7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvRnJlZWRvbUFORA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20220829/47fcf410955a46918c116b8bd9d51fde.png