位运算在数据库中的运用实践-以MySQL和PG为例

目次

媒介
一、两种不同的数据库计划
1、状态字段存储JSON
2、使用位运算
二、数据库中的位运算实践
1、MySQL中的位运算实践
2、PostgreSQL中位运算实践
三、总结

---

媒介

        最近在解决某用户的一个业务需求时，碰到一个很故意思的场景。首先先跟大家分享一下需求配景。用户主要是面向一线的企业工厂，在他们实际生产过程中，为了保障安全生产。由于在车间中，所有的装备和呆板都是全天运行，因此特别容易出现运行故障。因此，安全管理部门呢就结合生产时间，将组织专门的人员在上班时间内实现对运行装备的一个巡视，每个班组上班时间为8小时，同时要求每4个小时就要对装备举行一次巡查。根据上班时间分为早班、中班、晚班，每个班由1到多个人员组成。要求在他们上班后的一个小时内和快放工的一个小时内实现对目标装备的巡视，体系须要记载每次的检查记载，好比早班第一次和早班第二次等等。同时呢，在生产高峰期，由于订单的增加，有的呆板运行更加频繁，因此要求加大巡视力度，巡视次数增加至四次，即两个小时巡视一次。同样要求体系记载不同次数的状态，一天巡查结束后，体系自动提供巡视结果，能反应出应巡的次数和漏巡的次数，工厂的管理班组将根据情况对相应的车间和安全管理人员举行考核和评价，纳入到月的的绩效考核当中，对于提早发现的故障信息，处置得当的给予嘉奖和奖金。
        以上就是大致的需求，实在刚开始拿到这个需求的时候，对于状态的记载到底用什么字段来存储，如何能到达快速生存状态和检索。好比可以快速的设置第一次和第二次的巡视状态为已完成。同时在查询时能快速查询第N次是否已完成等等操作。在实际业务中可有哪些选择来支持以上的需求，既能满意业务需求，也能较少技术的复杂度。
        这就是本文的来由，本文以一个实际的工厂日常检查工作的状态标志场景为例，首先解说可以有哪些技术方案来实现上述需求，然后解说表结构的计划，其次偏重先容位运算的表计划方式，以及位运算的处置惩罚方式，最后以MySQL和PostgreSQL为例先容如何在这两个数据库中实现位运算，通过实际例子的解说，让朋侪明确位运算在数据库状态位的场景中的详细使用。通过本文，您可以把握在面对状态标志时的数据库计划方法，如何在位运算中体现多次，如何操作位运算来设置值，以及如何快速查询定位等知识。
一、两种不同的数据库计划

        本节将根据拿到的用户开端需求，对需求举行分析，根据分析结果完成数据库的计划，同时详细论述如何去举行表状态字段的更新和查询操作。计划没有好坏，根据不同的场景，有不同的应用。这里接待各位朋侪举行讨论。由于在实际情况下，在上面的巡视工作中，根据不同的工作需求，每个组的巡视次数可能不固定的，所以这里要思量实现次数的动态标志的需求。
1、状态字段存储JSON

        之所以思量使用JSON的方式来举行存储，第一个思量就是实现灵活的状态，如果是固定的次数，好比每个班就巡视两次，那么我们不妨计划出多个字段，好比早班第一次状态，早班第二次状态,不绝到早班第N次，如果N有限，我们的字段尚且还可以计划出来，如果N是一个不固定的值，那么这种计划也是一种灾难。这里我想可能有朋侪会说，多计划一些冗余字段是否可行，好比一次性计划8个字段出来。诚然，这种方案是没有大毛病，但是你想想这些问题，万一超了怎么办，尚有就是一个班组8个字段，3个班组就是24个字段，光用来标志状态的字典就有24个，再加上其它的业务字段，这就是25+了，云云计划不仅浪费，同时查询性能也低，扩展性也不好。
        这个方式的改良办法就是将多字段改成JSON，通过JSON的存储来实现动态的次数，好比{“1”：0，“0”:1}用如许的字符串表示第一次已完成，第二次未完成。然后在查询的时候每次只须要举行json的关联查询即可，性能临时还在可以接受的范围之内。你可以想想使用JSON存储值究竟有什么问题。
2、使用位运算

        其着实上面一节中已经大要讲了一下，在我们的业务中，我们只要次序的记载每一次任务的执行状态，好比用0表示未完成，1表示已完成。0和1是二进制中最简单的表示，应用到应用程序中也是，使用他们来举行数据查询和检索，速率也是非常快的。而且也能实现动态的效果。好比，我们计划一个8位的二进制数，如下所示：

