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.
 
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.
 
Note: MariaDB 5.1 and later follow the standard SQL
specification, 
and do 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
 
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/