mysql根据逗号将一行数据拆分成多行数据

目录
  • 分隔效果
  • 命令行解释
  • 涉及函数

分隔效果

-- 分隔前
1,2,3,4
-- 分隔后
1
2
3
4

分隔命令行

SELECT
 substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result
FROM
 mysql.help_topic b
where
 b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1);

命令行解释

help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表

可以用做计数的临时表，查询的语句只会用help_topic计数，超出的部分其实都是脏数据

b.help_topic_id < 获取分隔后的总行数

b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1)

这个语句其实是一个遍历，help_topic_id 从0开始递增到

按照顺序截取，先截取第 n 个分隔符之前的所有字符，再截取字符串最后一位

1    —— 1
1,2  —— 2
1,2,3 —— 3
1,2,3,4 —— 4

mysql.help_topic 无权限处理办法

mysql.help_topic 的作用是对 SUBSTRING_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。

如果 mysql.help_topic 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。

获取该字段最多可以分割成为几个字符串:

SELECT MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1) FROM `test` a;

创建临时表，并给临时表添加数据:

注意:

  • 临时表必须有一列从 0 或者 1 开始的自增数据
  • 临时表表名随意，字段可以只有一个
  • 临时表示的数据量必须比 MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1) 的值大

涉及函数

substring_index(str，delim，count)

参数名 解释
str 需要拆分的字符串
delim 分隔符，通过某字符进行拆分
count 当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

replace( str, from_str, to_str)

参数名 解释
str 需要进行替换的字符串
from_str 需要被替换的字符串
to_str 需要替换的字符串

length(str) 获取字符串长度