第8位	第7位	第6位	第5位	第4位	第3位	第2位	第1位
0	0	0	0	0	0	0	0

        在上面的二进制数表示中，我们采用8位（可以根据实际情况增加长度）来表示对应次数的状态位，第一位表示第一次的状态，0表示未完成。在计划字段时，我们会将状态只计划成一个字段，它的值则有这8位二进制数转成对应的十进制数来表示，如许子不仅大大的减少了字段数，同时还能实现不同次数的状态分别记载。下面举个例子：
        第一次巡视已完成的二进制表示如下：

第8位	第7位	第6位	第5位	第4位	第3位	第2位	第1位
0	0	0	0	0	0	0	1

        这里的第一位表示第一次为1，其它的仍为0，这是盘算出来的二进制值是1。下面再来举一个例子，我们将第二次和第四次的状态设置为1，则8位的二进制表示如下：

第8位	第7位	第6位	第5位	第4位	第3位	第2位	第1位
0	0	0	0	1	0	1	0

        此时，00001010这个数字换算成十进制的值为10,也就是十进制10表示二进制的00001010，表示该班组的第4次和第2次的巡视工作已完成，其它次数尚未完成。通过以上例子的解说，您是否发现，使用位运算是否极大的简化了相干的数据库计划，也降低了数据的数据更新和检索的难度。因此我们在此情况下决定采用位运算的方式举行对应工作状态的标志。
二、数据库中的位运算实践

        前一节详细的先容了我们选择的两种方案，也重点比力了两种方案的不同，优缺点也都举行了阐明。固然，以上两种方案都可以实现业务需求，也能实现动态灵活的方案，但是相比于复杂度，我们选择位运算来实现。
        本节将结合MySQL和PostgreSQL数据库来分别详细解说如何在这两个数据库中实现位运算，如何在这两个数据库中设置位运算结果和查询位状态。通过本节大家可以了解在MySQL和PostgreSQL数据库中纯熟的举行位运算的操作。
1、MySQL中的位运算实践

        这里使用的MySQL的版本是5.7.14-x的版本，位运算是基础的盘算，在更高级的版本中应该都是兼容的。本博客使用MySQL 5.7来做实验环境。
        查询MySQL版本，使用以下sql：

1. select VERSION();
3. 5.7.14-log

复制代码

        我们首先来创建一张表，表仅用作演示，不代表实际的业务，实际的业务表还请各位朋侪本身去计划。主要体现的位运算的处置惩罚过程，表的物理结构如下所示：

1. CREATE TABLE `example_table` (
2.   `id` INT AUTO_INCREMENT PRIMARY KEY,
3.   `status` INT NOT NULL DEFAULT 0
4. );

复制代码

        表结构非常简单，只有两个字段，第一个字段是主键ID，设置位自增，而第二个字段为状态位，存储的值是十进制的表示数，请注意，这里的数据范例请结合详细的二进制状态位的盘算过来定，int是一个非常大的数字了，用来存储是足够了，它的默认值用0表示，因为二进制8个0对应的十进制数也是0。
        首先我们查询一下表的数据，默认情况下，表里是没有数据的，须要我们手动插入数据，插入数据后的表数据如下：

1. select * from example_table;

复制代码

id	status
1	0
2	0
3	0
4	0
5	0
6	0
7	0

        下面我们来修改表记载的值，好比我们设置id为3的数据，第一次为1即标志已完成。sql语句如下：

1. -- 设置第N次为已完成 正确的做法
2. UPDATE example_table SET status = status | (1 << (N - 1)) WHERE id = 3;
4. -- N表示具体的次数，即N=1
5. UPDATE example_table SET status = status | (1 << (1 - 1)) WHERE id = 3;

复制代码

        在数据库客户端执行以下sql之后，客户端返回如下：

1. UPDATE example_table SET status = status | (1 << (1 - 1)) WHERE id = 3
2. > Affected rows: 1
3. > 时间: 0.089s

复制代码

        表名id=3的这条记载已经发生了变更且更新成功。为了验证这个结果是不是二进制的精确表达呢？我们选择在数据库中举行进制转换的查询展示：

1. select *, CONCAT(REPEAT('0', 8 - CHAR_LENGTH(BIN(status))),BIN(status)) AS binary_status FROM example_table ;

复制代码

        这里用到的函数有三个，最里面的是bin函数，表示将值转为二进制，然后用char_length函数求出转换出来的二进制数长度，再repeat函数和concat函数，最终拼成一个二进制字符串的表示，总的长度为8位，如果位数不足，则在前面补0，好比十进制0，二进制表达为：00000000。十进制1，二进制表示位:00000001。在客户端中执行以上的sql后可以在返回的结果中看到如下：

id	status	binary_status
1	0	00000000
2	0	00000000
3	1	00000001
4	0	00000000
5	9	00001001
6	0	00000000
7	0	00000000

        到这里，我们已经实现了状态位运算的动态更新，好比指定第几位为1，那么与之对应的另一个问题就是，如何查询出第几位为已完成。同样的我们也还是须要使用位运算，盘算的方法如下：

