存储过程动态执行插入语句
- 数据表不确定,按需求编号结尾数字插入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);