前景:
有一个更新表分区的存储过程,在MariaDB10.1.12下,是能正常运行的。某些业务要求,我同步了这个存储过程到另一台服务器的数据库中,版本为MariaDB10.0.19,这个存储过程执行报错!
存储过程如下:
CREATE PROCEDURE `proc_accesslog_partition_add`(in dbname VARCHAR(20), in tablename VARCHAR(30), in accdate VARCHAR(20))
BEGIN -- dbname 数据库名;tablename:表名;accdate:日期 DECLARE conditionSql VARCHAR(2000); DECLARE alterStr VARCHAR(1000); SET conditionSql = CONCAT("SELECT * FROM information_schema.partitions where table_schema='",dbname,"' and table_name='",tablename,"' AND PARTITION_NAME='p",accdate,"'"); SET alterStr = CONCAT("ALTER TABLE ",tablename," ADD PARTITION (PARTITION p",accdate," VALUES LESS THAN (TO_DAYS('",accdate,"')))");
SET @STR=CONCAT("IF NOT EXISTS(",conditionSql,") THEN ",alterStr,";END IF;"); PREPARE mainStmt FROM @STR; EXECUTE mainStmt;
END
在10.1下,这个过程是能正常执行的。
但是在10.0下,它会抱如下错误:
[SQL]CALL proc_test('ad_warehouse','t_access_log_30','20160323'); [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF NOT EXISTS(SELECT * FROM information_schema.partitions where table_schema='a' at line 1
MySQL prepare语法: PREPARE statement_name FROM preparable_SQL_statement; /*定义*/ EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/ {DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ;
重新修改存储过程:
BEGIN -- dbname 数据库名;tablename:表名;accdate:日期 DECLARE num INT; SET @if_sql = CONCAT("SELECT count(*) INTO @record FROM information_schema.partitions where table_schema='",dbname,"' and table_name='",tablename,"' AND PARTITION_NAME='p",accdate,"'"); SET @alter_sql = CONCAT("ALTER TABLE ",tablename," ADD PARTITION (PARTITION p",accdate," VALUES LESS THAN (TO_DAYS('",accdate,"')))"); PREPARE ifStmt FROM @if_sql; EXECUTE ifStmt; deallocate prepare ifStmt;
SET num=@record; IF num=0 THEN PREPARE alterStmt FROM @alter_sql; EXECUTE alterStmt; deallocate prepare alterStmt; END IF;
END
这个过程能在10.0和10.1下执行成功。
这个结果说明
1、mariaDB10.0 的prepare from execute语法中,preparable_SQL_statement只支持简单的SQL语句,不支持if exists 等复杂语句。
2、动态SQL语法执行,需要PREPARE FROM EXECUTE 来实现。
3、在存储过程需要获取上一个结果作为条件,用INTO,但有一点需要记住,EXECUTE只把@开头的当做参数。