MySQL高效实现字符串分割:SUBSTRING_INDEX函数实战解析(转列与转行场景)

MySql字符串拆分实现split功能(字段分割转列、转行) 这一步核心就是获取,有多少个分隔符,比如本文的案例,就是知道有多少个逗号因为help_topic_id是从0开始的,所以会得出help_topic_id值为0~3,共4行数据;Step2以”,”逗号为分隔符,根据help_topic_id的值来截取第n+1个分隔符之前所有的字符串。(此处n+1是因为help_topic_id是从0开始算起,而此处需从第1个分隔符开始获取。)eg当help_topic_id=0时,获取到的字符串=7654eg最终成功实现了以下效果~... 阅读详情

1. 字符串分割的常见场景与挑战

在日常数据库操作中,我们经常会遇到需要处理字符串分割的场景。比如电商系统中的订单号拼接、用户标签管理、日志分析等场景,都会涉及到将多个值存储在一个字段中,用逗号或其他分隔符连接的情况。

我遇到过最典型的案例是一个用户标签系统。当时设计时为了图方便,直接把用户的所有标签用逗号连接存在了一个字段里。等到要做标签统计和筛选时,才发现这种设计给查询带来了巨大麻烦。比如要找出所有带有"VIP"标签的用户,普通的WHERE条件根本无法实现精确匹配。

这种逗号分隔的存储方式虽然节省了表空间,但违反了数据库设计的第一范式(1NF),会给后续的查询和分析带来很多问题。这时候就需要用到字符串分割技术,把一行数据转换成多行,或者把一列数据拆分成多列。

2. SUBSTRING_INDEX函数基础

2.1 函数语法与参数

SUBSTRING_INDEX是MySQL中专门用于字符串分割的函数,它的基本语法如下:

SUBSTRING_INDEX(str, delim, count)

这个函数有三个参数:

  • str:要处理的原始字符串
  • delim:分隔符,可以是单个字符或多个字符
  • count:指定返回第几个分隔符之前或之后的部分

count参数的正负值决定了截取方向:

  • 正数:从字符串开头开始,返回第count个分隔符之前的所有内容
  • 负数:从字符串末尾开始,返回第count个分隔符之后的所有内容

2.2 基础用法示例

来看几个简单的例子:

-- 获取第二个逗号之前的部分
SELECT SUBSTRING_INDEX('苹果,香蕉,橙子,葡萄', ',', 2);
-- 结果:苹果,香蕉

-- 获取倒数第二个逗号之后的部分 
SELECT SUBSTRING_INDEX('苹果,香蕉,橙子,葡萄', ',', -2);
-- 结果:橙子,葡萄

-- 获取最后一个元素
SELECT SUBSTRING_INDEX('苹果,香蕉,橙子,葡萄', ',', -1);
-- 结果:葡萄

在实际项目中,我经常用这个函数来提取路径中的文件名、解析URL参数等。它的性能比正则表达式要好很多,特别适合处理简单的字符串分割需求。

3. 字符串转多行实战

3.1 电商订单号拆解案例

假设我们有一个订单表,其中一个字段存储了关联订单ID,用逗号分隔:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(50),
    related_orders VARCHAR(255)
);

INSERT INTO orders VALUES 
(1, 'ORD001', 'ORD002,ORD003,ORD005'),
(2, 'ORD004', 'ORD006,ORD007');

现在需要把这些关联订单拆分成多行,每行一个订单ID。我们可以使用以下SQL:

SELECT 
    o.id,
    o.order_no,
    SUBSTRING_INDEX(SUBSTRING_INDEX(o.related_orders, ',', h.help_topic_id + 1), ',', -1) AS single_order
FROM 
    orders o
JOIN 
    mysql.help_topic h
ON 
    h.help_topic_id < LENGTH(o.related_orders) - LENGTH(REPLACE(o.related_orders, ',', '')) + 1;

这个查询的核心思路是:

  1. 通过LENGTH和REPLACE计算分隔符数量,确定需要拆分的行数
  2. 使用help_topic表的自增ID作为计数器
  3. 嵌套SUBSTRING_INDEX函数提取每个位置的元素

3.2 用户标签分析案例

再来看一个用户标签的例子:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    tags VARCHAR(255)
);

INSERT INTO users VALUES 
(1, '张三', 'VIP,老客户,高消费'),
(2, '李四', '新客户,促销目标');

要统计每个标签的用户数量,需要先把标签拆分成多行:

SELECT 
    tag,
    COUNT(*) AS user_count
FROM (
    SELECT 
        u.user_id,
        SUBSTRING_INDEX(SUBSTRING_INDEX(u.tags, ',', h.help_topic_id + 1), ',', -1) AS tag
    FROM 
        users u
    JOIN 
        mysql.help_topic h
    ON 
        h.help_topic_id < LENGTH(u.tags) - LENGTH(REPLACE(u.tags, ',', '')) + 1
) AS temp
GROUP BY tag;

