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"]
)
12-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
3DROPPROCEDUREIFEXISTS insert_sql_generator;
4 DELIMITER %%
5CREATEPROCEDURE 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-- ################################################################################################################
11DROPTABLEifexists tbl_name;
12DROPTABLEifexists tbl_ref_tbl;
13DROPTABLEifexists tbl_cols;
14
15-- 存储需要导出数据的表
16CREATETABLEifnotexists tbl_name (tbl_name VARCHAR(128),tbl_comment VARCHAR(128));
17CREATETABLEifnotexists 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
28if(in_var_tbl_name_list ISNULLOR 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
29SELECT"表名列表不为空"AS ERROR;
30 leave label;
31ELSE
32-- 插入静态字段
33SET@exec_sql= CONCAT_WS("","INSERT ignore INTO tbl_name(tbl_name) values ("",REPLACE(in_var_tbl_name_list, ",",CONCAT_WS("",""),("")),"")");
34PREPARE stmt FROM@exec_sql;
35EXECUTE stmt;
36DEALLOCATEPREPARE stmt;
37ENDif;
38
39
40
41if(in_exclude_col_list ISNULLOR in_exclude_col_list="exclude_col_list"OR in_exclude_col_list=""OR in_exclude_col_list=""OR in_exclude_col_list="") then
42set in_exclude_col_list=NULL;
43ELSE
44-- 插入静态字段
45SET@exec_sql= CONCAT_WS("","INSERT ignore INTO tbl_cols(col) values ("",REPLACE(in_exclude_col_list, ",",CONCAT_WS("",""),("")),"")");
46PREPARE stmt FROM@exec_sql;
47EXECUTE stmt;
48DEALLOCATEPREPARE stmt;
49ENDif;
50
51-- ####################-----end:配置项-----##################
52/*
53-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
54-- 按照规则批量导入
55INSERT INTO tbl_name (tbl_name,tbl_comment)
56
57SELECT t.table_name , t.table_comment
58FROM information_schema.tables t
59WHERE t.table_schema = DATABASE()
60AND t.table_name LIKE "%%"
61AND t.table_comment LIKE"%%"
62AND t.TABLE_NAME !="tbl_name";
63
64
65SELECT * FROM tbl_name;
66*/
67
68
69-- 1.使用正则 /*.**/ 替换为空，去掉所有注释
70-- 2.使用正则 s*,
替换为, 去掉不必要的换行
71
72
73
74SET group_concat_max_len =4294967295;
75SET@in_db_name=DATABASE();
76
77-- 将表的注释更新进去
78UPDATE tbl_name SET tbl_comment=(SELECT t.table_comment FROM information_schema.tables t WHERE t.table_schema =@in_db_nameAND t.TABLE_NAME= tbl_name.tbl_name);
79
80-- 建立外键相关表
81CREATETABLEifNOTexists tbl_ref_tbl (tbl_name VARCHAR(128),col_nameVARCHAR(128),rf_name VARCHAR(128),rf_col VARCHAR(128));
82INSERTINTO tbl_ref_tbl
83SELECT
84 t.TABLE_NAME AS tbl_name,
85 k.column_name AScol_name,
86 k.REFERENCED_TABLE_NAME AS rf_name,
87 k.REFERENCED_COLUMN_NAME AS rf_col
88FROM
89 information_schema.TABLE_CONSTRAINTS t
90JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
91ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
92AND t.TABLE_NAME = k.TABLE_NAME
93AND t.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA
94WHERE t.CONSTRAINT_TYPE="FOREIGN KEY"AND t.table_schema =@in_db_nameAND t.TABLE_NAME IN (SELECT tbl_name FROM tbl_name)
95;
96
97-- #################################################
98-- 打印insert语句,注释在后
99-- #################################################
100
101SELECT CONCAT_WS("","SET foreign_key_checks=0;
",GROUP_CONCAT(t SEPARATOR ""),"SET foreign_key_checks=1;
") INTO@annotation_suffix
102FROM
103(
104SELECT
105 CONCAT_WS
106 ( ""
107 , CONCAT_WS("","
-- ",tbl_name.tbl_comment,"
")
108-- ------------遍历每个字段，之后合并，SET @var_tbl_cdk_partner_escrow_uuid= REPLACE(UUID(),"-","");
109 ,(SELECT
110 GROUP_CONCAT(
111case
112when t1.COLUMN_NAME="id"thenREPLACE("selectmax(id) +1into@var_xx_idfrom xx;
","xx",t1.TABLE_NAME)
113when t1.COLUMN_NAME="uuid"thenREPLACE("SET@var_xx_uuid=REPLACE(UUID(),"-","");
","xx",t1.TABLE_NAME)
114ELSE""
115END
116 SEPARATOR "")
117FROM
118 information_schema.COLUMNS t1
119WHERE
120 t1.table_schema=@in_db_nameAND
121 t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name NOTIN(SELECT col FROM tbl_cols)
122 )
123-- ------------------------------
124 ,"
"
125 ,CONCAT(" insert into ",tbl_name.tbl_name," set
")
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
136WHEN t1.COLUMN_NAME="uuid"THEN CONCAT("@var_",tbl_name.tbl_name,"_uuid")
137WHEN t1.COLUMN_NAME="id"THEN CONCAT_WS("","@var_",tbl_name.tbl_name,"_id")
138WHEN t1.COLUMN_NAME= (SELECTcol_nameFROM 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)
139ELSE CONCAT_WS("",""",t1.COLUMN_NAME,""")
140END
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*/
152SELECT
153max(
154 length
155 (
156 CONCAT_WS
157 (""
158 ,"`",t1.COLUMN_NAME,"`","="
159 ,CASE
160WHEN t1.COLUMN_NAME="uuid"THEN CONCAT("@var_",tbl_name.tbl_name,"_uuid")
161WHEN t1.COLUMN_NAME="id"THEN CONCAT_WS("","@var_",tbl_name.tbl_name,"_id")
162WHEN t1.COLUMN_NAME= (SELECTcol_nameFROM 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)
163ELSE CONCAT_WS("",""",t1.COLUMN_NAME,""")
164END
165 )
166 )
167 )
168FROM
169 information_schema.COLUMNS t1
170WHERE
171 t1.table_schema=@in_db_nameAND
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
183WHEN t1.COLUMN_NAME="uuid"THEN CONCAT("@var_",tbl_name.tbl_name,"_uuid")
184WHEN t1.COLUMN_NAME="id"THEN CONCAT_WS("","@var_",tbl_name.tbl_name,"_id")
185WHEN t1.COLUMN_NAME= (SELECTcol_nameFROM 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)
186ELSE CONCAT_WS("",""",t1.COLUMN_NAME,""")
187END
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 )
196ORDERBY t1.ORDINAL_POSITION SEPARATOR ",
"
197 )
198FROM
199 information_schema.COLUMNS t1
200WHERE
201 t1.table_schema=@in_db_nameAND
202 t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name NOTIN(SELECT col FROM tbl_cols)
203 )
204 ,"
;
"
205 ) AS t
206
207FROM 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.
215SELECT
216max(
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
228INTO@max_length
229FROM
230 information_schema.COLUMNS t1
231WHERE
232 t1.table_schema=@in_db_nameAND
233 t1.TABLE_NAME =@in_tbl_name;
234
235
236-- #############################################################
237-- 注释在前，insert语句
238-- #############################################################
239
240SELECT CONCAT_WS("","SET foreign_key_checks=0;
",GROUP_CONCAT(t SEPARATOR ""),"SET foreign_key_checks=1;
") INTO@annotation_pre_sql
241FROM
242(
243SELECT
244 CONCAT_WS
245 ( ""
246 , CONCAT_WS("","
-- ",tbl_name.tbl_comment,"
")
247-- ------------遍历每个字段，之后合并，SET @var_tbl_cdk_partner_escrow_uuid= REPLACE(UUID(),"-","");
248 ,(SELECT
249 GROUP_CONCAT(
250case
251when t1.COLUMN_NAME="id"thenREPLACE("selectmax(id) +1into@var_xx_idfrom xx;
","xx",t1.TABLE_NAME)
252when t1.COLUMN_NAME="uuid"thenREPLACE("SET@var_xx_uuid=REPLACE(UUID(),"-","");
","xx",t1.TABLE_NAME)
253ELSE""
254END
255 SEPARATOR "")
256FROM
257 information_schema.COLUMNS t1
258WHERE
259 t1.table_schema=@in_db_nameAND
260 t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name NOTIN(SELECT col FROM tbl_cols)
261 )
262-- ------------------------------
263 ,"
"
264 ,CONCAT(" insert into ",tbl_name.tbl_name," set
")
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
277max(
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
289FROM
290 information_schema.COLUMNS t1
291WHERE
292 t1.table_schema=@in_db_nameAND
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
316WHEN t1.COLUMN_NAME="uuid"THEN CONCAT("@var_",tbl_name.tbl_name,"_uuid")
317WHEN t1.COLUMN_NAME="id"THEN CONCAT_WS("","@var_",tbl_name.tbl_name,"_id")
318WHEN t1.COLUMN_NAME= (SELECTcol_nameFROM 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)
319ELSE CONCAT_WS("",""",t1.COLUMN_NAME,""")
320END
321 )
322-- -----------------------------------------------------
323 )
324ORDERBY t1.ORDINAL_POSITION SEPARATOR ",
"
325 )
326FROM
327 information_schema.COLUMNS t1
328WHERE
329 t1.table_schema=@in_db_nameAND
330 t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name NOTIN(SELECT col FROM tbl_cols)
331 )
332 ,"
;
"
333 ) AS t
334
335FROM tbl_name
336) tt;
337
338
339-- #############################################################
340-- 打印传统insert ###########################################
341-- #############################################################
342-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
343
344
345SELECT
346GROUP_CONCAT(
347 CONCAT_WS("","
-- ",tbl_comment,"
",col00,"insert into ",tbl_name,"
",col1,"
","values
",col2,";
")
348 SEPARATOR "") INTO@tradition_insert_sql
349FROM (
350SELECT
351 t.tbl_name AS tbl_name
352 ,t.tbl_comment AS tbl_comment
353 ,(SELECT
354 GROUP_CONCAT(
355case
356when t1.COLUMN_NAME="id"thenREPLACE("selectmax(id) +1into@var_xx_idfrom xx;
","xx",t.tbl_name)
357when t1.COLUMN_NAME="uuid"then concat_ws("",REPLACE("SET@var_xx_uuid= CONCAT(REPLACE(UUID(),"-",""),"_tt");
","xx",t.tbl_name),REPLACE("SET@var_xx_uuid=REPLACE(UUID(),"-","");
","xx",t.tbl_name))
358ELSE""
359END
360ORDERBY t1.ORDINAL_POSITION SEPARATOR "")
361FROM
362 information_schema.COLUMNS t1
363WHERE
364 t1.table_schema =DATABASE()
365AND t1.TABLE_NAME = t.tbl_name AND t1.column_name NOTIN(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
372ORDERBY t1.ORDINAL_POSITION SEPARATOR "`,`"),"`) ")
373FROM
374 information_schema.COLUMNS t1
375WHERE
376 t1.table_schema =DATABASE()
377AND t1.TABLE_NAME = t.tbl_name AND t1.column_name NOTIN(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
381REPLACE(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"))
387FROM
388 information_schema.COLUMNS t1
389WHERE
390 t1.table_schema =DATABASE()
391AND t1.TABLE_NAME = t.tbl_name AND t1.column_name NOTIN(SELECT col FROM tbl_cols)
392 ) AS col2
393FROM tbl_name t
394) tt;
395
396
397
398
399-- ---------------删除回退脚本---------------------------------------
400/*
401目标示例语句：
402SELECT
403cast(binary(GROUP_CONCAT(tt.t SEPARATOR "
")) AS char CHARACTER SET UTF8) AS "回退脚本"
404FROM (
405select CONCAT_WS("","delete from ","tbl_bts_goods_coupon"," where id="", @var_tbl_bts_goods_coupon_id,"";") as t
406union all
407select CONCAT_ws("","delete from ","tbl_bts_goods_coupon"," where uuid="", @var_tbl_bts_goods_coupon_uuid,"";") as t
408) tt;
409-- 执行上述语句后生成如下语句
410delete from tbl_bts_goods_coupon where id="";
411delete from tbl_bts_goods_coupon where uuid="";
412*/
413-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
414
415
416
417SELECT
418 CONCAT_WS(""
419 ,"SELECTcast(binary( GROUP_CONCAT(tt.t SEPARATOR "\r\n")) AScharCHARACTERSET UTF8) AS"回退脚本"FROM (
"
420 ,GROUP_CONCAT(
421 (SELECT
422 GROUP_CONCAT(
423case
424when 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")
425when 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")
426ELSE""
427END SEPARATOR "
union all
")
428FROM
429 information_schema.COLUMNS t1
430WHERE
431 t1.table_schema=@in_db_nameAND
432 t1.TABLE_NAME =tbl_name.tbl_name AND t1.column_name IN("id","uuid")
433 )
434 SEPARATOR "
union all
"
435 )
436 ,") tt;"
437 ) INTO@rollback_sql
438FROM tbl_name
439;
440
441-- #####################################################
442-- 打印真正的语句
443-- #####################################################
444
445SET@regx_help_code="
446-- 1.使用正则 /\*.*\*/ 替换为空，去掉所有注释
447-- 2.使用正则 \s*,\r\n 替换为, 去掉不必要的换行
448";
449
450
451SELECT"sql代码","功能" LIMIT 0
452UNIONALL
453SELECT@annotation_suffix,"人性化insert-sql,注释在后"
454UNIONall
455SELECT@annotation_pre_sql,"人性化insert-sql,注释在前"
456UNIONALL
457SELECT@tradition_insert_sql ,"传统insert语句"
458UNIONALL
459SELECT@rollback_sql ,"回退脚本"
460UNIONALL
461SELECT@regx_help_code,"正则工具，去除多余注释换行"
462;
463
464-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
465-- 扫尾操作，清空临时变量和表
466DROPTABLEifexists tbl_name;
467DROPTABLEifexists tbl_ref_tbl;
468DROPTABLEifexists tbl_cols;
469
470SET@in_db_name=NULL;
471
472END%%
473 DELIMITER ;
insert语句生成存储过程
如果你还是喜欢传统insert，支持传统insert语句，可以直接复制出传统insert语句，进行修改；
如果数据配置完成后，嫌弃注释太麻烦，影响美观，可以正则工具，去除多余注释和换行
-- 1.使用正则 /*.**/ 替换为空，去掉所有注释
-- 2.使用正则 s*,
替换为, 去掉不必要的换行
最终结果：
本文来自云海天，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15831071.html
以上是 insert语句生成的存储过程 的全部内容， 来源链接： utcz.com/z/536255.html
