MySQL and GROUP CONCAT maximum length

Question

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

User · Answer

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- gt 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- gt hostname 0                                  hostname   NULL               port   NULL               socket    this- gt hostname                    else                        hostname     persistent     TRUE                     p    this- gt hostname    this- gt hostname               port   empty  this- gt port    NULL    this- gt port               socket   NULL                      client flags     this- gt compress     TRUE    MYSQLI CLIENT COMPRESS   0           this- gt  mysqli   mysqli init              this- gt  mysqli- gt options MYSQLI OPT CONNECT TIMEOUT  10            this- gt  mysqli- gt options MYSQLI INIT COMMAND   SET SESSION group concat max len   1000000

User · Answer

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

User · Answer

You can try this  SET GLOBAL group concat max len   1000000

User · Answer

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

User · Answer

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

User · Answer

Include this setting in xampp my ini configuration file    mysqld  group concat max len   1000000   Then restart xampp mysql

User · Answer

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   nbsp 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] MySQL and GROUP_CONCAT() maximum length

Examples related to mysql

Examples related to group-concat