《MySQL必知必会》学习笔记 5 组合查询与全文搜索

核心观点：组合查询和全文搜索是MySQL进阶用法。

组合查询（UNION/UNIONALL） UNION自动去重，UNIONALL保留重复行。
两个查询列名和类型要一致，否则报错。
排序用ORDER BY放最后，比如： sql SELECT name FROM users UNION SELECT name FROM guests ORDER BY name;
全文搜索 vs 精确查询 精确查询像 LIKE '%keyword%'，全文本搜索用 MATCH() AGAINST()。
全文本搜索会算相关性，默认不区分大小写。
比如查邮件内容，全文本能找"email""emailing"相关词。

全文搜索实操 1 . 表创建时加 FULLTEXT(column) 索引： sql CREATE TABLE messages ( content TEXT, FULLTEXT(content) ); 2 . 查询用 MATCH() AGAINST('query')： sql SELECT content FROM messages WHERE MATCH(content) AGAINST('urgent'); 3 . 布尔模式能加逻辑运算符： sql SELECT content FROM messages WHERE MATCH(content) AGAINST('+money -spend' IN BOOLEAN MODE); 4 . 查询扩展 WITH QUERY EXPANSION 会关联相似词。

注意点 中文全文搜索效果差，因为词边界不明确。
MATCH() 忽略短词（<=3 字符）、停用词（默认有）。
频繁词（>5 0%行出现）也忽略，比如"the"。

小疑问： 全文本搜索和正则表达式效率比如何？

MySQL如何实现跨数据库查询_有哪些限制和技巧？

跨库查询主要有两种方式。

1 . 同一实例下用"数据库名.表名"。
比如SELECT FROM db1 .table1 WHERE db1 .id = db2 .id。
适用于同服务器多库关联，但大表关联会慢。

2 . FEDERATED引擎远程表映射。
创建表ENGINE=FEDERATED CONNECTION='mysql://...'; 可跨服务器，但性能差且无事务支持。

常见限制：
不同服务器数据无法直接查。

权限控制复杂。

性能下降明显。

事务一致难保证。

备份恢复麻烦。

优化技巧：
合并数据库设计。

用视图封装逻辑。

中间层聚合数据。

定时同步数据。

用MyCat等中间件。

你自己掂量。

从两张表提取数据并基于包含值进行比较

这个SQL方案，我以前在论坛上看到过类似的讨论。
说起来，那时候我还在一家小公司做数据库管理，正好遇到一个类似的场景，就是需要根据用户的不同排名，从两个表中筛选出相关的记录。

当时，我们也是用MySQL的REGEXP函数来处理这个问题。
说实话，那会儿我还在学习正则表达式的用法，写出来的代码可能有点偏激，不过最终还是解决了问题。

记得当时我们用的SQL语句是这样的：
sql SELECT tbl2 . FROM Table1 AS tbl1 JOIN Table2 AS tbl2 ON tbl2 .rank REGEXP REPLACE(tbl1 .rank, ',', '|') WHERE tbl1 .username = 'user2 ';
这个语句的核心就是利用了REPLACE函数，把rank字段里的逗号替换成管道符|，这样就能转换成正则表达式了。
然后通过REGEXP进行匹配，实现多值模糊查询。

在PHP代码的实现上，我也记得当时我们用了预处理语句来防止SQL注入，这个做法现在看来是挺重要的。
代码里这样写的：
php $stmt = $conn->prepare("SELECT tbl2 . FROM Table1 AS tbl1 JOIN Table2 AS tbl2 ON tbl2 .rank REGEXP REPLACE(tbl1 .rank, ',', '|') WHERE tbl1 .username = ?"); $stmt->bind_param("s", $user);
这个预处理语句加上bind_param，确实能提高安全性，防止恶意SQL注入。

至于性能优化，当时我也注意到了REGEXP在大型数据集上的效率问题。
我们当时是建议对Table2 的rank列建立索引，不过说实话，效果并不明显。
后来，我们考虑将排名数据规范化，拆分为关联表，这样查询效率会更高。

还有一点，如果排名值里包含正则的特殊字符，比如.或，那确实需要额外处理，进行转义。

最后，如果用MySQL 8 .0以上的版本，可以考虑使用JSON_TABLE或FIND_IN_SET，这些方法可能更高效一些。

总之，这个方案在当时还是挺实用的，不过随着技术的发展，肯定有更好的解决方案。
我记得当时讨论这个方案的时候，大家都是挺兴奋的，因为解决了大问题嘛。