MySQL?FIND_IN_SET字符串函數(shù)深度解析
更新時間：2025年12月18日 10:09:30 作者：安得小學(xué)僧-設(shè)計模式之美
FIND_IN_SET是MySQL中處理分隔字符串的重要函數(shù),適合處理標(biāo)簽、分類等多值場景,這篇文章主要介紹了MySQL?FIND_IN_SET字符串函數(shù)的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
函數(shù)概述
FIND_IN_SET() 是 MySQL 提供的一個字符串函數(shù)，用于在逗號分隔的字符串集合中查找指定值的位置。這個函數(shù)在處理某些特定的數(shù)據(jù)結(jié)構(gòu)時非常有用，但也容易被誤用，導(dǎo)致性能問題和邏輯錯誤。
函數(shù)簽名
FIND_IN_SET(str, strlist)
返回值
- 正整數(shù)：如果找到
str，返回其在strlist中的位置（從1開始計數(shù)） - 0：如果未找到
str或strlist為空字符串 - NULL：如果任一參數(shù)為 NULL
基本語法與用法
基礎(chǔ)示例
-- 基本查找
SELECT FIND_IN_SET('b', 'a,b,c,d'); -- 返回: 2
SELECT FIND_IN_SET('e', 'a,b,c,d'); -- 返回: 0
SELECT FIND_IN_SET('a', 'a,b,c,d'); -- 返回: 1
-- 在表查詢中使用
SELECT * FROM users
WHERE FIND_IN_SET('admin', roles) > 0;
-- 用于條件判斷
SELECT
name,
CASE
WHEN FIND_IN_SET('vip', user_tags) > 0 THEN 'VIP用戶'
ELSE '普通用戶'
END as user_type
FROM users;
邊界情況
-- 空字符串和NULL的處理
SELECT FIND_IN_SET('', 'a,b,c'); -- 返回: 0
SELECT FIND_IN_SET('a', ''); -- 返回: 0
SELECT FIND_IN_SET(NULL, 'a,b,c'); -- 返回: NULL
SELECT FIND_IN_SET('a', NULL); -- 返回: NULL
-- 特殊字符
SELECT FIND_IN_SET('a,b', 'a,b,c'); -- 返回: 0 (查找的是整個 'a,b' 字符串)
SELECT FIND_IN_SET('a,b', 'a,b,a,b,c'); -- 返回: 3 (找到完整的 'a,b')
工作原理
內(nèi)部實現(xiàn)邏輯
FIND_IN_SET 的工作原理可以理解為以下步驟：
- 字符串分割：將
strlist按逗號分割成多個子字符串 - 逐一比較：將
str與每個子字符串進(jìn)行精確匹配 - 返回位置：如果匹配成功，返回位置索引（從1開始）
-- 等價的實現(xiàn)邏輯（偽代碼）
FUNCTION FIND_IN_SET(needle, haystack):
IF needle IS NULL OR haystack IS NULL:
RETURN NULL
items = SPLIT(haystack, ',')
FOR i = 1 TO LENGTH(items):
IF items[i] = needle:
RETURN i
RETURN 0
字符匹配規(guī)則
-- 精確匹配，區(qū)分大小寫
SELECT FIND_IN_SET('A', 'a,b,c'); -- 返回: 0
SELECT FIND_IN_SET('a', 'A,b,c'); -- 返回: 0
-- 不進(jìn)行模糊匹配
SELECT FIND_IN_SET('ab', 'a,abc,c'); -- 返回: 0
SELECT FIND_IN_SET('abc', 'a,abc,c'); -- 返回: 2
常見陷阱與問題
1. 索引失效問題
最大的陷阱：無法使用索引
-- 這個查詢無法使用索引，即使在 user_roles 字段上有索引
SELECT * FROM users
WHERE FIND_IN_SET('admin', user_roles) > 0;
-- 執(zhí)行計劃顯示全表掃描
EXPLAIN SELECT * FROM users
WHERE FIND_IN_SET('admin', user_roles) > 0;
-- type: ALL (全表掃描)
2. 數(shù)據(jù)類型陷阱
-- 數(shù)值類型的隱式轉(zhuǎn)換
CREATE TABLE test (
id INT,
numbers VARCHAR(100) -- 存儲: '1,2,3,4,5'
);
-- 這些查詢的結(jié)果可能出乎意料
SELECT FIND_IN_SET(1, '1,2,3'); -- 返回: 1 (正確)
SELECT FIND_IN_SET('01', '1,2,3'); -- 返回: 0 (字符串 '01' != '1')
SELECT FIND_IN_SET(1.0, '1,2,3'); -- 返回: 1 (數(shù)值轉(zhuǎn)換)
3. 空值和空字符串陷阱
-- 空字符串在集合中的處理
SELECT FIND_IN_SET('', 'a,,c'); -- 返回: 0 (不匹配空元素)
SELECT FIND_IN_SET('a', 'a,,c'); -- 返回: 1
SELECT FIND_IN_SET('c', 'a,,c'); -- 返回: 3
-- 意外的空元素
INSERT INTO tags VALUES ('tag1,,tag3'); -- 中間有空元素
SELECT FIND_IN_SET('tag2', tags); -- 可能不是期望的結(jié)果
4. 逗號字符陷阱
-- 查找包含逗號的字符串
SELECT FIND_IN_SET('a,b', 'a,b,c'); -- 返回: 0 (查找整個 'a,b')
SELECT FIND_IN_SET('hello,world', 'hello,world,test'); -- 返回: 1
-- 數(shù)據(jù)中意外包含逗號
INSERT INTO categories VALUES ('電子產(chǎn)品,手機(jī),iPhone');
-- 如果某個分類名本身包含逗號，會破壞結(jié)構(gòu)
5. 性能陷阱
-- 大數(shù)據(jù)量時的性能問題
SELECT COUNT(*) FROM orders
WHERE FIND_IN_SET('completed', status_history); -- 在百萬級數(shù)據(jù)上很慢
-- 復(fù)雜查詢中的性能疊加
SELECT * FROM products p
JOIN categories c ON FIND_IN_SET(c.id, p.category_ids)
WHERE FIND_IN_SET('sale', p.tags) > 0; -- 雙重性能損失
性能分析
時間復(fù)雜度
- 單次調(diào)用：O(n)，其中 n 是逗號分隔列表的長度
- 表查詢：O(m×n)，其中 m 是行數(shù)，n 是平均列表長度
性能測試對比
-- 創(chuàng)建測試數(shù)據(jù)
CREATE TABLE performance_test (
id INT PRIMARY KEY,
tags VARCHAR(1000),
tag_id INT,
INDEX idx_tag_id (tag_id)
);
-- 插入100萬條測試數(shù)據(jù)
-- 方法1：FIND_IN_SET (慢)
SELECT COUNT(*) FROM performance_test
WHERE FIND_IN_SET('target_tag', tags) > 0;
-- 執(zhí)行時間: ~5-10秒
-- 方法2：規(guī)范化表結(jié)構(gòu) (快)
SELECT COUNT(DISTINCT pt.id)
FROM performance_test pt
JOIN product_tags pt2 ON pt.id = pt2.product_id
WHERE pt2.tag = 'target_tag';
-- 執(zhí)行時間: ~0.01-0.1秒
內(nèi)存使用
-- FIND_IN_SET 需要在內(nèi)存中處理整個字符串
-- 對于長字符串會消耗更多內(nèi)存
SELECT FIND_IN_SET('tag', REPEAT('other_tag,', 10000)); -- 高內(nèi)存消耗
最佳實踐
1. 適用場景
? 適合使用的場景：
-- 配置項存儲（少量、相對固定的值）
SELECT * FROM system_config
WHERE FIND_IN_SET('email_notifications', enabled_features) > 0;
-- 臨時數(shù)據(jù)處理
SELECT FIND_IN_SET(@user_role, 'admin,manager,supervisor') as has_permission;
-- 小表的簡單標(biāo)簽查詢
SELECT * FROM articles
WHERE FIND_IN_SET('featured', flags) > 0
AND created_date > DATE_SUB(NOW(), INTERVAL 1 MONTH);
? 不適合使用的場景：
-- 大表的頻繁查詢
SELECT * FROM products WHERE FIND_IN_SET(@category, categories); -- 避免
-- 復(fù)雜的多條件查詢
SELECT * FROM orders
WHERE FIND_IN_SET('express', shipping_methods)
AND FIND_IN_SET('paid', status_list); -- 避免
-- 需要統(tǒng)計聚合的場景
SELECT category, COUNT(*) FROM products
GROUP BY FIND_IN_SET(category, available_categories); -- 避免
2. 優(yōu)化技巧
-- 使用索引友好的輔助字段
ALTER TABLE products
ADD COLUMN has_sale_tag BOOLEAN AS (FIND_IN_SET('sale', tags) > 0) STORED,
ADD INDEX idx_has_sale_tag (has_sale_tag);
-- 查詢時使用輔助字段
SELECT * FROM products
WHERE has_sale_tag = 1; -- 可以使用索引
-- 結(jié)合其他條件減少掃描范圍
SELECT * FROM products
WHERE category_id = 1 -- 先用索引過濾
AND FIND_IN_SET('hot', tags) > 0; -- 再用FIND_IN_SET
3. 數(shù)據(jù)驗證
-- 確保數(shù)據(jù)格式正確
DELIMITER //
CREATE TRIGGER validate_tags_format
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
IF NEW.tags REGEXP '^[^,]+(,[^,]+)*$' = 0 AND NEW.tags != '' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid tags format';
END IF;
END//
DELIMITER ;
替代方案
1. 規(guī)范化表結(jié)構(gòu)（推薦）
-- 原始設(shè)計（不推薦）
CREATE TABLE products_bad (
id INT PRIMARY KEY,
name VARCHAR(255),
category_ids VARCHAR(255) -- '1,3,5,7'
);
-- 規(guī)范化設(shè)計（推薦）
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE product_categories (
product_id INT,
category_id INT,
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES products(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 查詢對比
-- 使用FIND_IN_SET（慢）
SELECT * FROM products_bad
WHERE FIND_IN_SET('3', category_ids) > 0;
-- 使用JOIN（快）
SELECT DISTINCT p.* FROM products p
JOIN product_categories pc ON p.id = pc.product_id
WHERE pc.category_id = 3;
2. JSON 字段（MySQL 5.7+）
-- 使用JSON存儲
CREATE TABLE products_json (
id INT PRIMARY KEY,
name VARCHAR(255),
categories JSON -- ["electronics", "mobile", "smartphone"]
);
-- JSON查詢
SELECT * FROM products_json
WHERE JSON_CONTAINS(categories, '"mobile"');
-- JSON查詢可以使用函數(shù)索引（MySQL 8.0+）
ALTER TABLE products_json
ADD INDEX idx_categories ((CAST(categories AS CHAR(255) ARRAY)));
3. 全文索引
-- 對于文本標(biāo)簽搜索
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(255),
tags TEXT,
FULLTEXT(tags)
);
-- 全文搜索
SELECT * FROM articles
WHERE MATCH(tags) AGAINST('programming' IN BOOLEAN MODE);
4. 位運算方案
-- 對于有限的選項集合
CREATE TABLE user_permissions (
user_id INT PRIMARY KEY,
permissions INT -- 使用位運算存儲權(quán)限
);
-- 權(quán)限定義
-- 1: READ (1)
-- 2: WRITE (2)
-- 4: DELETE (4)
-- 8: ADMIN (8)
-- 檢查權(quán)限
SELECT * FROM user_permissions
WHERE permissions & 4 > 0; -- 檢查DELETE權(quán)限
-- 設(shè)置權(quán)限
UPDATE user_permissions
SET permissions = permissions | 8 -- 添加ADMIN權(quán)限
WHERE user_id = 123;
實際案例分析
案例1：電商網(wǎng)站商品標(biāo)簽
場景：電商網(wǎng)站需要根據(jù)商品標(biāo)簽篩選商品
錯誤實現(xiàn)：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10,2),
tags VARCHAR(500) -- 'hot,sale,new,featured'
);
-- 查詢熱銷商品（性能差）
SELECT * FROM products
WHERE FIND_IN_SET('hot', tags) > 0
ORDER BY price DESC
LIMIT 20;
正確實現(xiàn)：
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10,2)
);
CREATE TABLE tags (
id INT PRIMARY KEY,
name VARCHAR(50) UNIQUE
);
CREATE TABLE product_tags (
product_id INT,
tag_id INT,
PRIMARY KEY (product_id, tag_id),
FOREIGN KEY (product_id) REFERENCES products(id),
FOREIGN KEY (tag_id) REFERENCES tags(id)
);
-- 查詢熱銷商品（性能好）
SELECT p.* FROM products p
JOIN product_tags pt ON p.id = pt.product_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = 'hot'
ORDER BY p.price DESC
LIMIT 20;
案例2：用戶權(quán)限系統(tǒng)
場景：檢查用戶是否具有特定權(quán)限
可接受的FIND_IN_SET使用：
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
roles VARCHAR(255) -- 'admin,editor,viewer'
);
-- 小規(guī)模用戶表，偶爾查詢，可以使用
SELECT * FROM users
WHERE FIND_IN_SET('admin', roles) > 0;
-- 但更好的做法仍然是規(guī)范化
CREATE TABLE user_roles (
user_id INT,
role_id INT,
PRIMARY KEY (user_id, role_id)
);
案例3：配置管理
場景：系統(tǒng)配置的啟用功能列表
合適的使用場景：
CREATE TABLE system_settings (
id INT PRIMARY KEY,
setting_key VARCHAR(100),
setting_value TEXT
);
-- 存儲啟用的功能列表
INSERT INTO system_settings VALUES
(1, 'enabled_modules', 'user_management,reporting,notifications');
-- 檢查某個模塊是否啟用
SELECT FIND_IN_SET('reporting', setting_value) > 0 as is_enabled
FROM system_settings
WHERE setting_key = 'enabled_modules';
總結(jié)
核心要點
- FIND_IN_SET 不是銀彈：它解決特定問題，但不應(yīng)該是首選方案
- 性能影響嚴(yán)重：無法使用索引，大數(shù)據(jù)量時性能極差
- 數(shù)據(jù)完整性風(fēng)險：容易出現(xiàn)數(shù)據(jù)不一致和格式錯誤
- 維護(hù)成本高：難以進(jìn)行復(fù)雜查詢和數(shù)據(jù)分析
使用建議
- 小數(shù)據(jù)量：可以考慮使用，但要注意后續(xù)擴(kuò)展性
- 配置數(shù)據(jù)：相對靜態(tài)的配置項可以使用
- 臨時處理：數(shù)據(jù)導(dǎo)入、臨時分析等場景可以使用
- 大型應(yīng)用：強(qiáng)烈建議使用規(guī)范化的表結(jié)構(gòu)
遷移策略
如果已經(jīng)在使用FIND_IN_SET，可以考慮以下遷移策略：
- 漸進(jìn)式重構(gòu)：新功能使用規(guī)范化設(shè)計
- 數(shù)據(jù)遷移：編寫腳本將逗號分隔數(shù)據(jù)遷移到關(guān)聯(lián)表
- 性能監(jiān)控：監(jiān)控FIND_IN_SET查詢的性能影響
- 分階段優(yōu)化：優(yōu)先處理性能影響最大的查詢
記?。?strong>好的數(shù)據(jù)庫設(shè)計是性能優(yōu)化的基礎(chǔ)，而FIND_IN_SET往往是設(shè)計問題的一個信號。
到此這篇關(guān)于MySQL FIND_IN_SET字符串函數(shù)的文章就介紹到這了,更多相關(guān)MySQL FIND_IN_SET函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- mysql中find_in_set()函數(shù)的使用及in()用法詳解
- mysql中find_in_set()函數(shù)用法及自定義增強(qiáng)函數(shù)詳解
- Mysql中find_in_set()函數(shù)用法詳解以及使用場景
- mysql中find_in_set()函數(shù)的使用詳解
- mysql通過find_in_set()函數(shù)實現(xiàn)where in()順序排序
- MySQL中find_in_set()函數(shù)用法示例詳解
- mysql中find_in_set()函數(shù)用法及自定義增強(qiáng)函數(shù)
- MySQL中FIND_IN_SET()函數(shù)與in的區(qū)別及說明
相關(guān)文章
MySQL深入詳解delete與Truncate及drop的使用區(qū)別
對于drop、truncate和delete雖然簡單,但是真要使用或者面試時候問到還是需要有一定的總結(jié),下面這篇文章主要給大家介紹了關(guān)于mysql中drop、truncate與delete區(qū)別的相關(guān)資料,需要的朋友可以參考下2022-07-07
一文帶你搞懂mysql中的三種數(shù)據(jù)讀取方式
在與MySQL數(shù)據(jù)庫交互時,數(shù)據(jù)的讀取方式有多種選擇,每種方式都有其獨特的原理、優(yōu)勢和劣勢,本文將對這三種讀取方式進(jìn)行詳細(xì)介紹,需要的可以參考下2024-12-12
這篇文章主要介紹了Mysql help命令（幫助信息）中文注解,英文不好的同學(xué)可以收藏起來了,非常好的一篇中文翻譯文章,需要的朋友可以參考下2014-09-09
大家好，本篇文章主要講的是構(gòu)建雙vip的高可用MySQL集群，感興趣的同學(xué)趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
這篇文章主要介紹了MySQL的存儲函數(shù)與存儲過程的區(qū)別,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-04-04
由于MySQL目前字段的默認(rèn)值不支持函數(shù)2009-05-05
這篇文章主要為大家詳細(xì)介紹了Win中安裝mysql的詳細(xì)步驟，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-10-10
MySQL數(shù)據(jù)恢復(fù)工具：ibd2sql離線解析.ibd文件轉(zhuǎn)換為SQL
ibd2sql是純Python3工具,用于離線解析MySQL?InnoDB?IBD文件生成SQL語句,支持多版本和復(fù)雜結(jié)構(gòu),可恢復(fù)誤刪數(shù)據(jù),優(yōu)點包括無依賴、靈活輸出,但存在性能慢、數(shù)據(jù)完整性風(fēng)險及資源消耗大的缺點,適用于數(shù)據(jù)恢復(fù)、遷移和審計場景2025-06-06
這篇文章主要介紹了mysql8.0 JSON_CONTAINS的使用說明，具有很好的參考價值，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-07-07
Mysql 錯誤問題匯總，我們在開發(fā)過程中經(jīng)?？吹絤ysql的一些錯誤，這里整理下，方便需要的朋友2012-07-07