I have a MySQL database in which each user has an account, and each account can have multiple permissions.
我有一个MySQL数据库，其中每个用户都有一个帐户，每个帐户可以拥有多个权限。
My ultimate goal is to end up with the account's username, and a comma-delimited list of permissions ids. There are two ways I can accomplish this:
我的最终目标是以帐户的用户名和逗号分隔的权限ID列表结束。我有两种方法可以实现这一目标：
SELECT a.username, GROUP_CONCAT(rp.permission_id) as permission_ids
FROM account AS a
JOIN role_permission AS rp
ON rp.role_id = a.role_id
WHERE a.id = 1902
... or ...
... 要么 ...
SELECT username
FROM account
WHERE id = 1902;
SELECT permission_id
FROM account_permission
WHERE account_id = 1902
With the single query, I get my results exactly as I want them. With two queries, I have to create the comma-delimited list in the app (PHP) using the second result set.
通过单个查询，我可以完全按照我的要求获得结果。有了两个查询，我必须使用第二个结果集在应用程序（PHP）中创建逗号分隔的列表。
Are there any performance reasons to NOT choose the first option? I have never used GROUP_CONCAT before, so I don't know the implications of it, performance-wise.
是否有任何性能原因不选择第一个选项？我之前从未使用过GROUP_CONCAT，所以我不知道它的含义，性能方面。
1 个解决方案
#1
6
The performance should be OK - better than two queries. You need to be aware that the length is limited though:
性能应该没问题 - 比两个查询更好。你需要知道长度有限但是：
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:
结果被截断为group_concat_max_len系统变量给出的最大长度，该变量的默认值为1024.尽管返回值的有效最大长度受max_allowed_packet的值约束，但该值可以设置得更高。在运行时更改group_concat_max_len的值的语法如下，其中val是无符号整数：
SET [GLOBAL | SESSION] group_concat_max_len = val;
#1
6
The performance should be OK - better than two queries. You need to be aware that the length is limited though:
性能应该没问题 - 比两个查询更好。你需要知道长度有限但是：
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:
结果被截断为group_concat_max_len系统变量给出的最大长度，该变量的默认值为1024.尽管返回值的有效最大长度受max_allowed_packet的值约束，但该值可以设置得更高。在运行时更改group_concat_max_len的值的语法如下，其中val是无符号整数：
SET [GLOBAL | SESSION] group_concat_max_len = val;