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

迈不过友情╰ 2022-08-10 00:45 248阅读 0赞

> 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][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][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中。
> 
>   
> 
> 
>   
> 
> 
>   
>

[DEFINER_root_localhost]: mailto:DEFINER=`root`@`localhost