如何在MySQL的WITH子句结果上使用MATCH/AGAINST？

问题原因 #

WITH子句生成的CTE（公共表表达式）本质是临时结果集，MySQL的临时表不支持全文索引，因此直接在CTE结果上执行MATCH/AGAINST会触发[HY000][1214] The used table type doesn't support FULLTEXT indexes错误。而原表本身带有全文索引，所以直接查询原表时能正常执行。

可行解决方法 #

• 优先在原表执行全文检索，再用CTE处理结果
  将MATCH/AGAINST逻辑嵌入WITH子句的子查询中，先在有全文索引的原表上完成过滤，再基于过滤后的结果进行后续操作。示例代码：

  WITH filtered_data AS (
    SELECT stuff, somefield 
    FROM somewhere
    WHERE MATCH(somefield) AGAINST(' +"term_a" +"term_b"' IN BOOLEAN MODE)
  )
  SELECT * FROM filtered_data;
  

  这种方式避开了临时表不支持全文索引的限制，同时保留CTE的语法优势。

• 将CTE结果存入临时表并创建全文索引（MySQL 8.0.19+适用）
  若必须先生成全量CTE结果再做全文检索，可借助临时表实现：

  1. 将CTE结果插入临时表
  2. 给临时表的目标字段创建全文索引
  3. 对临时表执行全文检索
     示例代码：

  -- 创建临时表并插入数据
  CREATE TEMPORARY TABLE temp_data
  SELECT stuff, somefield FROM somewhere;
  
  -- 创建全文索引
  CREATE FULLTEXT INDEX idx_temp_somefield ON temp_data(somefield);
  
  -- 执行全文检索查询
  SELECT * FROM temp_data
  WHERE MATCH(somefield) AGAINST(' +"term_a" +"term_b"' IN BOOLEAN MODE);
  

  注意：仅InnoDB引擎的临时表支持全文索引（需MySQL 8.0.19及以上版本），且临时表会在会话结束后自动销毁。

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