insert语句生成的存储过程

问题:

   1.如何配置数据库数据:

  方式一:图形界面点击输入数据,导出成sql。

  缺点:表多,数据多的时候非常繁琐,字段含义需要另外开窗口对照。

       方式二:徒手写或者修改已有语句:insert table_name ('','','','') values ('','','','') 。

  缺点:字段多的时候容易错位配错字段,而且极其不人性化,字段含义需要另外开窗口对照。

解决方式:

  针对myql写存储过程,生成人性化insert语句生成语句;

 

insert_sql生成器用法:
  

insert_sql_generator
(

tbl_name_list      [要生成insert-sql的表名列表:例如:"tbl_name1,tbl_name2,tbl_name3"]
,exclude_col_list [不需要打印的字段列表: 例如:"name,code,id"]

)

  1  
  2 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
  3 DROP PROCEDURE IF EXISTS insert_sql_generator;
  4 DELIMITER %%
  5 CREATE PROCEDURE insert_sql_generator(in_var_tbl_name_list VARCHAR(2048),in_exclude_col_list VARCHAR(2048)) 
  6 label:BEGIN
  7 -- ################################################################################################################
  8 -- ####################                人性化 insert语句               ###################################################
  9 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
 10 -- ################################################################################################################
 11 DROP TABLE if exists tbl_name;
 12 DROP TABLE if exists tbl_ref_tbl;
 13 DROP TABLE if exists tbl_cols;
 14 
 15 -- 存储需要导出数据的表
 16 CREATE TABLE if not exists tbl_name (tbl_name VARCHAR(128),tbl_comment VARCHAR(128));
 17 CREATE TABLE if not exists tbl_cols (col VARCHAR(128));
 18 -- ####################-----begin:配置项-----##################
 19 
 20 
 21 
 22 -- 需要导出配置语句的表 
 23 /* INSERT INTO tbl_name (tbl_name) VALUES
 24 ('tbl_cbm_face_ip'),
 25 ('tbl_cbm_face_info');
 26 */
 27 
 28 if(in_var_tbl_name_list IS NULL  OR in_var_tbl_name_list='tbl_name_list' OR in_var_tbl_name_list='' OR in_var_tbl_name_list=' ' OR in_var_tbl_name_list='  ') then 
 29     SELECT '表名列表不为空' AS ERROR; 
 30     leave label;
 31 ELSE
 32     -- 插入静态字段
 33     SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO tbl_name(tbl_name) values ('",REPLACE(in_var_tbl_name_list, ',',CONCAT_WS('',"'),('")),"')");
 34     PREPARE stmt  FROM @exec_sql;
 35     EXECUTE stmt;
 36     DEALLOCATE PREPARE stmt;
 37 END if;
 38 
 39 
 40 
 41 if(in_exclude_col_list IS NULL  OR in_exclude_col_list='exclude_col_list' OR in_exclude_col_list='' OR in_exclude_col_list=' ' OR in_exclude_col_list='  ') then 
 42   set in_exclude_col_list=NULL;
 43 ELSE
 44     -- 插入静态字段
 45     SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO tbl_cols(col) values ('",REPLACE(in_exclude_col_list, ',',CONCAT_WS('',"'),('")),"')");
 46     PREPARE stmt  FROM @exec_sql;
 47     EXECUTE stmt;
 48     DEALLOCATE PREPARE stmt;
 49 END if;
 50 
 51 -- ####################-----end:配置项-----##################
 52 /*
 53 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
 54 -- 按照规则批量导入
 55 INSERT INTO tbl_name (tbl_name,tbl_comment)
 56  
 57 SELECT t.table_name ,  t.table_comment 
 58 FROM information_schema.tables t
 59 WHERE t.table_schema = DATABASE() 
 60 AND t.table_name LIKE '%%'
 61 AND t.table_comment LIKE'%%'
 62 AND t.TABLE_NAME !='tbl_name';
 63 
 64 
 65 SELECT * FROM tbl_name;
 66 */
 67 
 68 
 69 -- 1.使用正则   /\*.*\*/   替换为空,去掉所有注释
 70 -- 2.使用正则   \s*,\r\n   替换为,  去掉不必要的换行
 71 
 72 
 73 
 74 SET  group_concat_max_len = 4294967295;
 75 SET @in_db_name=DATABASE();
 76     
 77 -- 将表的注释更新进去
 78 UPDATE tbl_name SET tbl_comment=(SELECT  t.table_comment   FROM information_schema.tables t WHERE t.table_schema = @in_db_name AND t.TABLE_NAME= tbl_name.tbl_name);
 79 
 80 -- 建立外键相关表        
 81 CREATE TABLE if NOT exists tbl_ref_tbl (tbl_name VARCHAR(128),col_name VARCHAR(128),rf_name VARCHAR(128),rf_col VARCHAR(128));
 82 INSERT INTO tbl_ref_tbl
 83      SELECT 
 84           t.TABLE_NAME AS tbl_name,
 85           k.column_name AS col_name,
 86           k.REFERENCED_TABLE_NAME AS rf_name,
 87           k.REFERENCED_COLUMN_NAME AS rf_col
 88       FROM
 89         information_schema.TABLE_CONSTRAINTS t 
 90       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
 91         ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
 92         AND t.TABLE_NAME = k.TABLE_NAME 
 93         AND t.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA
 94       WHERE  t.CONSTRAINT_TYPE='FOREIGN KEY' AND t.table_schema = @in_db_name AND t.TABLE_NAME IN (SELECT tbl_name FROM tbl_name)
 95 ;
 96 
 97 -- #################################################
 98 -- 打印insert语句,注释在后
 99 -- #################################################
