VARBINARY

VARBINARY

For our example of VARBINARY we picked the maximum number of bytes before the field would get auto-promoted to one of the smallest appropriate TEXT type. The limit is one byte short of 64K of data, but keep in mind that each character may require more than one byte of representation, which may make the cross-over point smaller than 65535 characters depending on the character set used. In our example we use the latin1 character set due to its single-byte representation:

CREATE TABLE varbinary_example (
   description VARCHAR(20),
   example VARBINARY(65535)
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearer

INSERT INTO varbinary_example VALUES
   ('Normal foo', 'foo'),
   ('Trailing spaces foo', 'foo      '),
   ('NULLed', NULL),
   ('Empty', ''),
   ('Maximum', RPAD('', 65535, CHAR(7)));

SELECT description, LENGTH(example) AS length
   FROM varbinary_example;

+---------------------+--------+
| description         | length |
+---------------------+--------+
| Normal foo          |      3 |
| Trailing spaces foo |      9 |
| NULLed              |   NULL |
| Empty               |      0 |
| Maximum             |  65535 |
+---------------------+--------+

Data Too Long

When SQL_MODE is strict (the default) a value is considered "too long" when its length exceeds the size of the data type, and an error is generated.

Example of data too long behavior for VARBINARY:

TRUNCATE varbinary_example;

INSERT INTO varbinary_example VALUES
   ('Overflow', RPAD('', 65536, CHAR(7)));

ERROR 1406 (22001): [7177] Data truncation: Data too long for column "example"