MySQL怎麼按指定字符合併及拆分 詳細實例分析

2020-12-14 站長之家

按照指定字符進行合併或拆分是經常碰到的場景，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腳本來實現vimtest_insert.sh# 添加如下內容 #!/bin/bash# gjcfor i in  {1..1025}domysql -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： 10241 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，acol_len： 20691 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

這樣也就實現了拆分。

按指定字符拆分

如果是其他分隔符的，修改瑞陽的分隔符欄位即可。

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表的權限)等情況下的需求。

本文轉載自微信公眾號【資料庫乾貨鋪】。

相關焦點

  • 如何將 Excel 合併單元格內容按換行符拆分成多行?
    合併單元格在工作中完全屬於中看不中用，如果是要用於進一步分析的源數據表，最好不要合併，否則在後續的操作中後患無窮、無比麻煩。如果別人發來的表格已經合併了，建議還是先拆分開來。如何按換行符將合併單元格內容拆分為多行？大家首先想到的可能是藉助記事本，具體操作方法可參閱
  • 基於Canal和Kafka實現MySQL的Binlog近實時同步
    基於日誌增量訂閱和消費的業務包括：資料庫鏡像資料庫實時備份索引構建和實時維護（拆分異構索引、倒排索引等）業務Cache刷新帶業務邏輯的增量數據處理Canal的工作原理MySQL主備複製原理：MySQL的Master實例將數據變更寫入二進位日誌
  • mysql實現php函數explode功能mysql_explode
    table temp_keys(id int(10) primary key auto_increment,keystr varchar(255));新建一個自定義函數mysql_explodeinsert into temp_keys values(null,'萬劍歸宗');insert into temp_keys values(null,'傲寒六決');自定義函數如下:drop functionif exists mysql_explode
  • 一個公式搞定數據信息按類別拆分到不同工作表
    在Excel表格中說起數據信息按類別拆分，很多朋友都會想到利用數據透視表顯示報表篩選頁的功能或者使用VBA代碼。這篇文章為朋友們分享一種使用函數實現數據拆分的方法。一.實例要求：把下圖所示的名稱為「信息表」的工作表中的內容按班級拆分到不同的工作表裡。
  • MySQL資料庫筆記
    <3>DOS命令窗口方式停止：net stop mysql 切記不要加分號登錄MySQL資料庫：mysql -uroot -p 回車 輸入密碼登錄MySQL資料庫：mysql -uroot -p密碼 回車語法：mysql -h 伺服器主機地址
  • Excel表格把數據按類別快速拆分到不同工作表或工作簿
    Excel表格複製多個工作簿的數據到一個工作表有時並不是所有的數據都能共享，這時就需要把數據按類別拆分到不同的工作表或者工作簿裡實例要求：下圖是某水果超市的進貨需求表。我們需要按產品的名稱匯總出各個品種水果的需求量，而且要把不同的數據放在不同的工作簿裡發給不同的採購人員。
  • 實例,PHP+MySql 實現簡單的分頁功能
    1、mysql limit 用法SELECT * FROM table limit [offset,] count;參數：實例，PHP+AJAX 實現無刷新上傳圖像
  • 如何正確地創建和銷毀軟體應用系統中JDBC資料庫連接對象實例
    如下示圖所示為MyEclipse開發工具中所提供的MyEclipse Database Explore資料庫系統透視圖功能的菜單項目，而該功能就是應用JDBC接口操作訪問用戶所指定的物理資料庫系統。但由於JDBC編程接口只對JDBC URL字符串提出了「形式」方面的規範要求，並沒有給出具體和統一的URL字符串模板。因此，在Java應用系統程序中連接不同的資料庫系統或者同一種資料庫系統的不同版本、不同的連接方式等，所對應的JDBC URL字符串可能都存在差別。
  • EXCEL中如何實現按字符數量進行排序?
    前面我們說到了按數值大小排序、按多個關鍵字排序、按名字的首字母進行排序，今天跟大家介紹下如何按字符數量進行排序。什麼叫按字符數量進行排序呢？簡單點說就是我有一列單元格，有些單元格字符數量比較少，有些單元格字符數量比較多，我們就可以按照字符數量的多少來給單元格做一個排序。不過EXCEL裡面有沒直接按字符數量進行排序的功能，這裡我們需要用到公式來轉換一下。
  • MySQL-mysqldump備份資料庫
    mysqldump備份1、備份命令格式：mysqldump -h主機名 -P埠 -u用戶名 -p密碼 --database 資料庫名 > 文件名.sql 例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database
  • Excel Replace函數與ReplaceB函數的使用方法,含7個替換實例
    在 Excel 中，Replace函數與ReplaceB函數用於替換指定的字符數和字節數，它們都有四個參數，其中三個參數相同，只有一個參數不同，也就是指定字符數和字節數的參數不同。以下是 Excel Replace函數與ReplaceB函數的使用方法，總共有7個實例，分別為把單槓替換為雙槓，替換姓名中間一個字，把部分數字替換為星號*，替換某個字後的所有字符，替換一段字符中間指定個字符、替換數字、字母和特殊字符和替換單字節與雙字節，實例操作所用版本均為 Excel 2016。
  • Loadrunner測試mysql資料庫
    loadrunner可以利用mysql lib庫，通過引用外部DLL，模擬mysql客戶端連接資料庫進行增刪改查的操作進行測試。下面主要介紹如何利用mysql lib庫連接mysql資料庫進行性能測試。一、準備工作1.
  • 10個很棒的 JavaScript 字符串技巧
    我們稱一個字符序列為字符串。這幾乎是所有程式語言中都有的基本類型之一。這裡跟大家展示關於 JS 字符串的10個很棒的技巧，你可能還不知道哦？1.如何多次複製一個字符串JS 字符串允許簡單的重複，與純手工複製字符串不同，我們可以使用字符串的repeat方法。2. 如何填充一個字符串到指定的長度有時，我們希望字符串具有特定長度。
  • 主庫n -> 從庫s - MySQL5.7多主一從(多源複製)同步配置 - 計算機...
    部署環境註：使用docker部署mysql實例，方便快速搭建演示環境。但本文重點是講解主從配置，因此簡略描述docker環境構建mysql容器實例。創建授權用戶連接mysql主資料庫，鍵入命令mysql -u root -p，輸入密碼後登錄資料庫。
  • Mysql資料庫部分
    在筆試題最後一般都是有一道關於mysql語句的問題，讓手寫出增、刪、改、查語句，今天我們就學習一下mysql語句。7.2 不要以字符格式聲明數字，要以數字格式聲明字符值。（日期同樣）否則會使索引無效，產生全表掃描。
  • EXCEL之VBA應用實例-自動合併多個工作薄所有工作表到一個工作薄
    Sub 合併工作薄()Dim str As String '定義一個字符串變量Dim wb As Workbook '定義一個工作薄對象Dim sht As Worksheet'定義一個工作表對象str = Dir("d:\2019年銷售記錄\*.xls*") '第一個Dir必須帶參數，指定文件路徑，本行代碼將獲取到d:\2019年銷售記錄目錄下第一個以擴展名以xls開頭的文件名稱，並賦值給變量strDo While str <> "" 'str查找文件的返回值，如果找到返回對應文件的名稱
  • 資料庫基礎:mysql主從集群搭建
    還好mysql資料庫提供了一種主從備份的機制，其實就是把主資料庫的所有的數據同時寫到備份的資料庫中。實現mysql資料庫的熱備份。 要想實現雙機的熱備，首先要了解主從資料庫伺服器的版本的需求。要實現熱備mysql的版本都高於3.2。還有一個基本的原則就是作為從資料庫的數據版本可以高於主伺服器資料庫的版本，但是不可以低於主伺服器的資料庫版本。
  • PDF文檔如何拆分並合併成1個新文檔?
    那麼如果需要將PDF文檔中部分文檔頁面拆分出來重新組合成新得文檔該如何操作呢？今天小編給大家支支招！PDF文檔拆分合併如果想要編輯PDF文檔肯定要藉助一些工作，這裡推薦大家一款國內非常好用得PDF轉換器：迅米PDF轉換器。大家自行關鍵詞搜索百度即可，這裡小編就不再贅述。
  • Excel如何把多個單元格內容合併到一起?1個函數,3秒搞定
    日常工作中，我們經常會遇到將一個單元格的內容拆分成多個單元格，或者將多個單元格內容合併在一起等情況。阿鍾老師以前的教程中分享過合併單元格的教程，如下圖，將一個單元格的內容拆分成多行或多列。今天我們碰到了新的問題：好好的員工信息表，一行記錄一名員工信息，老闆要求按部門把姓名合併到一個單元格內，如下圖，怎麼辦呢？
  • Excel怎樣合併和拆分單元格?3種方法可供選擇
    在Excel電子表格裡面，合併和拆分單元格是經常用到的，特別是在製作表頭的時候幾乎都離不開合併單元格操作。合併與拆分單元格也是Excel操作必備的基本技能之一。怎樣拆分單元格？有合併就有拆分，相對應的拆分的方法也有三種，在拆分操作之前先選中需要拆分的單元格。一是在開始菜單的對齊方式選項卡裡面操作。