1. -- 查询第N次是否完成，N表示第几次
2. select *, CONCAT(REPEAT('0', 8 - CHAR_LENGTH(BIN(status))),BIN(status)) AS binary_status FROM example_table
3. WHERE (status & (1 << (N - 1))) > 0 ;
5. -- 查询第N次是否完成，1表示第1次即N=1
6. select *, CONCAT(REPEAT('0', 8 - CHAR_LENGTH(BIN(status))),BIN(status)) AS binary_status FROM example_table
7. WHERE (status & (1 << (1 - 1))) > 0 ;

复制代码

        上述查询的结果如下：

id	status	binary_status
3	1	00000001
5	9	00001001

        到此，关于如何在MySQL中举行位运算的设置以及查询的效果演示就到此结束，关于其它的位运算可以参考其它网页的资料。
2、PostgreSQL中位运算实践

        在解说了位运算在MySQL中的应用之后，下面也来讲讲在PG的运用。实在位运算在SQL中的运用效果是差不多的，MySQL和PG的位运算过程效果差异不大，为什么这里还要拿出来讲呢？主要是在PG中，要想实现二进制字符串的展示不太直观，这里分享一种在PG中的处置惩罚方式，供大家参考。
        本文使用的PG版本如下：
        查询sql：SELECT version(); 执行后查询结果如下：

1. PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit

复制代码

        与MySQL一样的，为了演示上述的效果，我们依然创建一张Pg的表，相干DDL语言轻微有点区别：

1. CREATE TABLE "public"."example_table" (
2.   "id" int8 NOT NULL,
3.   "status" int4,
4.   CONSTRAINT "example_table_pkey" PRIMARY KEY ("id")
5. );

复制代码

        在PG中设置第N次为已完成即为1的sql语句与MySQL是同等的，如下：

1. -- 设置第N次为已完成 正确的做法
2. UPDATE example_table SET status = status | (1 << (N - 1)) WHERE id = 6;

复制代码

        这里有小同伴会问，上面的SQL是设置为已完成，那么重置为0应该怎么实现呢？可以使用下面的sql来实现：

1. -- 设置第几次为0
2. UPDATE example_table SET status = status & (~(1 << (N - 1))) WHERE  id = 5;

复制代码

        在PG数据库中，直接将二进制转换为十进制的字符串的方法没有，因此我们不能直接使用内置函数来完成，须要使用自界说函数的方式，自界说函数的逻辑与MySQL差不多，整体长度是8，不敷的位数用0来补齐。函数的实现如下：

1. CREATE OR REPLACE FUNCTION "public"."decimal_to_binary_string"("num" int8)
2.   RETURNS "pg_catalog"."text" AS $BODY$
3.   
4. DECLARE  
5.     binary_str TEXT := '';  
6.     temp_num BIGINT := num;  
7. BEGIN  
8.     WHILE temp_num > 0 LOOP  
9.         -- 使用模2运算来获取最低位的二进制值  
10.         binary_str := CAST(temp_num % 2 AS TEXT) || binary_str;  
11.         -- 使用整除2来去掉已经处理的最低位  
12.         temp_num := temp_num / 2;  
13.     END LOOP;  
14.       
15.     -- 如果输入为0，则直接返回'0'  
16.     IF binary_str = '' THEN  
17.         binary_str := '0';  
18.     END IF;  
19.       
20.     RETURN binary_str;  
21. END;  
23. $BODY$
24.   LANGUAGE plpgsql VOLATILE
25.   COST 100

复制代码

        在创建好以上的转换函数之后，在数据库中执行以下SQL：

1. select *, LPAD(decimal_to_binary_string(status), 8, '0') AS binary_status FROM example_table
2. order by id;

复制代码

        查询结果如下：

id	status	binary_status
1	0	00000000
2	0	00000000
3	0	00000000
4	0	00000000
5	0	00000000

        到此，我们将如何在PG中实现位运算举行了详细的阐明。
三、总结

        以上就是本文的主要内容，本文以一个实际的工厂日常检查工作的状态标志场景为例，首先解说可以有哪些技术方案来实现上述需求，然后解说表结构的计划，其次偏重先容位运算的表计划方式，以及位运算的处置惩罚方式，最后以MySQL和PostgreSQL为例先容如何在这两个数据库中实现位运算，通过实际例子的解说，让朋侪明确位运算在数据库状态位的场景中的详细使用。通过本文，您可以把握在面对状态标志时的数据库计划方法，如何在位运算中体现多次，如何操作位运算来设置值，以及如何快速查询定位等知识。行文急忙，难免有不足之处，如果有不足之处，还请各位专家朋侪在品评区不吝见教，不甚感激。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。