子查询上GROUP_CONCAT的奇怪结果 [英] Weird result for GROUP_CONCAT on subquery

问题描述

在子查询上使用GROUP_CONCAT时，我的行为很奇怪. 这是我的查询:

I am having a weird behavior when using GROUP_CONCAT on subquery. Here is my query :

SELECT
  name,
  GROUP_CONCAT(DISTINCT (id) SEPARATOR "-") AS id
FROM (
       (SELECT
          "APN"                                          AS name,
          GROUP_CONCAT(DISTINCT (site.id) SEPARATOR "-") AS id
        FROM site
        WHERE id IN
              (138, 147, 8918, 8916, 9033, 9240, 97, 9038, 8886, 9036, 9067, 146, 37, 9127, 52, 9031, 23, 8635, 8665, 
               46, 39, 18, 33, 9035, 137, 9051, 8766, 25, 20, 9160, 133, 8636, 9021, 8655, 21, 42, 8757, 22, 9017, 77, 
               9037, 44, 49, 9323, 55, 74, 150, 8, 67, 1, 8928, 58, 9025, 9221, 9019, 9069, 9214, 9176, 95, 40, 9335, 
               168, 9260, 8641, 9227, 9258, 24, 50, 29, 9073, 12, 36, 8882, 9, 43, 76, 9032, 51, 9060, 96, 8922, 9212,
               14, 9095, 28, 9213, 31, 41, 68, 9027, 8884, 9023, 9059, 9034, 9016, 11, 61, 9229, 8761, 9225, 8937, 9018,
               9121, 9119, 8659, 8926, 9096, 57, 9083, 8662, 9232, 149, 8643, 88, 19, 8660, 10, 8936, 9210, 9241, 17, 8872))
       UNION ALL
       (SELECT
          "smart"                                        AS name,
          GROUP_CONCAT(DISTINCT (site.id) SEPARATOR "-") AS id
        FROM site
        WHERE id IN
              (9129, 8981, 9136, 9169, 9170, 9171, 9172, 9297, 9147, 9155, 9139, 9138, 9142, 9296, 8987, 9216, 9252,
               9320, 8951, 8945, 8952, 8965, 8963, 9012, 9192, 8938, 8941, 8968, 8977, 9117, 9135, 9140, 9143, 9295,
               9298, 9137, 8988, 8989, 8992, 9164, 9156, 9165, 9168, 9173, 8953, 8999, 8939, 8940, 8942, 8943, 8954,
               8956, 8957, 8959, 8960, 8964, 8971, 8972, 8973, 8974, 8982, 9000, 9001, 9003, 8950, 8978, 8979, 8983,
               9002, 9005, 8984, 8955, 8986, 8980, 8993, 9008, 9010, 8949, 8998, 9150, 9122, 8944, 8946, 8948, 9006,
               9009, 9013, 9128, 9215, 9321, 9011, 9154, 8970, 8975, 8994, 9070, 8966, 8958, 9007, 9014))
     ) t
GROUP BY name;

(这是一个测试"查询，可以轻松显示该问题，真正的查询不是那个哑巴").它将两个子查询的结果重新组合.所有ID均存在并返回一行.

(This is a "test" query to show easily the issue, the real query is not that "dumb"). It regroups the result of two subqueries. All the IDs exist and return a row.

因此，当我单独运行第一个子查询时，我得到名称的结果"APN"和"1-8-9-10-11-12-14-17-18-19-20-21-22- 23-24-25-28-29-31-33-36-37-39-40-41-42-43-44-46-49-50-51-52-55-57-58-61-67- 68-74-76-77-88-95-96-97-133-137-138-146-147-149-150-168-8635-8636-8641-8643-8655-8659-8660-8662-8665- 8757-8761-8766-8872-8882-8884-8886-8916-8918-8922-8926-8928-8936-8937-9016-9017-9018-9019-9021-9023-9025-9027-9031-9032-9033- 9034-9035-9036-9037-9038-9051-9059-9060-9067-9069-9073-9083-9095-9096-9119-9121-9127-9160-9176-9210-9210-9212-9213-9214-9221-9225- 9227-9229-9232-9240-9241-9258-9260-9323-9335"(ID的完整列表)

