mysql binary varbinary的运用

数据库设计中用到binary类型，会将sha1和permission code的值用binary类型表示（sha1 可以是char(40),但是作为索引效率不高）。

permission code是字节的每个位代表一个permission。

 

 

(e.g.: AppInfo & PermissionMap

Sha1 = 0A2976C272F81049B899B3C1EC0260A34477AD4B

permissionCode = 1330526069999549579810939684362649600

==1329227995784915872903807060280344576 | 1298074214633706907132624082305024

== (1 << 120) | (1 << 110)

 

(While, if you call our db interface to access database, you may just pass parameters as Sha1 = ‘0A2976C272F81049B899B3C1EC0260A34477AD4B’

permissionCode = 1330526069999549579810939684362649600

)

                              

Sha1 = 53ED7D718E9053086D31FCD329BA56E58236F4A2):

mysql> describe AppInfo;

+----------------+----------------+------+-----+----------------------+-------+

| Field             | Type           | Null | Key | Default              | Extra |

+----------------+----------------+------+-----+----------------------+-------+

| Sha1                                     | binary(20)      | NO   | PRI |                      |       |

| PkgName                         | varchar(1024)  | YES  |     | NULL                 |       |

 

| PermissionCode            | varbinary(256) | YES  |     | NULL                 |       |

+----------------+----------------+------+-----+----------------------+-------+

 

mysql> describe PermissionMap;

+------------+----------------+------+-----+---------+-------+

| Field      | Type           | Null | Key | Default | Extra |

+------------+----------------+------+-----+---------+-------+

| id                         | varbinary(256) | NO   | PRI | NULL    |       |

| Permission      | varchar(64)    | YES  |     | NULL    |       |

| Summary         | varchar(512)   | YES  |     | NULL    |       |

+------------+----------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

mysql>insert into AppInfo(Sha1,PkgName) values( 0x53ED7D718E9053086D31FCD329BA56E58236F4A2 ,"test");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into AppInfo(Sha1,PkgName,AppLabel) values( 0x0A2976C272F81049B899B3C1EC0260A34477AD4B ,'com.hotel',' 经济酒店 ');

Query OK, 1 row affected (0.00 sec);

 

mysql> update AppInfo set PermissionCode=0x1330526069999549579810939684362649600 where Sha1=0x0A2976C272F81049B899B3C1EC0260A34477AD4B;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from AppInfo where Sha1= 0x0A2976C272F81049B899B3C1EC0260A34477AD4B ;

+----------------------+-----------+------+--------------+-------------+-------------+------------+------+---------------+---------------+---------------+----------+---------------------+

| Sha1                 | PkgName   | PermissionCode      |

+----------------------+-----------+------+--------------+-------------+-------------+------------+------+---------------+---------------+---------------+----------+---------------------+

|

)v ▒ r ▒ I ▒▒▒▒▒ ` ▒ Dw ▒ K | com.hotel  | 3& ▒▒ T ▒ y ▒    9hCbd ▒   |

+----------------------+-----------+------+--------------+-------------+-------------+------------+------+---------------+---------------+---------------+----------+---------------------+

1 row in set (0.00 sec)

 

mysql> insert into PermissionMap(id,Permission,Summary) values( 0x1298074214633706907132624082305024 ,"WRITE_SMS","Allows an application to write SMS messages.");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into PermissionMap(id,Permission,Summary) values( 0x1329227995784915872903807060280344576 ,"RECEIVE_SMS","Allows an application to monitor incoming SMS messages, to record or perform processing on them.");

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from PermissionMap where id= 0x1298074214633706907132624082305024;

+-------------------+------------+----------------------------------------------+

| id                | Permission | Summary                                      |

+-------------------+------------+----------------------------------------------+

| ▒ Bc7 ▒ q2b@ ▒ 0P$ | WRITE_SMS  | Allows an application to write SMS messages. |

+-------------------+------------+----------------------------------------------+

1 row in set (0.07 sec)