交流群:462197261站长百科站长论坛热门标签收藏本站北冥有鱼 互联网前沿资源第一站 助力全行业互联网+
点击这里给我发消息
  • 当前位置:
  • MySQL按指定字符合并以及拆分实例教程

    前言

    按照指定字符进行合并或拆分是经常碰到的场景,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表的权限)等情况下的需求。

    到此这篇关于MySQL按指定字符合并以及拆分的文章就介绍到这了,更多相关MySQL指定字符合并及拆分内容请搜索北冥有鱼以前的文章或继续浏览下面的相关文章希望大家以后多多支持北冥有鱼!


    广而告之:
    热门推荐:
    现代 JavaScript 开发编程风格Idiomatic.js指南中文版

    你为项目所择风格都应为最高准则。作为一个描述放置于你的项目中,并链接到这个文档作为代码风格一致性、可读性和可维护性的保证。 一、空白 1.永远都不要混用空格和Tab。2.开始一个项目,在写代码之前,选择软缩进(空格)或者 Tab(作为缩进方式),并将其作为最高准则···

    网站收录数量为何会大幅度波动

    网站的综合流量是一个个页面所支撑起来的,如果说网站单单只是首页有,那么可以基本判断这个网站的质量也好不到哪里去,也难以带来什么流量,更别谈其他的一些方面了。而这判断这方面的基础就是要网站页面的收录量,在搜索引擎里面能展现的页面越多,这其实也是反映出了网站的···

    PHP程序漏洞产生的原因分析与防范方法说明

    滥用include 1.漏洞原因: Include是编写PHP网站中最常用的函数,并且支持相对路径。有很多PHP脚本直接把某输入变量作为Include的参数,造成任意引用脚本、绝对路径泄露等漏洞。看以下代码: ... $includepage=$_GET["includepage"]; include($includepage); ... 很明···

    首页调用留言板内容截取字数的方式

    [gbookinfo]6,17,0,33[/gbookinfo] 页面模板内容为 [!--empirenews.listtemp--]<!--list.var1-->[!--empirenews.listtemp--] 列表内容模板为 <table width="92%" border="0" cellpadding="0" cellspacing="0"> <tr> &···

    Asp.net实现选择性的保留DataTable中的列

    复制代码 代码如下: //数据 DataTable dtObject = dt; //保留列 string[] saveColumns = new string[5]; saveColumns[0] = "X";//保留列1 saveColumns[1] = "XX";//保留列2 saveColumns[2] = "XXX"; saveColumns[3] = "XXXX"; saveColumns[4] = "XXXXX"; //移除不需要的···

    原生JS实现的碰撞检测功能示例

    本文实例讲述了原生JS实现的碰撞检测功能。分享给大家供大家参考,具体如下: <!doctype html> <html> <head> <meta charset="utf-8"> <title>www.kunjuke.com JS碰撞检测</title> <style> div{width:100px; height:100px; } #bo···

    织梦获取顶级栏目\当前栏目\上级栏目的相关信息教程

    织梦前端很多时候需要直接获取一个栏目的相关信息,获取在某个页面上获取顶级或者上一级栏目的相关信息,这样我们可以自己写个自定义方法来实现。   打开 \include\extend.func.php 在最下面添加 /** * 获取顶级栏目相关信息 * * @acce···

    js中for in语句的用法讲解

    复制代码 代码如下:  for(variable in object)    statement variable 是声明一个变量的var语句,数组的一个元素或者是对象的一个属性 在循环体内部,对象的一个属性名会被作为字符串赋给变量variable。 注意:对象的有些属性以相同的方式标记成了只读的,永久···

    ThinkPHP实现附件上传功能

    刚学的使用ThinkPHP框架简单上传附件(图片,文档,视频等文件) 首先需要了解tp框架中Upload.class.php(ThinkPHP/Library/Think/Upload,class,php)中的几个函数 1:getError() 获取上传错误信息 2:uploadOne() 上传单个文件 3:upload() 上传多个文件 4:rootPath 上传文件保存···

    ASP.NET中Cookie的使用方法

    Cookie 为 Web 应用程序保存用户相关信息提供了一种有用的方法。例如,当用户访问站点时,可以利用 Cookie 保存用户首选项或其他信息,这样,当用户下次再访问站点时,应用程序就可以检索以前保存的信息。 从技术上讲,Cookie是小段保存在客户端的数据(如果你安装的是XP,可以···