Laravel创建MySQL分区表时遭遇Error 1503问题求助

问题描述 #

我在Laravel应用中编写动态命令，实现表复制与MySQL分区配置，操作步骤如下：

• 复制原表结构到新表
• 添加基于created_at字段年份的存储生成列created_year
• 删除原主键，创建包含created_year的复合新主键
• 执行按created_year的RANGE分区时触发错误：

SQLSTATE[HY000]: General error: 1503 唯一索引必须包含表分区函数中的所有列（前缀列不被视为包含）

相关代码 #

// 复制原表
DB::statement("
    CREATE TABLE `$newTableName` LIKE `$tableName`
");

// 添加用于分区的created_year列
DB::statement("ALTER TABLE `$newTableName` ADD COLUMN created_year INT GENERATED ALWAYS AS (YEAR(created_at)) STORED");

// 获取原表主键信息
$databaseName = DB::connection()->getDatabaseName();
$primaryKeyInfo = DB::connection()->select("
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = '$databaseName'
    AND TABLE_NAME = '$tableName'
    AND CONSTRAINT_NAME = 'PRIMARY'
");

$primaryKeys = [];
foreach ($primaryKeyInfo as $info) {
    $primaryKeys[] = $info->COLUMN_NAME;
}

DB::statement("ALTER TABLE `$newTableName` DROP PRIMARY KEY");

$primaryKeys[] = 'created_year';
$newPrimaryKey = implode(',', $primaryKeys);

DB::statement("ALTER TABLE `$newTableName` ADD PRIMARY KEY ($newPrimaryKey)");

// 按created_year添加分区
DB::statement("
    ALTER TABLE `$newTableName`
    PARTITION BY RANGE COLUMNS(created_year) (
        PARTITION p2020 VALUES LESS THAN (2021),
        PARTITION p2021 VALUES LESS THAN (2022),
        PARTITION p2022 VALUES LESS THAN (2023),
        PARTITION p2023 VALUES LESS THAN (2024),
        PARTITION p2024 VALUES LESS THAN (2025),
        PARTITION p2025 VALUES LESS THAN MAXVALUE
    )
");

报错原因与解决方法 #

核心原因 #

MySQL分区规则明确要求：所有唯一索引（包括主键）必须包含分区函数中使用的所有列。你虽然更新了主键，但原表复制过来的其他唯一索引（比如邮箱、手机号等字段的唯一约束）并未包含created_year，这就违反了分区规则，导致报错。

解决步骤 #

1. 清理新表中的非主键唯一索引
   在删除原主键后，先查询并移除新表中所有不带created_year的唯一索引：

// 获取新表的非主键唯一索引
$uniqueIndexes = DB::connection()->select("
    SELECT INDEX_NAME
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = '$databaseName'
    AND TABLE_NAME = '$newTableName'
    AND INDEX_NAME != 'PRIMARY'
    AND NON_UNIQUE = 0
    GROUP BY INDEX_NAME
");

// 删除这些唯一索引
foreach ($uniqueIndexes as $index) {
    DB::statement("ALTER TABLE `$newTableName` DROP INDEX `{$index->INDEX_NAME}`");
}

2. 保留已完成的复合主键配置
   你已经将created_year加入主键，这一步符合分区要求，无需修改。

3. 按需重建包含created_year的唯一索引
   如果业务需要保留某些唯一约束，必须将created_year加入复合唯一索引中。例如原表有email唯一约束，需修改为：

// 示例：重建包含created_year的复合唯一索引
DB::statement("ALTER TABLE `$newTableName` ADD UNIQUE INDEX idx_email_year (email, created_year)");

4. 执行分区操作
   完成上述步骤后，再执行分区语句即可成功。

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