1.数据库结构:表名:income
id income int_date
主键:id,int_date。因为要按int_date分区,所以必须要加入该列主键
存储过程:
DELIMITER $$
USE newdatabase$$
DROP PROCEDURE
IF EXISTS `create_Partition_income`$$
CREATE PROCEDURE `create_Partition_income`()label :
BEGIN
/*分区要提前,要不然数据插入会出错,另外分区字段要设置为主键否则会出错。最好设置每日晚上的23点左右,Vlaue值in 只能用list分区Rang是less than*/
SELECT
REPLACE(
partition_name,
'incomep',
''
)INTO @PName
FROM
INFORMATION_SCHEMA. PARTITIONS
WHERE
table_name = 'income'
ORDER BY
partition_ordinal_position DESC
LIMIT 1 ;
IF ISNULL(@PName)THEN
/*从未做过表分区时,自动做表分区*/
SELECT
MIN(int_date)INTO @minIntDate
FROM
income ;
SET @maxIntDate = CAST(
DATE_FORMAT(now(), '%Y%m%d')AS UNSIGNED INTEGER
);
/* select @minIntDate,@maxIntDate ;*/
SET @partStr='';