MySQL添加联合全文索引遇重复键错误，多次尝试后成功原因排查

报错原因 #

1. MySQL全文索引的“空文档”判定逻辑 #

MySQL全文索引会将完全由停止词组成的字段内容视为“空文档”，等价于NULL。即便表中没有NULL或空字符串，若存在多行的name/body全是停止词（如"the"、"and"这类高频无意义词汇），联合索引会把这些行的索引条目判定为NULL-NULL，触发重复键冲突报错。

2. Rails迁移的事务限制 #

Rails 6.1默认在事务中执行迁移，而MySQL的全文索引创建操作在事务环境下存在兼容性问题——事务的原子性会放大索引构建时的冲突，导致报错后直接回滚；而直接在控制台执行ALTER TABLE时无事务包裹，多次尝试后MySQL可能跳过临时冲突或完成索引构建。

解决方案 #

方案1：禁用迁移事务并使用即时DDL #

在迁移文件中添加disable_ddl_transaction!规避事务影响，同时指定algorithm: :instant利用MySQL 8.0的即时DDL特性：

class AddFulltextIndexesToMyModel < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!

  def change
    add_index :my_model_table_name, [:name], type: :fulltext, algorithm: :instant
    add_index :my_model_table_name, [:body], type: :fulltext, algorithm: :instant
    add_index :my_model_table_name, [:name, :body], type: :fulltext, algorithm: :instant
  end
end

方案2：清理“空文档”数据 #

先定位被MySQL判定为“空文档”的行，修改内容确保包含有效词汇：

-- 查询符合条件的行
SELECT * FROM my_model_table_name 
WHERE MATCH(name) AGAINST('' IN BOOLEAN MODE) 
OR MATCH(body) AGAINST('' IN BOOLEAN MODE);

更新这些行的name或body字段，加入至少一个非停止词的内容后，再执行迁移或创建索引。

方案3：拆分迁移任务 #

将联合全文索引的创建单独放在独立迁移文件中，分开执行避免事务冲突：

1. 第一个迁移创建单个字段的全文索引：

class AddSingleFieldFulltextIndexes < ActiveRecord::Migration[6.1]
  def change
    add_index :my_model_table_name, [:name], type: :fulltext
    add_index :my_model_table_name, [:body], type: :fulltext
  end
end

2. 第二个迁移创建联合全文索引：

class AddCombinedFulltextIndex < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!

  def change
    add_index :my_model_table_name, [:name, :body], type: :fulltext, algorithm: :instant
  end
end

关于多次执行ALTER TABLE成功的说明 #

多次手动执行成功是因为无事务包裹时，MySQL在索引创建过程中可能自动跳过或处理了临时的重复条目冲突，但这属于不可靠的临时现象，不能作为常规解决方案。

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