到此这篇关于mysql根据逗号将一行数据拆分成多行数据的文章就介绍到这了,更多相关mysql根据逗号拆分内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MYSQL数据库数据拆分之分库分表总结

    数据存储演进思路一:单库单表 单库单表是最常见的数据库设计,例如,有一张用户(user)表放在数据库db中,所有的用户都可以在db库中的user表中查到. 数据存储演进思路二:单库多表 随着用户数量的增加,user表的数据量会越来越大,当数据量达到一定程度的时候对user表的查询会渐渐的变慢,从而影响整个DB的性能.如果使用mysql, 还有一个更严重的问题是,当需要添加一列的时候,mysql会锁表,期间所有的读写操作只能等待. 可以通过某种方式将user进行水平的切分,产生两个表结构完全一样的

  • MySQL截取和拆分字符串函数用法示例

    本文实例讲述了MySQL截取和拆分字符串函数用法.分享给大家供大家参考,具体如下: 首先说截取字符串函数: SUBSTRING(commentid,9) 这个很简单,从第9个字符开始截取到最后.SUBSTRING的参数有三个,最后一个是截取的长度,默认是到结尾,负数是倒数第几位. 接着说拆分字符串函数: SUBSTRING_INDEX(commentid, '-', 1) 这个就稍稍复杂一些了,他的意思是以 - 进行拆分字符串,从第一个关键词开始取前面所有的字符串.如果上面的第三个参数修改为 -

  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    MySQL 字符串拆分操作(含分隔符的字符串截取)

    无分隔符的字符串截取 题目要求 数据库中字段值: 实现效果:需要将一行数据变成多行 实现的sql SELECT LEFT(SUBSTRING('P1111',help_topic_id+1),1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('P1111'); 涉及的知识点 一.字符串截取:SUBSTRING(str,pos) 1.参数说明 参数名 解释 str 需要拆分的字符串 delim 分隔符,通过某字符进行拆分 co

  • MySQL 表的垂直拆分和水平拆分

    垂直拆分 垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表 通常我们按以下原则进行垂直拆分: 把不常用的字段单独放在一张表; 把text,blob等大字段拆分出来放在附表中; 经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤,然后查询的时候用jion关键起来即可; 水平拆分 水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放. 水平拆分的一些技巧 1. 拆分原则 通常情况下,我们使用取模的方式来进行

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

    前言 按照指定字符进行合并或拆分是经常碰到的场景,MySQL在合并的写法上比较简单,但是按指定字符拆分相对比较麻烦一点(也就是要多写一些字符).本文将举例演示如何进行按照指定字符合并及拆分. 1. 合并 MySQL数据库中按照指定字符合并可以直接用group_concat来实现. 创建测试表 mysql> create table tb_group(id int auto_increment primary key ,col1 varchar(20)); Query OK, 0 rows aff

  • MySQL 常见数据拆分办法

    在生产环境中,由于业务的增长或者业务的拆分,DBA经常需要拆库操作.那么我们常见的拆库手段有哪些呢? 我这里提供几种解决办法: 1. 使用mysqldump 把表逻辑倒出,然后再source 到其它地方 2. 使用xtrabackup 把表.或者库逻辑备份出,然后再recovery出一个实例 3. 使用MySQL自带的表空间转移(Transport)［这个需要MySQL 5.6.6 以上版本支持］ I: 先来看一下MySQL 的 Transport 表空间的特性吧 比如我们要把  tab_tes

  • 使用perl实现拆分数据表（mysql）并迁移数据实例

    随着业务量的增长,可能需要对表进行拆分来提高性能. 下面这个例子是将www.jb51.net的users表拆分成10个表ttlsa_user_0-ttlsa_user_9. 拆分迁移数据程序如下所示: 1.创建ttlsa_user_0-ttlsa_user_9表 复制代码 代码如下: #!/usr/bin/perl ################################### ### author: www.jb51.net ### ### QQ群:232608061  ### ###

  • MySQL 字符串拆分实例(无分隔符的字符串截取)

    MySQL 字符串拆分实例(无分隔符的字符串截取)

    无分隔符的字符串截取 题目要求 数据库中字段值: 实现效果:需要将一行数据变成多行 实现的sql SELECT LEFT(SUBSTRING('P1111',help_topic_id+1),1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('P1111'); 涉及的知识点 一.字符串截取:SUBSTRING(str,pos) 1.参数说明 参数名 解释 str 被截取的字符串 pos 从第几位开始截取,当 pos 为正数时

  • mysql根据逗号将一行数据拆分成多行数据

    mysql根据逗号将一行数据拆分成多行数据

    目录 分隔效果 命令行解释 涉及函数 分隔效果 -- 分隔前 1,2,3,4 -- 分隔后 1 2 3 4 分隔命令行 SELECT substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result FROM mysql.help_topic b where b.help_topic_id < (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ','

  • Python Pandas list列表数据列拆分成多行的方法实现

    1.实现的效果 示例代码: df=pd.DataFrame({'A':[1,2],'B':[[1,2],[1,2]]}) df Out[458]: A B 0 1 [1, 2] 1 2 [1, 2] 拆分成多行的效果: A  B 0  1  1 1  1  2 3  2  1 4  2  2 2.拆分成多行的方法 1)通过apply和pd.Series实现 容易理解,但在性能方面不推荐. df.set_index('A').B.apply(pd.Series).stack().reset_ind

  • pandas实现一行拆分成多行

    pandas实现一行拆分成多行

    目录 一行拆分成多行 1.根据某一列拆分 2.根据多列拆分 一行转多行.多行转一行 1.测试数据 2.一行转多行 3.多行转一行 一行拆分成多行 1.根据某一列拆分 效果: 代码: if __name__ == '__main__': import pandas as pd df = pd.DataFrame({'Country': ['China,US', 'Japan,EU', 'UK,Australia', 'Singapore,Netherland'], 'Number': [100,

  • Oracle以逗号分隔的字符串拆分为多行数据实例详解

    Oracle以逗号分隔的字符串拆分为多行数据实例详解

    前言 近期在工作中遇到某表某字段是可扩展数据内容,信息以逗号分隔生成的,现需求要根据此字段数据在其它表查询相关的内容展现出来,第一想法是切割数据,以逗号作为切割符,以下为总结的实现方法,以供大家参考.指教. 1.regexp_substr函数,通过正则来拆分字符串,函数用法为:(必须是oracle 10g+的版本才支持)  REGEXP_SUBSTR函数格式如下: function REGEXP_SUBSTR(String, pattern, position, occurrence, modi

  • MySQL中列如何以逗号分隔转成多行

    目录 MySQL列以逗号分隔转成多行 场景 解决方案 总结 MySQL列以逗号分隔转成多行 业务场景: 在数据库中,有一张的一个字段存储方式是采用以逗号分隔存储多个值,现在需要将其进行拆分成多个独立的值,与另外一张字典表进行关联,取的最终的字典表中的 label,再以逗号拼接成显示 label 的形式展现. 场景 表中存储的值: 期待最终的展现效果: 甜品,休闲食品,饮料 解决方案 1. 将一列转成多行 select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v

  • SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串

    SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串

    概述 STRING_AGG(合并):多行数据合并成一个字符串,以逗号隔开. STRING_SPLIT(拆分):一个字符串,拆分成多行. 一.多行数据合并成一个字符串 1.通过 FOR xml path('') 合并字符串记录 根据name字段,合并code declare @table1 table ( id int ,code varchar(10) , name varchar(20) ); insert into @table1 ( id,code, name ) values ( 1,

  • pandas将list数据拆分成行或列的实现

    pandas将list数据拆分成行或列的实现

    数据 import numpy as np import pandas as pd data = [{'Name': '小明', 'Chinese': [70, 80], 'Math': [90, 80]}, {'Name': '小红', 'Chinese': [70, 80, 90], 'Math': [90, 80, 70]}] data = pd.DataFrame(data) data 拆分成行 def split_row(data, column): '''拆分成行 :param da

  • Oracle根据逗号拆分字段内容转成多行的函数说明

    目录 使用场景 1.业务表A 2.实现拆分SQL 3.REGEXP_SUBSTR函数 1)参数说明 2)案例 4.REGEXP_REPLACE函数 1)参数说明 5.CONNECT BY函数 1)基本语法 2)案例 6.LENGTH函数 1)参数说明 2)案例 总结 使用场景 业务表A中一个字段存放用逗号分割的多个业务单元,现在需要将数据转成一个业务单元对应一个数据. 1.业务表A SELECT * FROM app_template_dept t WHERE t.evaluate_index_

  • 用Python将mysql数据导出成json的方法

    1.相关说明 此脚本可以将Mysql的数据导出成Json格式,导出的内容可以进行select查询确定. 数据传入参数有:dbConfigName, selectSql, jsonPath, fileName. 依赖的库有:MySQLdb.json,尤其MySQLdb需要事先安装好. 2.Python脚本及测试示例 /Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/mysqlData2json.py # -*- coding=utf-8 -*-

  • mysql group_concat 实现把分组字段写成一行的方法示例

    mysql group_concat 实现把分组字段写成一行的方法示例

    本文实例讲述了mysql group_concat 实现把分组字段写成一行的方法.分享给大家供大家参考,具体如下: 功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果. 函数语法: group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'] ) 示例 查询文章列表,将同一文章多个标签作为一个字段 标签表结构 CREATE TABLE `book_tag` ( `id` int(10)

随机推荐