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 ;

来源：https://www.cnblogs.com/wanglifeng717/p/15831071.html
图文来源于网络，如有侵权请联系删除。