mysql+当天创建分区表,在现有的mysql表上创建分区

下面是我的表结构,有15万条记录

CREATE TABLE `employees` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`city` varchar(255) DEFAULT NULL,

`emailid` varchar(255) DEFAULT NULL,

`join_date` date NOT NULL DEFAULT '0000-00-00',

PRIMARY KEY (`id`,`join_date`)

) ENGINE=InnoDB

我正在使用mysql版本：5.5.41

数据报告：

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

| join_date | count |

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

| 2015-05-01 | 100 |

| 2015-05-02 | 100 |

| 2015-05-03 | 100 |

| 2015-05-04 | 100 |

| 2015-05-05 | 100 |

| 2015-05-06 | 100 |

| 2015-05-07 | 67900 |

| 2015-05-08 | 30622 |

| 2015-05-09 | 10455 |

| 2015-05-10 | 40393 |

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

当我尝试执行以下命令时

Alter Table employees PARTITION by RANGE (TO_DAYS(join_date))

( PARTITION p1 values less than (TO_DAYS('2015-05-07')));

我收到以下错误：表没有为值736090进行分区.即使我有600条记录少于值2015-05-07

难道我做错了什么.

解决方法:

不确定,但是我认为您应该尝试：

ALTER TABLE employees PARTITION BY RANGE (TO_DAYS(join_date)) (

PARTITION p1 values LESS THAN(TO_DAYS('2015-05-07')),

PARTITION p2 VALUES LESS THAN(MAXVALUE)

);

标签：partitioning,sql,mysql,sharding

来源： https://codeday.me/bug/20191120/2042862.html