java - MySQL 查询使用逻辑操作获取数据列表

Question

以下是客户在图书馆阅读的各种书籍的列表。这些值以 2 的幂存储在名为 bookType 的列中。

我需要获取阅读者组合的书籍列表仅小说或仅童话或仅就寝时间或小说 + 童话 从具有逻辑操作查询的数据库。

获取以下组合的列表:

• 只读小说的人(在DB中存储为1)
• 同时阅读小说和童话故事的人(在数据库中存储为 1+2 = 3)
• 阅读所有三本书的人，即{小说 + 童话故事 + 就寝时间}(在 DB 中存储为 1+2+4 = 7)

The count of these are stored in the database in a column called BookType(marked with red in fig.)

如何使用 MySQL 查询获取上述列表

从示例中，我需要获取喜欢小说阅读器 (1,3,5,7) 的用户。

Answer 1

这个问题的核心是十进制到二进制的转换，而 mysql 有一个函数可以做 - CONV(num , from_base , to_base );在这种情况下，from_base 将为 10，to_base 将为 2。我会把它包装在一个 UDF 中如此给定

MariaDB [sandbox]> select id,username
    -> from users
    -> where id < 8;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | Jane     |
|  3 | Ali      |
|  6 | Bruce    |
|  7 | Martha   |
+----+----------+
5 rows in set (0.00 sec)

MariaDB [sandbox]> select * from t;
+------+------------+
| id   | type       |
+------+------------+
|    1 | novel      |
|    2 | fairy Tale |
|    3 | bedtime    |
+------+------------+
3 rows in set (0.00 sec)

这个用户定义函数

drop function if exists book_type;
delimiter //

CREATE DEFINER=`root`@`localhost` FUNCTION `book_type`(
    `indec` int
)
RETURNS varchar(255) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
declare   tempstring varchar(100);
declare   outstring  varchar(100);
declare   book_types varchar(100);
declare   bin_position int;
declare   str_length int;
declare  checkit int;
set tempstring =  reverse(lpad(conv(indec,10,2),4,0));
set str_length = length(tempstring);
set checkit = 0;
set bin_position = 0;
set book_types = '';
looper: while   bin_position < str_length do
        set bin_position = bin_position + 1;
        set outstring = substr(tempstring,bin_position,1);


        if outstring = 1    then
            set book_types = concat(book_types,(select trim(type) from t where id = bin_position),','); 
        end if;
end while; 

set outstring = book_types;

return outstring;
end //
delimiter ;

结果在

+----+----------+---------------------------+
| id | username | book_type(id)             |
+----+----------+---------------------------+
|  1 | John     | novel,                    |
|  2 | Jane     | fairy Tale,               |
|  3 | Ali      | novel,fairy Tale,         |
|  6 | Bruce    | fairy Tale,bedtime,       |
|  7 | Martha   | novel,fairy Tale,bedtime, |
+----+----------+---------------------------+
5 rows in set (0.00 sec)

注意 UDF 中用于遍历二进制字符串的循环，以及 1 的位置与查找表中的 ID 相关；我留给你编码错误和整理。