VARCHAR

SYNONYMS

The following are synonyms for VARCHAR:

• NVARCHAR

VARCHAR

For our example of VARCHAR 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 varchar_example (
   description VARCHAR(20),
   example VARCHAR(65535) CHARACTER SET latin1
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearer

INSERT INTO varchar_example VALUES
   ('Normal foo', 'foo'),
   ('Trailing spaces foo', 'foo      '),
   ('NULLed', NULL),
   ('Empty', ''),
   ('Maximum', RPAD('', 65535, 'x'));

SELECT description, LENGTH(example) AS length
   FROM varchar_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 VARCHAR:

TRUNCATE varchar_example;

INSERT INTO varchar_example VALUES
   ('Overflow', RPAD('', 65536, 'x'));

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