The power of binary and varbinary columns in MySQL
When talking about MySQL, the
VARCHAR columns are the most commonly known ways to store string values. However, there are other columns available in MySQL that can store string-ish values, such as the
VARBINARY columns. These store bytes of data rather than characters, allowing users to store raw binary data, little bits of binary data that cannot be represented as strings, or do not need to be stored in that way.
Understanding binary and varbinary columns
VARBINARY columns are quite similar to the
VARCHAR columns, with one main difference. While the
VARCHAR columns store characters and follow the rules of character sets and collations, the binary and
VARBINARY columns store bytes only. In other words, there is no character set or collation to be concerned about; it is just raw binary data.
BINARY column is a fixed length column, while the
VARBINARY column is a variable length column. With
VARBINARY columns, you can store up to a set number of bytes, rather than fixed data types. Although not too commonly used,
VARBINARY columns provide an efficient way to store binary data, such as hashes or UUIDs.
How to use binary columns in MySQL
To illustrate how to use binary columns in MySQL, we will use a simple example: storing hashes. To start, we will create a table with two columns: one
BINARY column and one variable
CREATE TABLE bins ( bin BINARY(16), varbin VARBINARY(100) );
Next, we will generate some binary data using the
MD5 function. This function returns the MD5 hash of a given string. For example, the MD5 hash of "hello" is "5d41402abc4b2a76b9719d911017c592". We can turn this hash into binary data using the
The output will be a binary representation of the hash, but it will still be displayed as a string of hexadecimal digits in TablePlus. To see the actual binary data, we need to connect to the MySQL server using the command-line client with the "skip binary as hex" flag.
mysql --skip-column-names --skip-binary-as-hex -e \"SELECT UNHEX(MD5('hello'))\"
The output will be something like: "\x5d\x41\x40\x2a\xbc\x4b\x2a\x76\x b9\x71\x9d\x91\x10\x17\xc5\x92". This shows that the function has generated binary data correctly.
Now that we have the binary data, we can insert it into the table we created earlier.
INSERT INTO bins (bin, varbin) VALUES (UNHEX(MD5('hello')), UNHEX(MD5('hello')));
To retrieve the data, we can use the following query:
SELECT * FROM bins;
This will display the data in binary format. To get a readable version of the data back, we can use the
SELECT HEX(bin), HEX(varbin) FROM bins;
This will display the data as a string of hexadecimal characters.
VARBINARY columns in MySQL provide an efficient way to store binary data that may not have a valid string representation. Using binary columns, you can store hash and UUID data more compactly on disk, without the need for character sets and collations. Although these columns may not be widely used, they can be essential for storing data in certain use cases.