MySQL根據表前綴批量修改、刪除表

• 2022 年 6 月 25 日
• 筆記
• MySQL

注意：請先調試好，以及做好備份，再執行操作。

批量修改表

批量給前綴為 xushanxiang_content_ 的表增加一個 username 的欄位：

SELECT CONCAT('ALTER TABLE ',table_name,' ADD username varchar(40) NULL COMMENT "用戶名";') FROM information_schema.TABLES WHERE table_name LIKE 'xushanxiang\_content\_%';

可以得到下面一些 SQL 語句列表，也就是生成了批量修改表的語句：

之後，我們再批量執行這些 SQL 語句即可。

批量執行語句

方式一、存儲過程 while 循環

# 參考程式碼：
DROP PROCEDURE IF EXISTS canal_test; # 刪除存儲過程

DELIMITER // # 設置分割符1
CREATE PROCEDURE canal_test()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<=10 DO
   update users set mileage=i;
SET i = i+1;
END WHILE;
END
//
DELIMITER; # 設置分割符2

CALL canal_test(); # 調用存儲過程

方式二、source 命令

待執行的 sql 文件為1.sql、2.sql、3.sql、4.sql等， 寫一個batch.sql文件：

source 1.sql;
source 2.sql;
source 3.sql;

在 mysql 下執行 source batch.sql; （注意路徑）

如果只有一個 SQL 文件，例如 mysqltest.sql 腳本在 D 盤根目錄， 進入 MySQL 的命令行窗口，直接輸入如下命令即可批量執行腳本中的 sql 語句【 註：結尾不帶分號執行 】

source D:\mysqltest.sql

方式三、在網站程式里動態執行

// 參考用例，可能和您的實際情況不一樣
$sqlArr = $this->model->all("SELECT CONCAT('ALTER TABLE ',table_name,' ADD username varchar(40) NULL COMMENT \'用戶名\';') FROM information_schema.TABLES WHERE table_name LIKE 'xushanxiang\_content\_%';");
// 得到一個SQL語句的數組;
foreach ($sqlArr as $key => $value) {
    foreach ($value as $k => $v) {
        $this->model->amd($v); // 依次執行sql
    }
}

批量刪除

參考 sql 如下：

SELECT CONCAT('drop table ',table_name,';') FROM information_schema.TABLES WHERE table_name LIKE 'xushanxiang\_content\_%';

接著，參考上面的進行批量執行即可。

題外話，既然說到對數據表進行批量操作，那可能少不了數據表的複製。

數據表的複製

只複製表結構到新表

create table 新表 like 舊錶

複製表結構及數據到新表

create table 新表 select * from 舊錶

複製一條數據到結構相同的表

INSERT INTO content_1 SELECT * FROM content_0 WHERE id=1

複製部分欄位到另一個表

INSERT INTO table2 ( name , price ) SELECT name , price  FROM table1  WHERE id=5

出處：MySQL根據表前綴批量修改、刪除表 (xushanxiang.com)