mysql简单存储过程实现，快速创建多张表水平分表

# 添加
DELIMITER $$
CREATE PROCEDURE insert_user (IN i_no CHAR(11),i_id CHAR,i_open CHAR(64))
BEGIN
	INSERT INTO user (id, open, no) VALUES (i_id, i_open, i_no);
END $$
DELIMITER;

# 删除
DELIMITER $$
CREATE PROCEDURE delete_user ( IN i_id CHAR ) BEGIN
	DELETE FROM user WHERE id = i_id;
END $$
DELIMITER;

# 修改
DELIMITER $$
CREATE PROCEDURE update_user (IN i_no CHAR(11),i_id CHAR,i_open CHAR(64))
BEGIN
	UPDATE user SET id = i_id,no = i_no WHERE open = i_open;
END $$
DELIMITER;

# 查询
DELIMITER $$
CREATE PROCEDURE select_user (IN i_no CHAR(11))
BEGIN
	SELECT * FROM user WHERE no = i_no;
END $$
DELIMITER;

# 存储过程的调用
call insert_user(13497944000,0,'2aBmONorQK5YunfWN1AK'); # 添加
call delete_user(0); # 删除
call update_user(13497944111,0,'2aBmONorQK5YunfWN1AF'); # 修改
call select_user(13497944000); # 查询

#水平分表快速创建多张表
# 删除已存在的存储过程
DROP PROCEDURE IF EXISTS create_table_proc;
#创键存储过程
DELIMITER ;;
CREATE PROCEDURE create_table_proc(IN n INT)
BEGIN
    DECLARE i INT DEFAULT(1);
    WHILE (i <= n) DO
        # 拼接sql
        SET @sqlStr:=CONCAT("CREATE TABLE `student",i,"` (
  `id` int(11) NOT NULL DEFAULT '0',
  `nm` varchar(32) NOT NULL DEFAULT '',
  `sub` varchar(32) NOT NULL DEFAULT '',
  `grade` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        #用PREPARE将该字符串转化为MySQL脚本中的Prepared Statements(简写stmt) 
        PREPARE stmt from @sqlStr;
        #执行stmt即可获得输出结果
        EXECUTE stmt;
        #通过DEALLOCATE释放该Prepared Statements
        DEALLOCATE PREPARE stmt;  
        SET i = i + 1;
    END WHILE;  
END
;;
DELIMITER ;

CALL create_table_proc(3)