我曾经在一个用户画像项目中用过类似的方案,成功将原本需要应用程序处理的逻辑下推到数据库层,性能提升了近10倍。

4. 字符串转多列方案

4.1 固定数量分割

当我们需要把字符串拆分成固定数量的列时,可以结合SUBSTRING_INDEX和其他字符串函数:

SELECT
    -- 第一列
    SUBSTRING_INDEX(ids, ',', 1) AS col1,
    -- 第二列
    CASE 
        WHEN LENGTH(ids) - LENGTH(REPLACE(ids, ',', '')) >= 1 
        THEN SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 2), ',', -1)
        ELSE NULL
    END AS col2,
    -- 第三列
    CASE
        WHEN LENGTH(ids) - LENGTH(REPLACE(ids, ',', '')) >= 2
        THEN SUBSTRING_INDEX(ids, ',', -1)
        ELSE NULL
    END AS col3
FROM (
    SELECT 'A,B,C' AS ids
) AS temp;

这种方案适合已知最大列数的情况,比如解析CSV格式的数据。

4.2 动态列数处理

对于列数不固定的情况,可以考虑使用存储过程动态生成SQL,或者在应用层处理。我曾经处理过一个产品属性表,属性数量不固定,最终采用了应用程序预处理的方式,因为纯SQL方案太过复杂。

5. 性能优化与替代方案

5.1 使用临时表优化

当处理大量数据时,频繁调用字符串函数会影响性能。可以考虑先拆分到临时表:

-- 创建临时表存储拆分结果
CREATE TEMPORARY TABLE temp_split_results AS
SELECT 
    o.id AS original_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(o.related_orders, ',', h.help_topic_id + 1), ',', -1) AS single_value
FROM 
    orders o
JOIN 
    mysql.help_topic h
ON 
    h.help_topic_id < LENGTH(o.related_orders) - LENGTH(REPLACE(o.related_orders, ',', '')) + 1;

-- 然后在临时表上执行后续查询

5.2 自定义序列表替代help_topic

mysql.help_topic表的ID数量有限(通常只有几百条),对于超长字符串可能不够用。可以创建自己的序列表:

-- 创建一个包含足够多连续数字的表
CREATE TABLE sequence (seq INT PRIMARY KEY);
INSERT INTO sequence VALUES (0),(1),(2),(3),(4),(5); -- 可以插入更多

-- 然后在查询中使用这个表替代help_topic
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', s.seq + 1), ',', -1)
FROM 
    your_table
JOIN 
    sequence s
ON 
    s.seq < LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1;

5.3 正则表达式方案

MySQL 8.0+支持正则表达式函数,可以实现更复杂的分割逻辑:

-- 使用REGEXP_SUBSTR提取第N个元素
SELECT 
    REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, 2) AS second_item;
-- 结果:B

不过正则表达式的性能通常不如SUBSTRING_INDEX,建议只在必要时使用。

6. 实际项目中的经验分享

在金融行业的一个数据仓库项目中,我遇到了需要处理银行交易附言字段的需求。这些字段包含了用特殊符号分隔的多个信息项,如交易类型、渠道、业务代码等。最初尝试用应用程序处理,发现ETL过程太慢,后来改用SUBSTRING_INDEX方案,处理速度提升了8倍。

几个值得注意的经验:

  1. 对于超长字符串(超过100个分隔符),建议先在应用层预处理
  2. 频繁调用的查询可以考虑物化视图或预计算结果
  3. 在MySQL 5.7中,可以尝试使用JSON函数处理复杂格式
  4. 始终要考虑NULL值和空字符串的情况,避免意外错误

有一次我踩过的坑是没考虑到某些记录的字段可能为NULL,导致整个查询失败。后来加了COALESCE处理就解决了:

ON h.help_topic_id < LENGTH(COALESCE(o.related_orders,'')) - LENGTH(REPLACE(COALESCE(o.related_orders,''), ',', '')) + 1

对于现代应用开发,我建议尽量避免在数据库中存储分隔符分隔的数据。但如果必须处理这种遗留设计,SUBSTRING_INDEX函数无疑是最可靠的解决方案之一。

MySql字符串拆分实现split功能(字段分割转列) 需求描述 实现的sql 案例演示 字符串拆分: SUBSTRING_INDEX(str, delim, count) 替换函数:replace( str, from_str, to_str) 获取字符串长度:LENGTH( str ) 实现的原理解析 实现sql 正式的原理解析 Step1:首先获取最后需被拆分成多少个字符串,利用 help_topic_id 来模拟遍历 第n个字符串。 Step2:根据“,”逗号来拆分字符串,此处利用 SUBSTRING_INDEX(str, delim, count) 函 阅读详情

