Table of Contents:

建表语句
定时删除分区
参考链接

【数据库分区的维护】

增加新分区的步骤：删除最大的分区--->新增分区--->添加最大分区
查看分区

SELECT
  partition_name part,
  partition_expression expr,
  partition_description descr,
  FROM_DAYS(partition_description) lessthan_sendtime,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = SCHEMA()
  AND TABLE_NAME='item_log';   //填表名就行

添加分区

alter table xxxxxxx add partition (partition p0 values less than(1991));  //只能添加大于分区键的分区

删除分区

alter table item_log drop partition p0; //可以删除任意分区

删除分区数据

alter table xxxxxx  truncate partition p1,p2;  
alter table xxxxxx  truncate partition all;

删除最大分区，并增加新分区

alter table item_log drop partition pextent; 


alter table item_log add partition (
PARTITION d201609p29 VALUES LESS THAN (unix_timestamp('2016-09-29')),
PARTITION d201609p30 VALUES LESS THAN (unix_timestamp('2016-09-30'))
);

增加最大分区（这个是出现异常值做的容错，避免出现一个超过所有分区的而无处容放）

alter table item_log add partition (
PARTITION pextent VALUES LESS THAN maxvalue 
);

删除分区

alter table item_log drop partition d201607p26;

建表语句

drop database if exists gamelogtest;
create database gamelogtest CHARACTER SET = 'UTF8';

use gamelogtest;

DROP TABLE IF EXISTS `item_log`;
CREATE TABLE `item_log` (
    `dept` varchar(20) NOT NULL DEFAULT '',
    `sid` bigint DEFAULT NULL,
    `user` varchar(255) NOT NULL DEFAULT '',
    `oldsid` bigint  NULL,
    `roleid` bigint  NULL,
    `time` int unsigned DEFAULT NULL,
    `vip` int(11) DEFAULT NULL,
    `level` int(11) DEFAULT NULL,
    `itemid` varchar(255) NOT NULL DEFAULT '',
    `op` int(2) DEFAULT NULL,
    `reason` varchar(30) NOT NULL DEFAULT '',
    `amount` int(11) DEFAULT NULL,
    `channel` varchar(30) NOT NULL DEFAULT '',

    KEY `user` (`user`,`time`),
    KEY `roleid` (`roleid`,`time`)
)ENGINE=myisam 
PARTITION BY RANGE (`time`)( 
    PARTITION d201607p29 VALUES LESS THAN (unix_timestamp('2016-07-29')),
    PARTITION d201607p30 VALUES LESS THAN (unix_timestamp('2016-07-30')),
    PARTITION d201607p31 VALUES LESS THAN (unix_timestamp('2016-07-31')),
    PARTITION pextent VALUES LESS THAN maxvalue     
);

DROP TABLE IF EXISTS `resource_log`;
CREATE TABLE `resource_log` (
  `dept` varchar(20) NOT NULL DEFAULT '',
  `sid` bigint DEFAULT NULL,
  `user` varchar(255) NOT NULL DEFAULT '',
  `oldsid` bigint DEFAULT NULL,
  `roleid` bigint DEFAULT NULL,
  `time` int unsigned DEFAULT NULL,
  `channel` varchar(30) NOT NULL DEFAULT '',
  `kind` varchar(255) NOT NULL DEFAULT '',
  `vip` int(11) DEFAULT NULL,
  `op` int(2) DEFAULT NULL,
  `reason` varchar(30) NOT NULL DEFAULT '',
  `amount` int(11) DEFAULT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `level` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `item_num` int(11) DEFAULT NULL,

  KEY `user` (`user`,`time`),
  KEY `roleid` (`roleid`,`time`)
)ENGINE=myisam 
PARTITION BY RANGE (`time`)( 
    PARTITION d201607p29 VALUES LESS THAN (unix_timestamp('2016-07-29')),
    PARTITION d201607p30 VALUES LESS THAN (unix_timestamp('2016-07-30')),
    PARTITION d201607p31 VALUES LESS THAN (unix_timestamp('2016-07-31')),
    PARTITION pextent VALUES LESS THAN maxvalue     
);

