mysql分区

1、创建一个用于分区的测试表 tv_dial_dns

DROP TABLE IF EXISTS `tv_dial_dns`;
CREATE TABLE `tv_dial_dns` (
`id`  int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id' ,
`dialTaskId`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '拨测任务id' ,
`version`  varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '版本号' ,
`indexType`  varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '指标类型' ,
`reportTime`  datetime NOT NULL COMMENT '上报时间' ,
`collectTime`  datetime NOT NULL COMMENT '采集时间' ,
`collectType`  varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`dnsAdr`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'url地址或IP' ,
`delay`  varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '平均解析时延（dns）' ,
`errno`  varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '返回的DNS错误码' ,
`businessTag`  varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '业务标签' ,
PRIMARY KEY (`id`,`collectTime`)
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1 ROW_FORMAT=COMPACT
PARTITION BY RANGE COLUMNS(collectTime)
	(
	PARTITION p0 VALUES LESS THAN ('2017-08-25 00:00:00'),
	PARTITION p1 VALUES LESS THAN MAXVALUE
	);

2、分区存储过程，这里只写了具体的存储过程的开始到结束

BEGIN

SELECT
	REPLACE (partition_name, 'p', '') INTO @PMAX
FROM
	INFORMATION_SCHEMA. PARTITIONS
WHERE
	TABLE_SCHEMA = 'gnstsprobe'
AND table_name = 'tv_dial_dns'
ORDER BY
	partition_ordinal_position DESC
LIMIT 1;

SELECT
	REPLACE (
		partition_description,
		"'",
		''
	) INTO @DNAME
FROM
(
	SELECT
		*
	FROM
		INFORMATION_SCHEMA. PARTITIONS
	WHERE
		TABLE_SCHEMA = 'gnstsprobe'
	AND table_name = 'tv_dial_dns'
	ORDER BY
		partition_ordinal_position DESC
	LIMIT 1,2
) g
WHERE
	TABLE_SCHEMA = 'gnstsprobe'
AND table_name = 'tv_dial_dns'
ORDER BY
	partition_ordinal_position DESC
LIMIT 1;

SET @t=CONCAT('alter table gnstsprobe.tv_dial_dns reorganize partition p',@PMAX,' into(partition p',@PMAX,' values less than (''',date(DATE_ADD(@DNAME,INTERVAL 1 DAY)),'''),partition p',@PMAX+1,' values less than MAXVALUE)');

SELECT @t;
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

COMMIT;
END

3、创建事件（每天执行）
e_set_tv_dial_dns

分区情况查询语句

	select 
	  partition_name part,  
	  partition_expression expr,  
	  partition_description descr,  
	  table_rows  
	from information_schema.partitions  where 
	  table_schema = schema()  
	  and table_name='de_broadband_score_detail';