今天遇到MySQL的分表问题,可以采用脚本方式循环建立新的表,也可以使用sql建立。下面以建立player_0到player_9的语句为例。
delimiter //
CREATE procedure create_table()
BEGIN
DECLARE `@i` int(11);
DECLARE `@sqlstr` varchar(2560);
SET `@i`=0;
WHILE `@i` < 10 DO
SET @sqlstr = CONCAT(
"CREATE TABLE player_",
`@i`,
"(
`player_id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL COMMENT '用户ID',
`personaname` varchar(50) NOT NULL COMMENT '昵称',
PRIMARY KEY (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表' AUTO_INCREMENT=1 "
);
prepare stmt from @sqlstr;
execute stmt;
SET `@i` = `@i` + 1;
END WHILE;
END;
call create_table();
drop procedure create_table;
--转自