全文索引与虚拟列索引需要注意的事项

在 MySQL 中，全文索引 (FULLTEXT) 与 UNION ALL 可以一起使用，但有一些限制和注意事项。

限制与问题：

1. 全文索引不能直接在 UNION ALL 中使用：MySQL 不允许在多个查询的 UNION 或 UNION ALL 操作中使用 MATCH ... AGAINST，因为每个 SELECT 语句都是独立处理的。如果你想在多个表上进行全文搜索，并且想使用 UNION ALL，全文搜索需要在各自的查询中独立进行。

2. MATCH ... AGAINST 的限制：当你在 UNION ALL 中使用时，MySQL 不能在 UNION 操作后再应用 MATCH，这意味着你不能对组合后的结果进行全文搜索。

解决方案：

要解决这个问题，通常有两种做法：

方法 1: 在每个 SELECT 子查询中单独使用 MATCH ... AGAINST

你可以在每个表的查询中分别使用 MATCH ... AGAINST，而不是对整个 UNION ALL 的结果进行全文搜索。

示例：

sql

CREATE TEMPORARY TABLE temp_results AS
SELECT p1.*
FROM cdr_voice_202409_0 AS p1
WHERE LENGTH(p1.calling_number) < 11
UNION ALL
SELECT p1.*
FROM cdr_voice_202408_0 AS p1
WHERE LENGTH(p1.calling_number) < 11
UNION ALL
SELECT p1.*
FROM cdr_voice_202407_0 AS p1
WHERE LENGTH(p1.calling_number) < 11;

在此方法中，每个表的查询单独使用全文索引进行搜索，MATCH ... AGAINST 会分别作用于每个表的数据。

方法 2: 使用临时表

如果你需要对多个表的结果进行全文搜索，可以将结果存储在临时表中，然后对该临时表进行 MATCH ... AGAINST 操作。虽然这稍微复杂一些，但在某些场景下能提高查询的灵活性。

示例：

1. 首先将查询结果插入到临时表中：

   sql

   CREATE TEMPORARY TABLE temp_results AS S
   ELECT p1.* FROM 
   cdr_voice_202409_0 AS p1 
   WHERE LENGTH(p1.calling_number) < 11 
   UNION ALL 
   SELECT p1.* FROM 
   cdr_voice_202408_0 AS p1 
   WHERE LENGTH(p1.calling_number) < 11 
   UNION ALL 
   SELECT p1.* 
   FROM cdr_voice_202407_0 AS p1 
   WHERE LENGTH(p1.calling_number) < 11;

2. 然后对临时表使用全文索引：

   SELECT * FROM temp_results WHERE MATCH(calling_number) AGAINST('10086') LIMIT 1000 OFFSET 0;

   在数据库中添加一个虚拟列（例如 MySQL 的 VIRTUAL 生成列）后，这个列通常不会自动出现在 Java 或其他编程语言中的实体类中，尤其是在使用 ORM（如 Hibernate 或 MyBatis）时。实体类中的字段是基于数据库表中物理存在的列定义的，而虚拟列可能只在数据库计算时存在，不会自动映射到实体类中。

总结：

• 全文索引不能跨 UNION ALL 直接使用，你需要在每个 SELECT 中分别使用 MATCH ... AGAINST。
• 如果需要对合并后的结果进行全文搜索，可以通过临时表来实现。