mysql存储过程执行拼接sql字符串

DELIMITER //

CREATE

PROCEDURE `test`.`add_test`()

BEGIN

DECLARE t1 int default date_format(DATE_ADD(NOW(),INTERVAL 1 MONTH),"%Y%m");

DECLARE t2 int default unix_timestamp(date_format(DATE_ADD(NOW(),INTERVAL 2 MONTH),"%Y-%m-01"));

declare s1 varchar(32);

DECLARE _sql VARCHAR(255);

set s1 = CONCAT('p',t1);

set _sql = CONCAT("ALTER TABLE test.`lzc_test` ADD PARTITION(PARTITION ",s1," VALUES LESS THAN (",t2,"))");

############以下为重点############

## 普通变量改为全局变量

set @test_sql = _sql;

## 预处理需要执行的动态SQL，其中stmt是一个变量

PREPARE stmt1 FROM @test_sql;

##执行SQL语句

EXECUTE stmt1;

## 释放掉预处理段

DEALLOCATE PREPARE stmt1 ;

############以上为重点############

END //

DELIMITER