Mysql动态操作表或者列

存储过程动态执行插入语句

• 数据表不确定，按需求编号结尾数字插入0-9张表

DROP PROCEDURE IF EXISTS pro_auto_exesql;
CREATE PROCEDURE pro_auto_exesql(in input VARCHAR(64))
BEGIN
   DECLARE tableId INT UNSIGNED DEFAULT 9;
   DECLARE relationId INT UNSIGNED DEFAULT 1008600;
   DECLARE total INT UNSIGNED DEFAULT 0;
   DECLARE tempTableId INT UNSIGNED DEFAULT 0;
   -- 创建表
   CREATE TABLE IF NOT EXISTS t_relation_0(
     id BIGINT NOT NULL PRIMARY KEY,
         `name` VARCHAR(128) not NULL DEFAULT '',
     `data` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   )ENGINE=INNODB DEFAULT CHARSET =utf8 COMMENT '关系表';

   WHILE tableId > 0 DO
     set @crateTableSql = CONCAT('create table if not exists t_relation_',tableId,' like t_relation_0');
     PREPARE strm1 FROM @crateTableSql;
     EXECUTE strm1;
     SET tableId = tableId -1;
   END WHILE;

    -- 插入10万条数据
   WHILE total < 100000 DO
     set tempTableId = RIGHT(relationId,1);
     set @insertTableSql = CONCAT('insert into t_relation_',tempTableId,'(id,`name`) 
                                        values(',relationId,',"名称")');
     PREPARE strm1 FROM @insertTableSql;
     EXECUTE strm1;
     SET total = total + 1;
         SET relationId = relationId +1;
   END WHILE;

END;

CALL pro_auto_exesql(NULL);