相关推荐

MySQL字符串分割技术详解与实战

在MySQL中,面对一些复杂的字符串分割需求时,仅依赖内置函数往往无法满足灵活的处理场景。例如,当字符串中存在多层嵌套结构、动态分隔符、或需要将分割结果返回为结果集时,就需要借助自定义函数(Stored Function)来实现更高级的字符串处理逻辑。本章将深入探讨如何设计并实现自定义函数来处理这些复杂的字符串分割任务。MySQL允许用户创建存储函数(Stored Function),这些函数可以接受输入参数、执行SQL逻辑并返回单个值。

weixin_29155599的博客 928

MySql-substring函数和substring_index函数的使用及练习

substring函数是文本处理函数,可以用来截取字符串。

weixin_40968325的博客 2万+

RK3568 开发实战:手动构建 Linux 4.19 内核镜像与设备树优化

本文详细介绍了为RK3568开发板手动编译Linux 4.19内核的完整实战流程。从环境搭建、源码获取、内核配置,到设备树深度定制与优化,再到镜像打包与烧写,旨在帮助开发者脱离官方SDK依赖,实现对嵌入式Linux系统的完全掌控,提升硬件适配与性能优化能力。

weixin_29175469的博客 270

SQL的substring_index()用法——MySQL字符串截取

1.substring_index函数的语法及其用法 (1)语法:substring_index(string,sep,num) 即substring_index(字符串,分隔符,序号) 参数说明 string:用于截取目标字符串的字符串。可为字段,表达式等。 sep:分隔符,string存在且用于分割的字符,比如“,”、“.”等。 num:序号,为非0整数。若为整数则表示从左到右数,若为负数则从右到左数。比如“www.mysql.com”截取字符‘www’,分割符为“.”,从左到右序号为1,

weixin_50853979的博客 7万+

STM32F103上跑mbedtls 2.24.0:手把手教你为MQTTS加密通信打好基础

本文详细介绍了如何在STM32F103上移植和裁剪mbedtls 2.24.0,为MQTTS加密通信打下坚实基础。通过版本选择、开发环境配置、深度裁剪策略和加密功能验证,帮助开发者在资源受限的MCU上实现安全通信。文章还提供了常见问题解决方案和性能优化技巧,助力物联网设备开发。

weixin_30321709的博客 462

数学建模小白必看:层次分析法(AHP)从入门到实战(附Python代码)

本文详细介绍了层次分析法(AHP)在数学建模竞赛中的应用,从构建判断矩阵到一致性检验,再到权重计算和实战案例。通过Python代码实现,帮助小白快速掌握AHP的核心技巧,解决评价类难题,提升建模效率。

web39explorer的博客 922

mysql 中 substring_index的用法,小白都能看懂的。

mysql 中 substring_index的用法,小白都能看懂的。在处理数据的时候非常好用,也适用于写于etl流程的sql中。

weixin_44228413的博客 1834

基于opencv自定义网格板的相机标定软件源码

