添加多列全文索引失败，出现1062个重复条目

Question

我正在尝试将全文索引添加到MySQL中的现有表中。我可以成功地在此表上添加单列全文索引，但如果我尝试添加多列索引，则会失败。我想添加一个多列全文索引，以便在SELECT查询中跨多列进行搜索。

以下架构：

CREATE TABLE `emailarchive_people` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`emailAddress` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `person` (`name`,`emailAddress`)
) ENGINE=InnoDB AUTO_INCREMENT=100230 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

以下单个索引创建查询有效：

ALTER TABLE
`emailarchive_people`
ADD
FULLTEXT INDEX `name` (`name`);

但是以下查询：

ALTER TABLE
`emailarchive_people`
ADD
FULLTEXT INDEX `personFT` (`name`,`emailAddress`);

失败，响应如下：

Error Code: 1062. Duplicate entry 'NULL-NULL' for key 'emailarchive_people.personFT'

在尝试添加personFT索引之前，我也尝试过删除唯一的person键，但我遇到了同样的错误。

Answer 1

这是MySQL中的一个错误：https://bugs.mysql.com/bug.php?id=109242

可能是因为您使用的是FULLTEXT INDEX，所以情况就是这样。

Answer 2

作为一种解决方法，您可以创建一个具有相同定义的新表，在此空表上创建多列全文搜索索引，然后从现有表中复制所有数据并切换它们。

CREATE TABLE `emailarchive_people_new` LIKE `emailarchive_people`;
ALTER TABLE `emailarchive_people_new` 
ADD FULLTEXT INDEX `personFT` (`name`,`emailAddress`);
INSERT INTO `emailarchive_people_new` SELECT * FROM `emailarchive_people`;
RENAME TABLE `emailarchive_people` TO `emailarchive_people_old`, 
`emailarchive_people_new` TO `emailarchive_people`;

注意：它可能会导致一些停机时间，并且在切换过程中添加的新数据可能会丢失。请确保有足够的磁盘存储空间用于表副本和索引。

Answer 3

我在应用多全文索引时遇到了完全相同的错误。

它只处理了少数数据行。10000以上时失败。

服务器版本：10.5.15-MariaDB-0+deb11u1 Debian 11

ALTER TABLE `customer`
ADD FULLTEXT INDEX `search` (`firstname`, `lastname`, `postcode`, `email`);

/*SQL Fehler(1062(：密钥"customer.key_paydate"的重复条目"NULL"*/

错误消息毫无意义，因为customer.key_paydate不是唯一的键，并且该列的任何条目都不是NULL

Answer 4

这件事也发生在我身上。

An exception occurred while executing 'CREATE FULLTEXT INDEX search_order_address ON shipping_order (pickup_address, delivery_address) WITH PARSER ngram':
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'shipping_order.search_order_address'

我搜索了一下，发现了一份错误报告，有全文优化的文档，还有一些关于这个错误的博客条目(这可能对我有用(。

所以我尝试的是OPTIMIZE TABLE(不确定是否需要，但以防万一(

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE shipping_order;

就在这个之后

ALTER TABLE shipping_order ENGINE=INNODB;

下次我运行时

CREATE FULLTEXT INDEX search_order_address ON shipping_order (pickup_address, delivery_address) WITH PARSER ngram;

它奏效了。

Answer 5

在我的例子中，我有一个包含250万条记录的表，对我来说有效的是在服务器conf文件中设置innodb-ddl-buffer-size=64M变量并重新启动服务器。这个变量似乎是动态的，所以你也可以在运行时更改它

SET GLOBAL innodb_ddl_buffer_size=67108864;

之后，我可以在几个列上添加一个FULLTEXT INDEX，而不会出现任何NULL值错误。