What's the difference between binary(10)
vs char(10)character set binary
?
And varbinary(10)
vs varchar(10)character set binary
?
Are they synonymous in all MySQL engines?
Is there any gotcha to watch out for?
What's the difference between binary(10)
vs char(10)character set binary
?
And varbinary(10)
vs varchar(10)character set binary
?
Are they synonymous in all MySQL engines?
Is there any gotcha to watch out for?
Copyright © 2021 Jogjafile Inc.
There isn't a difference.
However, there is a caveat if you're storing a string.
If you only want to store a byte array or other binary data such as a stream or file then use the binary type as that is what they are meant for.
Quote from the MySQL manual:
So, technically there is no difference.
However, when storing a string it must be converted from a string to byte values using a character set. The decision is to either do this yourself before the MySQL server or you leave it up to MySQL do to do for you. MySQL will perform with by casting a string to
BINARY
using the BIN character sets.If you want to store the encoding in another format, lets say you have a business requirement that says you must use 4 bytes per character (MySQL doesn't do this by default) you could then use the
CHARACTER SET BINARY
to a textual column and perform the character set encoding yourself.It is also worth reading The
BINARY
andVARBINARY
Types from the MySQL manual as this details important information such as padding.Summary: There is no technical difference as one is a synonym to the other. In my opinion it makes logical sense to store binary strings in data types that would normally hold a string using the
CHARACTER SET BINARY
and to store byte arrays / streams etc inBINARY
fields that cannot be represented by transforming the data though a character set.