存储过程练习

需求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');