MySQL左连接和group_concat返回重复和截断的数据

huangapple
未分类评论42阅读模式
英文:

MySQL left join and group_concat returning duplicate and cut-off data

问题

我有几张表格，我试图从中选择大量数据。我有一张名为`xguilds_guilds`的表格，这是我从中选择数据的主要表格。我还有一张名为`xguilds_players`的表格。所有玩家都与`xguilds_guilds`中的一个条目连接。对于`xguilds_permissions`也是一样的，每个权限都与`xguilds_guilds`中的一个条目连接。

我的选择查询如下：

    SELECT `xguilds_guilds`.*,
      GROUP_CONCAT(`xguilds_permissions`.`permission`) AS `permissions`,
      GROUP_CONCAT(`xguilds_permissions`.`rank`) AS `ranks`,
      GROUP_CONCAT(`xguilds_players`.`uuid`) AS `players`,
      GROUP_CONCAT(`xguilds_players`.`rank`) AS `plranks`
    FROM `xguilds_guilds`
      LEFT JOIN `xguilds_permissions` ON `xguilds_guilds`.`id` = `xguilds_permissions`.`guild`
      LEFT JOIN `xguilds_players` ON `xguilds_guilds`.`id` = `xguilds_players`.`guild`
    WHERE `admin` = 0
    GROUP BY `xguilds_guilds`.`id`;

然而，由于某种原因，连接表中的条目没有被正确选择。例如，我有一个id为6的公会，我期望为`players`列选择以下内容：

    030adc90-47a4-48ac-b713-e606ddfd4cb9,
    b9d9b3de-8e38-4277-999c-d95945658537,
    f273e51a-a9b8-44af-a019-897e88509ae5

然而，实际上它选择了以下字符串，其中uuid不断重复并且在末尾被截断：

    030adc90-47a4-48ac-b713-e606ddfd4cb9,
    b9d9b3de-8e38-4277-999c-d95945658537,
    f273e51a-a9b8-44af-a019-897e88509ae5,
    030adc90-47a4-48ac-b713-e606ddfd4cb9,
    b9d9b3de-8e38-4277-999c-d95945658537,
    f273e51a-a9b8-44af-a019-897e88509ae5,
    ...
    （重复多次，被截断）

我不知道是什么原因导致了这种奇怪的行为。在连接表方面我没有太多的经验，这可能是这种情况下的问题。我还尝试了以下查询，它能够正确工作：

    SELECT `xguilds_guilds`.*,
      GROUP_CONCAT(`xguilds_players`.`uuid`) AS `players`
    FROM `xguilds_guilds`
      LEFT JOIN `xguilds_players` ON `xguilds_guilds`.`id` = `xguilds_players`.`guild`
    WHERE `guild` = 6
    GROUP BY `xguilds_guilds`.`id`;

非常感谢您的任何帮助。
英文:

I have a few tables from which I am trying to SELECT quite a lot of data. I have a table called xguilds_guilds which is the main table I am selecting data from. I also have a table called xguilds_players. All the players are connected to an entry in xguilds_guilds. The same goes for xguilds_permissions where every permission is connected to an xguilds_guilds entry.

My select query is as follows:

SELECT `xguilds_guilds`.*,
  GROUP_CONCAT(`xguilds_permissions`.`permission`) AS `permissions`,
  GROUP_CONCAT(`xguilds_permissions`.`rank`) AS `ranks`,
  GROUP_CONCAT(`xguilds_players`.`uuid`) AS `players`,
  GROUP_CONCAT(`xguilds_players`.`rank`) AS `plranks`
FROM `xguilds_guilds`
  LEFT JOIN `xguilds_permissions` ON `xguilds_guilds`.`id` = `xguilds_permissions`.`guild`
  LEFT JOIN `xguilds_players` ON `xguilds_guilds`.`id` = `xguilds_players`.`guild`
WHERE `admin` = 0
GROUP BY `xguilds_guilds`.`id`;

However, for some reason the entries from the joined tables are not being selected correctly. For example, I have a guild with id 6 where I am expecting to select the following for the players column:

030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5

However, it actually selects the following string, in which the uuids are constantly being repeated and cut off in the end:

030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e606ddfd4cb9,
b9d9b3de-8e38-4277-999c-d95945658537,
f273e51a-a9b8-44af-a019-897e88509ae5,
030adc90-47a4-48ac-b713-e

I have no idea what is causing this strange behaviour. I don't have much experience with joining tables, which might be a problem in this situation. I have also tried the following query and that worked correctly:

SELECT `xguilds_guilds`.*,
  GROUP_CONCAT(`xguilds_players`.`uuid`) AS `players`
FROM `xguilds_guilds`
  LEFT JOIN `xguilds_players` ON `xguilds_guilds`.`id` = `xguilds_players`.`guild`
WHERE `guild` = 6
GROUP BY `xguilds_guilds`.`id`;

Any help appreciated. Thank you very much.

huangapple
  • 本文由 发表于 2020年4月5日 21:53:28
  • 转载请务必保留本文链接：https://java.coder-hub.com/61043698.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定