MySQL存储过程注意事项和利用存储过程实现复杂分区

2022-01-12 14:12:05 浏览数 (1)

MySQL 5.0 版本开始支持存储过程。存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程就是数据库 SQL 语言层面的代码封装与重用。

一、使用存储过程注意事项

1、存储过程程序中";"和mysql客户端解释用的“;”冲突。

因为存储过程是SQL 语言层面的代码封装，相当于是sql语义的一段程序代码块，而代码中往往含有“;”作为语句结束的标记，语句之间的分隔符，存储过程程序代码中的“;”会和mysql客户端解释的“;”有语义冲突；mysql本身因此在定义存储过程时，使用DELIMITER $$命令（或者DELIMITER ||命令）将语句的结束符号从分号;临时改为两个$$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

2、存储过程格式

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等，存储过程体格式：以begin开始，以end结束(可嵌套)

3、结束标记

每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

4、为语句块贴标签:

标签可以增强代码的可读性，在某些语句(例如:leave和iterate语句)，需要用到标签。

代码语言：javascript复制
label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

leave跳出循环，如：loop ·····endloop循环，当循环中的语句较多时，为了方便可读性，同时为了明确跳出循环的原因，常常用到标签，配合leave关键字跳出循环。由于loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件,因此使用 leave 语句的意义是离开循环。

代码语言：javascript复制
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v 1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

leave异常中断，如判断条件异常跳出，PART代码块中的程序中用if条件进行了异常判断，当异常触发是，通过leave PART可直接跳出程序：

代码语言：javascript复制
PART:
begin
    declare max_partition_description varchar(255) default '';
    declare p_name varchar(255) default 0;
    declare p_description varchar(255) default 0;
    declare isexist_partition varchar(255) default 0;
 declare i int default 1;
    select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
    if isexist_partition <=> "" then
       select "partition table not is exist" as "ERROR";
       leave PART;
    end if;

iterate迭代执行复合语句，和loop类似，也可以用iterate迭代执行复合语句：ITERATE 通过引用复合语句的标号,来重新开始复合语句:

代码语言：javascript复制
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v 1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v 1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

5、注释

MySQL 存储过程可使用两种风格的注释。一是两个横杆--：该风格一般用于单行注释。二是c 风格： 一般用于多行注释。

二、使用存储过程实现复杂分区

mysql存储过程最常用的是建立复杂分区，尤其是时间分区，无论是按年、月或者日进行分区，当分区较多时，不可能把每个分区一个个枚举罗列出来进行创建，因此，使用存储过程这种代码封装的方式通过封装创建分区函数进行自动创建就显得很有必要。

如下是一个常用的使用存储过程自动创建表分区的函数示例，可通过该函数动态创建年/月/日分区，也可以在此基础上进行扩展创建其他类型的分区(入参为数据库名称、数据表名称、分区数、分区类型（年2、月1、日0）、分区间隔)：

代码语言：javascript复制
DELIMITER $$
drop procedure if exists auto_create_partitions $$
create procedure auto_create_partitions (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
PROCESS:
begin
    declare max_partition_description varchar(255) default '';
    declare p_name varchar(255) default 0;
    declare p_description varchar(255) default 0;
    declare isexist_partition varchar(255) default 0;
 declare i int default 1;
    select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
    if isexist_partition <=> "" then
       select "partition table not is exist" as "ERROR";
       leave PROCESS;
    end if;
    select partition_description into max_partition_description  from information_schema.partitions where table_schema = databasename  and table_name = tablename order by partition_description desc limit 1;
    if max_partition_description <=> "" then
       select "partition table is error" as "ERROR";
       leave PROCESS;
    end if;
    while (i <= partition_number) do
                 if (partitiontype = 0) then
                     set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);
                 elseif (partitiontype = 1) then
                     set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);
                 else
                     set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
                 end if;
                 set p_name = REPLACE(p_description, ' ', '');
          set p_name = REPLACE(p_name, '-', '');
          set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS('', p_description ,'')))');
          select @sql;
          PREPARE stmt from @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
          set i = (i   1);
    end while;
end; $$

call auto_create_partitions('database_name', 'table_name', partition_number(int), 1, 1);

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

云数据库MySQL

0 人点赞