Variable-length binary string type. VARBINARY columns store binary strings of variable length up to a specified maximum.
The VARBINARY type is similar to the type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes.
It contains no , and comparison and sorting are based on the numeric value of the bytes.
If the maximum length is exceeded, and is not enabled , the extra characters will be dropped with a warning. If strict mode is enabled, an error will occur.
Unlike values, VARBINARY values are not right-padded when inserting.
In , RAW is a synonym for VARBINARY.
Inserting too many characters, first with strict mode off, then with it on:
Sorting is performed with the byte value:
Using to sort as a instead:
For our example of VARBINARY, we picked a maximum size that avoids overflowing the maximum row size (65535). Keep in mind that a multi-byte character set would need more space in the row than a single-byte character set. We also avoid the auto-conversion of a VARBINARY into a BLOB, MEDIUMBLOB, or LONGBLOB that can happen when STRICT_TRANS_TABLES is not set in the SQL_MODE.
The example:
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:
This page is licensed: GPLv2, originally from
VARBINARY(M)CREATE TABLE varbins (a VARBINARY(10));
INSERT INTO varbins VALUES('12345678901');
Query OK, 1 row affected, 1 warning (0.04 sec)
SELECT * FROM varbins;
+------------+
| a |
+------------+
| 1234567890 |
+------------+
SET sql_mode='STRICT_ALL_TABLES';
INSERT INTO varbins VALUES('12345678901');
ERROR 1406 (22001): Data too long for column 'a' at row 1TRUNCATE varbins;
INSERT INTO varbins VALUES('A'),('B'),('a'),('b');
SELECT * FROM varbins ORDER BY a;
+------+
| a |
+------+
| A |
| B |
| a |
| b |
+------+SELECT * FROM varbins ORDER BY CAST(a AS CHAR);
+------+
| a |
+------+
| a |
| A |
| b |
| B |
+------+CREATE TABLE varbinary_example (
description VARCHAR(20),
example VARBINARY(65511)
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO varbinary_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 65511, 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 | 65511 |
+---------------------+--------+TRUNCATE varbinary_example;
INSERT INTO varbinary_example VALUES
('Overflow', RPAD('', 65512, CHAR(7)));ERROR 1406 (22001): Data too long for column 'example' at row 1