Use BLOB or VARBINARY for encrypted data in MySQL?

Question

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

Should I use the BLOB or VARBINARY field? Are there any consequences for any type of field?

Answer 1

Both BLOB and VARBINARY are "string" data types that store binary strings (actually byte arrays), unlike ordinary string types that store character strings, with encoding encoding, etc.

In most cases, BLOB columns can be thought of as VARBINARY columns, which can be as large as possible.

BLOB differs from VARBINARY in the following ways:

• For BLOB columns, there is no deletion of trailing space when values ​​are stored or retrieved.
• For BLOB column indexes, you must specify the length of the index prefix.
• BLOB strings cannot have DEFAULT values.

Use a BLOB , because if your encrypted values ​​end with a space byte (hex 20), it will be truncated with VARBINARY, effectively distorting your value. In addition, you will not put the index in an encrypted value, so the problem with the index does not matter, and you will not have a default value.

Answer 2

I do not think the Czech answer is correct. VARBINARY as a BLOB - store binary data so that it can store any data, including spaces.

mysql> create table t1 (id integer auto_increment, data varbinary(100), primary key (id)); Query OK, 0 rows affected (0.09 sec) // inserting '0', ' ', '0', ' ' - 4 characters including trailing space mysql> insert into t1 (data) values (unhex('30203020')); Query OK, 1 row affected (0.02 sec) +----+------+ | id | data | +----+------+ | 1 | 0 0 | +----+------+ 1 row in set (0.00 sec) mysql> select t1.*, length(data) from t1; +----+------+--------------+ | id | data | length(data) | +----+------+--------------+ | 1 | 0 0 | 4 | +----+------+--------------+ 1 row in set (0.00 sec)