I need to store binary data, such as 1110000, in MySQL. When I select it, I need the return value to be the same 1110000 again.
What data type should I use? Can I use bit? Or would varbinary be better?
views:
318answers:
1
+5
A:
if you're dealing with binary numbers you can use a bit field, e.g.:
bit(64)
is a bit field with up to 64 significant bits (the maximum size allowed).
to insert constant values, you can use the b'value' notation like so:
insert into bits values (b'0001001101001');
you can convert a bit field to a number by just adding 0 or using cast(). there's also the handy bin(), hex(), and oct() function to print the value in a particular base.
if non-numeric, varbinary or blob would be the most efficient storage method. binary is also available (it will pad shorter values with nil bytes tho).
if you don't want to deal with the conversions, you can store the string in a varchar or char. it will only use up about 8 times the space of a compact varbinary.
to insert/read from your app, you'll want to convert your sequence into a packed byte array, then store the packed string in the varbinary column. in C# you might use BitConverter. in php you might use pack/unpack.
jspcal
2010-01-09 03:32:51
i've tried to do:INSERT INTO `db_test`.`t_bits` (`val`)VALUES (b '0001001101001');and then:SELECT val+0 FROM `t_bits` it returns 617SELECT cast(val as unsigned) FROM `t_bits` also returns 617SELECT cast(val as binary) FROM `t_bits` it returns 'i'SELECT bin(val) FROM `t_bits` it returns '1001101001'the return value that i want is same as value that has been inserted. '0001001101001'and how to do that in blob? i dont undestand.i'm very confused.. thx so much..
putse
2010-01-09 04:38:39
leading 0's aren't significant in a numeric value so 0001001101001 is read as 1001101001 which is 617. for non-numerical. the easiest is to store it in a varchar or char
jspcal
2010-01-09 04:44:18
i need data type that requires smallest storage.. if so, i would rather use varbinary, right? because, based on mysql manual, storage requirement for varchar and varbinary is the same..is there no other way with bit data type? i'm still curious.can u explain how to store binary string (not image) using blob? thx for your answers, jspcal.. :)
putse
2010-01-09 05:03:10