MySQL修改表添加分区时出现语法错误求助

Let's break down the syntax issues in your ALTER TABLE statement and fix them step by step:

1. Incorrect ALTER TABLE Structure for Primary Key #

You tried to define the primary key directly in parentheses after the table name, which isn't valid syntax for ALTER TABLE. Additionally, MySQL has a strict rule for partitioned tables: all unique keys (including the primary key) must include the partition column (in your case, dated). This ensures the database can enforce uniqueness across all partitions.

2. Trailing Comma in Partition List #

Your last partition definition ends with an extra comma (PARTITION p20180101 VALUES LESS THAN (TO_DAYS('2018-01-01')),), which will trigger a syntax error since there's no subsequent partition to follow it.

Corrected SQL Statements #

Scenario 1: Table has no existing primary key #

If your activity_log table doesn't have a primary key yet, use this statement to add a composite primary key (including the partition column dated) and apply partitions:

ALTER TABLE activity_log
ADD PRIMARY KEY (`activityId`, `dated`),
PARTITION BY RANGE(TO_DAYS(dated)) (
    PARTITION p20150101 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    PARTITION p20160101 VALUES LESS THAN (TO_DAYS('2016-01-01')),
    PARTITION p20170101 VALUES LESS THAN (TO_DAYS('2017-01-01')),
    PARTITION p20180101 VALUES LESS THAN (TO_DAYS('2018-01-01'))
);

Scenario 2: Table already has a primary key (without dated) #

If your table already has a primary key that only includes activityId, you'll need to drop the old primary key first, then add a new composite one with dated, before applying partitions:

ALTER TABLE activity_log
DROP PRIMARY KEY,
ADD PRIMARY KEY (`activityId`, `dated`),
PARTITION BY RANGE(TO_DAYS(dated)) (
    PARTITION p20150101 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    PARTITION p20160101 VALUES LESS THAN (TO_DAYS('2016-01-01')),
    PARTITION p20170101 VALUES LESS THAN (TO_DAYS('2017-01-01')),
    PARTITION p20180101 VALUES LESS THAN (TO_DAYS('2018-01-01'))
);

Key Notes #

• The requirement to include the partition column in the primary key is non-negotiable for MySQL partitioned tables. It prevents edge cases where duplicate unique key values could exist across different partitions.
• If your table already contains data, running this ALTER statement may take some time (depending on data volume) and lock the table temporarily. Schedule this during low-traffic periods and ensure you have a backup beforehand.

内容的提问来源于stack exchange，提问作者Jay Zamsol