对于mysql新增字段,相信大家都不陌生,那么,如果多个分表新增字段怎么处理呢?特别是分表后缀用不同取模等建表名方式,这个时候一个一个去添加,肯定是费时费力的,那有没有办法批量一次性添加呢,答案是肯定的,这里我们用到mysql的函数(或存储过程)实现。
我们先新建几张表,方便测试:
CREATE TABLE `test_1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `b` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `d` mediumtext COLLATE utf8_unicode_ci, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `test_2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `b` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `d` mediumtext COLLATE utf8_unicode_ci, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
接下来,创建一个mysql函数,添加如下代码并保存:
CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`() BEGIN DECLARE oneAddr varchar(8) default ''; DECLARE allAddr varchar(40) default ''; DECLARE done INT DEFAULT 0; DECLARE curl CURSOR FOR select table_name from information_schema.tables where table_schema='test' and table_name like 'test_%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN curl; REPEAT FETCH curl INTO oneAddr; IF not done THEN set @sql=concat('alter table ',oneAddr,' add c varchar(10)'); PREPARE stmt from @sql; execute stmt; set @sql=concat('alter table ',oneAddr,' CHANGE COLUMN `d` `d1` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL '); PREPARE stmt from @sql; execute stmt; set @sql=concat('alter table ',oneAddr,' DROP COLUMN `a` '); PREPARE stmt from @sql; execute stmt; END IF; UNTIL done END REPEAT; select allAddr; CLOSE curl; END
创建好之后,就可以执行该函数了,这里是分别给test_1表、test_2表添加c字段,修改d字段为d1,删除字段a。
完成。
转载请注明:永盟博客 » mysql批量给多个分表新增、删除和修改字段