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.

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

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?

5

7 Answers

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.

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.

6

Include this setting in xampp my.ini configuration file:

[mysqld]
group_concat_max_len = 1000000

Then restart xampp mysql

You can try this

SET GLOBAL group_concat_max_len = 1000000;

1

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.

0

The short answer: the setting needs to be setup when the connection to the MySQL server is established. For example, if using MYSQLi / PHP, it will look something like this:

$ myConn = mysqli_init();
$ myConn->options(MYSQLI_INIT_COMMAND, 'SET SESSION group_concat_max_len = 1000000');

Therefore, if you are using a home-brewed framework, well, you need to look for the place in the code when the connection is establish and provide a sensible value.

I am still using Codeigniter 3 on 2020, so in this framework, the code to add is in the application/system/database/drivers/mysqli/mysqli_driver.php, the function is named db_connect();

public function db_connect($persistent = FALSE) { // Do we have a socket path? if ($this->hostname[0] === '/') { $hostname = NULL; $port = NULL; $socket = $this->hostname; } else { $hostname = ($persistent === TRUE) ? 'p:'.$this->hostname : $this->hostname; $port = empty($this->port) ? NULL : $this->port; $socket = NULL; } $client_flags = ($this->compress === TRUE) ? MYSQLI_CLIENT_COMPRESS : 0; $this->_mysqli = mysqli_init(); $this->_mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10); $this->_mysqli->options(MYSQLI_INIT_COMMAND, 'SET SESSION group_concat_max_len = 1000000');
... }

1

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.

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; 

6

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.