Shulou(Shulou.com)06/03 Report--

This article mainly explains "what you need to pay attention to when using group_concat". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what you need to pay attention to when using group_concat".

1. Group_concat () has a length limit of 1024, and the configuration group_concat_max_len needs to be modified. If the size is exceeded, it will be truncated.

2. Group_concat () can sort the values of this group and concatenate them into strings

Eg:GROUP_CONCAT (name ORDER BY id DESC)

3. Group_concat () changes the delimiter:

GROUP_CONCAT (name SEPARATOR'|')

4. When you use group_concat, please note that if the connected fields are int, be sure to convert them into char and then put them together.

Otherwise, after you execute (ExecuteScalar or any other method that executes SQL to return the result), the return will not be a comma-separated string

It's byte [].

The GROUP_CONCAT function is used to concatenate multiple strings into a string. There will be a problem of splicing length when splicing into a string. The default maximum splicing length of mysql is 1024 bytes. Because 1024 bytes will be insufficient, sometimes you need to modify it as follows.

1. View the current mysql group_concat_max_len

Enter the mysql state, enter: show variables like 'group_concat_max_len'

If it has not been modified, you will get the following result

2. Modify mysql group_concat_max_len

A) if it is not convenient to restart mysql, you can set it in the mysql state by command, such as:

SET GLOBAL group_concat_max_len = 102400

SET SESSION group_concat_max_len = 102400

You can view it through method 1.

Note: after mysql restart, the configuration file will be read and reset, which will invalidate the setting, so it is recommended that you still modify the configuration file.

B), modify the configuration file: my.ini

Add new configuration under [mysqld]: group_concat_max_len = 102400

Restart, you can check it through method 1.

In particular, sometimes we do not know how many bytes are needed to meet the demand. In this case, we can consider not setting the maximum number of bytes (that is, using the maximum number of bytes), that is, setting group_concat_max_len=-1 in the configuration file.

Thank you for your reading, the above is the content of "what you need to pay attention to when using group_concat". After the study of this article, I believe you have a deeper understanding of what you need to pay attention to when using group_concat, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.