Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL "binary" vs "char character set binary"

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?

like image 960
Pacerier Avatar asked Mar 11 '13 09:03

Pacerier


People also ask

What is a binary character set?

The binary character set is the character set for binary strings, which are sequences of bytes. The binary character set has one collation, also named binary .

What is the difference between binary strings and character strings in SQL?

Unlike a character string which usually contains text data, a binary string is used to hold non-traditional data such as pictures. The length of a binary string is the number of bytes in the sequence. A binary string has a CCSID of 65535.

What is MySQL binary data type?

MySQL's BINARY data type, like CHAR , holds fixed-length strings. You still have to specify the width of the column, e.g. BINARY(20) . The only difference is that MySQL treats the data within the column as raw bytes rather than encoded text.


1 Answers

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:

The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary data type. For example, the following pairs of definitions are equivalent:

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

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 and VARBINARY 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 in BINARY fields that cannot be represented by transforming the data though a character set.

like image 56
Steve Avatar answered Oct 20 '22 01:10

Steve