应用如下:
假如一个人有9种权限,每种权限就是有或者没有,我想用 bit 类型保存到数据库中去,保存的结果就是1010这样的数据,1表示有权限,0表示没有。
CREATE TABLE `temp` (
`colid` bigint(20) NOT NULL,
`permisson` bit(10) NOT NULL,
`userid` bigint(20) NOT NULL,
PRIMARY KEY (`colid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 但是我手动插入一条数据(1,111111101,1)后发现 permisson字段显示成 000000000000000000000111111101,为什么不是10位?
假如我再插入一条数据(2,101011001,1)我要合并用户id为1的用户的权限值
合并的正确结果应该是 permisson userid
111111101 1
这个sql语句该怎么写呢?
假如一个人有9种权限,每种权限就是有或者没有,我想用 bit 类型保存到数据库中去,保存的结果就是1010这样的数据,1表示有权限,0表示没有。
CREATE TABLE `temp` (
`colid` bigint(20) NOT NULL,
`permisson` bit(10) NOT NULL,
`userid` bigint(20) NOT NULL,
PRIMARY KEY (`colid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 但是我手动插入一条数据(1,111111101,1)后发现 permisson字段显示成 000000000000000000000111111101,为什么不是10位?
假如我再插入一条数据(2,101011001,1)我要合并用户id为1的用户的权限值
合并的正确结果应该是 permisson userid
111111101 1
这个sql语句该怎么写呢?
BIT(M) approximately (M+7)/8 bytes
-> `colid` bigint(20) NOT NULL,
-> `permisson` bit(10) NOT NULL,
-> `userid` bigint(20) NOT NULL,
-> PRIMARY KEY (`colid`)
-> ) ;
Query OK, 0 rows affected (0.08 sec)mysql> insert into temp values (1,509,1);
Query OK, 1 row affected (0.06 sec)mysql> insert into temp values (2,345,1);
Query OK, 1 row affected (0.02 sec)mysql> select * from temp;
+-------+-----------+--------+
| colid | permisson | userid |
+-------+-----------+--------+
| 1 | ? | 1 |
| 2 | Y | 1 |
+-------+-----------+--------+
2 rows in set (0.00 sec)mysql> select length(permisson) from temp;
+-------------------+
| length(permisson) |
+-------------------+
| 2 |
| 2 |
+-------------------+
2 rows in set (0.00 sec)mysql>
INSERT INTO `temp` VALUES(1,b'111111101',1)
,(2,b'101011001',1)
2
SELECT userid,(BIN(permisson+0)) FROM `temp` GROUP BY userid
注意
INSERT INTO `temp` VALUES(1,b'111111101',1)
,(2,b'101011001',1)
再
SELECT userid,(BIN(permisson+0)) FROM `temp` GROUP BY userid
+-------+-----------+--------+------------------+
| colid | permisson | userid | BIN(PERMISSON+0) |
+-------+-----------+--------+------------------+
| 1 | ? | 1 | 111111101 |
| 2 | Y | 1 | 101011001 |
+-------+-----------+--------+------------------+
2 rows in set (0.00 sec)mysql> SELECT userid,(BIN(permisson+0)) FROM `temp` GROUP BY userid;
+--------+--------------------+
| userid | (BIN(permisson+0)) |
+--------+--------------------+
| 1 | 111111101 |
+--------+--------------------+
1 row in set (0.00 sec)
+------------------------+
| BIN(BIT_OR(permisson)) |
+------------------------+
| 111111101 |
+------------------------+
1 row in set (0.00 sec)mysql>
+----------------+
| BIN(permisson) |
+----------------+
| 111111101 |
| 101011001 |
+----------------+
2 rows in set (0.00 sec)mysql> select BIN(BIT_OR(permisson)) from temp;
+------------------------+
| BIN(BIT_OR(permisson)) |
+------------------------+
| 111111101 |
+------------------------+
1 row in set (0.00 sec)mysql>
,(2,b'0001000001',1) ,(3,b'0000010000',1) ,(3,b'0100000000',2)
select BIN(BIT_OR(permisson)),userid from temp group by userid;
+------------------------+-----------+
| BIN(BIT_OR(permisson)) | userid |
+------------------------+-----------+
| 1010001 | 1 |
| 100000101 | 2 |
+------------------------+-----------+
2 rows in set (0.00 sec)
运算是对的了,第一行数据的1010001 要在前面再补上3个0,第二行的也要补上一个0,谢谢2位大虾!
-> ,(2,b'0001000001',1) ,(3,b'0000010000',1) ,(3,b'0100000000',2) ;
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql>楼主,建议给例子的时候能给个对的例子,否则别人怎么测试呢?
mysql> select colid,BIN(permisson),userid from temp;
+-------+----------------+--------+
| colid | BIN(permisson) | userid |
+-------+----------------+--------+
| 1 | 101 | 2 |
| 2 | 1000001 | 1 |
| 3 | 10000 | 1 |
| 4 | 100000000 | 2 |
+-------+----------------+--------+
4 rows in set (0.00 sec)mysql> select mid(BIN(BIT_OR(permisson)+0x1000),4),
-> userid
-> from temp
-> group by userid;
+--------------------------------------+--------+
| mid(BIN(BIT_OR(permisson)+0x1000),4) | userid |
+--------------------------------------+--------+
| 0001010001 | 1 |
| 0100000101 | 2 |
+--------------------------------------+--------+
2 rows in set (0.00 sec)mysql>
SELECT userid,RIGHT(CONCAT('0000000000',BIN(permisson)),10) FROM `temp` GROUP BY userid