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"]
)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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语句,进行修改;
如果数据配置完成后,嫌弃注释太麻烦,影响美观,可以正则工具,去除多余注释和换行
-- 1.使用正则 /\*.*\*/ 替换为空,去掉所有注释
-- 2.使用正则 \s*,\r\n 替换为, 去掉不必要的换行
最终结果:
本文来自博客园,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/15831071.html