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 youcall 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>insertinto AppInfo(Sha1,PkgName) values(0x53ED7D718E9053086D31FCD329BA56E58236F4A2 ,"test");
Query OK, 1 rowaffected (0.00 sec)
?
mysql>insert into AppInfo(Sha1,PkgName,AppLabel) values(0x0A2976C272F81049B899B3C1EC0260A34477AD4B,'com.hotel','经济酒店');
Query OK, 1 rowaffected (0.00 sec);
?
mysql>update AppInfo set PermissionCode=0x1330526069999549579810939684362649600 whereSha1=0x0A2976C272F81049B899B3C1EC0260A34477AD4B;
Query OK, 1 rowaffected (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> insertinto PermissionMap(id,Permission,Summary) values(0x1298074214633706907132624082305024,"WRITE_SMS","Allows anapplication to write SMS messages.");
Query OK, 1 rowaffected (0.00 sec)
?
mysql>insert into PermissionMap(id,Permission,Summary) values(0x1329227995784915872903807060280344576,"RECEIVE_SMS","Allows anapplication to monitor incoming SMS messages, to record or perform processingon them.");
Query OK, 1 rowaffected (0.00 sec)
?
mysql>select * from PermissionMap where id=0x1298074214633706907132624082305024;
+-------------------+------------+----------------------------------------------+
|id???????????????| Permission |Summary?????????????????????????????????????|
+-------------------+------------+----------------------------------------------+
| ?Bc7?q2b@?0P$ | WRITE_SMS? | Allows an application to write SMSmessages. |
+-------------------+------------+----------------------------------------------+
1 row in set (0.07sec)
