How to extend group_concat() limit in MySQL & phpMyAdmin
To extend group_concat() limit in MySQL there are two methods, either using SQL command or by setting variable value directly in phpMyAdmin. First I am showing the method of setting variable value in phpMyAdmin.
How to extend group_concat() limit in phpMyAdmin
Before we start, we’ll execute some queries to prepare a database table so that, it will be easier and clear to understand the difference.
Create a sample Table with column id and description. Click to know more about Create and insert SQL.
|
1 2 3 4 |
CREATE TABLE sample_table ( id int(11) NOT NULL, description text NOT NULL ) |
Insert Data in the table by executing following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
INSERT INTO sample_tab (id, description) VALUES (1, 'This is sample paragraph which is the text entered in row one.'), (2, 'This is sample paragraph which is the text entered in row two.'), (3, 'This is sample paragraph which is the text entered in row three.'), (4, 'This is sample paragraph which is the text entered in row four.'), (5, 'This is sample paragraph which is the text entered in row five.'), (6, 'This is sample paragraph which is the text entered in row six.'), (7, 'This is sample paragraph which is the text entered in row seven.'), (8, 'This is sample paragraph which is the text entered in row eight.'), (9, 'This is sample paragraph which is the text entered in row nine.'), (10, 'This is sample paragraph which is the text entered in row ten.'), (11, 'This is sample paragraph which is the text entered in row eleven.'), (12, 'This is sample paragraph which is the text entered in row twelve.'), (13, 'This is sample paragraph which is the text entered in row thirteen.'), (14, 'This is sample paragraph which is the text entered in row fourteen.'), (15, 'This is sample paragraph which is the text entered in row fifteen.'), (16, 'This is sample paragraph which is the text entered in row sixteen.'), (17, 'This is sample paragraph which is the text entered in row seventeen.'), (18, 'This is sample paragraph which is the text entered in row eighteen.'), (19, 'This is sample paragraph which is the text entered in row nineteen.'), (20, 'This is sample paragraph which is the text entered in row twenty.'); |
Now fetch data by group_concat() function.
|
1 |
SELECT GROUP_CONCAT(description) AS grouped_data FROM sample_table; |
If full text is not showing, click on +option (shown in the image above) button above result and opt for Full texts and click on Go.
The full text result will be as shown in the below image.
Now follow the steps to extend limit of group_concat() function.
- Open phpMyAdmin and login to your database (may not be needed as in case of windows or any other).
After login you can access to home page of phpMyAdmin.
If you are not able to see index/home page then click on home icon (shown in the above image), present below logo of phpMyAdmin in the top left corner, to go on that page.
On the index page, there are some tabs (menu links) present on the right panel of the phpMyadmin page. You need to click on the variables tab.
Scroll down the page and find group concat max len, which is in the form of a link along with some option on both sides of the row.
Click on Edit on the left side of group concat max len in the same row.
AS you click, it will show a text box in the right side having a value 1024 (default value).
Change this value to your required value (as I did in the above image), for example 2048 will double the size of the function return value length.
Click on save on the left side or simply press enter.
Function limit is extended.
You can see the difference by re executing the query that was executed before extending the limit. (compare Img -3 to Img-11 to see difference)
|
1 |
SELECT GROUP_CONCAT(description) AS grouped_data FROM sample_table; |
Now complete text is displayed in the result. You can count the characters and if you want to test, add some more rows to test the limit.
How to extend group_concat() limit using SQL query in MySQL
The other method of extending group_concat() function character limit is using SQL query. Following is the query that is to be executed to extend limit of group_concat() function character limit upto 2048 characters.
|
1 |
SET GLOBAL group_concat_max_len = 2048 |
Thank you, very helpful.
I had a question, you give me the aswer 🙂
Thank a lot
Thanks a lot for this article,
it helped me in time