問題：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/420436.html
標籤：MySQL
上一篇：匯總資料庫資訊的存盤程序