DROP TABLE IF EXISTS `other_log`;
CREATE TABLE `other_log` (
  `interface` varchar(30) NOT NULL DEFAULT '',
  `sid` bigint DEFAULT NULL,
  `oldsid` bigint DEFAULT NULL,
  `user` varchar(255) NOT NULL DEFAULT '',
  `roleid` bigint DEFAULT NULL,
  `dept` varchar(20) NOT NULL DEFAULT '',
  `channel` varchar(30) NOT NULL DEFAULT '',
  `time` int unsigned DEFAULT NULL,
  `uid` varchar(255) NOT NULL DEFAULT '',
  `machine` varchar(255) NOT NULL DEFAULT '',
  `netw` varchar(255) NOT NULL DEFAULT '',
  `type` int(11) DEFAULT NULL,
  `rolename` varchar(255) NOT NULL DEFAULT '',
  `prof` varchar(30) NOT NULL DEFAULT '',
  `level` int(11) DEFAULT NULL,
  `ip` varchar(20) NOT NULL DEFAULT '',
  `guild` varchar(255) NOT NULL DEFAULT '',
  `vip_level` int(11) DEFAULT NULL,
  `vip` int(11) DEFAULT NULL,
  `money` decimal(10,2) NOT NULL DEFAULT '0.00',
  `amount` int(11) DEFAULT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `order` varchar(30) NOT NULL DEFAULT '',
  `kind` varchar(80) NOT NULL DEFAULT '',
  `reason` varchar(30) NOT NULL DEFAULT '',
  `item_num` int(11) DEFAULT NULL,
  `taskid` varchar(255) NOT NULL DEFAULT '',
  `result` varchar(255) NOT NULL DEFAULT '',
  `usetime` varchar(255) NOT NULL DEFAULT '',
  `jumpn` int(11) DEFAULT NULL,
  `startt` int(11) DEFAULT NULL,
  `sweepn` int(11) DEFAULT NULL,
  `award` varchar(255) NOT NULL DEFAULT '',
  `opid` varchar(255) NOT NULL DEFAULT '',
  `bef_num` int(11) DEFAULT NULL,
  `num` varchar(255) NOT NULL DEFAULT '',
  `onlinetime` int(11) DEFAULT NULL,
  `rolecount` varchar(255) NOT NULL DEFAULT '',
  `regtime` int(10) unsigned DEFAULT NULL,
  `ziz` int(11) DEFAULT NULL,
  `wear` int(11) DEFAULT NULL,
  `power` int(11) DEFAULT NULL,

  KEY `user` (`user`,`interface`,`time`),
  KEY `roleid` (`roleid`,`interface`,`time`)
)ENGINE=myisam 
PARTITION BY RANGE (`time`)( 
    PARTITION d201607p29 VALUES LESS THAN (unix_timestamp('2016-07-29')),
    PARTITION d201607p30 VALUES LESS THAN (unix_timestamp('2016-07-30')),
    PARTITION d201607p31 VALUES LESS THAN (unix_timestamp('2016-07-31')),
    PARTITION pextent VALUES LESS THAN maxvalue         
);


DROP TABLE IF EXISTS `log_offset`;
CREATE TABLE `log_offset` (
  `logname` bigint(20) NOT NULL,
  `logoffset` bigint(20) NOT NULL,
  `curlines` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

定时删除分区

<?php
set_time_limit(0);
error_reporting(0);
date_default_timezone_set("PRC");


$host       = '127.0.0.1';
$username   = 'root';
$passwd     = 's5UK52SlF31W#j5O';
$dbname     = 'gamelog';
$table      = ['item_log','other_log','resource_log'];

$link = mysql_connect($host, $username, $passwd);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db($dbname, $link);
mysql_query("set names 'utf8'");


foreach($table as $v){
    
    $query_part_sql = "SELECT
      partition_name part, 
      partition_expression expr, 
      partition_description descr, 
      FROM_DAYS(partition_description) lessthan_sendtime, 
      table_rows 
    FROM
      INFORMATION_SCHEMA.partitions 
    WHERE
      TABLE_SCHEMA = SCHEMA() 
      AND TABLE_NAME='$v' order by descr asc";

    $res = mysql_query($query_part_sql);

    
    $all_part_arr = [];
    while($data = mysql_fetch_assoc($res)){    
        $all_part_arr[] = $data;
    }

    //delete 5 oldest partition
    for($i = 0;$i<5;$i++){
        $sql = "alter table $v drop partition {$all_part_arr[$i][part]}";
        echo $sql."\n";
        //mysql_query($sql);
    }   
}

blog/A-IT/30-database/mysql/数据分区的维护&&行为道具日志

建表语句

定时删除分区

参考链接