Mysql將字串按照指定字元分割的正確方法

2022-05-30 22:04:13

前言

在某些場景下（比如：使用者上傳檔案或者圖片等），一般的做法是將檔案資訊（檔名，檔案路徑，檔案大小等）儲存到檔案表（user_file）中，然後再將使用者所有上傳的檔案的id用一個指定字元拼接然後存在表（user）中某個欄位裡（假設是：file_ids）。
在展示使用者上傳的檔案時就直接查詢檔案表中就好了：

-- 一般的語句是這樣的，假設使用者唯一鍵是id
select * from file where id in(select file_ids from user where id = 1);

sql語句沒有問題，檔案也能查詢出來，但是，上傳的檔案大於1個後，再用這個sql語句查詢就只返回1條記錄了，可能就會疑惑了，為什麼只返回一條記錄？？？；

肯定有人做過這樣的驗證

-- 先執行下面這個sql，正確返回1，2，3 假設上傳的檔案id是1，2，3；
select file_ids from user where id = 1
-- 然後返回的檔案id寫死在sql語句中,執行成功返回3條記錄
select file_ids from user where id in ('1' , '2' , '3');
-- 最後再整體試了下，結果返回一條......
select * from file where id in(select file_ids from user where id = 1)

-- 然後可能會想到：我把in裡面的拼接成'1','2','3',這樣總可以了吧？
select * from file where id in (select concat(''', replace(file_ids,',','','') ,''') from user where id = 1);
-- concat(''', replace(file_ids,',','','') ,''') 確實能拼接成上面說的形式，但是結果還是隻有一條

是因為這個查詢只返回一個欄位，所以只會返回一條記錄（即使有多個逗號拼接，或者是手動拼接的，Mysql只認為是一個值，具體底層不清楚…），正確做法如下：

一：分兩次查詢（不是本文重點，但可以實現）

select file_ids from user where id = 1

select file_ids from user where id in ('1' , '2' , '3');
或者
select file_ids from user where find_in_set(id , '1,2,3');

二：將file_ids欄位分割成多列，類似Mysql的行轉列

與Mysql行轉列區別：行轉列要知道列的內容，而這個不用，只需知道拼接的字元就行了

-- 下面語句將會把1，2，3，4一個欄位轉換成四行，依次是1，2，3，4
SELECT
	a.id,
	a.file_ids,
	substring_index(
		substring_index(
			a.file_ids,
			',',
			b.help_topic_id + 1
		),
		',' ,- 1
	) file_id
FROM
	user a
JOIN mysql.help_topic b ON b.help_topic_id < (
	length(a.file_ids) - length(REPLACE(a.file_ids, ',', '')) + 1
)
where id = 1
;
-- 然後將上面語句寫在in()裡面就行了，寫在in()裡面的話記住只能查詢一個欄位哦！

上面語句可以直接複製過去，只需將a表及a表欄位換成自己的表明及欄位就行了，至於mysql.help_topic，是Mysql自帶的，不用管的。

附：mysql如何將字串按分隔符拆分

1.字串拆分： SUBSTRING_INDEX（pressure 136/70 血壓），例如：

SUBSTRING_INDEX(pressure ,',',1)     #擷取第一個逗號（,）號以前的字串
SUBSTRING_INDEX(pressure ,',',-1)    #擷取倒數第一個逗號（,）號以後的字串

2.替換函數：replace( str, from_str, to_str)。例如：

UPDATE bgs_building_copy1 SET `name`=replace(`name`,'=',"");    #替換等號為空字串

總結

到此這篇關於Mysql將字串按照指定字元分割的文章就介紹到這了,更多相關Mysql字串分割內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

Mysql將字串按照指定字元分割的正確方法

目錄

前言

一：分兩次查詢（不是本文重點，但可以實現）

二：將file_ids欄位分割成多列，類似Mysql的行轉列

附：mysql如何將字串按分隔符拆分

總結

熱門文章