100 
101 SELECT CONCAT_WS('','SET foreign_key_checks=0;\r\n',GROUP_CONCAT(t SEPARATOR ''),'SET foreign_key_checks=1;\r\n') INTO @annotation_suffix
102 FROM 
103 (
104     SELECT 
105         CONCAT_WS
106         (  ''
107             , CONCAT_WS('','\r\n--    ',tbl_name.tbl_comment,'\r\n ')
108             -- ------------遍历每个字段,之后合并,SET @var_tbl_cdk_partner_escrow_uuid= REPLACE(UUID(),'-','');
109             ,(SELECT
110                     GROUP_CONCAT(
111                         case 
112                         when t1.COLUMN_NAME='id'   then  REPLACE("select max(id) + 1 into @var_xx_id from xx;\r\n",'xx',t1.TABLE_NAME)
113                         when t1.COLUMN_NAME='uuid' then  REPLACE("SET @var_xx_uuid= REPLACE(UUID(),'-','');\r\n",'xx',t1.TABLE_NAME)
114                         ELSE ''
115                         END  
116                     SEPARATOR '')
117                 FROM
118                     information_schema.COLUMNS t1
119                 WHERE
120                   t1.table_schema=@in_db_name AND 
121                   t1.TABLE_NAME =tbl_name.tbl_name  AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
122             ) 
123             -- ------------------------------
124             ,'\r\n'
125             ,CONCAT(' insert into ',tbl_name.tbl_name,' set \r\n') 
126             ,(SELECT 
127                 GROUP_CONCAT(
128                     CONCAT_WS(
129                                 '',
130                                -- -----------------------------------------------------
131                                 /*第一列:`id`=id  `create_time`='create_time' */
132                                  CONCAT_WS
133                                 (''
134                                  ,'`',t1.COLUMN_NAME,'`',"="
135                                 ,CASE  
136                                      WHEN t1.COLUMN_NAME='uuid' THEN CONCAT('@var_',tbl_name.tbl_name,'_uuid') 
137                                      WHEN t1.COLUMN_NAME='id'   THEN CONCAT_WS('','@var_',tbl_name.tbl_name,'_id') 
138                                     WHEN t1.COLUMN_NAME= (SELECT col_name FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name ) then (SELECT CONCAT_WS('','@var_',rf_name,'_',rf_col) FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name)
139                                      ELSE CONCAT_WS('',"'",t1.COLUMN_NAME,"'")  
140                                   END
141                                 ) 
142                         /* 第二列:对齐用的空格*/
143                         ,repeat
144                         (' ',
145                             (  
146                                 (/*
147                                     begin :查找最长的一条记录长度
148                                     `id`=id 
149                                     `uuid`=@var_tbl_cdkmall_goods_uuid
150                                     `create_time`='create_time'
151                                     */
152                                   SELECT 
153                                         max(
154                                          length
155                                           (
156                                              CONCAT_WS
157                                                  (''
158                                                  ,'`',t1.COLUMN_NAME,'`',"="
159                                                  ,CASE  
160                                                          WHEN t1.COLUMN_NAME='uuid' THEN CONCAT('@var_',tbl_name.tbl_name,'_uuid') 
161                                                         WHEN t1.COLUMN_NAME='id' THEN CONCAT_WS('','@var_',tbl_name.tbl_name,'_id') 
162                                                         WHEN t1.COLUMN_NAME= (SELECT col_name FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name ) then (SELECT CONCAT_WS('','@var_',rf_name,'_',rf_col) FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name) 
163                                                         ELSE CONCAT_WS('',"'",t1.COLUMN_NAME,"'")  
164                                                    END
165                                                 )
166                                            )
167                                        ) 
168                                        FROM
169                                            information_schema.COLUMNS t1
170                                        WHERE
171                                             t1.table_schema=@in_db_name AND 
172                                          t1.TABLE_NAME = tbl_name.tbl_name
173                                 )/*-----end : 查找最长的一条记录长度 结束-----*/
174                             
175                              - 
176                              
177                                     LENGTH
178                                     (
179                                             CONCAT_WS
180                                             (''
181                                              ,'`',t1.COLUMN_NAME,'`',"="
182                                             ,CASE 
183                                                  WHEN t1.COLUMN_NAME='uuid' THEN CONCAT('@var_',tbl_name.tbl_name,'_uuid') 
184                                                  WHEN t1.COLUMN_NAME='id' THEN CONCAT_WS('','@var_',tbl_name.tbl_name,'_id') 
185                                                  WHEN t1.COLUMN_NAME= (SELECT col_name FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name ) then (SELECT CONCAT_WS('','@var_',rf_name,'_',rf_col) FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name) 
186                                                  ELSE CONCAT_WS('',"'",t1.COLUMN_NAME,"'")  
187                                                END
188                                             ) 
189                                     )
190                             )
191                     )
192                     /*第三列:注释*/
193                     ,CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
194                      -- -----------------------------------------------------
195                 )
196              ORDER BY t1.ORDINAL_POSITION  SEPARATOR ',\r\n'
197              ) 
198             FROM
199                information_schema.COLUMNS t1
200             WHERE
201                t1.table_schema=@in_db_name AND 
202                t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
203             )
204             ,'\r\n; \r\n'
205         ) AS t
206     
207     FROM tbl_name
208 ) tt;
209 
210 /*************************************************************************************/
211 /************************打印insert 语句,注释在前*************************************/
212 /*************************************************************************************/
213 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
214 -- 中文显示占两个空格,length中文=3个,char_length中文=1,所以( length+char_length )/2=2  而英文都是1.
215 SELECT 
216    max(
217          length
218          (
219          CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
220          )          
221     + 
222          char_length
223          (
224          CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
225          )
226          
227     )/2 
228      INTO @max_length    
229 FROM
230     information_schema.COLUMNS t1
231 WHERE
232       t1.table_schema=@in_db_name AND 
233       t1.TABLE_NAME = @in_tbl_name;    
234 
235 
236 -- #############################################################      
237 -- 注释在前,insert语句
238 -- #############################################################
239 
240 SELECT CONCAT_WS('','SET foreign_key_checks=0;\r\n',GROUP_CONCAT(t SEPARATOR ''),'SET foreign_key_checks=1;\r\n') INTO @annotation_pre_sql
241 FROM 
242 (
243     SELECT 
244         CONCAT_WS
245         (  ''
246             , CONCAT_WS('','\r\n--    ',tbl_name.tbl_comment,'\r\n ')
247             -- ------------遍历每个字段,之后合并,SET @var_tbl_cdk_partner_escrow_uuid= REPLACE(UUID(),'-','');
248             ,(SELECT
249                     GROUP_CONCAT(
250                         case 
251                         when t1.COLUMN_NAME='id'   then  REPLACE("select max(id) + 1 into @var_xx_id from xx;\r\n",'xx',t1.TABLE_NAME)
252                         when t1.COLUMN_NAME='uuid' then  REPLACE("SET @var_xx_uuid= REPLACE(UUID(),'-','');\r\n",'xx',t1.TABLE_NAME)
253                         ELSE ''
254                         END  
255                     SEPARATOR '')
256                 FROM
257                     information_schema.COLUMNS t1
258                 WHERE
259                   t1.table_schema=@in_db_name AND 
260                   t1.TABLE_NAME =tbl_name.tbl_name  AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
261             ) 
262             -- ------------------------------
263             ,'\r\n'
264             ,CONCAT(' insert into ',tbl_name.tbl_name,' set \r\n') 
265             ,(SELECT 
266                 GROUP_CONCAT(
267                     CONCAT_WS(
268                                 '',
269                                -- -----------------------------------------------------
270                                    /*第一列:注释*/
271                                 CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
272                         /* 第二列:对齐用的空格*/
273                         ,repeat
274                         (' ',
275                                     (
276                                         (SELECT 
277                                                max(
278                                                      length
279                                                      (
280                                                      CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
281                                                      )          
282                                                 + 
283                                                      char_length
284                                                      (
285                                                      CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
286                                                      )
287                                                      
288                                                 )/2 
289                                             FROM
290                                                 information_schema.COLUMNS t1
291                                             WHERE
292                                                   t1.table_schema=@in_db_name AND 
293                                                   t1.TABLE_NAME = tbl_name.tbl_name
294                                         )
295                                          - 
296                                         (    (
297                                              length
298                                              (
299                                                  CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
300                                              )
301                                               +
302                                              char_length
303                                              (
304                                                  CONCAT_WS('','/*',t1.column_comment,' | ',t1.column_type,' | ',if(t1.is_nullable='YES','可空','非空'),if(t1.extra='','',CONCAT_WS('',' | ',t1.extra)),' | ','默认=',ifnull(t1.COLUMN_DEFAULT,'null'),'  */')
305                                              )
306                                             )/2
307                                         )
308                                     )
309 
310                     ),
311                     /*第一列:`id`=id  `create_time`='create_time' */
312                                  CONCAT_WS
313                                 (''
314                                  ,'`',t1.COLUMN_NAME,'`',"="
315                                 ,CASE  
316                                      WHEN t1.COLUMN_NAME='uuid' THEN CONCAT('@var_',tbl_name.tbl_name,'_uuid') 
317                                      WHEN t1.COLUMN_NAME='id'   THEN CONCAT_WS('','@var_',tbl_name.tbl_name,'_id') 
318                                     WHEN t1.COLUMN_NAME= (SELECT col_name FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name ) then (SELECT CONCAT_WS('','@var_',rf_name,'_',rf_col) FROM tbl_ref_tbl WHERE tbl_ref_tbl.tbl_name=tbl_name.tbl_name AND tbl_ref_tbl.col_name=t1.column_name)
319                                      ELSE CONCAT_WS('',"'",t1.COLUMN_NAME,"'")  
320                                   END
321                                 ) 
322                      -- -----------------------------------------------------
323                 )
324              ORDER BY t1.ORDINAL_POSITION  SEPARATOR ',\r\n'
325              ) 
326             FROM
327                information_schema.COLUMNS t1
328             WHERE
329                t1.table_schema=@in_db_name AND 
330                t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
331             )
332             ,'\r\n; \r\n'
333         ) AS t
334     
335     FROM tbl_name
336 ) tt;
337 
338 
339 -- #############################################################
340 -- 打印传统insert ###########################################
341 -- #############################################################
342 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
343 
344 
345 SELECT
346 GROUP_CONCAT(
347 CONCAT_WS('','\r\n-- ',tbl_comment,'\r\n',col00,'insert into ',tbl_name,'\r\n',col1,'\r\n','values\r\n',col2,';\r\n')
348 SEPARATOR '')  INTO @tradition_insert_sql
349 FROM (
350 SELECT 
351     t.tbl_name AS tbl_name
352     ,t.tbl_comment AS tbl_comment
353     ,(SELECT  
354         GROUP_CONCAT(
355             case 
356                 when t1.COLUMN_NAME='id'   then  REPLACE("select max(id) + 1 into @var_xx_id from xx;\r\n",'xx',t.tbl_name)
357                 when t1.COLUMN_NAME='uuid' then  concat_ws('',REPLACE("SET @var_xx_uuid= CONCAT(REPLACE(UUID(),'-',''),'_tt');\r\n",'xx',t.tbl_name),REPLACE("SET @var_xx_uuid= REPLACE(UUID(),'-','');\r\n\r\n",'xx',t.tbl_name))
358             ELSE ''
359             END 
360         ORDER BY t1.ORDINAL_POSITION SEPARATOR '')
361         FROM
362             information_schema.COLUMNS t1
363         WHERE
364             t1.table_schema = DATABASE()
365         AND  t1.TABLE_NAME = t.tbl_name  AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
366     ) AS col00
367     
368 --    (`id`,`name`,`status`,`valid_start_time`,`valid_end_time`,`create_time`,`last_update_time`) 
369     ,(SELECT  
370         CONCAT_WS('',' (`',GROUP_CONCAT(
371            t1.COLUMN_NAME
372         ORDER BY t1.ORDINAL_POSITION SEPARATOR '`,`'),'`) ')
373     FROM
374         information_schema.COLUMNS t1
375     WHERE
376          t1.table_schema = DATABASE()
377     AND  t1.TABLE_NAME = t.tbl_name  AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
378     ) AS col1
379 --   ('id','name','status','valid_start_time','valid_end_time','create_time','last_update_time') 
380     , (SELECT  
381         REPLACE(CONCAT_WS(''," ('",GROUP_CONCAT(
382     -- 开始字段过滤
383          t1.COLUMN_NAME
384         
385      -- 字段过滤结束
386      ORDER BY t1.ORDINAL_POSITION SEPARATOR "','"),"')"),"'uuid'",CONCAT_WS('','@var_',@in_tbl_name,'_uuid'))
387     FROM
388         information_schema.COLUMNS t1
389     WHERE
390          t1.table_schema = DATABASE()
391     AND  t1.TABLE_NAME = t.tbl_name AND t1.column_name NOT IN(SELECT col FROM tbl_cols)
392     ) AS col2
393 FROM tbl_name t
394 ) tt;
395 
396 
397 
398 
399 --  ---------------删除回退脚本---------------------------------------
400 /*
401 目标示例语句:
402 SELECT
403 cast(binary(GROUP_CONCAT(tt.t SEPARATOR '\r\n')) AS char CHARACTER SET UTF8) AS '回退脚本' 
404 FROM (
405 select CONCAT_WS('','delete from ','tbl_bts_goods_coupon',' where id=\'', @var_tbl_bts_goods_coupon_id,'\';') as t
406 union all 
407 select CONCAT_ws('','delete from ','tbl_bts_goods_coupon',' where uuid=\'', @var_tbl_bts_goods_coupon_uuid,'\';') as t
408 ) tt;
409 -- 执行上述语句后生成如下语句
410 delete from tbl_bts_goods_coupon where id='';
411 delete from tbl_bts_goods_coupon where uuid='';
412 */
413 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
414 
415 
416 
417 SELECT 
418 CONCAT_WS(''
419     ,"SELECT cast(binary( GROUP_CONCAT(tt.t SEPARATOR '\\r\\n')) AS char CHARACTER SET UTF8) AS '回退脚本' FROM (\r\n"
420     ,GROUP_CONCAT(
421         (SELECT
422             GROUP_CONCAT(
423             case 
424             when t1.COLUMN_NAME='id'   then  CONCAT_WS('',"select concat_ws('','delete from ','",tbl_name.tbl_name,"',' where id=\\'', @var_",tbl_name.tbl_name,"_id,'\\';') as t")
425             when t1.COLUMN_NAME='uuid' then  CONCAT_WS('',"select concat_ws('','delete from ','",tbl_name.tbl_name,"',' where uuid=\\'', @var_",tbl_name.tbl_name,"_uuid,'\\';') as t")
426             ELSE ''
427             END  SEPARATOR '\r\nunion all\r\n')
428         FROM
429             information_schema.COLUMNS t1
430         WHERE
431               t1.table_schema=@in_db_name AND 
432               t1.TABLE_NAME =tbl_name.tbl_name  AND t1.column_name IN('id','uuid')
433         )  
434         SEPARATOR '\r\nunion all\r\n'
435     )
436     ,") tt;"
437 )  INTO @rollback_sql
438 FROM tbl_name
439 ;
440 
441 -- #####################################################
442 -- 打印真正的语句
443 -- #####################################################
444 
445 SET @regx_help_code='
446 -- 1.使用正则   /\\*.*\\*/   替换为空,去掉所有注释
447 -- 2.使用正则   \\s*,\\r\\n   替换为,  去掉不必要的换行
448 ';
449 
450 
451 SELECT 'sql代码','功能' LIMIT 0
452 UNION ALL 
453 SELECT @annotation_suffix,'人性化insert-sql,注释在后'
454 UNION all
455 SELECT @annotation_pre_sql,'人性化insert-sql,注释在前'
456 UNION ALL
457 SELECT @tradition_insert_sql ,'传统insert语句'
458 UNION ALL 
459 SELECT @rollback_sql ,'回退脚本'
460 UNION ALL 
461 SELECT  @regx_help_code,'正则工具,去除多余注释换行'
462 ;
463  
464  -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
465  --  扫尾操作,清空临时变量和表
466 DROP TABLE if exists tbl_name;
467 DROP TABLE if exists tbl_ref_tbl;
468 DROP TABLE if exists tbl_cols;
469 
470 SET  @in_db_name=NULL;
471 
472 END %%
473 DELIMITER ;  
insert语句生成存储过程

 

 

 

 

 

如果你还是喜欢传统insert,支持传统insert语句,可以直接复制出传统insert语句,进行修改;

 

 

 

 

 

如果数据配置完成后,嫌弃注释太麻烦,影响美观,可以正则工具,去除多余注释和换行

-- 1.使用正则 /\*.*\*/ 替换为空,去掉所有注释
-- 2.使用正则 \s*,\r\n 替换为, 去掉不必要的换行


 

 

 

最终结果:

 

 

 本文来自博客园,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/15831071.html

posted @ 2022-01-24 11:38  王李峰  阅读(383)  评论(0编辑  收藏  举报