大表添加全文索引报错 Duplicate entry 'NULL-NULL'

前言

遇到一个情况，对一个表添加全文索引报错，而且这个重复值不太正常：

ERROR 1062 (HY000): Duplicate entry 'NULL-NULL' for key 'test.t1' 

看了一下基本原理 InnoDB Full-Text Indexes，好像并没有会出现这种情况的状况描述，那这大概率就是 BUG 了。

一顿查找发现还真有 https://bugs.mysql.com/bug.php?id=109242

里面描述了，概率会出现这种情况，MySQL 在构建多列 FULLTEXT 索引的内部流程中存在已知 BUG（#BUG 109242），会在生成内部文档标识（FTS_DOC_ID）时产生重复值（如 NULL-NULL
），因而违反唯一性约束，导致 ERROR 1062 (HY000): Duplicate entry 'NULL-NULL'
 报错

但是在 MySQL Server 8.0.35 版本中修复了。InnoDB：创建跨多列的全文索引时，索引创建可能会失败，并出现类似“ERROR 1062 (23000): 重复条目‘NULL-NULL’”的错误。 （BUG #109242、BUG #33542939、BUG #34846823）

临时解决方案

虽然已经修复了，但是需要升级也比较麻烦，所以我提供一些已经测试过的临时方案：

1. 复制表结构：

   CREATE TABLE `t1_new` LIKE `t1`;
   
   

2. 在空表上添加多列全文索引：

   ALTER TABLE `t1_new`
     ADD FULLTEXT INDEX `idx_name` (`name`,`info`)  /*!50100 WITH PARSER `ngram` */ ; ;
   
   

3. 导入数据并切换表名：

   INSERT INTO`t1_new`
   SELECT * FROM`t1`;
   RENAMETABLE
   `t1`TO`t1_old`,
   `t1_new`TO`t1`;
   #可选 drop table t1_old;
   
   

原理就是在没有数据的时候创建索引就捕获出现 FTS_DOC_ID 冲突问题

参考

https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html

https://bugs.mysql.com/bug.php?id=109242