MySQL merge and split by specified characters example tutorial

MySQL merge and split by specified characters example tutorial

Preface

Merging or splitting by specified characters is a common scenario. MySQL is relatively simple in writing merges, but splitting by specified characters is relatively troublesome (that is, you need to write more characters). This article will give examples to demonstrate how to merge and split according to specified characters.

1. Merger

In MySQL database, group_concat can be used to merge data according to specified characters.

Create a test table

mysql> create table tb_group(id int auto_increment primary key ,col1 varchar(20));
Query OK, 0 rows affected (0.01 sec)

Insert test data

mysql> insert into tb_group(col1) values('a'),('c'),('dddd'),('ewdw'),('vxgdh');;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

Merge the contents of the col1 field

By default, they are merged by commas, for example:

mysql> select group_concat(col1) from tb_group; 
+---------------------+
| group_concat(col1) |
+---------------------+
| a,c,dddd,ewdw,vxgdh |
+---------------------+
1 row in set (0.01 sec)

Specify delimiters to merge, for example, specify to merge using the || symbol

mysql> select group_concat(col1,'||') from tb_group; 
+-------------------------------+
| group_concat(col1,'||') |
+-------------------------------+
| a||,c||,dddd||,ewdw||,vxgdh|| |
+-------------------------------+
1 row in set (0.00 sec)

Notice

By default, the combined length cannot exceed 1024, otherwise the result will be truncated.

For example, I write a script to insert some data

# Use shell script to implement vim test_insert.sh
# Add the following content #!/bin/bash
#gjc

for i in {1..1025}
do
 mysql -uroot -p'123456' --socket=/data/mysql3306/tmp/mysql.sock -e "insert into testdb.tb_group1(col1)values('a') "
done

# Run the script to insert data sh test_insert.sh
mysql> select count(*)from tb_group;
+----------+
| count(*) |
+----------+
| 1030 |
+----------+
1 row in set (0.00 sec)

Merge again

mysql> select group_concat(col1)cols, length(group_concat(col1)) col_len from tb_group\G
*************************** 1. row ***************************
 cols: a,c,dddd,ewdw,vxgdh,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,
col_len: 1024
1 row in set, 2 warnings (0.01 sec)

It can be seen that the total length bytes in the result are only 1024

For this situation, it is definitely not satisfactory in actual use. How to solve it? In fact, this length is directly related to the group_concat_max_len parameter of the MySQL database (the default is 1024)

mysql> show global variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| group_concat_max_len | 1024 |
+----------------------+-------+
1 row in set (0.08 sec)

Let’s adjust the parameters.

/* Modify the global parameters so that all new connections will take effect*/
mysql> set global group_concat_max_len=102400;
Query OK, 0 rows affected (0.01 sec)

/* Modify the session parameters so that the current connection can take effect without exiting*/
mysql> set session group_concat_max_len=102400;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'group_concat_max_len';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| group_concat_max_len | 102400 |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> show variables like 'group_concat_max_len';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| group_concat_max_len | 102400 |
+----------------------+--------+
1 row in set (0.01 sec)

Let’s merge it again.

mysql> select group_concat(col1)cols, length(group_concat(col1)) col_len from tb_group\G
*************************** 1. row ***************************
 cols: a,c,dddd,ewdw,vxgdh,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a
col_len: 2069
1 row in set (0.01 sec)

This is the right result. Therefore, it is recommended to adjust this parameter to an appropriate size in the production environment.

(Tips: You can use listagg or wm_concat and other methods to implement this in Oracle database. It is also relatively simple and can be tested by yourself)

2. Split

Splitting a string by specified characters is also a common scenario. However, string splitting in MySQL database is not as convenient as in other databases (other databases have splitting functions directly), and requires the help of the mysql.help_topic table in the mysql library to assist in implementation. Here are some examples:

Create test tables and data

mysql> create table tb_split(id int primary key auto_increment,col1 varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb_split(col1) values('a,b,c,d'),('c,a,g,h');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

Split by comma

mysql> SELECT a.id, substring_index(substring_index(a.col1, ',', b.help_topic_id + 1), ',',- 1) NAME FROM tb_split a JOIN mysql.help_topic b ON b.help_topic_id < (length(a.col1) - length(REPLACE(a.col1, ',', '')) + 1);
+----+------+
| id | NAME |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
| 2 | c |
| 2 | a |
| 2 | g |
| 2 | h |
+----+------+
8 rows in set (0.00 sec)

This achieves the split.

Split by specified characters

If it is another delimiter, just modify the delimiter field of Ruiyang.

mysql> insert into tb_split(col1) values('a|v|f');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_split;
+----+---------+
| id | col1 |
+----+---------+
| 1 | a,b,c,d |
| 2 | c,a,g,h |
| 3 | a|v|f |
+----+---------+
3 rows in set (0.01 sec)

mysql> SELECT a.id, substring_index(substring_index(a.col1, '|', b.help_topic_id + 1), '|',- 1) col_split FROM tb_split a JOIN mysql.help_topic b ON b.help_topic_id < (length(a.col1) - length(REPLACE(a.col1, '|', '')) + 1) where a.id=3;
+----+-----------+
| id | col_split |
+----+-----------+
| 3 | a |
| 3 | v |
| 3 | f |
+----+-----------+
3 rows in set (0.00 sec)

This completes the merging and splitting according to the specified characters.

3. Conclusion

This article introduces the common merge and split methods of MySQL. Students who are good at writing SQL can also use other methods to solve the needs of insufficient permissions (for example, permissions for the help_topic table of the MySQL library are required when splitting).

This is the end of this article about MySQL merging and splitting by specified characters. For more information about MySQL merging and splitting by specified characters, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL string splitting example (string extraction without separator)
  • MySQL string splitting operation (string interception containing separators)
  • Vertical and horizontal splitting of MySQL tables
  • MySQL interception and split string function usage examples
  • Summary of MYSQL database data splitting: sub-library and sub-table
  • MySQL common data splitting methods
  • Use Perl to split the data table (MySQL) and migrate the data instance
  • mysql splits a row of data into multiple rows based on commas

<<:  Examples of using provide and inject in Vue2.0/3.0

>>:  Detailed explanation of the process of configuring multiple SVN repositories on Linux servers

Recommend

How to deploy redis in linux environment and install it in docker

Installation Steps 1. Install Redis Download the ...

Low-version Druid connection pool + MySQL driver 8.0 causes thread blocking and performance limitation

Table of contents Phenomenon Root Cause Analysis ...

Implementation of multi-site configuration of Nginx on Mac M1

Note: nginx installed via brew Website root direc...

JavaScript to achieve a simple carousel effect

What is a carousel? Carousel: In a module or wind...

Details on using JS array methods some, every and find

Table of contents 1. some 2. every 3. find 1. som...

Vue implements scrollable pop-up window effect

This article shares the specific code of Vue to a...

Vue template compilation details

Table of contents 1. parse 1.1 Rules for intercep...

Learn about JavaScript closure functions in one article

Table of contents Variable Scope The concept of c...

Vue implements button switching picture

This article example shares the specific code of ...

Three Discussions on Iframe Adaptive Height Code

When building a B/S system interface, you often en...

Introduction to encryption of grub boot program in Linux

Table of contents 1. What is grub encryption 2. g...

Two ways to specify the character set of the html page

1. Two ways to specify the character set of the h...

js to achieve simple accordion effect

This article shares the specific code of js to ac...

Implementation example of nginx access control

About Nginx, a high-performance, lightweight web ...

Introduction to the pitfalls of Linux high concurrency and performance optimization

Table of contents Preface Analysis and solution o...