本文介绍了MySQL拆分字符串并选择结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我目前有2张桌子,像这样:

I have currently 2 tables looking like this:

frontend_users:
uid | usergroup | name
1   | 1,2,3     | Michael
2   | 2         | Tobias
3   | 1         | Colin
...

usergroups:
uid | title
1   | member
2   | reporter
3   | admin

我试图拆分用户组,并为每个数字选择用户组,然后将其添加到显示结果中,在最佳情况下应如下所示:

I tried to split the usergroup and select for each number the usergroup and add it to the result of the display, which should look like this in the best case:

uid | name    | groups
1   | Michael | member, reporter, admin
2   | Tobias  | reporter
3   | Colin   | member

到目前为止,我发现最好的结果是从这里:在MySQL中拆分字符串并执行计算但我似乎无法从另一个带有substring_index结果的表中进行选择.我当前的查询如下:

The best result I found so far was from here:Split String and Do Calculation in MySQLbut I can't seem to be able to select from the other table with the substring_index result.My current query looks like this:

SELECT frontend_users.uid, frontend_users.name
CASE
    WHEN frontend_users.usergroup LIKE '%,%,%' THEN
        CONCAT((SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(frontend_users.usergroup, ',', 1)),
            (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 2), ',', -1)),
            (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 3), ',', -1)))
    WHEN frontend_users.usergroup LIKE '%,%' THEN
        CONCAT((SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(frontend_users.usergroup, ',', 1)),
            (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 2), ',', -1))),
    ELSE (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = frontend_users.usergroup)
END AS groups
FROM frontend_users, usergroups

我尝试将for循环用于组数,但结果甚至更糟.

I tried to use a for loop for the number of groups but the results were even worse.

有人暗示如何将选择查询与substring_index结果一起使用吗?

Any hints how I can use the select query with the substring_index result?

推荐答案

您应该考虑规范化.但是,对于当前架构,请考虑以下内容

You should think about the normalization. However for the current schema consider the following

mysql> select * from frontend_users ;
+------+-----------+---------+
| uid  | usergroup | name    |
+------+-----------+---------+
|    1 | 1,2,3     | Michael |
|    2 | 2         | Tobias  |
|    3 | 1         | Colin   |
+------+-----------+---------+
3 rows in set (0.00 sec)

mysql> select * from usergroup ;
+------+----------+
| uid  | title    |
+------+----------+
|    1 | member   |
|    2 | reporter |
|    3 | admin    |
+------+----------+
3 rows in set (0.00 sec)

要获得理想的结果,您可以使用以下查询,该查询对于长期运行而言效率不高

To get the desired result you may use the following query which is not efficient for long run

select
u.uid,
u.name,
group_concat(g.title) as groups
from frontend_users u
join usergroup g on find_in_set(g.uid,u.usergroup) > 0
group by u.uid ;

+------+---------+-----------------------+
| uid  | name    | groups                |
+------+---------+-----------------------+
|    1 | Michael | admin,reporter,member |
|    2 | Tobias  | reporter              |
|    3 | Colin   | member                |
+------+---------+-----------------------+

现在更好的方法是将关联表创建为

Now a better approach would be to create an association table as

mysql> create table user_to_group (uid int, gid int);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into user_to_group values (1,1),(1,2),(1,3),(2,2),(3,1);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

一个更好的查询是

select
u.uid,
u.name,
group_concat(g.title) as groups
from frontend_users u
join user_to_group ug on ug.uid = u.uid
join usergroup g on g.uid = ug.gid
group by u.uid ;

+------+---------+-----------------------+
| uid  | name    | groups                |
+------+---------+-----------------------+
|    1 | Michael | member,admin,reporter |
|    2 | Tobias  | reporter              |
|    3 | Colin   | member                |
+------+---------+-----------------------+

这篇关于MySQL拆分字符串并选择结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 08:15
查看更多