秋風落葉、萍水相逢 - Matt 程式 旅行 攝

首先先切換到information_schema;

mysql> use information_schema;

然後用以下:

mysql> select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` Add Column...blah...") as MySQLCMD from TABLES where TABLE_SCHEMA = "test";

Add Column...blah... //這邊改成你需要下的指令

TABLE_SCHEMA //改成你要的資料庫

然後就會出現以下，在使用這些指令就可以一次修改完整個資料庫的每一個table:

+----------------------------------------------------------------+
| MySQLCMD                                                       |
+----------------------------------------------------------------+
| Alter Table `test`.`a_psposts` Add Column...blah...            | 
| Alter Table `test`.`a_ththreads` Add Column...blah...          | 
| Alter Table `test`.`ab` Add Column...blah...                   | 
| Alter Table `test`.`accounts` Add Column...blah...             | 
| Alter Table `test`.`adotest` Add Column...blah...              | 
| Alter Table `test`.`baseball` Add Column...blah...             | 
| Alter Table `test`.`book` Add Column...blah...                 | 
| Alter Table `test`.`books` Add Column...blah...                | 
| Alter Table `test`.`candc` Add Column...blah...                | 
| Alter Table `test`.`cc` Add Column...blah...                   | 
| Alter Table `test`.`chapters` Add Column...blah...             | 
| Alter Table `test`.`colors` Add Column...blah...               | 
| Alter Table `test`.`contacts` Add Column...blah...             | 
| Alter Table `test`.`cult_inno` Add Column...blah...            | 
| Alter Table `test`.`daycount` Add Column...blah...             | 
| Alter Table `test`.`orgs` Add Column...blah...                 | 
| Alter Table `test`.`parametervalue` Add Column...blah...       | 
| Alter Table `test`.`parts` Add Column...blah...                | 
| Alter Table `test`.`products` Add Column...blah...             | 
| Alter Table `test`.`products_sold` Add Column...blah...        | 
| Alter Table `test`.`psionics` Add Column...blah...             | 
| Alter Table `test`.`psiunlimited` Add Column...blah...         | 
| Alter Table `test`.`random_num1` Add Column...blah...          | 
| Alter Table `test`.`random_num2` Add Column...blah...          | 
| Alter Table `test`.`random_numbers` Add Column...blah...       | 
| Alter Table `test`.`rifts` Add Column...blah...                | 
| Alter Table `test`.`services_sold` Add Column...blah...        | 
| Alter Table `test`.`skills` Add Column...blah...               | 
| Alter Table `test`.`spells` Add Column...blah...               | 
| Alter Table `test`.`survey` Add Column...blah...               | 
| Alter Table `test`.`t` Add Column...blah...                    | 
| Alter Table `test`.`t_view` Add Column...blah...               | 
| Alter Table `test`.`table1` Add Column...blah...               | 
| Alter Table `test`.`tablea` Add Column...blah...               | 
| Alter Table `test`.`tableb` Add Column...blah...               | 
| Alter Table `test`.`tbl1` Add Column...blah...                 | 
| Alter Table `test`.`test` Add Column...blah...                 | 
| Alter Table `test`.`test_getstring` Add Column...blah...       | 
| Alter Table `test`.`test_one` Add Column...blah...             | 
| Alter Table `test`.`testtable` Add Column...blah...            | 
| Alter Table `test`.`time_test` Add Column...blah...            | 
| Alter Table `test`.`times` Add Column...blah...                | 
| Alter Table `test`.`urltable` Add Column...blah...             | 
| Alter Table `test`.`user_msg` Add Column...blah...             | 
| Alter Table `test`.`xtest` Add Column...blah...                | 
+----------------------------------------------------------------+