So when I run the first subquery alone, I get the result "APN" for name, and " 1-8-9-10-11-12-14-17-18-19-20-21-22-23-24-25-28-29-31-33-36-37-39-40-41-42-43-44-46-49-50-51-52-55-57-58-61-67-68-74-76-77-88-95-96-97-133-137-138-146-147-149-150-168-8635-8636-8641-8643-8655-8659-8660-8662-8665-8757-8761-8766-8872-8882-8884-8886-8916-8918-8922-8926-8928-8936-8937-9016-9017-9018-9019-9021-9023-9025-9027-9031-9032-9033-9034-9035-9036-9037-9038-9051-9059-9060-9067-9069-9073-9083-9095-9096-9119-9121-9127-9160-9176-9210-9212-9213-9214-9221-9225-9227-9229-9232-9240-9241-9258-9260-9323-9335" for ID (the full list of IDs)

与第二个子查询相同，除了名称为"smart"且ID不同.所以这是预期的行为.

It is the same for the second subquery, except that the name is "smart" and the IDs are different. So this is the expected behavior.

问题是当我运行完整的查询时，对于名称APN，我得到以下ID列表: 1-8-9-10-11-12-14-17-18-19-20-21-22-23-24-25-28-29-31-33-36-37-39-40-41- 42-43-44-46-49-50-51-52-55-57-58-61-67-68-74-76-77-88-95-96-97-133-137-138-146- 147-149-150-168-8635-8636-8641-8643-8655-8659-8660-8662-8665-8757-8761-8766-8872-8882-8884-8886-8916-8918-8922-8926-8928- 8936-8937-9016-9017-9018-9019-9021-9023-9025-9027-9031-9032-9033-9034

The issue is when I run the complete query, for the name APN, I get the following list of IDs : 1-8-9-10-11-12-14-17-18-19-20-21-22-23-24-25-28-29-31-33-36-37-39-40-41-42-43-44-46-49-50-51-52-55-57-58-61-67-68-74-76-77-88-95-96-97-133-137-138-146-147-149-150-168-8635-8636-8641-8643-8655-8659-8660-8662-8665-8757-8761-8766-8872-8882-8884-8886-8916-8918-8922-8926-8928-8936-8937-9016-9017-9018-9019-9021-9023-9025-9027-9031-9032-9033-9034

因此，此列表比第一个列表小得多.这与名称"smart"相同.

So this list is much smaller than the first one. And this is the same for the name "smart".

我尝试将两个子查询替换为(选择"APN"作为名称，将"1-8-9-10-11-12-14-17-etc ..."作为ID，从站点限制1)替换为完整ID列表(名称与"smart"相同)，并且完整查询的结果与预期的一样(每个名称的ID完整列表).

I tried replacing my two subqueries by (SELECT "APN" as name, "1-8-9-10-11-12-14-17-etc..." as id FROM site LIMIT 1) with the complete list of IDs (and the same for the name "smart"), and with that, the result of the full query is as expected (the full list of ID for each name).

我的服务器上的group_concat_max_len是1024(并且我的完整ID列表比1024个角色小得多)

The group_concat_max_len is 1024 on my server (and my full ID list are much more smaller than 1024 caracters)

那么，您是否知道为什么结果与预期不符?

So, do you have any idea why the result is not as expected ?

推荐答案

您的查询有点奇怪.

