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
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
67
68
69 -- 1.使用正则 替换为空，去掉所有注释
70 -- 2.使用正则 s*,
替换为, 去掉不必要的换行
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;
",GROUP_CONCAT(t SEPARATOR ""),"SET foreign_key_checks=1;
") INTO @annotation_suffix
102 FROM
103 (
104 SELECT
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(
111 case
112 when t1.COLUMN_NAME="id" then REPLACE("select max(id) + 1 into @var_xx_id from xx;
","xx",t1.TABLE_NAME)
113 when t1.COLUMN_NAME="uuid" then REPLACE("SET @var_xx_uuid= REPLACE(UUID(),"-","");
","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 ,"
"
125 ,CONCAT(" insert into ",tbl_name.tbl_name," set
")
126 ,(SELECT
127 GROUP_CONCAT(
128 CONCAT_WS(
129 "",
130 -- -----------------------------------------------------
131
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 (
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 )
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("","")
194 -- -----------------------------------------------------
195 )
196 ORDER BY t1.ORDINAL_POSITION SEPARATOR ",
"
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 ,"
;
"
205 ) AS t
206
207 FROM tbl_name
208 ) tt;
209
210
211
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("","")
220 )
221 +
222 char_length
223 (
224 CONCAT_WS("","")
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;
",GROUP_CONCAT(t SEPARATOR ""),"SET foreign_key_checks=1;
") INTO @annotation_pre_sql
241 FROM
242 (
243 SELECT
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(
250 case
251 when t1.COLUMN_NAME="id" then REPLACE("select max(id) + 1 into @var_xx_id from xx;
","xx",t1.TABLE_NAME)
252 when t1.COLUMN_NAME="uuid" then REPLACE("SET @var_xx_uuid= REPLACE(UUID(),"-","");
","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 ,"
"
264 ,CONCAT(" insert into ",tbl_name.tbl_name," set
")
265 ,(SELECT
266 GROUP_CONCAT(
267 CONCAT_WS(
268 "",
269 -- -----------------------------------------------------
270
271 CONCAT_WS("","")
272
273 ,repeat
274 (" ",
275 (
276 (SELECT
277 max(
278 length
279 (
280 CONCAT_WS("","")
281 )
282 +
283 char_length
284 (
285 CONCAT_WS("","")
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("","")
300 )
301 +
302 char_length
303 (
304 CONCAT_WS("","")
305 )
306 )/2
307 )
308 )
309
310 ),
311
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 ",
"
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 ,"
;
"
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("","
-- ",tbl_comment,"
",col00,"insert into ",tbl_name,"
",col1,"
","values
",col2,";
")
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;
","xx",t.tbl_name)
357 when 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))
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
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 (
"
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 "
union all
")
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 "
union all
"
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语句，进行修改；
如果数据配置完成后，嫌弃注释太麻烦，影响美观，可以正则工具，去除多余注释和换行
-- 1.使用正则 替换为空，去掉所有注释
-- 2.使用正则 s*,
替换为, 去掉不必要的换行
最终结果：
本文来自云海天，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15831071.html
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/46.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
