Mysql如何建立表分区

（1）、查看表分区

SELECT
      partition_name part, 
      partition_expression expr, 
      partition_description descr, 
      FROM_DAYS(partition_description) lessthan_sendtime, 
      table_rows 
    FROM
      INFORMATION_SCHEMA.partitions 
    WHERE
      TABLE_SCHEMA = SCHEMA() 
      AND TABLE_NAME='rpt_office_lvl'; ---这里是表名

      
 （2）、给已建的表增加分区

 ALTER TABLE test_rpt_office_lvl
    PARTITION BY RANGE COLUMNS(data_date) ( 
    PARTITION trol_201708 VALUES LESS THAN ('2017-09-01'),
    PARTITION trol_201709 VALUES LESS THAN ('2017-10-01'),
    PARTITION trol_201710 VALUES LESS THAN ('2017-11-01')
    );

  （3）、删除分区

alter table test_rpt_office_lvl drop PARTITION trol_201711,trol_201712,trol_201801;

 

  （4）、利用存储过程自动创建表分区

     以下是按月分区

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Procedure structure for `SP_TABLE_PARTITION_AUTO`
-- ----------------------------
DROP PROCEDURE IF EXISTS `SP_TABLE_PARTITION_AUTO`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `SP_TABLE_PARTITION_AUTO`(tableName varchar(30),dataDate varchar(7),tempName varchar(10))
BEGIN
	# 自动给表建立分区,建下个月的分区
	# tableName：表名 dataDate：数据日期   tempName：分区前缀  

  set @curYear = SUBSTR(dataDate,1,4);

	set @curMonth = SUBSTR(dataDate,6,2);

	set @parYear = '';

	set @parMonth = '';

	if @curMonth = '11' then 
			set @parYear = @curYear +1;
			set @parMonth = '01';
	elseif @curMonth = '12' then 
			set @parYear = @curYear +1;
			set @parMonth = '02';
	ELSE
			set @parYear = @curYear;
			set @parMonth = @curMonth +2;
			if @parMonth < 10 THEN
				 set @parMonth = CONCAT('0',@parMonth);
			end if;
	end if;
	
	if @curMonth = '12' then 
			set @curYear = @curYear +1;
			set @curMonth = '01';
	ELSE
			set @curMonth = @curMonth +1;
			if @curMonth < 10 THEN
				 set @curMonth = CONCAT('0',@curMonth);
			end if;
	end if;
	
#select @curYear as 'curYear',@curMonth as 'curMonth',@parYear as 'parYear',@parMonth as 'parMonth';


	set @sqlStr = CONCAT('ALTER TABLE ',tableName,' add PARTITION ( PARTITION ',tempName,'_',@curYear,@curMonth,
			' VALUES LESS THAN (\'',@parYear,'-',@parMonth,'-','01','\'))');

	PREPARE stmt FROM @sqlStr;  
	EXECUTE stmt; 

END
;;
DELIMITER ;