如何在MySQL中查询SET类型字段的首个元素？

问题背景 #

现有test表，建表语句如下：

CREATE TABLE `test` (`testset` set('one','two') )

表中数据：

SELECT * FROM test;
+---------+
| testset |
+---------+
| one     |
| two     |
| one,two |
+---------+

需要查询每条记录中testset字段的首个元素，预期结果：

+---------+
| testset |
+---------+
| one     |
| two     |
| one     |
+---------+

但testset[0]的写法不生效，需替换为有效方法。

解决方案 #

方法1：使用SUBSTRING_INDEX函数 #

利用SET字段的字符串表现形式是逗号分隔的特性，直接截取第一个逗号前的内容：

SELECT SUBSTRING_INDEX(testset, ',', 1) AS testset FROM test;

这个方法无需依赖SET的内部数值存储，适用于任何SET类型字段，不管元素数量多少。

方法2：利用SET的数值存储特性 #

SET类型在MySQL中是以整数位掩码存储的，每个元素对应一个2的幂次值：

• 'one'对应1（2⁰）
• 'two'对应2（2¹）

可以通过位运算判断首个被设置的元素：

SELECT 
  CASE
    WHEN testset & 1 THEN 'one'
    WHEN testset & 2 THEN 'two'
  END AS testset
FROM test;

这个方法需要明确每个SET元素对应的位值，适合固定元素的SET字段，性能比字符串分割更优。

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