Syntax
------

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

Description
-----------

A variable-length string. M represents the maximum column length in
characters. The range of M is 0 to 65,532. The effective maximum length of a
VARCHAR is subject to the maximum row size and the character set used. For
example, utf8 characters can require up to three bytes per character, so a
VARCHAR column that uses the utf8 character set can be declared to be a
maximum of 21,844 characters.

Note: For the ColumnStore engine, M represents the maximum column length in
bytes.

MariaDB stores VARCHAR values as a one-byte or two-byte length prefix plus
data. The length prefix indicates the number of bytes in the value. A VARCHAR
column uses one length byte if values require no more than 255 bytes, two
length bytes if values may require more than 255 bytes.

MariaDB follows the standard SQL specification, and does not remove trailing
spaces from VARCHAR values.

VARCHAR(0) columns can contain 2 values: an empty string or NULL. Such columns
cannot be part of an index. The CONNECT storage engine does not support
VARCHAR(0).

VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard
SQL way to define that a VARCHAR column should use some predefined character
set. MariaDB uses utf8 as this predefined character set, as does MySQL 4.1 and
up. NVARCHAR is shorthand for NATIONAL VARCHAR.

Before MariaDB 10.2, all MariaDB collations were of type PADSPACE, meaning
that VARCHAR (as well as CHAR and TEXT values) are compared without regard for
trailing spaces. This does not apply to the LIKE pattern-matching operator,
which takes into account trailing spaces. From MariaDB 10.2, a number of NO
PAD collations are available.

If a unique index consists of a column where trailing pad characters are
stripped or ignored, inserts into that column where values differ only by the
number of trailing pad characters will result in a duplicate-key error.

Examples
--------

The following are equivalent:

VARCHAR(30) CHARACTER SET utf8
NATIONAL VARCHAR(30)
NVARCHAR(30)
NCHAR VARCHAR(30)
NATIONAL CHARACTER VARYING(30)
NATIONAL CHAR VARYING(30)

Trailing spaces:

CREATE TABLE strtest (v VARCHAR(10));
INSERT INTO strtest VALUES('Maria   ');

SELECT v='Maria',v='Maria   ' FROM strtest;
+-----------+--------------+
| v='Maria' | v='Maria   ' |
+-----------+--------------+
|         1 |            1 |
+-----------+--------------+

SELECT v LIKE 'Maria',v LIKE 'Maria   ' FROM strtest;
+----------------+-------------------+
| v LIKE 'Maria' | v LIKE 'Maria   ' |
+----------------+-------------------+
|              0 |                 1 |
+----------------+-------------------+

Truncation
----------

* Depending on whether or not strict sql mode is set, you will either get a
warning or an error if you try to insert a string that is too long into a
VARCHAR column. If the extra characters are spaces, the spaces that can't fit
will be removed and you will always get a warning, regardless of the sql mode
setting.

Difference Between VARCHAR and TEXT
-----------------------------------

* VARCHAR columns can be fully indexed. TEXT columns can only be indexed over
a specified length.
* Using TEXT or BLOB in a SELECT query that uses temporary tables for storing
intermediate results will force the temporary table to be disk based (using
the Aria storage engine instead of the memory storage engine, which is a bit
slower. This is not that bad as the Aria storage engine caches the rows in
memory. To get the benefit of this, one should ensure that the
aria_pagecache_buffer_size variable is big enough to hold most of the row and
index data for temporary tables.

Oracle Mode
-----------

MariaDB starting with 10.3
--------------------------
In Oracle mode from MariaDB 10.3, VARCHAR2 is a synonym.

For Storage Engine Developers
-----------------------------

* Internally the full length of the VARCHAR column is allocated inside each
TABLE objects record[] structure. As there are three such buffers, each open
table will allocate 3 times max-length-to-store-varchar bytes of memory.
* TEXT and BLOB columns are stored with a pointer (4 or 8 bytes) + a 1-4 bytes
length.  The TEXT data is only stored once. This means that internally TEXT
uses less memory for each open table but instead has the additional overhead
that each TEXT object needs to be allocated and freed for each row access
(with some caching in between).

URL: https://mariadb.com/kb/en/varchar/