Mysql 函数进行相似表的增删字段的操作

碰到分表的库，里面好多表都是分出来的，如cms_news_1，cms_news_2，cms_news_3 。。。反正就是好多好多
要同时给这么多表增加一个字段 city_id。手动一个表一个表的增加，估计几个百表下来，必定会有出入的。
找捷径，用Mysql的可编译的函数—存储过程 来写一下。

找出相似的表,取出表名，进行循环。

   DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='库名' and table_name like  'cms_news%' ;  
    OPEN taskCursor;  
    FETCH taskCursor INTO tableName;  

为了防止有的表，已经存在了该字段，这里有个判断。

注：这里如果有加库名，有时会连带查询别的库,返回结果就不对了!

IF NOT EXISTS (
				 SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='库名' and TABLE_NAME = 'tableName' AND COLUMN_NAME = 'city_id' )THEN 	
				 
....

 END IF;

完整代码：

	DELIMITER //
drop procedure if exists customerAddField//
CREATE PROCEDURE customerAddField()  
BEGIN  
    DECLARE tableName varchar(100) default '';   
    DECLARE done INT DEFAULT 0;  
    DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='库名' and table_name like  'cms_news%' ;  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
    OPEN taskCursor;  
    REPEAT  
      FETCH taskCursor INTO tableName;  
      IF not done THEN  

				IF NOT EXISTS (
				 SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='库名' and TABLE_NAME = 'tableName' AND COLUMN_NAME = 'city_id' )THEN 				
				 set @sql2=concat('ALTER TABLE  ',tableName,' ADD  city_id int(8) DEFAULT 100000');
		         PREPARE stmt from @sql2;
		         execute stmt;		 
				 END IF;

      END IF;  
    UNTIL done END REPEAT;  
    CLOSE taskCursor;
END
//
DELIMITER;
CALL customerAddField();