存储过程—增减字段

情景：由于公司业务需要，需要对大批量表进行字段修改，这些表的表名类似：cms_new_0 cms_new_1 cms_new_2...

新建存储过程，批量删除字段 city_id

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='zhaoshayou_fabuz' and table_name like  'cms_new_%';  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
    OPEN taskCursor;  
    REPEAT  
      FETCH taskCursor INTO tableName;  
      IF not done THEN  
			IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='zhaoshayou_fabuz' and TABLE_NAME = tableName AND COLUMN_NAME = 'city_id' )THEN
		 	set @sql2=concat('ALTER TABLE  ',tableName,' drop city_id');
         PREPARE stmt from @sql2;
         execute stmt;		 
 			 END IF;
      END IF;  
    UNTIL done END REPEAT;  
    CLOSE taskCursor;
END

新建存储过程，批量添加字段 province_id

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='zhaoshayou_fabuz' and table_name like  'cms_new_%';  
    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='zhaoshayou_fabuz' and TABLE_NAME = tableName AND COLUMN_NAME = 'province_id' )THEN
		 	set @sql2=concat('ALTER TABLE  ',tableName,' ADD province_id int(11) DEFAULT 0');
         PREPARE stmt from @sql2;
         execute stmt;		 
 			 END IF;
      END IF;  
    UNTIL done END REPEAT;  
    CLOSE taskCursor;
END