在数据库中调用存储过程进行分区，包括分区初始化和分区自动递增

CREATE DEFIdata_value_table_partition_initNER=`root`@`localhost` PROCEDURE `table_partition_init`()
BEGIN
      set @number=(SELECT count(*) partition_name FROM information_schema.partitions where table_schema='数据库名' and   table_name = '表名' and partition_name LIKE '%p%'); 

    if (@number=0) then

         SET @today_partition_name = (SELECT concat('p',DATE_FORMAT(DATE_ADD(curdate()-day(curdate())+1,interval 1 month),'%Y%m%d')));
     SET @today_no = (select DATE_ADD(curdate()-day(curdate())+1,interval 1 month) );
     
     SET @test_init_partition_sql=CONCAT('ALTER TABLE 表名 partition by range columns(date_time)( partition ',@today_partition_name,' values less than(\'',@today_no,'\'));');
         PREPARE stmt FROM @test_init_partition_sql;
         EXECUTE stmt;
        end if;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `table_create_partition`()
L_END:
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;
        declare partition_sum int default 1;
        declare scada_db varchar(50) default 'scada_db';
        declare data_value_table varchar(50)  default 表名';
        declare partSQL varchar(255);
        declare sql1 MediumText;
    
     
        
    select partition_name into isexist_partition from information_schema.partitions where table_schema = 数据库名 and table_name = 表名 limit 1;
    
    if isexist_partition <=> "" then
       select "partition table not is exist" as "ERROR";
       leave L_END;
    end if;
 
    
    select partition_description into max_partition_description  from information_schema.partitions where table_schema = 数据库名 and table_name = 表名 order by partition_description desc limit 1;
   
    
    if max_partition_description <=> "" then
       select "partition table is error" as "ERROR";
       leave L_END;
    end if;
    
    
        
        set max_partition_description = REPLACE(max_partition_description, '\'', '');
    
    
      
      
label:BEGIN
 
              set @data_value_table_number=(SELECT  COUNT(*) AS CNT FROM information_schema.PARTITIONS WHERE     TABLE_NAME = '表名'  AND       TABLE_SCHEMA = '数据库'); 
    
        if (@data_value_table_number>=360) then
      
       leave label;
    end if;
        

        
        set partSQL = CONCAT('ALTER TABLE ', 表名 ,' ADD PARTITION ( ');
        set sql1 = '';
    
        while (i < 360) do
             set p_description = DATE_ADD(max_partition_description, interval i*1 month); 
             
             set p_name = REPLACE(p_description, ' ', '');
                        set p_name = REPLACE(p_name, '-', '');
             set p_name = REPLACE(p_name, ':', '');
             
             if i > 1 then
                    set sql1 = CONCAT(sql1,',');
             end if;
             
             set sql1 = CONCAT(sql1, 'PARTITION p', p_name ,' VALUES LESS THAN (\'', p_description ,'\')');  
             
             set i = (i + 1) ;
     end while ;

        set @sql = concat(partSQL, sql1, ');');
        PREPARE stmt from @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

END label;
                         
end