将这些表新增一个名为“DID”的字段
通过SHOW TABLES得到所有表名,复制到EXCEL,在EXCEL中利用公式生成如下,
alter table DPC_table1 add DID varchar(20);
alter table DPC_table2 add DID varchar(20);
alter table DPC_table3 add DID varchar(20);
alter table DPC_table4 add DID varchar(20);
alter table DPC_table5 add DID varchar(20);
alter table DPC_table6 add DID varchar(20);
alter table DPC_table7 add DID varchar(20);
alter table DPC_table8 add DID varchar(20);
alter table DPC_table9 add DID varchar(20);
alter table DPC_table10 add DID varchar(20);
alter table DPC_table11 add DID varchar(20);
alter table DPC_table12 add DID varchar(20);
alter table DPC_table13 add DID varchar(20);
alter table DPC_table14 add DID varchar(20);
alter table DPC_table15 add DID varchar(20);
alter table DPC_table16 add DID varchar(20);
alter table DPC_table17 add DID varchar(20);
alter table DPC_table18 add DID varchar(20);
alter table DPC_table19 add DID varchar(20);
alter table DPC_table20 add DID varchar(20);
alter table DPC_table21 add DID varchar(20);
alter table DPC_table22 add DID varchar(20);
alter table DPC_table23 add DID varchar(20);
alter table DPC_table24 add DID varchar(20);
alter table DPC_table25 add DID varchar(20);
alter table DPC_table26 add DID varchar(20);
alter table DPC_table27 add DID varchar(20);
alter table DPC_table28 add DID varchar(20);
假设MYSQL 5以上,示例
delimiter $$
creaTE PROCEDURE test()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_a INT;
DECLARE cur1 CURSOR FOR SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` a WHERE a.`TABLE_NAME` LIKE 'DPC_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
FETCH cur1 INTO v_a;
WHILE done=0 DO
SET @sqlstmt=CONCAT('alter table ',v_a,' add did int ');
PREPARE stmt1 FROM @sqlstmt;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
FETCH cur1 INTO v_a;
END WHILE;
END$$
delimiter ;
mysql> select group_concat('alter table ',table_name,' add a int' SEPARATOR ';') from information_schema.tables where table_name like 'DPC\_%';
+-------------------------------------------------------------------------------------+
| group_concat('alter table ',table_name,' add a int' SEPARATOR ';') |
+-------------------------------------------------------------------------------------+
| alter table DPC_1 add a int;alter table DPC_2 add a int;alter table DPC_3 add a int |
+-------------------------------------------------------------------------------------+