MySQL - 查询将整数按位字段拆分为每个位的 bool 列

Question

给定以下示例数据:

mysql> select * from test2;
+-------+--------+
| name  | status |
+-------+--------+
| bob   |      0 |
| jim   |      2 |
| karen |      5 |
| jane  |      1 |
| roy   |      7 |
+-------+--------+

我想提出一个查询，将状态列的值拆分为整数的每个单独位的伪 bool 值列。

我现在要做的是让应用程序生成一个查询，该查询使用 case 语句和每个列的按位与运算符来获取结果，如下所示:

mysql> select 
    ->     name, 
    ->     (case when (status & 1) then 'yes' else 'no' end) as bit1, 
    ->     (case when (status & 2) then 'yes' else 'no' end) as bit2, 
    ->     (case when (status & 4) then 'yes' else 'no' end) as bit4, 
    ->     (case when (status & 8) then 'yes' else 'no' end) as bit8 
    -> from test2;
+-------+------+------+------+------+
| name  | bit1 | bit2 | bit4 | bit8 |
+-------+------+------+------+------+
| bob   | no   | no   | no   | no   |
| jim   | no   | yes  | no   | no   |
| karen | yes  | no   | yes  | no   |
| jane  | yes  | no   | no   | no   |
| roy   | yes  | yes  | yes  | no   |
+-------+------+------+------+------+
5 rows in set (0.00 sec)

现在它可以工作了，而且效率很高，因为没有子查询或连接，但它不是很优雅。

我想要一个更优雅的解决方案来动态地执行此操作，而不必创建 case 语句并测试每一位。

这是只有几位的示例数据，但在适当的实现中，将有数万行，并且可以设置更多的位。

奖励:

基于包含每个位定义的表生成列(列名和测试)，如下所示:

mysql> select * from bit_spec;
+-----+------+
| bit | desc |
+-----+------+
|   1 | bit1 |
|   2 | bit2 |
|   4 | bit3 |
|   8 | bit4 |
+-----+------+

目前，应用程序会处理此问题，并且由 MySQL 服务器来完成此处理并不是硬性要求，但如果由 MySQL 服务器来完成，那将是有利的。

这是 SQL Fiddle包含示例数据、示例位定义表和我当前的解决方案。

Answer 1

这可以通过将每个用户/位/值组合检索到行中，然后将这些行旋转到列中来实现。

select 
  name, 
  `desc`,
  ((status & bit) > 0) value
from test2, bit_spec

上面的查询产生以下格式的结果

name  |  desc  |  value
bob   |  bit1  |  0
bob   |  bit2  |  0

从这里我们可以将结果转换为所需的格式，其中每个 desc/value 对都转换为一个名为 desc 的列，其中包含值值。

使用以下帖子 https://stackoverflow.com/a/12005676/3574819

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when `desc` = ''',
      `desc`,
      ''' then value end) AS ',
      replace(`desc`, ' ', '')
    )
  ) INTO @sql
from bit_spec;

SET @sql = CONCAT('SELECT t1.name, ', @sql, ' from
(select 
  name, 
  `desc`,
  ((status & bit) > 0) value
from test2, bit_spec) t1
group by t1.name
order by t1.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

http://sqlfiddle.com/#!2/5f6d0/22

NAME        BIT1    BIT2    BIT3    BIT4
bob         0       0       0       0
jane        1       0       0       0
jim         0       1       0       0
karen       1       0       1       0
roy         1       1       1       0

旁注

如果您需要按权限搜索(即显示所有拥有 bit1 和 bit2 而不是 bit3 的用户)，那么我不建议将您的权限存储为单个整数中的位，因为数据库无法为结果编制索引。在这种情况下，由列(user_id、permission_id)组成的索引连接表会产生更快的结果。