[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
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.
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 | +----------------+-------------------+
In Oracle mode from MariaDB 10.3, VARCHAR2
is a synonym.
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).
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/varchar/