需求1:设计一个数据表,包括1个主键id字段和至少其它4个字段,使用存储过程模拟随机插入五百万条数据,其中部分字段设置索引。
员工表:id、姓名(有索引)、性别、年龄(有索引)、地址;
插入数据存储过程SQL语句:
DELIMITER $$
CREATE PROCEDURE `sofware_development`.`insert_employee` (IN maxINT INT)
BEGIN
DECLARE i INT DEFAULT 0 ;
DECLARE v INT DEFAULT 0 ;
START TRANSACTION ;
WHILE
i < maxINT DO
SELECT
RAND() * 100 INTO v ;
INSERT INTO t_employee (id, NAME, age, address, gender)
VALUES
(
i,
CONCAT('name', i),
v,
CONCAT('address', i),
FLOOR(RAND() * 2)
) ;
SET i = i + 1 ;
END WHILE ;
COMMIT ;
END $$
DELIMITER ;
调用存储过程:
CALL insert_employee(5000000);
需求2:按 t_employee 表中 id 字段进行分表,拆分成 5 个子表;并创建存储过程,将主表的数据插入到 5 个子表中,每个子表中有 1000000 条数据。
DELIMITER $$
CREATE PROCEDURE `sofware_development`.`exe_sql`(IN sqlVar VARCHAR(4096))
BEGIN
SET @v_s = sqlVar;
PREPARE stmt FROM @v_s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
DELIMITER $
CREATE PROCEDURE `divide_table`(IN tableNamePrefix VARCHAR(1024), IN tableNum INT, IN whereField VARCHAR(128))
BEGIN
DECLARE sqlVar VARCHAR(4096);
DECLARE tableName VARCHAR(128);
DECLARE i INT DEFAULT 0;
WHILE i < tableNum DO
SET tableName = CONCAT(tableNamePrefix, '_', i);
SET sqlVar = CONCAT('CREATE TABLE IF NOT EXISTS ', tableName, ' LIKE ', tableNamePrefix);
CALL exe_sql(sqlVar);
SET sqlVar = CONCAT('INSERT INTO ',tableName,' SELECT * FROM ',tableNamePrefix,' where ',whereField,' % ',tableNum,'=',i);
CALL exe_sql(sqlVar);
SET i = i + 1;
END WHILE;
END$
DELIMITER ;
注:tableNamePrefix 是主表表;tableNum 是分表的数量;whereField 是按哪个字段分表;
调用分表存储过程:
CALL divide_table('t_employee', 5, 'id');