MySQL Hexadecimal Binary Limit -

i have table 2 columns: 'id' (datatype=int) , 'representation' (datatype=binary). want store hexadecimal value in form of binary digits in 'representation' column. max number of binary digits can store in 'representation' column ?

mysql

binary

the mysql docs on binary data type, mention:

the permissible maximum length same binary , varbinary char , varchar, except length binary , varbinary length in bytes rather in characters.

so binary put on same level char, , varbinary varchar.

the docs on char data type, mention:

the length of char column fixed length declare when create table. length can value 0 255.

so maximum size binary therefore achieved this:

create table mytable (     id int,     representation binary(255) ) 

this corresponds 255 bytes of data, corresponds 510 hexadecimal digits, or 2040 bits.

varbinary

the varbinary type can store 65,535 bytes, sizes of other columns must subtracted. again, follows docs on varchar:

values in varchar columns variable-length strings. length can specified value 0 65,535. effective maximum length of varchar subject maximum row size (65,535 bytes, shared among columns) , character set used.

so let's need room 500 bytes in other columns, defined table:

create table mytable (     id int, // takes 4 bytes     representation binary(65000),     // other fields come here, taking less 532 bytes ) 

... have 65,000 bytes, i.e. 130,000 hexadecimal digits or 520,000 bits.

sql server binary

the transact-sql docs on binary state:

binary [ ( n ) ]

fixed-length binary data length of n bytes, n value 1 through 8,000. storage size n bytes.

this means table definition:

create table mytable (     id int,     representation binary(8000) ) 

... can store 8,000 bytes, i.e. 16,000 hexadecimal digits or 64,000 bits.

note limit varbinary same. following advise given in docs:

use varbinary when sizes of column data entries vary considerably.