MySQL数据库的分区表、存储过程、event定时任务的创建

本文介绍了如何在MySQL中创建分区表并仅保留最近三天的数据。步骤包括确认表状态、将非分区表转为分区表、创建存储过程处理分区和删除操作、建立Event定时任务以及最后的验证。示例中,通过存储过程daypartition和定时任务day_partition确保testdb库的par_tab01表始终保持最近三天的数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >



MySQL数据库中有一个分区表,并且只保留最近三天的;现在有另外一个表,也想修改为相同的模式,需要的步骤有:


整体思路:

将一个表进行分区,并保留最近3天的数据,创建步骤:

1.确认现在表的状态;

2.将现在的非分区表,修改为分区表;      --分区表的片键,必须在主键中,主键修改必须先删除再重建,而自增id必须先取消自增才能删除,

3.创建进行表分区和删除分区的存储过程;      --存储过程的创建

4.创建event 设定定时任务,调用上面的存储过程;    --event的创建

5.等待定时任务执行后,确认存储过程和event的执行情况;   --确认创建完成后的状态



测试:

对现有testdb库中有一个 par_tab01 表,是一个分区表,对改变创建了一个名称为daypartition的存储过程,和一个名称为day_partition的时间,每天会定时创建新的分区,并删除超过三天的分区,保证该分区表保留最近三天的数据。


查看 par_table01的相关信息为:


表结构定义:
>>show create table  par_tab01\G
*************************** 1. row ***************************
       Table: error_log
