ob4.3.5 全文索引 支持模糊查询吗

OceanBase 技术问题
#1

【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】
【 使用版本 】V4.3.5
【问题描述】ob4.3.5 全文索引 支持模糊查询吗
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

1 个赞
#3

根据文档库中的信息，在OceanBase数据库MySQL模式下创建的全文索引是支持一定形式的模糊查询的。但是需要注意的是，这里的“模糊查询”主要是指通过自然语言搜索、布尔模式匹配等方式实现的查询，而不是像LIKE '%text%'这样的SQL模式匹配。

对于具体的模糊查询支持情况，请参考以下几点：

  1. 自然语言搜索：这是最常用的全文检索方式，它会根据文本的相关性返回结果。可以使用MATCH() ... AGAINST()语法进行查询。
  2. 布尔模式：允许更精确地控制哪些词必须存在，哪些不应该出现等。同样也是通过MATCH() ... AGAINST()来指定，并且在AGAINST子句中加入布尔模式标识符。

例如，如果你有一个包含全文索引的表articles和字段body，你可以这样执行模糊查询（基于自然语言或布尔模式）：

-- 自然语言搜索
SELECT * FROM articles WHERE MATCH(body) AGAINST ('database');

-- 布尔模式搜索
SELECT * FROM articles WHERE MATCH(body) AGAINST ('+OceanBase -MySQL' IN BOOLEAN MODE);

以上内容依据的是《创建全文索引》以及《全文查询》章节。

请注意，虽然这些方法提供了强大的文本搜索能力，但它们并不直接支持传统的SQL LIKE操作符来进行前缀、后缀或者中间字符串匹配的模糊查找。如果需要类似的功能，可能需要结合其他技术手段，如正则表达式或者额外的应用层处理。

如果您想了解更多关于全文索引的具体用法和限制，建议查阅官方文档中有关全文索引的部分。

详情请参考:

  1. 向量检索常见问题
  2. OceanBase 数据库和 MySQL 兼容性介绍
  3. OceanBase 数据库在 MySQL 模式租户下的扩展功能
  4. 表扫描方式规范
  5. OceanBase 术语
  6. 创建索引
  7. 系统视图总览
  8. 0001 ~ 3999
  9. 通过 Liquibase 管理 OcenBase 数据库版本
  10. 3.x 与 4.x 视图变更
1 个赞
#5

你可以看看 官方文档 有解释

MATCH AGAINST

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002017607

1 个赞
#6

我试了下， 某一列（数据是中英文混合）创建了全文索引，使用 match against，搜索的字符串表里也有，但是实际会查不到。 我看文档 是只要存在字符串就能匹配出来 @论坛小助手

1 个赞
#7

根据您提供的信息，创建了全文索引并且使用 MATCH() ... AGAINST() 进行查询时遇到了问题：即使搜索的字符串确实存在于列中，查询结果仍然为空。这种情况可能由多个因素引起。以下是一些常见的原因及解决方案：

1. 分词器配置

OceanBase 的全文索引依赖于分词器来解析文本内容。对于中英文混合的数据，确保使用了正确的分词器非常重要。默认情况下，MySQL 模式的全文索引可能只支持英文分词。对于中文支持，通常需要配置或安装额外的分词插件（如 ngram 或 meCab）。

  • 检查当前使用的分词器：

    SHOW VARIABLES LIKE 'ft_parser';

  • 如果未启用适合中文的分词器，可以尝试更改分词器设置或者安装新的分词插件。例如，使用 ngram 分词器：

    ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram;

2. 最小单词长度限制

默认情况下，MySQL 全文索引有一个最小单词长度限制（通常是 3 或 4 个字符）。这意味着较短的单词可能会被忽略。对于中英文混合的情况，这可能导致一些有效词汇无法正确索引。

  • 查看当前最小单词长度设置：

    SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

  • 调整最小单词长度（如果必要）：

    SET GLOBAL innodb_ft_min_token_size = 2; -- 根据需求调整值

3. 停用词表

