我正好有楼主类似的需求，每个季度为几个表增加一个分区，表的基本名称是在一个叫设备类型的表里，每天计划执行一个过程，在过程里从系统表中判断是否已经创建了相关的分区，如果没创建就创建它
楼主可以参考一下，记得在my.ini 文件里配置event_scheduler=on
/**定时每天检查各个设备类型的历史数据表，如果历史数据表的所在分区已经
接近当前日期，则为此设备类型追加分区
*/
-- 得到按月分区的日期值
delimiter
drop function if exists fnGetPartitionDateForMonth
delimiter
create function fnGetPartitionDateForMonth() returns INT
begin
declare v_today datetime default date_add(now(), INTERVAL 2 month)
return year(v_today) * 100 + month(v_today)
end
-- 得到按季度分区的日期值
delimiter
drop function if exists fnGetPartitionDateForQuarter
delimiter
create function fnGetPartitionDateForQuarter() returns int
begin
declare v_today datetime default date_add(now(), interval 3 month)
declare v_month int
set v_month = month(v_today)
if v_month = 1 or v_month = 2 or v_month = 3 then
set v_today = DATE_ADD(v_today, INTERVAL (4 - v_month) month)
elseif v_month = 4 or v_month = 5 or v_month = 6 THEN
set v_today = DATE_ADD(v_today, INTERVAL (7 - v_month) month)
elseif v_month = 7 or v_month = 8 or v_month = 9 THEN
set v_today = date_add(v_today, INTERVAL (10 - v_month) month)
ELSE
set v_today = date_add(v_today, INTERVAL (13 - v_month) month)
end if
return year(v_today) * 100 + month(v_today)
end
-- 得到按半年分区的日期值
delimiter
drop function if exists fnGetPartitionDateForHalfYear
delimiter
create function fnGetPartitionDateForHalfYear() returns int
begin
declare v_today datetime default date_add(now(), interval 6 month)
declare v_month int
set v_month = month(v_today)
if v_month <= 6 THEN
set v_today = date_add(v_today, INTERVAL (7 - v_month) month)
else
set v_today = DATE_ADD(v_today, INTERVAL (13 - v_month) month)
end if
return year(v_today) * 100 + month(v_today)
end
-- 维护按年分区
delimiter
drop function if exists fnGetPartitionDateForYear
delimiter
create function fnGetPartitionDateForYear() returns int
begin
declare v_today datetime default date_add(now(), INTERVAL 2 year)
return year(v_today) * 100
end
delimiter
drop procedure if exists spMaintainPartitions
delimiter
create procedure spMaintainPartitions()
BEGIN
declare v_sql varchar(2000)
declare v_cnt int
declare v_deviceTypeId int
declare v_tablename varchar(50)
declare v_tablename_analog varchar(50)
declare v_tablename_digital varchar(50)
declare v_partitionType int
declare v_fileDir varchar(1000)
declare v_tablenames varchar(1000) default ''
declare v_intDate int
declare v_partitionName varchar(100)
declare done int default 0
declare c_deviceType cursor
for select Id, TableName, PartitionType, DataFileDir
from tbDeviceType
where Generated = 1
declare continue handler for not found set done = 1
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), 'spMaintainPartitions start......')
open c_deviceType
deviceType_loop: LOOP
fetch c_deviceType into v_deviceTypeId, v_tablename, v_partitionType, v_fileDir
set v_fileDir = replace(v_fileDir, '\\', '/')
if locate(':', v_fileDir) > 0 and locate(':/', v_fileDir) = 0 then
set v_fileDir = replace(v_fileDir, ':', ':/')
end if
if done = 1 then
leave deviceType_loop
end if
set v_intDate = null
if v_partitionType = 1 then
set v_intDate = fnGetPartitionDateForMonth()
ELSEIF v_partitionType = 2 THEN
set v_intDate = fnGetPartitionDateForQuarter()
ELSEIF v_partitionType = 3 then
set v_intDate = fnGetPartitionDateForHalfYear()
elseif v_partitionType = 4 then
set v_intDate = fnGetPartitionDateForYear()
end if
if v_intDate is null then
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), Concat('DeviceTypeId = ', cast(v_deviceTypeId As char(10)), ' did not define paritition schedule'))
else
set v_partitionName = concat('p', cast(v_intDate as char(6)))
-- 模拟量表
set v_tablename_analog = concat(v_tablename, '_Analog')
select count(*) into v_cnt
from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_analog
if v_cnt > 0 then
select count(*) into v_cnt
from
information_schema.`PARTITIONS`
where
TABLE_SCHEMA = database() and table_name = v_tablename_analog and partition_name = v_partitionName
if v_cnt = 0 then
set v_sql = CONCAT('alter table ', v_tablename_analog, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')')
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), concat('sql = ''', v_sql))
set @sql = v_sql
prepare cmd from @sql
execute cmd
deallocate prepare cmd
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), concat('execute complete: ', v_sql))
end if
end if
-- 数字量表
set v_tablename_digital = concat(v_tablename, '_Digital')
select count(*) into v_cnt
from information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_digital
if v_cnt > 0 then
select count(*) into v_cnt
from
information_schema.`PARTITIONS`
where
TABLE_SCHEMA = database() and table_name = v_tablename_digital and partition_name = v_partitionName
if v_cnt = 0 then
set v_sql = CONCAT('alter table ', v_tablename_digital, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')')
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
Values(Now(), concat('sql = ''', v_sql))
set @sql = v_sql
prepare cmd from @sql
execute cmd
deallocate prepare cmd
insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)
values(Now(), concat('execute complete: ', v_sql))
end if
end if
end if
end loop deviceType_loop
close c_deviceType
END
delimiter
drop event if exists e_DataPartitionMaintain
create event e_DataPartitionMaintain
on SCHEDULE every 60 Second
on completion PRESERVE
do call spMaintainPartitions()
set global event_scheduler = on
可以看mysql的data文件夹下面的数据库文件，就可以查看当前分区情况。还有几种获取MySQL分区表信息的常用方法SHOW CREATE TABLE 可以查看创建分区表的CREATE语句 SHOW TABLE STATUS 可以查看表是否为分区表 查看INFORMATION_SCHEMA.PARTITIONS表 可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等重要信息欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）