IT博客汇 | MySQL分区表：按照日期实现的RANGE分区以及真实边界的查询（时间函数）

MySQL分区表：按照日期实现的RANGE分区以及真实边界的查询（时间函数）

Adamhuan发表于 2017-05-31 06:32:22

在MySQL中，如果按照日期来做范围分区，可能你会这么做：

mysql> SELECT partition_name,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,data_length,MAX_DATA_LENGTH FROM information_schema.partitions WHERE table_name='event_by_day';
+----------------+----------------------------+-----------------------+-------------+-----------------+
| partition_name | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | data_length | MAX_DATA_LENGTH |
+----------------+----------------------------+-----------------------+-------------+-----------------+
| e_date_p9      |                          1 | 1495782000            |       16384 |            NULL |
| e_date_p10     |                          2 | 1496214000            |       16384 |            NULL |
+----------------+----------------------------+-----------------------+-------------+-----------------+
2 rows in set (0.01 sec)

mysql> alter table event_by_day add partition (partition e_date_p11 values less than (unix_timestamp('2017-05-32')));
ERROR 1697 (HY000): VALUES value for partition 'e_date_p11' must have type INT
mysql> 
mysql> alter table event_by_day add partition (partition e_date_p11 values less than (unix_timestamp('2017-06-01')));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table event_by_day add partition (partition e_date_p12 values less than (unix_timestamp('2017-06-02')));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT partition_name,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,data_length,MAX_DATA_LENGTH FROM information_schema.partitions WHERE table_name='event_by_day';
+----------------+----------------------------+-----------------------+-------------+-----------------+
| partition_name | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | data_length | MAX_DATA_LENGTH |
+----------------+----------------------------+-----------------------+-------------+-----------------+
| e_date_p9      |                          1 | 1495782000            |       16384 |            NULL |
| e_date_p10     |                          2 | 1496214000            |       16384 |            NULL |
| e_date_p11     |                          3 | 1496300400            |       16384 |            NULL |
| e_date_p12     |                          4 | 1496386800            |       16384 |            NULL |
+----------------+----------------------------+-----------------------+-------------+-----------------+
4 rows in set (0.00 sec)

mysql>

如上，不同的分区的边界时间可以通过【partition_description】去查看。
不过这一列的值并不是以时间格式展示的，而是以秒为单位展示的。

通过之前的创建可以看到我每个分区相隔的时间是一天，一天为：86400秒，如下：

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2017-06-01','2017-06-02');
+-------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2017-06-01','2017-06-02') |
+-------------------------------------------------+
|                                            1440 |
+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT TIMESTAMPDIFF(SECOND,'2017-06-01','2017-06-02');
+-------------------------------------------------+
| TIMESTAMPDIFF(SECOND,'2017-06-01','2017-06-02') |
+-------------------------------------------------+
|                                           86400 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>

但是上面的时间格式的秒并不是直接通过日期得到的秒。

直接通过日期转换的秒是这样的：

mysql> select to_seconds('2017-06-02');
+--------------------------+
| to_seconds('2017-06-02') |
+--------------------------+
|              63663580800 |
+--------------------------+
1 row in set (0.00 sec)

mysql>

很明显，跟上面的数字，差别很大。

它其实是UNIX_TIME，如下：

mysql> select unix_timestamp('2017-06-02');
+------------------------------+
| unix_timestamp('2017-06-02') |
+------------------------------+
|                   1496386800 |
+------------------------------+
1 row in set (0.00 sec)

mysql>

所以，是可以通过上面的数字转换为标准的日期格式的：

mysql> select from_unixtime(1496300400);
+---------------------------+
| from_unixtime(1496300400) |
+---------------------------+
| 2017-06-01 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select from_unixtime(1496386800);
+---------------------------+
| from_unixtime(1496386800) |
+---------------------------+
| 2017-06-02 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)

mysql>

最后，查阅分区表的SQL，这样写，会明白很多：

mysql> SELECT partition_name,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,from_unixtime(PARTITION_DESCRIPTION) FROM information_schema.partitions WHERE table_name='event_by_day';
+----------------+----------------------------+-----------------------+--------------------------------------+
| partition_name | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | from_unixtime(PARTITION_DESCRIPTION) |
+----------------+----------------------------+-----------------------+--------------------------------------+
| e_date_p9      |                          1 | 1495782000            | 2017-05-26 00:00:00.000000           |
| e_date_p10     |                          2 | 1496214000            | 2017-05-31 00:00:00.000000           |
| e_date_p11     |                          3 | 1496300400            | 2017-06-01 00:00:00.000000           |
| e_date_p12     |                          4 | 1496386800            | 2017-06-02 00:00:00.000000           |
+----------------+----------------------------+-----------------------+--------------------------------------+
4 rows in set (0.00 sec)

mysql>

——————————————
Done。