某些常见词汇（如“the”、“is”等）会被认为是停用词，并从索引中排除。确保您的目标词汇不是停用词之一。

  • 查看当前停用词列表：

    SHOW VARIABLES LIKE 'innodb_ft_default_stopword';

  • 自定义停用词表（如果需要）：

    CREATE TABLE my_stopwords(word VARCHAR(30));
INSERT INTO my_stopwords VALUES ('example'), ('words');
ALTER TABLE your_table DROP INDEX idx_your_column, ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram INVISIBLE;

4. 数据同步与索引重建

有时，即使已经创建了全文索引，新插入的数据可能不会立即反映在索引中。确保所有相关数据都已同步到索引。

  • 重建全文索引：
    ALTER TABLE your_table DROP INDEX idx_your_column;
ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column);

5. 测试查询

为了验证是否为上述原因之一，您可以尝试以下几种方式：

  • 使用布尔模式进行精确匹配：

    SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('"+specific_word"' IN BOOLEAN MODE);

  • 检查特定记录的内容：

    SELECT your_column FROM your_table WHERE id = specific_id;

通过以上步骤，应该能够帮助诊断并解决 MATCH() ... AGAINST() 查询不返回预期结果的问题。如果问题依然存在，请提供更多的上下文信息，包括具体的 SQL 语句、样本数据以及任何相关的配置参数，以便进一步分析。

详情请参考:

  1. 9500 ~ 9999
  2. 0001 ~ 3999
  3. 阅读和管理 OceanBase 数据库 SQL 执行计划
  4. 5000 ~ 5999
  5. SQL FAQ
  6. 4000 ~ 4499
1 个赞
#8

全文索引和全文搜索 比如数据库里面是 有一行得全文索引字段是‘ 阿里12345’，然后我用match against搜索 ‘1234’ ，是不是能搜索到 阿里12345 这行。如果搜索不到，大概是什么问题

1 个赞
#9

根据您提供的信息，使用 MATCH() … AGAINST() 搜索部分数字字符串（如 ‘1234’）时，确实可能会遇到无法匹配到包含该子串的记录（例如 ‘阿里12345’）。这种情况可能由以下几个原因引起：

1. 分词器配置

OceanBase 的全文索引依赖于分词器来解析文本内容。对于中英文混合的数据，尤其是包含数字的情况，默认分词器可能不会将连续的数字视为独立的词汇，而是作为一个整体处理。

解决方案：

  • 确保使用了适合中英文混合数据的分词器，如 ngram 或其他支持中文和数字的分词器。
  • 使用 ngram 分词器可以更好地处理短语和数字组合。

检查当前使用的分词器：

SHOW VARIABLES LIKE 'ft_parser';

更改分词器设置：

ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram;

2. 最小单词长度限制

默认情况下，MySQL 全文索引有一个最小单词长度限制（通常是 3 或 4 个字符）。这意味着较短的单词或数字可能会被忽略。

查看当前最小单词长度设置：

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

调整最小单词长度（如果必要）：

SET GLOBAL innodb_ft_min_token_size = 2; -- 根据需求调整值

3. 停用词表

某些常见词汇（如“the”、“is”等）会被认为是停用词，并从索引中排除。虽然数字通常不会被视为停用词，但确保您的目标词汇不是停用词之一仍然是重要的。

查看当前停用词列表：

SHOW VARIABLES LIKE 'innodb_ft_default_stopword';

自定义停用词表（如果需要）：

CREATE TABLE my_stopwords(word VARCHAR(30));
INSERT INTO my_stopwords VALUES ('example'), ('words');
ALTER TABLE your_table DROP INDEX idx_your_column, ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram INVISIBLE;

4. 布尔模式搜索

尝试使用布尔模式进行精确匹配，这可以提高匹配的准确性。

示例查询：

SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('"+1234"' IN BOOLEAN MODE);

5. 测试查询

为了验证是否为上述原因之一，您可以尝试以下几种方式：

  • 使用布尔模式进行精确匹配：

    SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('"+1234"' IN BOOLEAN MODE);

  • 检查特定记录的内容：

    SELECT your_column FROM your_table WHERE id = specific_id;

6. 正则表达式或 LIKE 查询

