A field of multiple records in mysql is spliced into one field method
This article mainly tells you about the method of splicing a field of multiple records in mysql into a field. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that a field of multiple records in mysql can be spliced into a field method. This article can bring you some practical help.
problem
The GROUP_CONCAT function is used to concatenate multiple strings into a single 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 it needs to be modified according to the situation.
Use SHOW VARIABLES LIKE "% group_concat_max_len%" to view
Mysql > SHOW VARIABLES LIKE "group_concat_max_len%" +-- +-+ | Variable_name | Value | +-- +-+ | group_concat_max_len | 1024 | +-- +-+ 1 row in set. Sec) mysql > 1 temporary modification (command line) 1.1 modify group_concat_max_len length: 10240mysql > SET GLOBAL group_concat_max_len = 10240 Mysql > SET SESSION group_concat_max_len = 10240bot 1.2 View mysql > SHOW VARIABLES LIKE "% group_concat_max_len%" +-- +-+ | Variable_name | Value | +-- +-+ | group_concat_max_len | 10240 | +-- +-+ 1 row in set. Sec) mysql > 2 permanent modification (configuration file) 2.1 modify configuration file: my.cnf
Add new configuration under [mysqld]: group_concat_max_len = 10240
$vim / etc/ my.cnf.[ mysqld] .group _ concat_max_len = 10240 # add 2.2 restart the mysql service
If the mysql service is not restarted, it can be used with temporary modifications.
$service mysqld restart2.3 View mysql > SHOW VARIABLES LIKE "group_concat_max_len%" +-- +-+ | Variable_name | Value | +-+-+ | group_concat_max_len | 10240 | +-+-+ 1 row in set (0.00 sec) mysql > 3 attached
Sometimes we don't 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), or setting group_concat_max_len=-1 in the configuration file.
In this case, the group_concat_max_len is 4294967295
Note: 4294967295 = 2 ^ 32-1
Mysql multiple records of a field spliced into a field method to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.
