mysql——逗號分割字段情況【find_in_set】_mysql find_in_set能不能把逗號換成分號詳情 - mysql,數據庫,List,字段,Java,Html,CSS,前端開發雲端築夢師博客

MySQL逗號分隔字段的MyBatis操作指南

📋 背景説明

字段定義：

create your_table(
    supplier_id varchar(255) NULL  -- （多個ID逗號分隔）數據格式示例：1,5,3,8,12
);

現狀分析：

字段已在生產環境使用，不能修改表結構
需要支持單個ID的增刪改查操作
需要保持數據格式的一致性

🔍 查詢操作

1.1 精確查詢（推薦使用）

<!-- 方法1：使用FIND_IN_SET函數 -->
<select id="selectBySupplierId" resultType="YourEntity">
    SELECT * FROM your_table 
    WHERE FIND_IN_SET(#{supplierId}, supplier_id) > 0
</select>

<!-- 方法2：使用LIKE（更安全，處理邊界情況） -->
<select id="selectBySupplierIdSafe" resultType="YourEntity">
    SELECT * FROM your_table 
    WHERE CONCAT(',', supplier_id, ',') LIKE CONCAT('%,', #{supplierId}, ',%')
</select>

兩種方法的對比：

方法	優點	缺點	適用場景
`FIND_IN_SET`	語法簡潔，易理解	不能使用索引，性能較差	數據量小的表
`LIKE`	可部分利用索引，更安全	語法複雜	數據量較大的表

1.2 多條件查詢

<!-- 查詢同時包含多個supplier_id的記錄 -->
<select id="selectBySupplierIds" resultType="YourEntity">
    SELECT * FROM your_table 
    WHERE 
    <foreach collection="supplierIds" item="id" separator=" AND ">
        FIND_IN_SET(#{id}, supplier_id) > 0
    </foreach>
</select>

<!-- 查詢包含任意一個supplier_id的記錄 -->
<select id="selectByAnySupplierId" resultType="YourEntity">
    SELECT * FROM your_table 
    WHERE 
    <foreach collection="supplierIds" item="id" separator=" OR ">
        FIND_IN_SET(#{id}, supplier_id) > 0
    </foreach>
</select>

✏️ 修改/更新操作

2.1 添加新的supplier_id

<!-- 安全添加，避免重複 -->
<update id="addSupplierId">
    UPDATE your_table 
    SET supplier_id = 
        CASE 
            WHEN supplier_id IS NULL OR supplier_id = '' THEN #{newSupplierId}
            WHEN FIND_IN_SET(#{newSupplierId}, supplier_id) = 0 
                THEN CONCAT(supplier_id, ',', #{newSupplierId})
            ELSE supplier_id
        END
    WHERE id = #{recordId}
</update>

2.2 移除特定的supplier_id

<!-- 安全移除，處理各種邊界情況 -->
<update id="removeSupplierId">
    UPDATE your_table 
    SET supplier_id = TRIM(BOTH ',' FROM 
        REPLACE(
            REPLACE(CONCAT(',', supplier_id, ','), CONCAT(',', #{removeId}, ','), ','),
            ',,', ','
        )
    )
    WHERE FIND_IN_SET(#{removeId}, supplier_id) > 0
    AND id = #{recordId}
</update>

移除操作的詳細處理邏輯：

CONCAT(',', supplier_id, ',') - 在首尾添加逗號，統一格式
REPLACE(CONCAT(...), CONCAT(',', #{removeId}, ','), ',') - 替換目標ID
REPLACE(..., ',,', ',') - 處理可能產生的連續逗號
TRIM(BOTH ',' FROM ...) - 移除首尾逗號

2.3 批量更新整個supplier_id列表

<update id="updateSupplierIds">
    UPDATE your_table 
    SET supplier_id = 
    <choose>
        <when test="supplierIds != null and supplierIds.size() > 0">
            <foreach collection="supplierIds" item="id" separator="," open="" close="">
                #{id}
            </foreach>
        </when>
        <otherwise>NULL</otherwise>
    </choose>
    WHERE id = #{recordId}
</update>

🗑️ 刪除操作

3.1 刪除包含特定supplier_id的記錄

<delete id="deleteBySupplierId">
    DELETE FROM your_table 
    WHERE FIND_IN_SET(#{supplierId}, supplier_id) > 0
</delete>

💻 Java代碼實現

4.1 Mapper接口定義

public interface YourMapper {
    // 基礎查詢
    YourEntity selectById(@Param("id") Long id);
    
    // 根據supplier_id查詢
    List<YourEntity> selectBySupplierId(@Param("supplierId") String supplierId);
    List<YourEntity> selectBySupplierIds(@Param("supplierIds") List<String> supplierIds);
    
    // 更新操作
    int addSupplierId(@Param("recordId") Long recordId, @Param("newSupplierId") String newSupplierId);
    int removeSupplierId(@Param("recordId") Long recordId, @Param("removeId") String removeId);
    int updateSupplierIds(@Param("recordId") Long recordId, @Param("supplierIds") List<String> supplierIds);
    
    // 刪除操作
    int deleteBySupplierId(@Param("supplierId") String supplierId);
}

4.2 Service層實現

@Service
@Transactional
public class SupplierService {
    
    @Autowired
    private YourMapper yourMapper;
    
    /**
     * 安全移除supplier_id
     */
    public boolean safeRemoveSupplierId(Long recordId, String removeId) {
        try {
            YourEntity entity = yourMapper.selectById(recordId);
            if (entity == null || entity.getSupplierId() == null) {
                return false;
            }
            
            // 使用Java邏輯處理，更安全
            List<String> ids = Arrays.stream(entity.getSupplierId().split(","))
                .filter(id -> !id.trim().isEmpty())
                .collect(Collectors.toList());
            
            boolean removed = ids.remove(removeId);
            if (!removed) {
                return false; // 要移除的ID不存在
            }
            
            if (ids.isEmpty()) {
                yourMapper.updateSupplierIds(recordId, null);
            } else {
                yourMapper.updateSupplierIds(recordId, ids);
            }
            
            return true;
        } catch (Exception e) {
            throw new RuntimeException("移除supplier_id失敗", e);
        }
    }
    
    /**
     * 安全添加supplier_id
     */
    public boolean safeAddSupplierId(Long recordId, String newId) {
        YourEntity entity = yourMapper.selectById(recordId);
        if (entity == null) return false;
            
        if (entity.getSupplierId() == null || entity.getSupplierId().isEmpty()) {
            yourMapper.updateSupplierIds(recordId, Collections.singletonList(newId));
            return true;
        }
        
        List<String> ids = Arrays.stream(entity.getSupplierId().split(","))
            .filter(id -> !id.trim().isEmpty())
            .collect(Collectors.toList());
            
        if (ids.contains(newId)) {
            return false; // 已存在，不重複添加
        }
        
        ids.add(newId);
        yourMapper.updateSupplierIds(recordId, ids);
        return true;
    }
    
    /**
     * 獲取所有的supplier_id列表
     */
    public List<String> getSupplierIds(Long recordId) {
        YourEntity entity = yourMapper.selectById(recordId);
        if (entity == null || entity.getSupplierId() == null) {
            return Collections.emptyList();
        }
        
        return Arrays.stream(entity.getSupplierId().split(","))
            .filter(id -> !id.trim().isEmpty())
            .collect(Collectors.toList());
    }
}

⚠️ 重要注意事項

5.1 性能考慮

問題：

FIND_IN_SET 無法使用索引，全表掃描
數據量大時查詢性能差

優化建議：

-- 可以考慮添加全文索引（如果MySQL版本支持）
ALTER TABLE your_table ADD FULLTEXT(supplier_id);

-- 查詢時使用MATCH AGAINST（需要調整業務邏輯）
SELECT * FROM your_table 
WHERE MATCH(supplier_id) AGAINST('+1' IN BOOLEAN MODE);

5.2 數據一致性保障

// 在Service層添加數據校驗
private void validateSupplierId(String supplierId) {
    if (supplierId == null || supplierId.trim().isEmpty()) {
        throw new IllegalArgumentException("supplierId不能為空");
    }
    if (supplierId.contains(",")) {
        throw new IllegalArgumentException("單個supplierId不能包含逗號");
    }
}

// 在添加/移除時調用校驗
public boolean safeAddSupplierId(Long recordId, String newId) {
    validateSupplierId(newId);
    // ... 其餘邏輯
}

5.3 事務管理

@Service
@Transactional(rollbackFor = Exception.class)
public class SupplierService {
    
    /**
     * 批量操作，保證原子性
     */
    @Transactional
    public void batchUpdateSupplierIds(Long recordId, List<String> toAdd, List<String> toRemove) {
        for (String removeId : toRemove) {
            safeRemoveSupplierId(recordId, removeId);
        }
        for (String addId : toAdd) {
            safeAddSupplierId(recordId, addId);
        }
    }
}

🚀 長期優化建議

6.1 推薦方案：建立關聯表

-- 創建關聯表（推薦方案）
CREATE TABLE supplier_relation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    main_id BIGINT NOT NULL,
    supplier_id VARCHAR(255) NOT NULL,
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_main_id (main_id),
    INDEX idx_supplier_id (supplier_id),
    UNIQUE KEY uk_main_supplier (main_id, supplier_id)
);

-- 遷移現有數據（一次性操作）
INSERT INTO supplier_relation (main_id, supplier_id)
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(supplier_id, ',', n.digit+1), ',', -1)
FROM your_table
INNER JOIN (
    SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    -- 根據最大數量擴展
) n ON LENGTH(REPLACE(supplier_id, ',' , '')) <= LENGTH(supplier_id)-n.digit;

6.2 漸進式改造方案

// 1. 先創建新表，雙寫維護
// 2. 逐步將查詢遷移到新表
// 3. 最終移除舊字段

@Component
public class SupplierMigrationService {
    
    @Autowired
    private OldMapper oldMapper;
    
    @Autowired
    private NewMapper newMapper;
    
    /**
     * 雙寫策略，保證數據一致性
     */
    @Transactional
    public void addSupplierId(Long recordId, String newId) {
        // 寫入舊錶（兼容現有功能）
        oldMapper.addSupplierId(recordId, newId);
        
        // 寫入新表
        newMapper.insertSupplierRelation(recordId, newId);
    }
}

📝 總結

操作類型	推薦方案	注意事項
查詢	`FIND_IN_SET` 或 `LIKE`	注意性能問題，大數據量需要優化
添加	先查詢再拼接，避免重複	處理空值和邊界情況
移除	Java邏輯處理更安全	注意逗號邊界處理
批量	在Service層處理邏輯	保證事務一致性

最佳實踐：

短期：使用上述方案維持現有功能
中期：考慮添加緩存優化查詢性能
長期：推動表結構重構，使用關聯表方案

這種逗號分隔的字段設計雖然不符合數據庫範式，但在現有系統下通過合理的技術方案可以穩定運行。

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

雲端築夢師博客

雲端築夢師博客

博客 / 詳情

mysql——逗號分割字段情況【find_in_set】_mysql find_in_set能不能把逗號換成分號

MySQL逗號分隔字段的MyBatis操作指南

📋 背景説明

🔍 查詢操作

1.1 精確查詢（推薦使用）

1.2 多條件查詢

✏️ 修改/更新操作

2.1 添加新的supplier_id

2.2 移除特定的supplier_id

2.3 批量更新整個supplier_id列表

🗑️ 刪除操作

3.1 刪除包含特定supplier_id的記錄

💻 Java代碼實現

4.1 Mapper接口定義

4.2 Service層實現

⚠️ 重要注意事項

5.1 性能考慮

5.2 數據一致性保障

5.3 事務管理

🚀 長期優化建議

6.1 推薦方案：建立關聯表

6.2 漸進式改造方案

📝 總結

發佈評論

Product

Company

Support

Company

博客 / 詳情

mysql——逗號分割字段情況【find_in_set】_mysql find_in_set能不能把逗號換成分號

MySQL逗號分隔字段的MyBatis操作指南

📋 背景説明

🔍 查詢操作

1.1 精確查詢（推薦使用）

1.2 多條件查詢

✏️ 修改/更新操作

2.1 添加新的supplier_id

2.2 移除特定的supplier_id

2.3 批量更新整個supplier_id列表

🗑️ 刪除操作

3.1 刪除包含特定supplier_id的記錄

💻 Java代碼實現

4.1 Mapper接口定義

4.2 Service層實現

⚠️ 重要注意事項

5.1 性能考慮

5.2 數據一致性保障

5.3 事務管理

🚀 長期優化建議

6.1 推薦方案：建立關聯表

6.2 漸進式改造方案

📝 總結

發佈 評論

發佈評論