MySQL主键INT转UNSIGNED INT无锁无停机变更方案咨询

直接执行ALTER TABLE会锁表导致业务中断，结合Symfony Doctrine迁移系统，我们可以通过双写+分批同步+主键切换的方式实现无锁无停机变更，同时保证数据一致性。以下是分步方案：

阶段1：添加新列并启用双写 #

首先在原表中新增一个INT UNSIGNED类型的自增列，同时修改实体类实现双写（新写入的数据同时填充新旧主键列）。

1. 生成Doctrine迁移脚本：

public function up(Schema $schema): void
{
    $table = $schema->getTable('table_name');
    // 获取旧id的最大值，设置新列自增起始值
    $maxIdResult = $this->connection->executeQuery('SELECT MAX(id) FROM table_name');
    $maxId = $maxIdResult->fetchOne() ?: 0;
    
    $table->addColumn('new_id', 'integer', [
        'unsigned' => true,
        'autoincrement' => true,
        'notnull' => true,
    ]);
    $this->connection->executeQuery("ALTER TABLE table_name AUTO_INCREMENT = {$maxId} + 1");
}

public function down(Schema $schema): void
{
    $table = $schema->getTable('table_name');
    $table->dropColumn('new_id');
}

2. 修改实体类：
   添加newId属性并配置映射，同时在prePersist方法中保证新旧列值一致：

// src/Entity/YourEntity.php
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(name="table_name")
 */
class YourEntity
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private int $id;

    /**
     * @ORM\Column(type="integer", unsigned=true, nullable=false)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private int $newId;

    // ... 其他属性和方法

    /**
     * @ORM\PrePersist
     */
    public function prePersist(): void
    {
        // 新插入时同步新旧id值，保持双写一致性
        $this->id = $this->newId;
    }
}

部署代码后，新写入的数据会同时填充id和new_id，旧数据的new_id为NULL。

阶段2：分批同步历史数据 #

批量更新旧数据的new_id为id的值，必须分批执行以避免锁表。

生成迁移脚本：

public function up(Schema $schema): void
{
    $connection = $this->connection;
    $batchSize = 1000;
    $lastProcessedId = 0;

    do {
        // 每次更新1000条，按id排序避免遗漏
        $updatedRows = $connection->executeQuery(
            'UPDATE table_name SET new_id = id WHERE id > ? AND new_id IS NULL ORDER BY id LIMIT ?',
            [$lastProcessedId, $batchSize]
        )->rowCount();

        // 更新最后处理的id，循环直到所有数据同步完成
        $lastResult = $connection->executeQuery(
            'SELECT MAX(id) FROM table_name WHERE new_id IS NOT NULL AND id > ?',
            [$lastProcessedId]
        );
        $lastProcessedId = $lastResult->fetchOne() ?? 0;

        // 每次批量更新后短暂休眠，避免占用过多数据库资源
        usleep(50000); // 50ms
    } while ($updatedRows > 0);
}

public function down(Schema $schema): void
{
    // 回滚时清空new_id
    $this->connection->executeQuery('UPDATE table_name SET new_id = NULL');
}

执行此迁移，直到所有旧数据的new_id都被填充。

阶段3：切换主键与外键 #

完成数据同步后，将主键切换到新列，并处理关联表的外键（如果有）。

3.1 处理外键关联（若存在） #

如果有其他表通过外键关联到原id列，需同步执行以下操作：

• 给关联表添加INT UNSIGNED类型的新外键列（如new_your_entity_id）
• 分批同步关联表的新列值为旧外键列的值
• 切换外键约束到新列，删除旧外键列

3.2 切换主表主键 #

生成迁移脚本：

public function up(Schema $schema): void
{
    $table = $schema->getTable('table_name');
    // 移除旧主键约束和自增属性
    $table->dropPrimaryKey();
    $oldIdColumn = $table->getColumn('id');
    $oldIdColumn->setAutoincrement(false);

    // 设置new_id为主键并保留自增
    $newIdColumn = $table->getColumn('new_id');
    $newIdColumn->setAutoincrement(true);
    $table->addPrimaryKey(['new_id']);
}

public function down(Schema $schema): void
{
    $table = $schema->getTable('table_name');
    // 回滚主键到原id列
    $table->dropPrimaryKey();
    $oldIdColumn = $table->getColumn('id');
    $oldIdColumn->setAutoincrement(true);
    $table->addPrimaryKey(['id']);

    $newIdColumn = $table->getColumn('new_id');
    $newIdColumn->setAutoincrement(false);
}

3.3 更新实体类 #

将主键映射切换到newId，移除原id的主键注解：

// src/Entity/YourEntity.php
/**
 * @ORM\Id
 * @ORM\Column(type="integer", unsigned=true)
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private int $newId;

/**
 * @ORM\Column(type="integer", nullable=false)
 */
private int $id;

部署代码后，所有读写操作将使用新的new_id主键。

阶段4：清理旧列 #

确认所有业务已完全切换到新主键，且无代码依赖旧id列后，删除旧列并将new_id重命名为id。

生成迁移脚本：

public function up(Schema $schema): void
{
    $table = $schema->getTable('table_name');
    // 删除旧id列
    $table->dropColumn('id');
    // 将new_id重命名为id
    $table->getColumn('new_id')->setName('id');
}

public function down(Schema $schema): void
{
    $table = $schema->getTable('table_name');
    // 回滚时重新添加旧id列
    $table->addColumn('id', 'integer', [
        'autoincrement' => true,
        'notnull' => true,
    ]);
    $table->addPrimaryKey(['id']);
    // 将id重命名回new_id
    $table->getColumn('id')->setName('new_id');
}

更新实体类 #

将newId重命名为id，恢复原有映射：

// src/Entity/YourEntity.php
/**
 * @ORM\Id
 * @ORM\Column(type="integer", unsigned=true)
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private int $id;

核心注意事项 #

• 分批操作：所有批量更新必须分批次执行，避免长时间锁表影响业务。
• 双写一致性：双写阶段确保新数据的新旧列值一致，避免数据不一致。
• 多服务器同步：Doctrine迁移依赖版本控制，确保所有数据库服务器按顺序执行相同迁移步骤，建议用CI/CD自动部署。
• 测试验证：所有步骤必须先在测试环境验证，确认无锁、无数据丢失后再上线。

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