TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT
column with a maximum length of 65,535
(216 - 1
) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT
value is stored using a two-byte length prefix that indicates the number of bytes in the value. If you need a bigger storage, consider using MEDIUMTEXT instead.
An optional length M
can be given for this type. If this is done, MariaDB creates the column as the smallest TEXT
type large enough to hold values M
characters long.
Before MariaDB 10.2, all MariaDB collations were of type PADSPACE, meaning that TEXT (as well as VARCHAR and CHAR values) are compared without regard for trailing spaces. This does not apply to the LIKE pattern-matching operator, which takes into account trailing spaces.
Before MariaDB 10.2.1, BLOB
and TEXT
columns could not be assigned a DEFAULT value. This restriction was lifted in MariaDB 10.2.1.
Trailing spaces:
CREATE TABLE strtest (d TEXT(10)); INSERT INTO strtest VALUES('Maria '); SELECT d='Maria',d='Maria ' FROM strtest; +-----------+--------------+ | d='Maria' | d='Maria ' | +-----------+--------------+ | 1 | 1 | +-----------+--------------+ SELECT d LIKE 'Maria',d LIKE 'Maria ' FROM strtest; +----------------+-------------------+ | d LIKE 'Maria' | d LIKE 'Maria ' | +----------------+-------------------+ | 0 | 1 | +----------------+-------------------+
TEXT
columns can only be indexed over a specified length. 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).
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/text/