How To How to extend group_concat() limit in MySQL & phpMyAdmin

How to extend group_concat() limit in MySQL & phpMyAdmin

Extend group_concat() limit in phpMyAdmin and Mysql | TechBriefers

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.

Insert Data in the table by executing following query.

Now fetch data by group_concat() function.

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.

group concat limit full text show - techbriefers.com
Img-3: Complete result before limit extension

Now follow the steps to extend limit of group_concat() function.

  1. Open phpMyAdmin and login to your database (may not be needed as in case of windows or any other).
phpmyadmin login - techbriefers.com
Img-4: phpMyaAmin login – techbriefers.com

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.

Tabs in phpMyAdmin (variables tab focused) | group_concat() – techbriefers.com
Tabs in phpMyAdmin (variables tab focused) | group_concat() – techbriefers.com

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.

group concat max len variable option – techbriefers.com
group concat max len variable option – techbriefers.com

Click on Edit on the left side of group concat max len in the same row.

group concat limit variable option saved - techbriefers.com
Img-8: Edit option for group concat max len variable – techbriefers.com

AS you click, it will show a text box in the right side having a value 1024 (default value).

group concat max len variable edit value - techbriefers.com
Img-9: group concat max len variable edit value – techbriefers.com

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.

group concat max len variable change value and save - techbriefers.com
Img-10: group concat max len variable change value and save – techbriefers.com

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)

group concat limit full text show after - techbriefers.com
Img-11: Limit extended and showing full text – techbriefers.com

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.

Share your thoughts

avatar
  Subscribe  
Notify of