mysql 在值中展开一个字段循环并创建一个新列，其标题与这些值相关联

Question

我有两个表users 和services，我尝试编写一个查询来创建一个新列skills。 skills 列中的值将是 service_title，它映射到 user_skills 中存储的 service_id。

以下是所用表格的示例:

Table users:
+---------+---------------+----------------+----------------+
|   id    |   user_fname  |   user_lname   |   user_skills  |
+---------+---------------+----------------+----------------+ 
|   1     |    kiran      |    bhattarai   |     1,2        |  
|   2     |    sujan      |    bhattarai   |     2,4        |
|   3     |    manorath   |    dad         |     1,2,3,4    |
|   4     |    bhagawoti  |    mom         |     2,3        |
+---------+---------------+----------------+----------------+

Table services:
+-----------------+------------------+
|   service_id    |   service_title  |  
+-----------------+------------------+
|   1             |     cook         |
|   2             |     clean        |
|   3             |     grocessery   |
|   4             |     teach        |
+-----------------+------------------+

目前我正在使用这个查询:

SELECT users.user_fname,
users.user_lname,
(SELECT GROUP_CONCAT(service_title)
FROM `services`
WHERE `service_id` IN (1,2,3,4)) as skills
FROM users
WHERE
id =3;

上述查询的结果:

+---------------+----------------+----------------------------------------+
|   user_fname  |   user_lname   |                 skills                 |
+---------------+----------------+----------------------------------------+ 
|    manorath   |       dad      |     cook,clean,grocessery,teach        |
+---------------+----------------+----------------------------------------+  

我尝试使用 IN (users.user_skills) 而不是使用 IN (1,2,3,4) 因为 user_skills 中的值> 一直在变化，结果是:

+---------------+----------------+----------------------------------------+
|   user_fname  |   user_lname   |                 skills                 |
+---------------+----------------+----------------------------------------+ 
|    manorath   |       dad      |                  cook                  |
+---------------+----------------+----------------------------------------+ 

每次添加新服务时，我都必须在查询的 IN (1,2,3,4,new service id) 中添加 service_id不是一个合适的解决方案。我已经尝试使用 php 和另一个查询来解决这个问题，这样做的缺点是它会减慢进程。我应该如何在单个查询中解决这个问题。

Answer 1

您可以使用 FIND_IN_SET() JOIN 两个表。

SELECT
  u.user_fname,
  u.user_lname,
  GROUP_CONCAT(s.service_title) as skills
FROM users u
LEFT JOIN services s
  ON FIND_IN_SET(s.service_id, u.user_skills)
WHERE u.id = 3

请注意，带有 FIND_IN_SET() 条件的 JOIN 不能使用任何索引。这可能会导致性能不佳。

一般来说，将关系存储在单独的字符串列中是个坏主意。参见 Is storing a delimited list in a database column really that bad? .

你应该normalize您的设计并为您的多对多关系创建一个单独的表。表格看起来像

Table users_services:
+---------+------------+
| user_id | service_id |
+---------+------------+
|   1     |    1       |
|   1     |    2       |
|   2     |    2       |
|   2     |    4       |
|   3     |    1       |
|   3     |    2       |
|   3     |    3       |
|   3     |    4       |
|   4     |    2       |
|   4     |    3       |
+---------+------------+

查询将是

SELECT
  u.user_fname,
  u.user_lname,
  GROUP_CONCAT(s.service_title) as skills
FROM users u
LEFT JOIN users_services us ON us.user_id = u.id
LEFT JOIN services s ON s.service_id = us.service_id 
WHERE u.id = 3