需求:业务改造,需要把某张表的数据洗到另一张表中
需要将一个字符串数组(字段),转到另一张表,且需要根据分隔符分割后生成多行数据。
于是我百度找到了一种mysql处理json的方案,那就是JSON_TABLE,使用非常简单,在测试环境使用JSON_TABLE成功实现我想要的功能,但是在生成环境却报错了。原来是生成环境的Mysql版本太低了不支持JSON_TABLE,用的是Mysql5.7版本,而测试环境用的是Mysql8.0版本。
接下来看看怎么把图一的数据转换成图二的数据:
图一:
图二:
一、通过JSON_TABLE实现
SELECT
channel.`code` AS channel_code,
'' AS zone_name,
channel.create_by,
channel.update_by,
t.*
FROM channel,
JSON_TABLE (REPLACE (channel.dest_postal_code, '\'', '"' ),'$[*]'
COLUMNS (
begin_postal_code VARCHAR (10) PATH '$[0]',
end_postal_code VARCHAR (10) PATH '$[1]'
)) AS t;
二、通过编写sql实现
想办法找规律,发现可以先把多余的符号全部去掉:
SELECT
`code`,
REPLACE (
REPLACE (
REPLACE (
REPLACE ( dest_postal_code, '],[', ' ' ),//替换成空格
'"', '' ),//去掉双引号
'[[', '' ), //去掉[[
']]', '' ) //去掉]]
AS dest_postal_code
FROM
channel
WHERE
dest_postal_code != '[]';
然后通过substring_index进行切割,通过mysql内置的表help_topic中的help_topic_id来当切割符的索引
INSERT INTO channel_postal_zone ( channel_code, zone_name, create_by, update_by, begin_postal_code, end_postal_code )
SELECT
t.`code` AS channel_code,
'' AS zone_name,
create_by,
update_by,
substring_index( t.dest_postal_code, ',', 1 ) AS begin_postal_code,
substring_index( t.dest_postal_code, ',',- 1 ) AS end_postal_code
FROM
(
SELECT
info.`code`,
create_by,
update_by,
substring_index( substring_index( info.dest_postal_code, ' ', b.help_topic_id + 1 ), ' ',- 1 ) AS dest_postal_code
FROM
(
SELECT
`code`,
create_by,
update_by,
REPLACE ( REPLACE ( REPLACE ( REPLACE ( dest_postal_code, '],[', ' ' ), '"', '' ), '[[', '' ), ']]', '' ) AS dest_postal_code
FROM
channel
WHERE
dest_postal_code != '[]'
) AS info
JOIN mysql.help_topic b ON b.help_topic_id < ( length( info.dest_postal_code ) - length( REPLACE ( info.dest_postal_code, ' ', '' )) + 1 )
) AS t;
最后有兴趣的话可以去官方文档了解一下Mysql的JSON处理功能:
https://dev.mysql.com/doc/refman/8.0/en/json.html