mysql 分区 按 PARTITION BY RANGE (TO_DAYS(startTime))

to_days()

Given a date date, returns a day number (the number of days since year 0).

给定一个date 日期,返回天数

Create Table: CREATE TABLE `ClientActionTrack` (
  `sn` bigint(20) NOT NULL AUTO_INCREMENT,
  `clientSn` int(11) DEFAULT NULL,
  `ip` varchar(32) DEFAULT NULL,
  `url` varchar(1000) DEFAULT NULL COMMENT 'request url',
  `httpMethod` varchar(100) DEFAULT NULL COMMENT 'http method',
  `requestParams` text COMMENT '请求参数',
  `requestHeader` varchar(2000) DEFAULT NULL COMMENT '请求头信息',
  `pageUrl` varchar(500) DEFAULT NULL COMMENT '页面Url',
  `sessionId` varchar(100) DEFAULT NULL COMMENT 'session的Id',
  `startTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '访问开始时间',
  `completeTime` datetime DEFAULT NULL COMMENT '访问完成时间',
  PRIMARY KEY (`sn`,`startTime`)
) ENGINE=InnoDB AUTO_INCREMENT=4424745 DEFAULT CHARSET=utf8 COMMENT='用户访问记录表'
/*!50100 PARTITION BY RANGE (TO_DAYS(startTime))
(PARTITION p20151204 VALUES LESS THAN (736302) ENGINE = InnoDB,
 PARTITION p20151205 VALUES LESS THAN (736303) ENGINE = InnoDB,
 PARTITION p20151206 VALUES LESS THAN (736304) ENGINE = InnoDB,
 PARTITION p20151207 VALUES LESS THAN (736305) ENGINE = InnoDB,
 PARTITION p20151208 VALUES LESS THAN (736306) ENGINE = InnoDB,
 PARTITION p20151209 VALUES LESS THAN (736307) ENGINE = InnoDB,
 
  PARTITION p20160824 VALUES LESS THAN (736566) ENGINE = InnoDB,  ##小于'2016-08-25 23:59:47'
 PARTITION p20160825 VALUES LESS THAN (736567) ENGINE = InnoDB,
 PARTITION p20160826 VALUES LESS THAN (736568) ENGINE = InnoDB,
 PARTITION p20160827 VALUES LESS THAN (736569) ENGINE = InnoDB,
 
 mysql> select TO_DAYS('2016-08-25 23:59:47') from  Client limit 1;
+--------------------------------+
| TO_DAYS('2016-08-25 23:59:47') |
+--------------------------------+
|                         736566 |
+--------------------------------+
1 row in set (0.00 sec)


mysql> select TO_DAYS('2016-08-26 23:59:47') from  Client limit 1;
+--------------------------------+
| TO_DAYS('2016-08-26 23:59:47') |
+--------------------------------+
|                         736567 |
+--------------------------------+
1 row in set (0.00 sec)


mysql> SELECT min(startTime),max(startTime)  from ClientActionTrack  PARTITION (p20160825);
+---------------------+---------------------+
| min(startTime)      | max(startTime)      |
+---------------------+---------------------+
| 2016-08-25 00:04:57 | 2016-08-25 23:59:47 |
+---------------------+---------------------+
1 row in set (0.03 sec)

mysql> select min(startTime),max(startTime) from ClientActionTrack where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';
+---------------------+---------------------+
| min(startTime)      | max(startTime)      |
+---------------------+---------------------+
| 2016-08-25 00:04:57 | 2016-08-25 23:57:57 |
+---------------------+---------------------+
1 row in set (0.03 sec)