bit 函数和操作符
MySQL
&
Bitwise AND
<<
Left shift
>>
Shift right
BIT_COUNT
Returns the number of set bits
^
Bitwise XOR
|
Bitwise OR
~
Bitwise NOT
PostgreSQL
| Operator | Description | Example | Result |
|---|---|---|---|
| || | concatenation | B'10001' || B'011' | 10001011 |
| & | bitwise AND | B'10001' & B'01101' | 00001 |
| | | bitwise OR | B'10001' | B'01101' | 11101 |
| # | bitwise XOR | B'10001' # B'01101' | 11100 |
| ~ | bitwise NOT | ~ B'10001' | 01110 |
| << | bitwise shift left | B'10001' << 3 | 01000 |
| >> | bitwise shift right | B'10001' >> 2 | 00100 |
PostgreSQL bit_count
需要自定义, 写两个C函数来解决
> vi bit_count.c
#include "postgres.h"
#include "fmgr.h"
#include "utils/varbit.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(bit_count1);
PG_FUNCTION_INFO_V1(bit_count2);
Datum
bit_count1(PG_FUNCTION_ARGS)
{
VarBit *arg = PG_GETARG_VARBIT_P(0);
uint32 mask;
bits8 *r;
int nbits = 0;
/* Check that the bit string is not too long */
if (VARBITLEN(arg) > 32)
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("integer out of range")));
mask = 0;
for (r = VARBITS(arg); r < VARBITEND(arg); r++)
{
mask <<= BITS_PER_BYTE;
mask |= *r;
}
/* Now shift the mask to take account of the padding at the end */
mask >>= VARBITPAD(arg);
/* this code relies on mask being an unsigned type */
while (mask)
{
if (mask & 1)
nbits++;
mask >>= 1;
}
PG_RETURN_INT32(nbits);
}
Datum
bit_count2(PG_FUNCTION_ARGS)
{
uint32 mask = PG_GETARG_INT32(0);
int nbits = 0;
while (mask)
{
if (mask & 1)
nbits++;
mask >>= 1;
}
PG_RETURN_INT32(nbits);
}
> gcc -O3 -Wall -Wextra -Werror -I /home/digoal/postgresql-9.5.0/src/include -g -fPIC -c ./bit_count.c -o bit_count.o
> gcc -O3 -Wall -Wextra -Werror -I /home/digoal/postgresql-9.5.0/src/include -g -shared bit_count.o -o libbit_count.so
> cp libbit_count.so /home/digoal/pgsql9.5/lib/
> psql
postgres=# create or replace function bit_count(varbit) returns int as '$libdir/libbit_count.so', 'bit_count1' language c strict ;
CREATE FUNCTION
postgres=# create or replace function bit_count(int) returns int as '$libdir/libbit_count.so', 'bit_count2' language c strict ;
CREATE FUNCTION
postgres=# select bit_count(bit'1111');
bit_count
-----------
4
(1 row)
postgres=# select bit_count(bit'1111011001');
bit_count
-----------
7
(1 row)
postgres=# select bit_count(99);
bit_count
-----------
4
(1 row)
postgres=# select bit_count(10);
bit_count
-----------
2
(1 row)
PostgreSQL还支持set_bit
set_bit
postgres=# select set_bit(bit'11111',1,0);
set_bit
---------
10111
(1 row)
postgres=# select set_bit(bit'11111',0,0);
set_bit
---------
01111
(1 row)