select name, GROUP_CONCAT(DISTINCT(id) SEPARATOR "-") AS id FROM (
        (select "APN" AS name, GROUP_CONCAT(DISTINCT(site.id) SEPARATOR "-") AS id  from site WHERE id IN (138,147,8918,8916,9033,9240,97,9038,8886,9036,9067,146,37,9127,52,9031,23,8635,8665,46,39,18,33,9035,137,9051,8766,25,20,9160,133,8636,9021,8655,21,42,8757,22,9017,77,9037,44,49,9323,55,74,150,8,67,1,8928,58,9025,9221,9019,9069,9214,9176,95,40,9335,168,9260,8641,9227,9258,24,50,29,9073,12,36,8882,9,43,76,9032,51,9060,96,8922,9212,14,9095,28,9213,31,41,68,9027,8884,9023,9059,9034,9016,11,61,9229,8761,9225,8937,9018,9121,9119,8659,8926,9096,57,9083,8662,9232,149,8643,88,19,8660,10,8936,9210,9241,17,8872)) 
    UNION ALL 
        (select "smart" AS name, GROUP_CONCAT(DISTINCT(site.id) SEPARATOR "-") AS id from site WHERE id IN (9129,8981,9136,9169,9170,9171,9172,9297,9147,9155,9139,9138,9142,9296,8987,9216,9252,9320,8951,8945,8952,8965,8963,9012,9192,8938,8941,8968,8977,9117,9135,9140,9143,9295,9298,9137,8988,8989,8992,9164,9156,9165,9168,9173,8953,8999,8939,8940,8942,8943,8954,8956,8957,8959,8960,8964,8971,8972,8973,8974,8982,9000,9001,9003,8950,8978,8979,8983,9002,9005,8984,8955,8986,8980,8993,9008,9010,8949,8998,9150,9122,8944,8946,8948,9006,9009,9013,9128,9215,9321,9011,9154,8970,8975,8994,9070,8966,8958,9007,9014))
) t GROUP BY name;

等于:

    (select "APN" AS name, GROUP_CONCAT(DISTINCT(site.id) SEPARATOR "-") AS id  from site WHERE id IN (138,147,8918,8916,9033,9240,97,9038,8886,9036,9067,146,37,9127,52,9031,23,8635,8665,46,39,18,33,9035,137,9051,8766,25,20,9160,133,8636,9021,8655,21,42,8757,22,9017,77,9037,44,49,9323,55,74,150,8,67,1,8928,58,9025,9221,9019,9069,9214,9176,95,40,9335,168,9260,8641,9227,9258,24,50,29,9073,12,36,8882,9,43,76,9032,51,9060,96,8922,9212,14,9095,28,9213,31,41,68,9027,8884,9023,9059,9034,9016,11,61,9229,8761,9225,8937,9018,9121,9119,8659,8926,9096,57,9083,8662,9232,149,8643,88,19,8660,10,8936,9210,9241,17,8872)) 
    UNION ALL 
    (select "smart" AS name, GROUP_CONCAT(DISTINCT(site.id) SEPARATOR "-") AS id from site WHERE id IN (9129,8981,9136,9169,9170,9171,9172,9297,9147,9155,9139,9138,9142,9296,8987,9216,9252,9320,8951,8945,8952,8965,8963,9012,9192,8938,8941,8968,8977,9117,9135,9140,9143,9295,9298,9137,8988,8989,8992,9164,9156,9165,9168,9173,8953,8999,8939,8940,8942,8943,8954,8956,8957,8959,8960,8964,8971,8972,8973,8974,8982,9000,9001,9003,8950,8978,8979,8983,9002,9005,8984,8955,8986,8980,8993,9008,9010,8949,8998,9150,9122,8944,8946,8948,9006,9009,9013,9128,9215,9321,9011,9154,8970,8975,8994,9070,8966,8958,9007,9014))

除非您的原始查询产生具有相同ID组的许多APN行，否则无需按名称和ID进行父分组.

No need for parent grouping by name and id unless your original query produces many APN Rows with same group of ID's.

回到您的问题:您是正确的，group_concat的最大长度为1024，但是排序/联合操作会进一步截断为1/3(1024/3 = 341). (尽管已知，但尚无官方文件来支持此操作)

back to your question: You are correct that group_concat has max length of 1024 but a sort /union operation truncates further to 1/3 (1024/3 = 341). (although it's known but no official document is available to back this up)

在您的情况下，只需增加组连接最大长度值即可:

In your case, just increase the group concat max length value:

SET group_concat_max_len = 5000;

这应该可以提供所需的输出而不会被截断.

and that should give your desired output without truncating.

您可以创建临时表并将其合并，也可以将grou_concat结果输出到变量中.无论哪种方式，grop_concat都将按其原始默认值截断.

You can create temporary tables and union them, or you can output the grou_concat result into a variable. In both way grop_concat will truncate by its original default value.

这篇关于子查询上GROUP_CONCAT的奇怪结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！