MySQL 存储过程创建表 - 每月自动生成新表

场景

由于存储数据量太大，当一个表超过 1000w 的数据时 MySQL 性能会逐渐下降，对数据操作时非常耗时，为了优化数据库性能，使用了分表的方式每个月对数据实现水平分割，也就是说每月自动生成表一张表。

创建有参存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `auto_create_month_table`(
	IN `database_name` VARCHAR(50),
	IN `table_name` VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '每月自动生成新表'
BEGIN
	-- 声明变量
	DECLARE old_table_name VARCHAR(128);
	DECLARE new_table_name VARCHAR(128);
	DECLARE done INT DEFAULT 0;
	
	-- 声明游标
	DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = database_name AND TABLE_NAME = table_name;
	-- 如果没有数据返回或者 select 出现异常，程序继续，并将变量 done 设为 1
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
	
	-- 打开游标
	OPEN table_cursor;   
	-- 遍历游标
	REPEAT
		--  获取当前游标指针记录，使用 fetch 来取出数据，取出值赋给自定义的变量
		FETCH table_cursor INTO old_table_name;
		IF NOT done THEN 
			-- 真正要操作的需求
			SELECT concat(table_name,'_',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 1 MONTH), '%Y%m')) INTO new_table_name;
			
			SET @sql_cmd = CONCAT('create table if not exists `',new_table_name,'` like `' , old_table_name,'`');
			-- 如果需要变量返回，使用 select，如：select 变量名
			-- SELECT  @sql_cmd;
			
			-- 预定义一个语句，并将它赋给 pre_stmt
			PREPARE pre_stmt FROM @sql_cmd; 
			-- 执行语句，存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value，如果 pre_stmt 不存在，将会引发一个错误
			EXECUTE pre_stmt;
			-- 释放一个预定义语句的资源
			DEALLOCATE PREPARE pre_stmt;
		END IF ;
	-- 根据 done 判断是否结束
	UNTIL done END REPEAT;
	-- 关闭游标
	CLOSE table_cursor;
END

存储过程名称为 auto_create_month_table，参数分别为：database_name，table_name。

调用存储过程

CALL `auto_create_month_table`('test', 'temp_bigint');

注意

执行存储过程时出现一下异常

the user specified as a definer ('root'@'%') does not exist

此问题是权限问题，操作存储过程的用户不存在，授权权限即可。

添加 root 权限

grant all privileges on *.* to root@"%" identified by ".";

刷新权限

flush privileges;

---

声明变量

declare {变量} {数据类型}

声明变量使用 declare 命令，变量必须先声明后使用。变量是有作用域的，作用范围在 begin 与end 中使用。

default 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。

变量赋值

set {变量名=表达式}
-- 或
sel select{变量名=表达式}

declare 和 set 区别

• declare 的变量和参数传入的变量则必须用 concat 来连接
• set var=value 这样定义的变量直接写在字符串中就会被当作变量转换
• declare 时用来声明变量，变量默认赋值使用的 default；改变变量值需要使用 set 变量=值；

游标

游标是一个存储在MySQL服务器上的数据库查询，它不是一条 selec t语句，而是被该语句所检索出来的结果集。

定义游标

DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = database_name AND TABLE_NAME = table_name;

查询 `information_schema`.`TABLES` 表的 table_name 作为游标.

存储过程异常处理

有时候，不希望存储过程抛出错误中止执行，而是希望返回一个错误码。 可以通过定义 continue/exit 异常处理的 handler 来捕获 sqlwarning/not found/sqlexception (警告/无数据/其他异常)。

for 后面可以改为 sqlwarning, not found, sqlexception 来指示所有异常都处理，当不进行异常处理时，以下代码将直接抛出一个 error 1062 (23000) 。

例：

declare continue handler for sqlstate '02000' set done = 1;

定义 continue/exit 异常处理的 handler 来捕获 sqlstate，避免抛出错误，定义一个返回参数 done 赋予特殊值来表示失败，可以通过获取返回值而不是捕获异常的方式来处理业务逻辑。

'02000' 代表发生下述异常之一：

• SELECT INTO 语句或 INSERT 语句的子查询的结果为空表
• 在搜索的 UPDATE 或 DELETE 语句内标识的行数为零
• 在 FETCH 语句中引用的游标位置处于结果表最后一行之后

当 fetch 游标到了数据库表格最后一行的时候，设置 done=1。

返回值

变量需要返回，可以使用select语音，如：select 变量名

---

存储过程的优缺点

优点：

• 将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用
• 存储过程是预编译过的，执行效率高
• 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯，节省开销；
• 可提高数据库的安全性和数据的完整性
• 存储过程可以重复使用，减少数据库开发人员的工作量

缺点：

• 如果使用大量存储过程，会使这些存储过程的每个连接的内存大大增加
• 存储过程的构造不是为开发复杂和灵活的业务逻辑而设计的
• 调试存储过程很困难
• 开发和维护存储过程并不容易
• 可移植性差