如何在MariaDB中编写函数拆分字符串并关联表实现颜色筛选？

使用的MariaDB版本为10.3.34。
无法修改外部数据库结构，但可以添加函数。数据库中物品的颜色ID以|分隔的字符串存储在things表的colorsid字段中，具体表结构如下：

表结构 #

things表 #

colors表 #

需求 #

需要编写一个thing_has_color函数，支持如下查询：

SELECT name from things WHERE thing_has_color( name, 'red' );

预期返回结果：

性能要求合理即可，数据库中颜色最多几十种，物品数量不超过10000条。

可以创建如下函数来实现需求：

DELIMITER //

CREATE FUNCTION thing_has_color(p_thing_name VARCHAR(255), p_color_name VARCHAR(255))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE v_color_id INT;
    DECLARE v_colorsid VARCHAR(255);
    
    -- 获取目标颜色对应的ID
    SELECT id INTO v_color_id FROM colors WHERE color = p_color_name;
    
    -- 如果颜色不存在，直接返回false
    IF v_color_id IS NULL THEN
        RETURN FALSE;
    END IF;
    
    -- 获取物品对应的颜色ID字符串
    SELECT colorsid INTO v_colorsid FROM things WHERE name = p_thing_name;
    
    -- 处理colorsid为null的情况
    IF v_colorsid IS NULL THEN
        RETURN FALSE;
    END IF;
    
    -- 匹配颜色ID：处理单个ID、开头、中间、结尾的情况，避免误匹配
    RETURN CONCAT('|', v_colorsid, '|') LIKE CONCAT('%|', v_color_id, '|%');
END //

DELIMITER ;

函数说明 #

1. 先根据传入的颜色名称从colors表中获取对应ID，若颜色不存在直接返回FALSE。
2. 根据物品名称获取其colorsid字符串，若为NULL则返回FALSE。
3. 通过在colorsid前后拼接|，统一匹配格式，避免出现类似20匹配205的错误，确保精确匹配单个颜色ID。

测试验证 #

执行目标查询语句：

SELECT name from things WHERE thing_has_color( name, 'red' );

将得到预期结果：

+--------+
| name   |
+--------+
| 'door' |
| 'box'  |
+--------+

内容的提问来源于stack exchange，提问作者exore