MySQL数据转换：如何高效地实现行列互转和字符串拆分？

说实话，在MySQL的这些行列转换和字符串分割的过程中，我遇到了很多坑。
但过了河寻找石头后，我终于明白了一些事情。
你贴的方法很全面。
我根据我遇到的具体情况给大家详细介绍一下。

我们先来说一下行转换。
使用JSON_TABLE确实没什么麻烦，但是有一个问题需要注意：分割特别长的字符串，MySQL 8 .0+的性能会突然下降。
我记得有一个项目，几十万条记录被分割，使用JSON_TABLE直接卡住了。
最终通过使用存储过程进行批处理解决了这个问题。
关键是要记住 CONCAT 中的字符串必须用单引号引起来。
JSON_TABLE的PATH参数也很容易写错。
$ 符号前面不能有反斜杠。
我当时都快被这件事搞糊涂了。

我们来谈谈转行的事。
合并UNIONALL列是一个老技巧，但是当列名重复时就会出现问题。
有一个客户表，包含三列：类型、状态和注释。
合并后，所有记录都成为类型列。
最终花了一个下午的时间给UNIONALL的各个部分添加不同的别名。
后来我养成了在每个UNIONALL前面加注释的习惯，像这样： SQL -
列类型 SELECT id, 'type' AS field_name, type FROM your_table 联合所有 -
状态栏 SELECT id, 'status', status FROM your_table
这样可以在出现错误时更容易定位问题。
至于生成自增ID，我更喜欢使用临时表的方式，比如： SQL 创建临时表 temp_data AS 从 your_table 中选择 id, 1 AS seq 联合所有 SELECT id, 2 FROM your_table
然后直接查询temp_data表，比SET@row_num=0更直观。

优化建议中，版本适配尤为重要。
有一个外包项目使用MySQL 5 .7 ，但客户坚持无法升级。
结果，当分割一百万条记录时，存储过程中的循环体卡在了 SUBSTRING_INDEX 函数上。
后来切换到REPEATABLE READ隔离级别，用用户定义的变量慢慢拆解，终于执行完成了。
这件事让我意识到，低版本MySQL的字符串处理功能确实不是吃素的。

当数据量太大时，批处理才是硬道理。
我有一个电商场景，每天需要拆分几千万个产品属性的逗号字符串。
直接执行JSON_TABLE会导致主从延迟。
最后换成基于产品ID哈希的bucket，每个bucket单独拆分，然后汇总。
性能得到显着提高。

最后，我们来说说一些轶事。
分割字符串时，MySQL 默认忽略连续的分隔符。
例如，将“1 2 ,3 ”相除后，就只剩下“1 ”和“2 ,3 ”。
如果想保留空行，需要使用该函数正则表达式 REPLACE+REGEXP_REPLACE。
这是一个很棒的技巧，但是它消耗大量性能，并且仅在数据量不大的场景下使用。

总之，这些方法都是现成的，但是使用时需要根据实际情况进行调整。
例如，在一个项目中，需要修改客户表中的2 00列。
通过硬写UNIONALL并最终使用存储过程动态拼接SQL解决了该问题。
就技术而言，灵活性是最重要的。

SQLserver用逗号隔开的数据如何改为分行

STRING_SPLIT 函数。
2 01 6 年版本中引入。
SELECT FROM STRING_SPLIT('苹果、香蕉、梨、橙子', ',') 这是一个洞。
版本太低了。
记住STRING_SPLIT。

一行拆多行 sql implala

上周我尝试了 Impala split 功能。

2 02 3 年使用的场景是日志分析。

该表有一个名为 user_actions 的字段。

存储逗号分隔的字符串。

例如，“点击、查看、点赞”
使用拆分功能进行拆分。

SQL 选择 split(user_actions, ',') 作为 actions_list 来自日志表 限制 1 0；
拿出来就是一个数组。

那我想一个人去。

使用爆炸功能。

SQL 选择explode(split(user_action,','))作为single_action 来自日志表 限制 1 0；
划分后，每个动作都在自己的线上进行。

还尝试过 JSON 数据。

有一个名为 user_preferences 的字段。

存储的是一个JSON数组。

例如[“电影”、“音乐”、“书籍”]
使用 json_extract_array_elements 函数。

SQL 选择 json_extract_array_elements(user_preferences) 作为首选项 从用户表 限制 1 0；
可以将 JSON 数组中的每个元素拆分为单独的行。

这些功能还是比较实用的。

这取决于你。