如何在php中使用MySQL按位运算？

Question

我正在尝试使用 MySQL 按位运算进行查询，我有以下示例：

table1
id      ptid
1       3
2       20
3       66
4       6

table2
id     types
1      music
2      art
4      pictures
8      video
16     art2
32     actor
64     movies
128    ..
...

现在，table1 中的 id = 3 是“66”，这意味着它有 64 或电影 和2 或艺术

但

他不是也有两次32 或演员 和2 或艺术 吗？

希望你能明白我的困惑在哪里。我如何控制我想要返回的结果。在这种情况下，我想要 64 或电影 和 2 或艺术。

但有时我希望 table2 中的三个 id 属于 table1 中的 id

有什么想法吗？

谢谢

Answer 1

使用按位 OR

以下查询返回表 2 中 66 中的所有项目：

SELECT *
FROM table2
WHERE id | 66 = 66

但是 32 + 32 = 64？

虽然 32 + 32 = 64 ，对我们没有影响。

这是二进制的 64：

01000000

这是二进制的 32：

00100000

这是二进制的 2：

00000010

这是我们在本例中使用的 1 的位置，而不是值。不会有任何两个。每个标志要么打开，要么关闭。

这是二进制的 66。请注意，64 和 2 已打开，而不是 32：

01000010

使用按位 AND 而不是 OR

另一种编写查询的方法是使用按位 AND，如下所示：

SELECT *
FROM table
WHERE id & 66 <> 0

因为 0 = false 到MySQL，可以进一步缩写为：

SELECT *
FROM table
WHERE id & 66

Answer 2

select * from table2 where id & 66

Answer 3

尽管关于如何在 MySQL 中执行按位运算的问题已经得到解答，但评论中关于为什么这可能不是最佳数据模型的子问题仍然悬而未决。

在给出的示例中有两个表；一个带有位掩码，另一个带有每个位代表的细分。这意味着，在某些时候，两个表必须连接在一起才能返回/显示各个位的含义。

这种连接要么是显式的，例如

SELECT * 
FROM Table1 
    INNER JOIN TABLE2 
        ON table1.ptid  & table2.id <> 0

，要么是隐式的，您可以从table1中选择数据到您的应用程序中，然后进行第二次调用来查找位掩码值，例如，

SELECT * 
FROM table2
WHERE id & $id <> 0 

这些选项都不是想法，因为它们不是“sargable”，即数据库无法构造搜索 ARGument。因此，您无法使用索引优化查询。查询的成本超出了无法利用索引的范围，因为对于表中的每一行，数据库必须计算和评估表达式。这很快就会变得非常内存、CPU 和 I/O 密集型，并且如果不从根本上改变表结构就无法对其进行优化。

除了完全无法优化查询之外，读取数据、报告数据也可能很尴尬，并且您还可能会遇到添加更多位的限制（8 位列中的 64 个值现在可能没问题，但不一定总是如此）因此，它们也使系统难以理解，并且我认为这种设计违反了第一范式，

尽管在数据库中使用位掩码通常是糟糕设计的标志，但有时使用它们是可以的。确实是一对多关系 您

实现这种类型关系的典型方法如下所示：

table1 
Id        Val1         Val2
---------------------------
1         ABC          DEF
2         ABC          DEF
3         ABC          DEF
4         ABC          DEF
5         ABC          DEF
6         ABC          DEF

table2
id     types
-------------
1      music
2      art
3      pictures
4      video
5      art2
6      actor
7      movies

table1-table2-relationshitp
table1ID    Table2ID
---------------------
1           1
1           2 
2           3
2           5
3           2
3           7
...

将这样查询数据

SELECT table1.*, table2.types
FROM table1 
     INNER JOIN table1-table2-relationship 
          ON table1.id = table1-table2-relationship.table1id
     INNER JOIN table2 
          ON table1-table2-relationship.table2.id = table2.id

根据这些表的访问模式，您通常会将关系表上的两列索引为复合索引（我通常将它们视为复合主键。）该索引将允许数据库快速查找关系表中的相关行，然后查找表2中的相关行。

Answer 4

在研究了 Marcus Adams 的答案后，我想我应该提供另一个例子来帮助我理解如何使用按位运算连接两个表。

考虑以下示例数据，它定义了一个元音表和一个单词表，其中单个值表示该单词中存在的元音。

# Create sample tables.
drop temporary table if exists Vowels;
create temporary table Vowels 
(
    Id int, 
    Letter varchar(1)
);
drop temporary table if exists Words;
create temporary table Words
(
    Word varchar(20),
    Vowels int
);

# Insert sample data.
insert into Vowels
    select 1,   'a' union all
    select 2,   'e' union all
    select 4,   'i' union all
    select 8,   'o' union all
    select 16,  'u';
insert into Words 
    select 'foo',           8  union all 
    select 'hello',         10 union all
    select 'language',      19 union all
    select 'programming',   13 union all
    select 'computer',      26;      

现在，我们可以将 Vowel 表连接到 Word 表，如下所示：

# List every word with its vowels.
select Word, Vowels, Letter, Id as 'Vowel Id'
from (
    select *
    from Words
) w
join Vowels v
where v.Id | w.Vowels = w.Vowels
order by Word, Letter;

当然，我们可以将任何条件应用于内部查询。

# List the letters for just the words with a length < 6
select Letter
from (
    select *
    from Words
    where length(Word) < 6
) w
join Vowels v
where v.Id | w.Vowels = w.Vowels
order by Word, Letter