腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MYSQL根據(jù)JSON列將一行拆為多行的操作方法

更新時(shí)間：2023年06月13日 14:56:04 作者：Mingvvv

這篇文章主要介紹了MYSQL根據(jù)JSON列將一行拆為多行的操作方法,本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

MYSQL根據(jù)JSON列將一行拆為多行

使用JSON_TABLE

例如表中存在 json 字段 json_filed

我們要實(shí)現(xiàn)如下效果

可以使用 json_table 去實(shí)現(xiàn)，json_table 可以將 json 字段轉(zhuǎn)換為 table 去使用

SELECT json_field,j.json_single_value FROM `user`
left join 
json_table(json_field, '$[*]' columns (json_single_value int path '$')) as j on true

查詢結(jié)果如下，由一行分割成多行

Incorrect arguments to JSON_TABLE

有些時(shí)候我們會用到子查詢，如下

SELECT a.json_field,j.json_single_value FROM 
( select * from `user` where 1=1) as a
left join json_table(a.json_field, '$[*]' columns (json_single_value int path '$')) as j on true

我們需要先對目標(biāo)表進(jìn)行篩選，用來節(jié)省占用內(nèi)存，并提高查詢效率，然后再將其 json 字段轉(zhuǎn)換為 table
但是當(dāng)我們運(yùn)行上面的 SQL 時(shí)會爆出 Incorrect arguments to JSON_TABLE 的錯(cuò)誤。
原因是我們子查詢中的 json_field 字段格式有問題，需要我們強(qiáng)制轉(zhuǎn)換一下

SELECT a.json_field,j.json_single_value FROM 
( select * from `user` where 1=1) as a
left join json_table(CAST(a.json_field AS JSON), '$[*]' columns (json_single_value int path '$')) as j on true

MySql 一行變多行(根據(jù)特定符號分割)

一、測試數(shù)據(jù)

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) DEFAULT NULL,
`num` int(8),
PRIMARY KEY (`id`)
);
INSERT INTO `test`(`name`, `num`) VALUES ('a1,b258,c', 11);
INSERT INTO `test`(`name`, `num`) VALUES ('f,g123456,h,i85,j', 33);
INSERT INTO `test`(`name`, `num`) VALUES ('d,e1234', 22);

那么: SELECT * FROM test; :

那么，把數(shù)據(jù)轉(zhuǎn)換成為下面這樣，需要怎么樣實(shí)現(xiàn)呢:

二、普通 sql 實(shí)現(xiàn)(需要依賴 mysql.help_topic 表)

SELECT
    a.id,a.num,SUBSTRING_INDEX( SUBSTRING_INDEX( a.`name`, ',', b.help_topic_id + 1 ), ',',-1 ) name
FROM
    test a
    JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`name`) - LENGTH( REPLACE ( a.`name`, ',', '' ) ) + 1 );

三、mysql.help_topic 無權(quán)限處理辦法

mysql.help_topic 的作用是對 SUBSTRING_INDEX 函數(shù)出來的數(shù)據(jù)(也就是按照分割符分割出來的)數(shù)據(jù)連接起來做笛卡爾積。

如果 mysql.help_topic 沒有權(quán)限，可以自己創(chuàng)建一張臨時(shí)表，用來與要查詢的表連接查詢。

獲取該字段最多可以分割成為幾個(gè)字符串:

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

創(chuàng)建臨時(shí)表，并給臨時(shí)表添加數(shù)據(jù):

注意:

臨時(shí)表必須有一列從 0 或者 1 開始的自增數(shù)據(jù)
臨時(shí)表表名隨意，字段可以只有一個(gè)
臨時(shí)表示的數(shù)據(jù)量必須比 MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1) 的值大

DROP TABLE IF EXISTS tmp_help_topic;
CREATE TABLE IF NOT EXISTS tmp_help_topic (
help_topic_id bigint(20) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (help_topic_id)
);
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();

查詢:

SELECT
    a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) name
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );

四、函數(shù)的意思

4.1 REPLACE 函數(shù):

把 字符串   a,b,c,d   里面的逗號替換成空字符串
SELECT REPLACE('a,b,c,d', ',', '');
-- 輸出: abcd

那么:

# 獲取逗號的個(gè)數(shù)
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));
# 按照逗號分割后會有幾個(gè)元素，這里分割后就是 a b c d，就是 4 個(gè)元素 
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1);

4.2 SUBSTRING_INDEX 函數(shù):

SUBSTRING_INDEX 是字符串截取函數(shù)

SUBSTRING_INDEX(str, delim, count)

str : 表示需要拆分的字符串
delim : 表示分隔符，通過某字符進(jìn)行拆分
count : 當(dāng) count 為正數(shù)，取第 n 個(gè)分隔符之前的所有字符；當(dāng) count 為負(fù)數(shù)，取倒數(shù)第 n 個(gè)分隔符之后的所有字符。

例如:

SELECT SUBSTRING_INDEX('a*b*c*d', '*', 1); -- 返回: a
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 2); -- 返回: a*b 
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 3); -- 返回: a*b*c
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 4); -- 返回: a*b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -1); -- 返回: d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -2); -- 返回: c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -3); -- 返回: b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -4); -- 返回: a*b*c*d

那么:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 1), '*', -1); -- 返回: a
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 2), '*', -1); -- 返回: b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 3), '*', -1); -- 返回: c
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 4), '*', -1); -- 返回: d

五、一行變多行原理

回到 sql:

SELECT
    a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) 
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );

SUBSTRING_INDEX(SUBSTRING_INDEX(a.name, ',', b.help_topic_id), ',',-1 ) 就是獲取 tmp_help_topic 表的 help_topic_id 字段的值作為 name 字段的第幾個(gè)子串
使用了 join 就會把字段 name 分為 (LENGTH( a.name) - LENGTH(REPLACE(a.name, ',', '')) + 1 ) 行，并且每行的字段剛好是 name 字段的第 help_topic_id 個(gè)子串

到此這篇關(guān)于MYSQL -- 根據(jù)JSON列將一行拆為多行的文章就介紹到這了,更多相關(guān)mysql一行拆為多行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: