欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時(shí)間：2023年06月13日 14:56:04   作者：Mingvvv  
這篇文章主要介紹了MYSQL根據(jù)JSON列將一行拆為多行的操作方法,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(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

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

Incorrect arguments to JSON_TABLE

有些時(shí)候我們會(huì)用到子查詢(xún)，如下

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

我們需要先對(duì)目標(biāo)表進(jìn)行篩選，用來(lái)節(jié)省占用內(nèi)存，并提高查詢(xún)效率，然后再將其 json 字段轉(zhuǎn)換為 table
但是當(dāng)我們運(yùn)行上面的 SQL 時(shí)會(huì)爆出 Incorrect arguments to JSON_TABLE 的錯(cuò)誤。
原因是我們子查詢(xún)中的 json_field 字段格式有問(wèn)題，需要我們強(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ù)特定符號(hào)分割)

一、測(cè)試數(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)(需要依賴(lài) 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 無(wú)權(quán)限處理辦法

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

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

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

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

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

注意:

  • 臨時(shí)表必須有一列從 0 或者 1 開(kāi)始的自增數(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 ();

查詢(xún):

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   里面的逗號(hào)替換成空字符串
SELECT REPLACE('a,b,c,d', ',', '');
-- 輸出: abcd

那么:

# 獲取逗號(hào)的個(gè)數(shù)
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));
# 按照逗號(hào)分割后會(huì)有幾個(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 : 表示分隔符，通過(guò)某字符進(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 就會(huì)把字段 name 分為 (LENGTH( a.name) - LENGTH(REPLACE(a.name, ',', '')) + 1 ) 行，并且每行的字段剛好是 name 字段的第 help_topic_id 個(gè)子串

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

相關(guān)文章

  • mysql創(chuàng)建刪除表的實(shí)例詳解

    mysql創(chuàng)建刪除表的實(shí)例詳解

    這篇文章主要介紹了mysql創(chuàng)建刪除表的相關(guān)資料，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧
    2017-10-10
  • MySQL數(shù)據(jù)庫(kù)主從同步實(shí)戰(zhàn)過(guò)程詳解

    MySQL數(shù)據(jù)庫(kù)主從同步實(shí)戰(zhàn)過(guò)程詳解

    這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)主從同步,結(jié)合實(shí)例形式詳細(xì)分析了MySQL數(shù)據(jù)庫(kù)主從同步基本配置方法與操作注意事項(xiàng),需要的朋友可以參考下
    2020-05-05
  • 銀河麒麟V10安裝MySQL5.7的詳細(xì)過(guò)程

    銀河麒麟V10安裝MySQL5.7的詳細(xì)過(guò)程

    這篇文章主要介紹了銀河麒麟V10安裝MySQL5.7,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
    2023-05-05
  • MySQL Limit執(zhí)行過(guò)程分析探索

    MySQL Limit執(zhí)行過(guò)程分析探索

    limit是MySql的內(nèi)置函數(shù),一般用于查詢(xún)表中記錄的條數(shù),作用是用于限制查詢(xún)條數(shù),下面這篇文章主要給大家介紹了關(guān)于SQL中l(wèi)imit函數(shù)語(yǔ)法與用法的相關(guān)資料,詳細(xì)講了MySQL Limit執(zhí)行過(guò)程
    2022-12-12
  • MySQL如何使用時(shí)間作為判斷條件

    MySQL如何使用時(shí)間作為判斷條件

    這篇文章主要介紹了MySQL如何使用時(shí)間作為判斷條件,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
    2020-11-11
  • MySQL 日期時(shí)間格式化函數(shù) DATE_FORMAT() 的使用示例詳解

    MySQL 日期時(shí)間格式化函數(shù) DATE_FORMAT() 的使用示例詳解

    `DATE_FORMAT()`是MySQL中用于格式化日期時(shí)間的函數(shù),本文詳細(xì)介紹了其語(yǔ)法、格式化字符串的含義以及常見(jiàn)日期時(shí)間格式組合,感興趣的朋友一起看看吧
    2025-03-03
  • MySQL文件權(quán)限存在的安全問(wèn)題和解決方案

    MySQL文件權(quán)限存在的安全問(wèn)題和解決方案

    MySQL文件權(quán)限是用來(lái)控制用戶(hù)對(duì)數(shù)據(jù)庫(kù)文件以及目錄的操作權(quán)限,在MySQL中,這些權(quán)限通常由系統(tǒng)管理員分配,通過(guò)GRANT和REVOKE語(yǔ)句進(jìn)行管理,本文給大家介紹了MySQL文件權(quán)限存在的安全問(wèn)題和解決方案,需要的朋友可以參考下
    2024-07-07
  • linux下mysql自動(dòng)備份腳本代碼

    linux下mysql自動(dòng)備份腳本代碼

    mysql是以mysql用戶(hù)身份運(yùn)行的,對(duì)/home /mybackup不可寫(xiě)也會(huì)失敗
    2010-07-07
  • mysql忘記密碼重置的方法實(shí)現(xiàn)

    mysql忘記密碼重置的方法實(shí)現(xiàn)

    本文主要介紹了mysql忘記密碼重置的方法實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2023-03-03
  • MySQL中SQL連接操作左連接查詢(xún)(LEFT?JOIN)示例詳解

    MySQL中SQL連接操作左連接查詢(xún)(LEFT?JOIN)示例詳解

    這篇文章主要給大家介紹了關(guān)于MySQL中SQL連接操作左連接查詢(xún)(LEFT?JOIN)的相關(guān)資料,左連接（LEFT?JOIN）是SQL中用于連接兩個(gè)或多個(gè)表的一種操作,它返回左表的所有行,并根據(jù)連接條件從右表中匹配行,需要的朋友可以參考下
    2024-12-12

最新評(píng)論