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、分区存储过程，这里只写了具体的存储过程的开始到结束，sql中的‘’和“”被转义了，所以sql没法直接运行
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’;