mysql - 如何为自动完成框选择标签？

Question

这是我的表结构:

// tags
+----+------------+----------------------------------+----------+----------+------------+
| id |    name    |            description           | related  | used_num | date_time  |
+----+------------+----------------------------------+----------+----------+------------+
| 1  | PHP        | some explanations for PHP        | 1        | 4234     | 1475028896 |
| 2  | SQL        | some explanations for SQL        | 2        | 734      | 1475048601 |
| 3  | jQuery     | some explanations for jQuery     | 3        | 434      | 1475068321 | 
| 4  | MySQL      | some explanations for MySQL      | 2        | 657      | 1475068332 |
| 5  | JavaScript | some explanations for JavaScript | 3        | 3325     | 1475071430 |
| 6  | HTML       | some explanations for HTML       | 6        | 2133     | 1475077842 |
| 7  | postgresql | some explanations for postgresql | 2        | 43       | 1475077851 |
| 8  | script     | some explanations for script     | 8        | 3        | 1475077935 |
+----+------------+----------------------------------+----------+----------+------------+

现在我需要根据标签的一部分名称来选择标签，我还需要选择所有相关标签。

---

例如:

字符串:scr。预期输出:

+----+------------+----------------------------------+----------+----------+------------+
| 3  | jQuery     | some explanations for jQuery     | 3        | 434      | 1475068321 | 
| 5  | JavaScript | some explanations for JavaScript | 3        | 3325     | 1475071430 |
| 8  | script     | some explanations for script     | 8        | 3        | 1475077935 |
+----+------------+----------------------------------+----------+----------+------------+
-- Noted that "jQuery" tag is selected because of its relation with "JavaScript" tag

字符串:ph。预期输出:

+----+------------+----------------------------------+----------+----------+------------+
| 1  | PHP        | some explanations for PHP        | 1        | 4234     | 1475028896 |
+----+------------+----------------------------------+----------+----------+------------+

字符串:是。预期输出:

+----+------------+----------------------------------+----------+----------+------------+
| 2  | SQL        | some explanations for SQL        | 2        | 734      | 1475048601 |
| 4  | MySQL      | some explanations for MySQL      | 2        | 657      | 1475068332 |
| 7  | postgresql | some explanations for postgresql | 2        | 43       | 1475077851 |
+----+------------+----------------------------------+----------+----------+------------+
-- Noted that both "SQL" and "postgresql" are selected because of their relation with "MySQL" tag

---

我该怎么做？

实际上我可以这样做:

SELECT * FROM tags WHERE name LIKE %:str%

但是我的查询不支持related列。

Answer 1

连接表本身并查找出现在两个名称列之一中的行。这样，您也可以搜索相关元素的名称。

SELECT t1.*
FROM tags t1
LEFT JOIN tags t2 ON t1.id = t2.related
WHERE t1.name LIKE %:str% OR t2.name LIKE %:str%;