如果全文索引无法满足需求，可以考虑使用正则表达式或 LIKE 查询作为替代方案。请注意，这些方法在大数据集上可能会影响性能。

示例查询：

SELECT * FROM your_table WHERE your_column LIKE '%1234%';

总结

为了确保能够正确匹配包含部分数字字符串的记录，建议首先检查并调整分词器配置和最小单词长度设置。如果问题仍然存在，可以尝试使用布尔模式进行更精确的匹配，或者考虑使用正则表达式或 LIKE 查询作为替代方案。

如果您能提供更多具体的 SQL 语句、样本数据以及任何相关的配置参数，将进一步有助于诊断和解决问题。

更多信息请查看:

  1. 0001 ~ 3999
  2. OceanBase 数据库和 MySQL 兼容性介绍
1 个赞
#10

现在V4.3.5 全文索引 支持 使用布尔模式 进行全文搜索吗？@论坛小助手

1 个赞
#11

根据文档库中的信息，OceanBase 数据库 V4.3.5 在 MySQL 模式下是支持使用布尔模式进行全文搜索的。具体来说，您可以使用 MATCH() ... AGAINST() 语法，并在 AGAINST 子句中指定 IN BOOLEAN MODE 来启用布尔模式查询。

以下是关于如何在 OceanBase V4.3.5 中使用布尔模式进行全文搜索的具体步骤和注意事项：

使用布尔模式进行全文搜索

  1. 确保已创建全文索引

    首先，确保您的表中已经为需要全文搜索的列创建了全文索引。例如：

    ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column);

  2. 使用布尔模式查询

    使用 MATCH() ... AGAINST() 语法并指定 IN BOOLEAN MODE。例如：

    SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('"+1234"' IN BOOLEAN MODE);

    这里的 "+1234" 表示必须包含字符串 “1234” 的记录。

  3. 分词器配置

    确保使用了适合中英文混合数据的分词器，如 ngram 或其他支持中文和数字的分词器。这可以通过以下命令检查当前使用的分词器：

    SHOW VARIABLES LIKE 'ft_parser';

    如果需要更改分词器设置，可以使用如下命令：

    ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram;

  4. 最小单词长度限制

    默认情况下，MySQL 全文索引有一个最小单词长度限制（通常是 3 或 4 个字符）。这意味着较短的单词或数字可能会被忽略。您可以通过以下命令查看当前最小单词长度设置：

    SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

    如果需要调整最小单词长度，可以执行以下命令：

    SET GLOBAL innodb_ft_min_token_size = 2; -- 根据需求调整值

  5. 停用词表

    确保您的目标词汇不是停用词之一。查看当前停用词列表：

    SHOW VARIABLES LIKE 'innodb_ft_default_stopword';

    如果需要自定义停用词表，可以创建一个新表并插入停用词，然后重新创建全文索引：

    CREATE TABLE my_stopwords(word VARCHAR(30));
INSERT INTO my_stopwords VALUES ('example'), ('words');
ALTER TABLE your_table DROP INDEX idx_your_column, ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram INVISIBLE;

示例查询

假设您有一个名为 articles 的表，其中有一列 content 包含中英文混合的数据，并且您希望搜索包含 “阿里12345” 的记录：

-- 创建全文索引（如果尚未创建）
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content) WITH PARSER ngram;

-- 使用布尔模式进行精确匹配
SELECT * FROM articles WHERE MATCH(content) AGAINST('"+阿里12345"' IN BOOLEAN MODE);

-- 或者更灵活的匹配方式
SELECT * FROM articles WHERE MATCH(content) AGAINST('阿里 +1234' IN BOOLEAN MODE);

通过上述步骤，您应该能够在 OceanBase V4.3.5 中成功使用布尔模式进行全文搜索。如果有任何进一步的问题或需要更多帮助，请随时告知。

检索到的文档如下:

  1. 向量检索常见问题
  2. 大对象和文本类型概述
  3. 系统视图总览
  4. 通过 Liquibase 管理 OcenBase 数据库版本
  5. 3.x 与 4.x 视图变更
  6. 分页查询
  7. 兼容模式
  8. 物理备库
  9. OceanBase 术语
1 个赞
#12