Create Table: CREATE TABLE ` par_tab01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `imei` varchar(32) DEFAULT '',
  `model` varchar(32) DEFAULT '',
  `app_ver` varchar(12) DEFAULT '',
  `sys_ver` varchar(24) DEFAULT '',
  `nickname` varchar(32) DEFAULT '',
  `ip_addr` varchar(24) DEFAULT '',
  `create_time` int(10) NOT NULL DEFAULT '0',
  `compile_time` varchar(64) DEFAULT '',
  `platform` varchar(32) DEFAULT '',
  `gameid` int(6) DEFAULT '0',
  `error_type` varchar(20) DEFAULT '',
  `script_ver` varchar(20) DEFAULT '',
  `info` blob NOT NULL,
  `cur_packageid` int(10) DEFAULT '0',
  `cur_gameid` int(10) DEFAULT '0',
  `cur_packagever` int(10) DEFAULT '0',
  `md5` varchar(50) DEFAULT '',
  `coredump_md5` char(36) DEFAULT '',
  PRIMARY KEY (`id`,`create_time`),
  KEY `search_index1` (`app_ver`,`gameid`,`create_time`),
  KEY `search_index` (`app_ver`,`gameid`,`script_ver`,`create_time`),
  KEY `idx_md5_createtime` (`md5`,`create_time`),
  KEY `idx_coredump_dump_createtime` (`coredump_md5`,`create_time`)
) ENGINE=MyISAM AUTO_INCREMENT=220326871 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (create_time)
(PARTITION p20151103 VALUES LESS THAN (1446480000) ENGINE = MyISAM,
 PARTITION p20151104 VALUES LESS THAN (1446566400) ENGINE = MyISAM,
 PARTITION p20151105 VALUES LESS THAN (1446652800) ENGINE = MyISAM,
 PARTITION p20151106 VALUES LESS THAN (1446739200) ENGINE = MyISAM,
 PARTITION p20151107 VALUES LESS THAN (1446825600) ENGINE = MyISAM) */
1 row in set (0.00 sec)

查看相关事件:
show events;

show events;
+---------+-------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db      | Name  | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+---------+-------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| testdb | day_partition | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2015-02-04 00:10:00 | NULL | ENABLED |     133185 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+-------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.03 sec)

在testdb库中,有一个名称为day_partition的时间,以root用户执行,每天都会执行,执行开始时间为 2015-02-04 00:10:00 ,字符集要校验规则为utf8;

查看事件创建情况:

show create event day_partition\G
>>show create event day_partition\G
*************************** 1. row ***************************
               Event: day_partition
            sql_mode: 
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `day_partition` ON SCHEDULE EVERY 1 DAY STARTS '2015-02-04 00:10:00' ON COMPLETION NOT PRESERVE ENABLE DO begin
call daypartition;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

查看具体的 day_partition事件创建语法,前面的定义在状态中已经看到了,最终event调用了一个存储过程 daypartition,event定义主要为一句话:
CREATE DEFINER=`root`@`localhost` EVENT `day_partition` ON SCHEDULE EVERY 1 DAY STARTS '2015-02-04 00:10:00' ON COMPLETION NOT PRESERVE ENABLE DO begin
call daypartition;
end



查看相关存储过程:
show procedure status;

>>show procedure status;
+---------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db      | Name              | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| testdb | daypartition      | PROCEDURE | root@localhost | 2015-02-03 14:21:57 | 2015-02-03 14:21:57 | INVOKER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| jjstat  | cleanclienterrlog | PROCEDURE | root@localhost | 2015-02-03 20:49:48 | 2015-02-03 20:49:48 | INVOKER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| jjstat  | clienterrlog      | PROCEDURE | root@localhost | 2015-02-03 16:40:44 | 2015-02-03 16:40:44 | INVOKER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.01 sec)


select `name` from mysql.proc where db = 'testdb' and `type` = 'PROCEDURE';

>>select `name` from mysql.proc where db = 'testdb' and `type` = 'PROCEDURE';
+--------------+
| name         |
+--------------+
| daypartition |
+--------------+
1 row in set (0.00 sec)



查看存储过程创建语法:

show create procedure daypartition\G

>>show create procedure daypartition\G
*************************** 1. row ***************************
           Procedure: daypartition
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `daypartition`()
    SQL SECURITY INVOKER
begin
declare v_counta,v_countk int;

set @t1=unix_timestamp(curdate()+interval 2 day);
set @p1=date_format(curdate()+interval 2 day,'%Y%m%d');
select count(if(table_name='par_tab01',table_name,null)) into v_counta from information_schema.partitions where partition_name=concat('p',@p1);
if v_counta=0 then
set @sqla=concat('alter table par_tab01 add partition (partition p',@p1,' values less than (',@t1,'));');
prepare stmta from @sqla;
execute stmta;
deallocate prepare stmta;
end if;

set @p2=date_format(curdate()-interval 3 day,'%Y%m%d');
select count(if(table_name='par_tab01',table_name,null)) into v_countk from information_schema.partitions where partition_name=concat('p',@p2);
if v_countk=1 then
set @sqlk=concat('alter table par_tab01 drop partition p',@p2,';');
prepare stmtk from @sqlk;
execute stmtk;
deallocate prepare stmtk;
end if;

end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

存储过程的具体定义为:
CREATE DEFINER=`root`@`localhost` PROCEDURE `daypartition`()
    SQL SECURITY INVOKER
begin
declare v_counta,v_countk int;

set @t1=unix_timestamp(curdate()+interval 2 day);
set @p1=date_format(curdate()+interval 2 day,'%Y%m%d');
select count(if(table_name='par_tab01',table_name,null)) into v_counta from information_schema.partitions where partition_name=concat('p',@p1);
if v_counta=0 then
set @sqla=concat('alter table par_tab01 add partition (partition p',@p1,' values less than (',@t1,'));');
prepare stmta from @sqla;
execute stmta;
deallocate prepare stmta;
end if;

set @p2=date_format(curdate()-interval 3 day,'%Y%m%d');
select count(if(table_name='par_tab01',table_name,null)) into v_countk from information_schema.partitions where partition_name=concat('p',@p2);
if v_countk=1 then
set @sqlk=concat('alter table par_tab01 drop partition p',@p2,';');
prepare stmtk from @sqlk;
execute stmtk;
deallocate prepare stmtk;
end if;

end

对于一个目前并不是分区表的表par_tab02,目前的表结构定义为:
>>show create table par_tab02\G
*************************** 1. row ***************************
       Table: ios_coredump
Create Table: CREATE TABLE `par_tab02` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `imei` varchar(32) DEFAULT '',
  `model` varchar(32) DEFAULT '',
  `app_ver` varchar(12) DEFAULT '',
  `sys_ver` varchar(24) DEFAULT '',
  `nickname` varchar(32) DEFAULT '',
  `gameid` int(6) DEFAULT '0',
  `compiletime` varchar(64) DEFAULT '',
  `ip_addr` varchar(24) DEFAULT '',
  `script_ver` varchar(20) DEFAULT '',
  `cur_packageid` int(10) DEFAULT '0',
  `cur_gameid` int(10) DEFAULT '0',
  `cur_packagever` int(10) DEFAULT '0',
  `filename` varchar(50) DEFAULT '',
  `dump_time` int(11) DEFAULT '0',
  `server_addr` varchar(24) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `search_index1` (`app_ver`,`gameid`,`dump_time`)
) ENGINE=MyISAM AUTO_INCREMENT=118161 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

执行脚本:
分析之后,整体的处理过程为:
将一个表进行分区,并保留最近3天的数据,创建步骤:
1.确认现在表的状态;
2.将现在的非分区表,修改为分区表;      --分区表的片键,必须在主键中,主键修改必须先删除再重建,而自增id必须先取消自增才能删除,
3.创建进行表分区和删除分区的存储过程;      --存储过程的创建
4.创建event 设定定时任务,调用上面的存储过程;    --event的创建
5.等待定时任务执行后,确认存储过程和event的执行情况;   --确认创建完成后的状态



编写的相关脚本为:

#!/bin/sh
# create  by zhaofx on 20151027
# this script is used to partition the table testDB.par_tab02 as the testDB.par_tab01  on 2015.11.05

echo "1.修改前状态确认"
echo ""
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock <<EOF
use test;
show create table par_tab02\G
show events;
show create event day_partition\G
show procedure status;
select name from mysql.proc where db = 'testDB' and type = 'PROCEDURE';
show create procedure daypartition\G
EOF

echo ""
echo "2.修改表的主键,然后修改为分区表"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "use test;alter table par_tab02 change  id id int(11) NOT NULL ;"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "use test;alter table par_tab02 drop primary key;"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "use test;alter table par_tab02 add primary key (id,dump_time);"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "use test;alter table par_tab02  /*!50100 PARTITION BY RANGE (dump_time)
(PARTITION p20151103 VALUES LESS THAN (1446480000) ENGINE = MyISAM,
 PARTITION p20151104 VALUES LESS THAN (1446566400) ENGINE = MyISAM,
 PARTITION p20151105 VALUES LESS THAN (1446652800) ENGINE = MyISAM,
 PARTITION p20151106 VALUES LESS THAN (1446739200) ENGINE = MyISAM,
 PARTITION p20151107 VALUES LESS THAN (1446825600) ENGINE = MyISAM) */;"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "use test;alter table par_tab02 change id id int(11) NOT NULL AUTO_INCREMENT;"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock -e "use test;show create table par_tab02\G"
echo ""

echo "3.添加存储过程daypartition02,用于按日期添加和删除分区"
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock <<EOF
use test;
delimiter // 

CREATE DEFINER=root@localhost PROCEDURE daypartition02()
    SQL SECURITY INVOKER
begin
declare v_counta,v_countk int;

set @t1=unix_timestamp(curdate()+interval 2 day);
set @p1=date_format(curdate()+interval 2 day,'%Y%m%d');
select count(if(table_name='par_tab02',table_name,null)) into v_counta from information_schema.partitions where partition_name=concat('p',@p1);
if v_counta=0 then
set @sqla=concat('alter table par_tab02 add partition (partition p',@p1,' values less than (',@t1,'));');
prepare stmta from @sqla;
execute stmta;
deallocate prepare stmta;
end if;

set @p2=date_format(curdate()-interval 3 day,'%Y%m%d');
select count(if(table_name='par_tab02',table_name,null)) into v_countk from information_schema.partitions where partition_name=concat('p',@p2);
if v_countk=1 then
set @sqlk=concat('alter table par_tab02 drop partition p',@p2,';');
prepare stmtk from @sqlk;
execute stmtk;
deallocate prepare stmtk;
end if;

end
//

delimiter ; 
EOF
date
echo ""

echo "4.添加event事件day_partition02,用于每天定时调用存储过程daypartition02操作"
date 
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock <<EOF
use test;
delimiter // 

CREATE DEFINER=root@localhost EVENT day_partition02 ON SCHEDULE EVERY 1 DAY STARTS '2015-11-07 00:20:00' ON COMPLETION NOT PRESERVE ENABLE DO begin
call daypartition02;
end
//

delimiter ; 
EOF
date
echo ""

echo "5.确认修改par_tab02后状态"
echo ""
date
echo ""
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql_3306/tmp/mysql.sock <<EOF
use test;
show create table par_tab02\G
show events;
show create event day_partition\G
show procedure status;
select name from mysql.proc where db = 'testDB' and type = 'PROCEDURE';
show create procedure daypartition\G
EOF


MySQL相关存储过程,event的操作命令为:
*********************************************
mysql的事件event添加、修改、删除、查询命令:
查询:
show events;
select name from mysql.event;
show create event day_partition\G
添加:
CREATE DEFINER=`root`@`localhost` EVENT `day_partition02` ON SCHEDULE EVERY 1 DAY STARTS '2015-11-06 00:20:00' ON COMPLETION NOT PRESERVE ENABLE DO begin call daypartition02;
end
修改:
ALTER DEFINER=`root`@`localhost` EVENT `day_partition02` ON SCHEDULE EVERY 1 DAY STARTS '2015-11-05 15:20:00' ON COMPLETION NOT PRESERVE ENABLE DO begin call daypartition02;
end
删除:
DROP [IF EXISTS] EVENT `day_partition02`;

MySQL的存储过程 procedure 的添加,删除,修改,查询命令:
查询:
 show procedure status;
 select `name` from mysql.proc where db = 'test' and `type` = 'PROCEDURE'; 
show create procedure daypartition\G   

添加:
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM test01;
END;
//
delimiter ;
修改:
delimiter //
ALTER PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t limit 10;
show tables;
END;
//
delimiter ;
删除:
DROP PROCEDURE simpleproc;
执行:
CALL simpleproc;  
SELECT INTO FROM语句
      语句形式为:SELECT vale1, value2 into Table2 from Table1
  
要求目标表Table2不存在,因为在插入时会自动创建表Table2, 并将Table1中指定字段数据复制到Table2中。




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值