MySQL按指定字符合并以及拆分实例教程

吾爱主题阅读：207 2024-04-05 16:21:54 评论：0

前言

按照指定字符进行合并或拆分是经常碰到的场景，MySQL在合并的写法上比较简单，但是按指定字符拆分相对比较麻烦一点（也就是要多写一些字符）。本文将举例演示如何进行按照指定字符合并及拆分。

1、合并

MySQL数据库中按照指定字符合并可以直接用group_concat来实现。

创建测试表

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

插入测试数据

1 2 3 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

合并col1字段的内容

默认是按照逗号进行合并的，例如：

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

指定分隔符合并，例如指定使用 || 符号进行合并

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

注意

默认情况下，合并后的长度不能超过1024，否则结果会被截断

例如，我再写个脚本插入一些数据

1 2 3 4 5 6 7 8 9 10 11 12 13 # 使用shell脚本来实现 vim test_insert.sh # 添加如下内容 #!/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 # 运行脚本插入数据 sh test_insert.sh

1 2 3 4 5 6 7 mysql> select count (*) from tb_group; + ----------+ | count (*) | + ----------+ | 1030 | + ----------+ 1 row in set (0.00 sec)

再进行合并

可以看出，结果中总长度字节只有1024

对于这种情况，实际使用时肯定是不满足的，如何解决呢？其实此长度与MySQL数据库的group_concat_max_len参数有直接关系（默认为1024）

那我们调整一下参数看看

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 /* 修改全局参数，这样所有的新连接都会生效 */ mysql> set global group_concat_max_len=102400; Query OK, 0 rows affected (0.01 sec) /* 修改本会话参数，这样当前连接不用退出也可以生效 */ 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)

再合并一下看看

1 2 3 4 5 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)

这样结果就对了。因此生产环境中该参数建议调整为合适的大小。

（Tips：Oracle数据库中可以使用listagg或wm_concat等多种方式实现，也比较简单，可以自行测试）

2、拆分

按指定字符拆分字符串，也是比较常见的场景。但是MySQL数据库中字符串的拆分没有其他数据库那么方便（其他数据库直接有拆分函数），且需要借助mysql库中的mysql.help_topic表来辅助实现。例子如下：

创建测试表及数据

1 2 3 4 5 6 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

按照逗号拆分

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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)

这样也就实现了拆分。

按指定字符拆分

如果是其他分隔符的，修改瑞阳的分隔符字段即可。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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)