应该和分词器有关系 目前ob用的应该是二元分词 你可以尝试都是英文或者都是中文或着都是数字

#13

我试了 NGRAM是可以支持中文。但是用TOKENIZE 看了，只能分2个字符得，我想要分最少比如3个或者4个这种

#14

根据您的描述，您希望使用 ngram 分词器时能够调整最小分词长度（即 n-gram 的大小），以便支持更长的字符组合。默认情况下，ngram 分词器在 OceanBase 中确实会将文本分割成两个字符的片段（bigram）。要调整这个行为，您可以修改 ngram 分词器的相关配置参数。

调整 ngram 分词器的最小和最大 n-gram 长度

OceanBase 支持通过配置项来调整 ngram 分词器的行为，具体来说是设置最小和最大 n-gram 长度。以下是具体步骤：

  1. 查看当前的 ngram 分词器配置

    您可以使用以下命令查看当前的 ngram 分词器配置：

    SHOW VARIABLES LIKE 'ngram_token_size';

  2. 调整 ngram 分词器的最小和最大 n-gram 长度

    • 全局设置：如果您有管理员权限，可以通过修改系统变量来全局调整 ngram 分词器的最小和最大 n-gram 长度。

      SET GLOBAL ngram_token_size = 3; -- 设置为三元组 (trigram)

    • 会话级别设置：如果您没有全局修改权限，可以在会话级别临时调整这些设置。

      SET SESSION ngram_token_size = 3; -- 设置为三元组 (trigram)

    注意：ngram_token_size 是一个全局或会话级别的变量，它控制了 ngram 分词器生成的 n-gram 的最小长度。例如，设置为 3 表示每个 n-gram 将包含至少 3 个字符。

  3. 重建全文索引

    修改 ngram_token_size 后，需要重新创建或重建全文索引以使更改生效。假设您的表名为 your_table，列名为 your_column，可以执行以下命令：

    ALTER TABLE your_table DROP INDEX idx_your_column;
ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram;

  4. 验证分词效果

    使用 TOKENIZE 函数验证新的分词效果。例如：

    SELECT TOKENIZE('阿里12345', 'ngram') AS tokens;

示例

假设您有一个表 articles，其中有一列 content 包含中英文混合的数据，并且您希望搜索包含 “阿里12345” 的记录，同时确保分词器生成至少 3 个字符的 n-gram：

-- 查看当前 ngram_token_size 设置
SHOW VARIABLES LIKE 'ngram_token_size';

-- 设置 ngram_token_size 为 3
SET GLOBAL ngram_token_size = 3;

-- 重建全文索引
ALTER TABLE articles DROP INDEX idx_content;
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content) WITH PARSER ngram;

-- 验证分词效果
SELECT TOKENIZE('阿里12345', 'ngram') AS tokens;

-- 使用布尔模式进行精确匹配
SELECT * FROM articles WHERE MATCH(content) AGAINST('"+阿里123"' IN BOOLEAN MODE);

注意事项

  • 性能影响：增加 n-gram 的长度可能会对索引大小和查询性能产生影响，请根据实际需求权衡。
  • 数据同步：确保所有相关数据都已同步到新的索引中，特别是在生产环境中进行此类更改时要格外小心。

通过上述步骤，您应该能够调整 ngram 分词器的最小分词长度，从而更好地支持中文和数字组合的全文搜索需求。如果有任何进一步的问题或需要更多帮助，请随时告知。

检索到的文档如下:

  1. OceanBase 数据库和 MySQL 兼容性介绍
  2. 使用 OceanBase 数据库分区表进行水平拆分
  3. 使用 MySQL 租户做常见数据库开发
  4. collation_server

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

#15

@论坛小助手

我刚刚用ODC试了用布尔模式 全文搜索。 会报错不支持。文档里我看了，写着目前只支持 自然语言搜索模式。

还有个问题：我用NGRAM分词器，我想设置最小分词为3个字符。没找到方法。

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

你写的这个’innodb_ft_min_token_size’; 数据库里找不到环境变量

#16

