how mysql varbinary datatype works

Consider a table 'Collection' has the below columns:

collectionId SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
allocationBitMap VARBINARY(1000) NOT NULL,
deviceCount SMALLINT NOT NULL DEFAULT 0,


Try to save a String value '1' into VARBINARY column as shown below. Any value in single quotes is treated as String by Mysql:

insert into Collection(allocationBitMap,deviceCount) values('1',1);

If you query the inserted data, you'll notice '1' being stored as 0x31 (in hex represenation) 49 (in decimal representation. Based on the character set of the OS, numerical representation for the character '1' is stored in the database.

collectionId;HEX(allocationBitMap);deviceCount
1;0x31;1

Try to save hex value 0x12345 into VARBINARY column:

insert into Collection(allocationBitMap,deviceCount) values(0x12345,1);



If you query the inserted data, you'll notice 0x12345 being stored as 0x012345. VARBINARY stores data in bytes. It starts with least significant bits, collects 8 bits, stores them in a bytes, collects next set of 8 bits stores them in the next byte. In our example it starts with the first set of least significant 8 bits which is 0x45, next least is 0x23, next least is 1. As byte is the storage unit one is converted to 0x01 and stored in the next byte. 
collectionId;HEX(allocationBitMap);deviceCount

2;0x012345;1

When I read this data into Java program byte[], it is represented as below in the debugger window. Debugger displayed decimal value for 0x01, 0x23, 0x45:


allocationBitMap (id=69) 
 [0] 1 
 [1] 35 
 [2] 69 

When we try to display VARBINARY value, how the value get displayed is dependent on the client. 
If the client is Console, it tries to display every thing it read on the screen. So it considers every thing it read from the database to be a numerical representation of ASCII character set and tries to map those numerical codes to ASCII characters. If the binary data doesn't belong to ASCII character set then some unidentified symbols get displayed on the screen. 


If the client is more intelligent MySql client, then it knows about VARBINARY mysql type, It won't try to convert them to a specific character set as VARBINARY can hold any kind of binary data (photos, vedios) that won't map to any character set.