Mysql存储过程_一次性建立多张表

最新推荐文章于 2022-04-13 22:47:07 发布

mylomen

最新推荐文章于 2022-04-13 22:47:07 发布

阅读量6.3k

点赞数

文章标签：存储过程

本文链接：https://blog.youkuaiyun.com/fangfengzhen115/article/details/46987107

版权

BEGIN


DECLARE i INT;
DECLARE table_name VARCHAR(20); 
DECLARE table_pre VARCHAR(20); 
DECLARE sql_text VARCHAR(2000); 
SET i=0;
SET table_name='';
SET table_pre='user_info_';
SET sql_text='';



while i<100 do

    #IF i<10 THEN SET table_name=CONCAT(table_pre,i);
    #ELSE SET table_name=CONCAT(table_pre,i);
    #END IF;
SET table_name=CONCAT(table_pre,i);

SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
 id INT(11) NOT NULL COMMENT \'用户id\' AUTO_INCREMENT,
 userName VARCHAR(32)  COMMENT \'用户名\',
 service INT(11) DEFAULT 0 COMMENT \'服务\',
 passportUserName VARCHAR(32)  COMMENT \'y\',
 email  VARCHAR(32) COMMENT \'email\',
 phone  VARCHAR(15) COMMENT \'电话\',
         trueName  VARCHAR(12) COMMENT \'真实姓名\',
         idNumber  VARCHAR(18) COMMENT \'身份证\',
         nickName  VARCHAR(32) COMMENT \'昵称\',
         maxMsgId  INT(18) COMMENT \'消息id\',
         gameIds  VARCHAR(32)DEFAULT null COMMENT \'昵称？\',
         crmVip  INT(11) COMMENT \'消息id\',
         status  INT(11) COMMENT \'状态\',
         updateTime  Date COMMENT \'更新时间\',
bigHead  VARCHAR(200)DEFAULT null COMMENT \'bigHead\',
smallHead  VARCHAR(200)DEFAULT null COMMENT \'smallHead\',
 PRIMARY KEY (id)  
) ENGINE=INNODB DEFAULT CHARSET=utf8' );

SELECT sql_text; 
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;  
SET i=i+1;



end while;

END