- bounty还有1小时到期此问题的答案有资格获得+50声望奖励。MrCujo希望引起更多关注**这个问题。

我使用MySQL 8，我有一个名为records的表，我为它添加了以下索引，以便在其上执行文本搜索：

CREATE FULLTEXT INDEX all_records_idx ON records (`title`, `label`, `description`, `catalog_number`,`barcode`);

现在，我得到了以下数据（复制为JSON）：

[
  {
    "barcode": "79027066021",
    "title": "White Blood Cells",
    "slug": "White-blood-cells",
    "image": null,
    "description": "rev",
    "artist_id": 3,
    "genre_id": 1,
    "num_of_records": 1,
    "original_release_year": 2001,
    "original_release_date": null,
    "reissue_release_date": null,
    "label": null,
    "sleeve_side_color": "ff1500",
    "vinyl_color": "ff1500",
    "size": 12,
    "barcode_in_record": null,
    "catalog_number": null,
    "owned_copies": 1,
    "active": 1,
    "created_at": "2023-06-11 21:32:17.000",
    "updated_at": null,
    "deleted_at": null
  },
  {
    "barcode": "889854036119",
    "title": "Nilsson Schmilsson",
    "slug": "nilsson-schmilsson",
    "image": null,
    "description": null,
    "artist_id": 6,
    "genre_id": 1,
    "num_of_records": 1,
    "original_release_year": 1971,
    "original_release_date": null,
    "reissue_release_date": null,
    "label": null,
    "sleeve_side_color": "490d57",
    "vinyl_color": "000000",
    "size": 12,
    "barcode_in_record": null,
    "catalog_number": null,
    "owned_copies": 1,
    "active": 1,
    "created_at": "2023-06-11 21:32:17.000",
    "updated_at": null,
    "deleted_at": null
  },
  {
    "barcode": "C10777774644617",
    "title": "Abbey Road",
    "slug": "abbey-road",
    "image": null,
    "description": null,
    "artist_id": 2,
    "genre_id": 1,
    "num_of_records": 1,
    "original_release_year": 1969,
    "original_release_date": null,
    "reissue_release_date": null,
    "label": null,
    "sleeve_side_color": "46518f",
    "vinyl_color": "000000",
    "size": 12,
    "barcode_in_record": null,
    "catalog_number": null,
    "owned_copies": 1,
    "active": 1,
    "created_at": "2023-06-11 21:32:17.000",
    "updated_at": null,
    "deleted_at": null
  },
  {
    "barcode": "C10777774644618",
    "title": "Revolver",
    "slug": "revolver",
    "image": null,
    "description": null,
    "artist_id": 2,
    "genre_id": 1,
    "num_of_records": 1,
    "original_release_year": 1966,
    "original_release_date": null,
    "reissue_release_date": null,
    "label": null,
    "sleeve_side_color": "46518f",
    "vinyl_color": "000000",
    "size": 12,
    "barcode_in_record": null,
    "catalog_number": null,
    "owned_copies": 1,
    "active": 1,
    "created_at": "2023-06-11 21:32:17.000",
    "updated_at": null,
    "deleted_at": null
  },
  {
    "barcode": "C10777774644619",
    "title": "arevolat",
    "slug": "aurevolat",
    "image": null,
    "description": "this is a test",
    "artist_id": 2,
    "genre_id": 1,
    "num_of_records": 1,
    "original_release_year": 1999,
    "original_release_date": null,
    "reissue_release_date": null,
    "label": null,
    "sleeve_side_color": null,
    "vinyl_color": null,
    "size": 12,
    "barcode_in_record": null,
    "catalog_number": null,
    "owned_copies": 1,
    "active": 1,
    "created_at": null,
    "updated_at": null,
    "deleted_at": null
  }
]

因此，表中目前有5条记录。为了测试搜索功能，我运行了以下查询：

