SQL查詢之欄位是逗號分隔開的陣列如何查詢匹配資料問題

2023-03-07 06:03:29

欄位是逗號分隔開的陣列如何查詢匹配資料

SELECT
    * 
FROM
    U_PUR1 
WHERE
    CHARINDEX( ',' + '100306' + ',', ',' + DeptCode + ',' ) >0

匹配字串開始位置的函數 CHARINDEX(str1,str,[start])

CHARINDEX(str1,str,[start]) 函數返回子字串 str1 在字串 str 中的開始位置，start 為搜尋的開始位置，如果指定 start 引數，則從指定位置開始搜尋；如果不指定 start 引數或者指定為0或者負值，則從字串開始位置搜尋。

SELECT
    * 
FROM
    U_PUR1 
WHERE
    ',' + DeptCode + ',' LIKE '%,' + '100306' + ',%';

Where Like 的方式顯然更好理解一些，首先在欄位收尾加上逗號，這樣可以使得欄位中每一個值都擁有完整的格式，再使用Like進行模糊匹配時，可以使首尾的值都能匹配到。

在工作中，經常會遇到一對多的關係，比如使用者和愛好。想要在mysql中儲存這種關係，一般有兩種方式，一種是建立一張使用者愛好表user_hobby(user_id, hobby_id)，這樣一條user_id就會存在多條記錄。

或者採用第二種方式，直接在使用者表中增加hobby_ids欄位，將hobby_id拼接成用逗號分隔的字串儲存起來。

那麼問題來了，如果採用第二種方式的話，查詢的時候要如何處理呢？下面介紹幾種常見的處理方式。

可能最新想到的是採用like的形式，如下：

select * from user where hobby_ids like '%1%';

這種方式是否可行呢？行，但是有適用範圍，如果hobby_id在10以內，那麼這麼處理完全是可以的，但是一旦超過了10，那'%1%'就會把10，11,這些帶1的id全都匹配處理。

為了解決like存在的問題，還可以使用mysql提供的find_in_set(str, strlist)，sql可以這樣寫：

select * from user where find_in_set('1', hobby_ids);

這樣mysql就會把hobby_ids的值，按照逗號分隔的一個個元素去匹配。如果我們要匹配多個值要怎麼辦呢，比如查詢喜歡1-籃球或2-足球的使用者，可以

select * from user where find_in_set('1', hobby_ids) or find_in_set('2', hobby_ids);

如果匹配的值較多，我們可能要拼很長的sql，那有沒有稍微簡短的方式呢，我們可以採用下面的方式。

regexp是mysql提供的強大的正則匹配方式。上面的sql就可以改寫成：

select * from user where hobby_ids regexp '(^|,)(1|2)(,|$)';

這裡是完全採用字串正則匹配的方式，(1|2)表示匹配1或者2，(^|,)表示數位前面必須是字串開頭或者是一個逗號，(,|$)表示數位後面必須是逗號或者字串的結尾。這樣就可以完整匹配逗號分隔的元素了。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。