SQLWays Product Page - Migration to MySQL
Free Evaluation
Versions: MySQL 5.x, 4.x and 3.23
| MySQL - BINARY | |
|---|---|
| Syntax | BINARY[(n)] |
| Data | Fixed-length binary data |
| Parameters | n is the maximum number of bytes, optional |
| Range | 0 ⇐ n ⇐ 255 |
| Default | n is 1 |
| Padding | Right-padded with 0 bytes to n |
| Trailing Zeros | Not removed when the value is retrieved. Trailing zeros are significant in comparisons |
| Empty String | BINARY(0) is allowed that can store 2 values: '' (empty string) and NULL |
| Error Handling | Exceeding data truncated and a warning is generated if strict SQL mode is not enabled, otherwise an error is raised |
| Storage Size | n bytes |
| Standards | MySQL Extension |
Example:
mysql>CREATE TABLE tab_bin (c1 BINARY(0));
mysql>INSERT INTO tab_bin VALUES (NULL);
mysql>INSERT INTO tab_bin VALUES (0);
ERROR 1406 (22001): Data too long for column 'c1' at row 1
mysql>INSERT INTO tab_bin VALUES (0x0);
ERROR 1406 (22001): Data too long for column 'c1' at row 1
mysql>INSERT INTO tab_bin VALUES ('');
Query OK, 1 row affected (0.35 sec)
mysql>SELECT * FROM tab_bin;
+------+
| c1 |
+------+
| NULL |
| |
+------+
2 rows in set (0.03 sec)
MySQL BINARY - Equivalents in Other Databases
| Database | Data Type and Conversion |
|---|---|
| Oracle | RAW(n), 0 ⇐ n ⇐ 2000, not right padded, variable length |
| SQL Server | BINARY(n), 1 ⇐ n ⇐ 8000 |
| PostgreSQL | BYTEA, variable length, 2G |
| Sybase ASE | BINARY(n), 1 ⇐ n ⇐ pagesize (2K, 4K, 8K or 16K) |
| Informix | BYTE, 2G, BLOB, 2T |
Related Data Types in MySQL
| Data Types | ||||
|---|---|---|---|---|
| Variable-length binary data | VARBINARY(n) | TINYBLOB | BLOB | MEDIUMBLOB |
| Binary large objects | LONGBLOB | |||
All Data Types
| Oracle | SQL Server | IBM DB2 | MySQL | PostgreSQL | Sybase ASE | Sybase ASA | Informix DS | Teradata | HP Neoview | Interbase/Firebird |
Discussion