歡迎您光臨本站 註冊首頁

MySQL按指定字符合並以及拆分實例教程

←手機掃碼閱讀     techdo @ 2020-06-27 , reply:0

前言

按照指定字符進行合併或拆分是經常碰到的場景,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_groupG  *************************** 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_groupG  *************************** 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表的權限)等情況下的需求。



[techdo ] MySQL按指定字符合並以及拆分實例教程已經有131次圍觀

http://coctec.com/docs/mysql/show-post-239879.html