按照指定字元進行合併或拆分是經常碰到的場景，MySQL在合併的寫法上比較簡單，但是按指定字元拆分相對比較麻煩一點（也就是要多寫一些字元）。本文將舉例演示如何進行按照指定字元合並及拆分。
1、 合併
MySQL資料庫中按照指定字元合並可以直接用group_concat來實現。
建立測試表
mysql> create table tb_group(id int auto_increment primary key ,col1 varchar(20)); Query OK, 0 rows affected (0.01 sec)
插入測試資料
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欄位的內容
預設是按照逗號進行合併的，例如：
mysql> select group_concat(col1) from tb_group; +---------------------+ | group_concat(col1) | +---------------------+ | a,c,dddd,ewdw,vxgdh | +---------------------+ 1 row in set (0.01 sec)
指定分隔符合並，例如指定使用 || 符號進行合併
mysql> select group_concat(col1,'||') from tb_group; +-------------------------------+ | group_concat(col1,'||') | +-------------------------------+ | a||,c||,dddd||,ewdw||,vxgdh|| | +-------------------------------+ 1 row in set (0.00 sec)
注意
預設情況下，合併後的長度不能超過1024，否則結果會被截斷
例如，我再寫個指令碼插入一些資料
# 使用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
mysql> select count(*)from tb_group; +----------+ | count(*) | +----------+ | 1030 | +----------+ 1 row in set (0.00 sec)
再進行合併
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)
可以看出，結果中總長度位元組只有1024
對於這種情況，實際使用時肯定是不滿足的，如何解決呢？其實此長度與MySQL資料庫的group_concat_max_len引數有直接關係（預設為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)
那我們調整一下引數看看
/* 修改全域性引數，這樣所有的新連線都會生效 */ 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)
再合併一下看看
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表來輔助實現。例子如下：
建立測試表及資料
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
按照逗號拆分
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)
這樣也就實現了拆分。
按指定字元拆分
如果是其他分隔符的，修改瑞陽的分隔符欄位即可。
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)
這樣就完成按照指定字元的合併及拆分了。
3、 結語
本文介紹了MySQL常用的合併及拆分方法，對於擅長寫SQL的同學也可以使用其他方式實現，以便解決許可權不足（例如拆分時需要使用mysql庫的help_topic表的許可權）等情況下的需求。
想了解更多內容或參與技術交流可以關注微信公眾號【資料庫乾貨鋪】或進技術交流群溝通。