本软件开发版使用的是VS2013和QT5.7, 安装版的需要安装VS核心库(目录里的vcredist_x64.exe, 已安装VS的不需要)。采用强大的模板匹配算法,本程序适用于自定义大范围网格标定(本例程网格使用的是自己画的网格板31*21*10cm),网格点识别度高,矫正速度快,精度高。 使用步骤 第1步: 打开图片,打开标定工具GridCalibTool.exe, 然后选择打开一张待标定的图(CalibFiles文件夹下的500.jpg)。如下图: 第2步: 提取待标定区域(去掉多余部分,只保留网

qq_33138437的博客 1123

mysql中SUBSTRING_INDEX函数用法详解

mysql中SUBSTRING_INDEX函数用法详解

phplife的技术专栏,专注于php,golang,ai等技术应用 1万+

【CODESYS】-软件Bug与处理方法(20240710更)

【CODESYS】-软件Bug与处理方法(不定期更新)

qq_35316470的博客 5190

【Kubernetes】怎么根据pod找到关联的deployment呢

在文章中我们删除pod需要删除deployment,那么怎么根据pod找到关联的deployment呢。

九师兄 505

SUBSTRING_INDEX()用法

select SUBSTRING_INDEX('strlist',',',1), -- 截取字符串中第一个','前的内容

qq_39822451的博客 1万+

机器视觉应用工程师职位深度解析与面试指南

广东奥普特科技招聘机器视觉应用工程师,要求大专以上理工科专业背景,熟悉C/C++或PLC编程,具备自动化设备调试经验。岗位职责包括视觉项目方案评估、系统安装调试、售后技术支持及异常处理,需接受外派出差。该职位面向专业对口的应届生或实习生,强调动手能力、沟通技巧和吃苦耐劳精神。机器视觉工程师需掌握图像处理算法、硬件配置等核心技术,在工业自动化领域具有良好发展前景。求职者应注重编程能力和实践经验的积累,以胜任这一技术密集型岗位。

郑伟强dev的专栏 435

Mysql 常用函数(42)- substring_index 函数

Mysql常用函数的汇总,可看下面系列文章 https://www.cnblogs.com/poloyy/category/1765164.html 作用 按照指定的分隔符划分字符串,可指定分隔符出现的次数 substring_index 的语法格式 SUBSTRING_INDEX(str, delim, count) 语法格式说明 str:需要操作的字符串 delim:...

qq_33801641的博客 2987

6.10华为OD机试真题 新系统 - 查找温度记录统计信息 (Java/Py/C/C++/Js/Go)

6.10华为OD机试真题 新系统 - 查找温度记录统计信息 (Java/Py/C/C++/Js/Go)

算法大师! 439

jre9下载 jdk9

(Java SE Runtime Environment 9.0) 9.0u175 官方最新版 【x86|x64】

5分钟学会substring_index:字符串分割入门指南

简单来说,它是MySQL中用来按分隔符截取字符串的函数。SUBSTRING_INDEX(字符串, 分隔符, 计数)。最常用的场景比如拆分邮箱域名、解析日志字段等。

EmeraldWolf23的博客 623

Statistics and Machine Learning Toolbox

Statistics and Machine Learning Toolbox(带书签),MATLAB统计学习工具

MySQL SUBSTRING_INDEX函数详解:分割字符串、提取URL参数实战与性能避坑

摘要:MySQL的SUBSTRING_INDEX函数是处理字符串分割的专用工具,特别适合提取URL参数、拆分ID列表等场景。文章详细解析了该函数的语法规则、正负计数逻辑和实际应用案例,重点对比了与SUBSTR、REPLACE的性能差异,指出双层嵌套会重复扫描字符串导致性能问题。针对接口日志提取verify_idf_id参数的实际业务场景,提供了通用解决方案和性能优化建议,并总结了五大常见踩坑点及规避方法。最后明确了函数适用边界,强调固定前缀场景应优先使用SUBSTR方案,动态字符串才推荐用分割函数,大数据量

得塔云的博客 252

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

Outlook Express 6 (OE6) for Win7/Win8/Win10

英文版 Outlook Express6.0, 兼容Win7/Win10/WinXP,解压就可用。

mysql函数substring_index与oralce函数substr

mysql 语法: substring_index(str,delim,count) 返回字符串str中在第 count 个出现的分隔符 delim之前的子串,和java中的str.substring函数类似。   如果count是正数时候,substring_index是从左到右的顺序检查分隔符delim所在的位置,返回的是当前delim位置左侧字符串。   如果count是负数时候,s...

qijunran的博客 2940

MySQL字符串拆分的七种武器:从SUBSTRING_INDEX到JSON函数实战

本文详细解析MySQL中七种高效的字符串拆分方法,从传统的SUBSTRING_INDEX组合技到现代的JSON函数应用,帮助开发者处理电商SKU编码、多标签字段等常见场景。通过性能对比和实战案例,展示如何选择最适合业务需求的字符串拆分方案,提升数据处理效率。

jwt8token的博客 80

5个真实业务场景下的MySQL SUBSTRING高级用法

在日常开发中,我们经常需要从字符串中提取或处理特定部分的数据。今天,我就通过5个真实业务场景,分享一下SUBSTRING函数在实际工作中的应用。平台提供了即时的MySQL环境,无需本地安装数据库就能测试各种字符串处理场景,对于SQL学习和开发来说非常方便。比如订单号可能是'20230515123456',其中前8位'20230515'表示下单日期。系统日志中的时间戳可能是'2023-05-15 14:30:22 [INFO]'这样的格式,我们需要提取出具体时间部分。在实际工作中,我发现使用。

GreyWolf12的博客 1019

5个实际业务场景中的SUBSTRING_INDEX应用案例

在数据库操作中,字符串处理是常见需求,MySQL的SUBSTRING_INDEX函数是一个非常实用的工具。特别是当需要分享SQL示例给同事时,直接生成可运行的代码片段,对方打开链接就能看到完整效果,省去了搭建本地环境的麻烦。电商系统中,订单信息经常以特定格式存储。假设我们有一个包含产品SKU和属性的字符串,格式为"SKU-属性1-属性2",如"A123-RED-L"表示红色大号的A123商品。它的响应速度快,界面简洁,还能一键保存代码片段,对我这种经常需要尝试不同SQL写法的人来说真是帮了大忙。

IndigoNight21的博客 934
上一篇: LWIP移植避坑指南:ENC28J60驱动与协议栈配置的黄金法则
下一篇: 质因数分解的工业级应用与竞赛题目的距离
docker8compose
博客等级 码龄1天 0粉丝 1102原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值