php - 使用mysql搜索多个表？

Question

我有这个 sql 可以按条款获取帖子:

SELECT * FROM posts where content like '%" . $_GET['term'] . "%'"

但我想一次搜索更多表格，例如标签和用户。即

SELECT * FROM users where username like '%" . $_GET['term'] . "%'"
SELECT * FROM topics where tag like '%" . $_GET['term'] . "%'"

我想知道如何将它们组合在一起进行查询。我知道你必须用 As 语句做一些事情，即

content as Value
username as Value
tag as Value

而且我还想获​​取返回的查询类型，即如果返回的结果是一个用户，那将是 TYPE 用户，所以我可以区分它们。

Answer 1

一个简单的 UNION ALL 就足够了:

Select 'user' as hit_type, id from users where username like '%" . $_GET['term'] . "%'"
union all
Select 'topic' as hit_type, id FROM topics where tag like '%" . $_GET['term'] . "%'"