感谢您的反馈。根据您提供的信息，确实存在一些特定于 OceanBase 社区版 V4.3.5 的限制和配置问题。让我们逐一解决这些问题。

1. 布尔模式不支持

根据您提到的文档内容，OceanBase 社区版 V4.3.5 目前仅支持自然语言搜索模式（Natural Language Search Mode），而不支持布尔模式（Boolean Mode）。因此，尝试使用布尔模式进行全文搜索时会报错。

解决方案：

  • 使用自然语言搜索模式：您可以继续使用自然语言搜索模式来进行全文搜索。例如：

    SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('1234');

  • 替代方案：如果需要更复杂的查询逻辑，可以考虑使用 LIKE 或正则表达式（虽然这些方法在大数据集上可能会影响性能）：

    -- 使用 LIKE 进行模糊匹配
SELECT * FROM your_table WHERE your_column LIKE '%1234%';

-- 使用正则表达式进行匹配
SELECT * FROM your_table WHERE your_column REGEXP '1234';

2. 设置 ngram 分词器最小分词长度

关于设置 ngram 分词器的最小分词长度，您提到的 innodb_ft_min_token_size 确实不是适用于 OceanBase 的变量。对于 ngram 分词器，应该使用 ngram_token_size 来控制最小和最大 n-gram 长度。

解决方案：

  1. 查看当前 ngram 分词器配置：

    SHOW VARIABLES LIKE 'ngram_token_size';

  2. 调整 ngram 分词器的最小 n-gram 长度：

    • 全局设置（如果您有管理员权限）：
      SET GLOBAL ngram_token_size = 3; -- 设置为三元组 (trigram)
    • 会话级别设置（如果您没有全局修改权限）：
      SET SESSION ngram_token_size = 3; -- 设置为三元组 (trigram)

  3. 重建全文索引：
    修改 ngram_token_size 后，需要重新创建或重建全文索引来使更改生效。假设您的表名为 your_table，列名为 your_column，可以执行以下命令：

    ALTER TABLE your_table DROP INDEX idx_your_column;
ALTER TABLE your_table ADD FULLTEXT INDEX idx_your_column (your_column) WITH PARSER ngram;

  4. 验证分词效果：
    使用 TOKENIZE 函数验证新的分词效果。例如：

    SELECT TOKENIZE('阿里12345', 'ngram') AS tokens;

示例

假设您有一个表 articles，其中有一列 content 包含中英文混合的数据，并且您希望搜索包含 “阿里12345” 的记录，同时确保分词器生成至少 3 个字符的 n-gram：

-- 查看当前 ngram_token_size 设置
SHOW VARIABLES LIKE 'ngram_token_size';

-- 设置 ngram_token_size 为 3
SET GLOBAL ngram_token_size = 3;

-- 重建全文索引
ALTER TABLE articles DROP INDEX idx_content;
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content) WITH PARSER ngram;

-- 验证分词效果
SELECT TOKENIZE('阿里12345', 'ngram') AS tokens;

-- 使用自然语言搜索模式进行匹配
SELECT * FROM articles WHERE MATCH(content) AGAINST('1234');

总结

  1. 布尔模式不支持：目前 OceanBase 社区版 V4.3.5 只支持自然语言搜索模式，建议使用自然语言搜索模式或替代方案如 LIKE 和正则表达式。
  2. ngram 分词器配置：使用 ngram_token_size 而不是 innodb_ft_min_token_size 来调整 ngram 分词器的最小分词长度，并确保重建全文索引以使更改生效。

如果有任何进一步的问题或需要更多帮助，请随时告知。

检索到的文档如下:

  1. ODC V4.2.3
  2. ODC V4.2.4
  3. ODC V4.1.1
  4. ODC V4.3.0
  5. ODC V4.0.2
  6. ODC V4.1.3
  7. ODC V4.0.0
  8. 逻辑库变更管理
  9. 使用限制
#17

目前不支持分词长度的调整 预计ob435bp1支持 等待版本发布 会有说明

#19

收到。那分词长度可以自定义后，布尔模式是不是也能同步支持了？

#20

等待新版本发布吧 会有说明 如果说明没有 就是不支持 目前没有办法给你准确答案

#21

同问