繁依Fanyi0

这个屌丝很懒，什么也没留下！

热门标签

mysql partition by range_mysql 分区按 PARTITION BY RANGE (TO_DAYS(startTime))

作者：繁依Fanyi0 | 2024-05-25 21:32:34

踩

mysql partition by range(day())

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

+---------------------+---------------------+

| min(startTime) | max(startTime) |

+---------------------+---------------------+

| 2016-08-25 00:04:57 | 2016-08-25 23:57:57 |

+---------------------+---------------------+

1 row in set (0.03 sec)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/623855

mysql partition by range_mysql 分区 按 PARTITION BY RANGE (TO_DAYS(startTime))

mysql partition by range_mysql 分区按 PARTITION BY RANGE (TO_DAYS(startTime))