SELECT * FROM records WHERE MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`)
AGAINST ('*rev*' IN BOOLEAN MODE)

我希望能得到三个记录回来：

条形码79027066021为1，应与描述“description”匹配："牧师"
条形码C10777774644618为1，应与标题“title”匹配：左轮手枪
条形码C10777774644619为1，应与标题“title”匹配："arevolat"。

但我只能拿回前两张唱片

条形码79027066021为1，应与描述“description”匹配："牧师"
条形码C10777774644618为1，应与标题“title”匹配：左轮手枪

这一个不存在：

条形码C10777774644619为1，应与标题“title”匹配："arevolat"。

即使我使用*作为搜索的一部分。
DDL：

CREATE TABLE `records` (
  `barcode` varchar(48) NOT NULL,
  `title` varchar(160) NOT NULL,
  `slug` varchar(250) NOT NULL,
  `image` varchar(191) DEFAULT NULL,
  `artist_id` bigint unsigned DEFAULT NULL,
  `genre_id` bigint unsigned DEFAULT NULL,
  `num_of_records` bigint NOT NULL,
  `original_release_date` date DEFAULT NULL,
  `reissue_release_date` date DEFAULT NULL,
  `label` longtext,
  `sleeve_side_color` varchar(191) DEFAULT NULL,
  `vinyl_color` longtext,
  `size` bigint DEFAULT NULL,
  `barcode_in_record` tinyint(1) DEFAULT NULL,
  `catalog_number` longtext,
  `owned_copies` bigint DEFAULT '1',
  `active` tinyint(1) DEFAULT '1',
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `original_release_year` bigint NOT NULL,
  `description` text,
  PRIMARY KEY (`barcode`),
  UNIQUE KEY `barcode` (`barcode`),
  UNIQUE KEY `slug` (`slug`),
  KEY `fk_records_artist` (`artist_id`),
  KEY `fk_records_genre` (`genre_id`),
  FULLTEXT KEY `all_records_idx` (`title`,`label`,`description`,`catalog_number`,`barcode`),
  CONSTRAINT `fk_records_artist` FOREIGN KEY (`artist_id`) REFERENCES `artists` (`id`),
  CONSTRAINT `fk_records_genre` FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

知道发生什么事了吗
谢谢

3条答案

按热度按时间

zfycwa2u1#

“%”、“*”和其他标点符号对于AGAINST、LIKE和RLIKE具有特定且不同的含义。

MATCH(title, ...) AGAINST('*rev*')  -- ignores the leading punctuation mark
title LIKE '%rev%'  -- finds the desired 3 rows, but only in `title`; and slow
title RLIKE 'rev'  -- finds the desired 3 rows, but only in `title`; and slow
title LIKE '%rev%' OR label LIKE '%rev%' OR ... -- even slower

在适当的情况下，FULLTEXT几乎总是比LIKE或RLIKE快;通常是“更快”。我建议您更改要求和/或期望。正如您从上面看到的，没有既简单又快速的语法。

赞(0）回复(0）举报 2023-06-21

mec1mxoz2#

在MySQL中，通配符是%而不是*。
试试AGAINST ('%rev%' IN BOOLEAN MODE)
有时，*充当修饰符，以匹配“它前面的事物的零个或多个示例”。
参见https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
但是，上面的模式匹配适用于扩展的正则表达式（例如使用RLIKE时）。
对于布尔全文搜索，*是截断运算符。表达式末尾的 * 表示rev必须出现在一个或多个单词的开头。这解释了结果中缺少记录的原因。
我怀疑第一个*被忽略了。
更多信息：https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

pxiryf3j3#

12.9.5全文限制

“%”字符不是全文搜索所支持的通配符。
https://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html
星号 *
星号用作截断（或通配符）运算符。与其他运算符不同，它被附加到要受影响的单词后面。如果单词以 * 运算符前面的单词开始，则匹配。[...]
通配符被认为是一个前缀，必须出现在一个或多个单词的开头。

以下示例演示了一些使用布尔全文运算符的搜索字符串：[...]

'apple*'
查找包含“apple”、“apples”、“applesauce”或“applet”等单词的行。
https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

MySQL全文搜索未返回所有匹配结果

3条答案

相关问题

热门标签

最新问答