Mysql 存储过程遍历字段建表_Mysql 存储过程批量建表

CREATE DEFINER=`root`@`%` PROCEDURE `createTables`()

begin

declare i int;

declare suffix varchar(20);

declare createsql varchar(2048);

set i = 0;

while i < 100 do

set suffix = hex(i);

if length(suffix)<=1 then

set suffix = concat('0',hex(i));

end if;

set suffix = lower(suffix);

set createsql = concat('CREATE TABLE table_',suffix,'(

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'自增主键\',

`uuid` varchar(32)  NOT NULL COMMENT \'业务\',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8');

set @createsql = createsql;

prepare stmt from @createsql;

execute stmt;

set i = i + 1;

end while;

end

里面用到了几个mysql自带的函数：

1. hex(number): 十进制转成16进制

2. length(string) : 字符串长度

3. concat(str1,str2) : 字符串拼接

4. lower(str) : 字符串转小写