Use BLOB or VARBINARY for Encrypted Data in MySQL?

Question

I'm working on a PHP application that accepts user input via a text area. It will be stored encrypted in the database (using AES_ENCRYPT).

Should I use a BLOB or VARBINARY field? Are there performance implications for either type of field?

Answer 1

Both BLOB and VARBINARY are "string" data types, that store binary strings (effectively byte arrays), as opposed to the usual string types, which store character strings, with charset encoding etc.

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like.

BLOB differs from VARBINARY in the following ways:

• There is no trailing-space removal for BLOB columns when values are stored or retrieved.
• For indexes on BLOB columns, you must specify an index prefix length.
• BLOB columns can not have DEFAULT values.

Use BLOB, because if your encrypted values happen to end in a space byte (hex 20), it would be truncated with VARBINARY, effectively corrupting your value. Also, you won't be putting an index on the encrypted value so the index issue doesn't matter, nor will you have a default value.