MySQL和GROUP_CONCAT()最大长度

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

我在MySQL查询中使用GROUP_CONCAT()将多个行转换为单个字符串。但是，这个函数的结果的最大长度是1024个字符。

I'm very well aware that I can change the param group_concat_max_len to increase this limit:

我很清楚我可以更改param group_concat_max_len以增加这个限制:

SET SESSION group_concat_max_len = 1000000;

However, on the server I'm using, I can't change any param. Not by using the preceding query and not by editing any configuration file.

但是，在我正在使用的服务器上，我不能更改任何参数。不是通过使用前面的查询，也不是通过编辑任何配置文件。

So my question is: Is there any other way to get the output of a multiple row query into a single string?

我的问题是:是否有其他方法将多个行查询的输出转换为单个字符串?

6 个解决方案

#1

SET SESSION group_concat_max_len = 1000000;

is a temporary, session-scope, setting. It only applies to the current session You should use it like this.

是一个临时的、会话范围的设置。它只适用于当前会话，您应该这样使用它。

SET SESSION group_concat_max_len = 1000000;
select group_concat(column) from table group by column

You can do this even in sharing hosting, but when you use an other session, you need to repeat the SET SESSION command.

即使在共享主机中也可以这样做，但是当您使用其他会话时，您需要重复SET会话命令。

#2

The correct parameter to set the maximum length is:

设置最大长度的正确参数为:

SET @@group_concat_max_len = value_numeric;

value_numeric must be > 1024; by default the group_concat_max_len value is 1024.

value_numeric必须是> 1024;默认情况下，group_concat_max_len值是1024。

#3

Include this setting in xampp my.ini configuration file:

在xampp中包含这个设置。ini配置文件:

[mysqld] group_concat_max_len = 1000000

(mysqld)group_concat_max_len = 1000000

Then restart xampp mysql

然后重启xampp mysql

#4

CREATE TABLE some_table (
  field1 int(11) NOT NULL AUTO_INCREMENT,
  field2 varchar(10) NOT NULL,
  field3 varchar(10) NOT NULL,
  PRIMARY KEY (`field1`)
);

INSERT INTO `some_table` (field1, field2, field3) VALUES
(1, 'text one', 'foo'),
(2, 'text two', 'bar'),
(3, 'text three', 'data'),
(4, 'text four', 'magic');

This query is a bit strange but it does not need another query to initialize the variable; and it can be embedded in a more complex query. It returns all the 'field2's separated by a semicolon.

这个查询有点奇怪，但是它不需要另一个查询来初始化变量;它可以嵌入到更复杂的查询中。它返回由分号分隔的所有'field2'。

SELECT result
FROM   (SELECT @result := '',
               (SELECT result
                FROM   (SELECT @result := CONCAT_WS(';', @result, field2) AS result,
                               LENGTH(@result)                            AS blength
                        FROM   some_table
                        ORDER  BY blength DESC
                        LIMIT  1) AS sub1) AS result) AS sub2; 

#5

The correct syntax is mysql> SET @@global.group_concat_max_len = integer;
If you do not have the privileges to do this on the server where your database resides then use a query like:
mySQL="SET @@session.group_concat_max_len = 10000;"or a different value.
Next line:
SET objRS = objConn.Execute(mySQL)  your variables may be different.
then
mySQL="SELECT GROUP_CONCAT(......);" etc
I use the last version since I do not have the privileges to change the default value of 1024 globally (using cPanel).
Hope this helps.

正确的语法是mysql>设置@@global。group_concat_max_len =整数;如果您没有权限在数据库所在的服务器上执行此操作，那么请使用以下查询:mySQL=“SET @@session”。group_concat_max_len = 10000;”或一个不同的值。下一行:设置objRS = objConn.Execute(mySQL)您的变量可能是不同的。然后mySQL="SELECT GROUP_CONCAT(……);"等，我使用最后一个版本，因为我没有权限全局更改1024的默认值(使用cPanel)。希望这个有帮助。

#6

You can try this

你可以试试这个

SET GLOBAL group_concat_max_len = 1000000;