mysql存储过程按月创建表分区 方式一

mysql存储过程创建表分区（一）
关于分区表，表分区 请看：http://www.cnblogs.com/freeton/p/4265228.html
关于定时任务 请看：http://www.cnblogs.com/dreamhome/archive/2012/10/25/2739037.html
关于分区索引 请看 http://google3030.blog.163.com/blog/static/16172446520103591023745/
不啰嗦，直接贴代码了。

--设置mysql自动允许定时任务
set global event_scheduler =1;

--建日志表(依据此表的createtime字段进行分区，注建表是不能含有主键，或者主键必须是createtime，原因解释清空上面的连接‘[关于分区索引](http://google3030.blog.163.com/blog/static/16172446520103591023745/)’)
CREATE TABLE `sys_log_storage` (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `ip` varchar(16) DEFAULT NULL COMMENT '服务器IP',
  `lvl` varchar(16) DEFAULT NULL COMMENT '日志级别',
  `title` varchar(64) DEFAULT NULL COMMENT '标题',
  `content` varchar(1024) DEFAULT NULL,
  `createtime` datetime NOT NULL COMMENT '创建时间',
  `classname` varchar(128) DEFAULT NULL,
  `method` varchar(64) DEFAULT NULL
) ENGINE=InnoDB CHARSET=utf8 COMMENT='日志存储';


--按**月**创建分区的存储过程
--(注：上面创建的sys_log_storage表必须没有数据，或者数据createtime字段值小于当前系统时间的下个月第一天的时间值。因为在创建第一个表分区的时候，会以下个月第一天的时间值做为分区分界线，如果有值大于此时间，则会报错误：Table has no partition for value from column_list。)

create PROCEDURE pro_sys_logByMonth(IN tableName VARCHAR(20),IN timeColName VARCHAR(20) )
COMMENT '每月按时添加表分区的存储过程，由定时任务调用'
BEGIN
    DECLARE p_id int;
    DECLARE p_des date;
    DECLARE lasttime VARCHAR(20);
    DECLARE nexttime varchar(20);
    --获取表中的现有的分区数量数量
    SELECT COUNT(partition_name) into p_id FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
    if p_id=0 then
        --获取下个月第一天的时间值，根据此值设置时间分区
        SELECT DATE_FORMAT(DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH),'%Y-%m-%d') INTO p_des from DUAL;
        --创建第一个表分区的动态sql
        set @v_add=CONCAT('ALTER table ',tableName,' PARTITION by range COLUMNS(',timeColName,')
         (partition ',CONCAT('par',p_id),' values less than (\'',p_des,'\'))');
    ELSE
        --获取表中现有的最大的分区日期
        SELECT max(partition_description) des into lasttime from INFORMATION_SCHEMA.partitions 
        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
        --lasttime的值是'2015-12-01' 因是含引号格式的字符串，直接用str_to_date函数和 date_format函数处理不成功。必须通过动态sql把它转换成没有引号的时间字符串
into @nexttime from dual ');
        PREPARE stm from @v_add_a;
        EXECUTE stm;
        DEALLOCATE PREPARE stm;
        --将编译执行的stm结果存储到nexttime中
        set nexttime=@nexttime; 
        --添加表分区的sql
        set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),' values less than (\'',nexttime,'\'))');
    END IF;
    PREPARE stmt from @v_add;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;
--每月创建一个分区的定时任务
create event event_sysLogTwo on SCHEDULE EVERY 1 MONTH STARTS CURRENT_TIMESTAMP
on COMPLETION PRESERVE
ENABLE
do call pro_sys_log('sys_log_storage','createtime');


--添加联合索引
alter table sys_log_storage add PRIMARY KEY(id,createtime);
alter table sys_log_storage change id id bigint(32) not null auto_increment;
alter table sys_log_storage auto_increment=1;
--添加索引
alter TABLE sys_log_storage add index inx_syslog_ip(ip);

--查看sql执行时，查询了那些分区，及使用的索引
EXPLAIN PARTITIONs select * from sys_log_storage  where ip='192.168.0.5' and  createtime <'2016-01-20';

**当数据量大时，需要调整分区规则，比如按周或者天，代码如下：**

-- 按日期格式自动添加分区存储过程
create PROCEDURE pro_sys_logByWeekDay (IN tableName VARCHAR(20),IN timeColName VARCHAR(20),IN DateFormat VARCHAR(10) )
COMMENT '按日期格式(年YEAR，月month，周week，日day)添加表分区的存储过程，由定时任务调用'
BEGIN
    DECLARE p_id int;
    DECLARE lasttime VARCHAR(20);
    DECLARE nexttime varchar(20);
    -- 获取表中现有的最大的分区日期
    SELECT COUNT(partition_name),max(partition_description) des into p_id,lasttime 
    from INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
    -- lasttime的值是'2015-12-01' 含引号格式的字符串  必须转换成没有引号的字符串
    set @v_add_a=CONCAT('select adddate(str_to_date(',lasttime,',\'%Y-%c-%d\'),Interval 1 ',DateFormat,') into @nexttime from dual ');
    PREPARE stm from @v_add_a;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
    -- 将编译执行的stm结果存储到lasttimeadd中
    set nexttime=@nexttime; 
    set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),' values less than (\'',nexttime,'\'))');
    PREPARE stmt from @v_add;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

-- 创建每周生成一次表分区的定时任务
create EVENT event_sysLogWeek on SCHEDULE EVERY 1 MONTH STARTS CURRENT_TIMESTAMP
on COMPLETION PRESERVE
ENABLE
DO call  pro_sys_logByWeekDay('sys_log_storage','createtime','Week');

-- 创建每天生成一次表分区的定时任务
create EVENT event_sysLogDay on SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
on COMPLETION PRESERVE
ENABLE
DO call  pro_sys_logByWeekDay('sys_log_storage','createtime','day');

一个刚升级为奶爸的码农的个人整理。