Variable-length character string. A TEXT column can store up to 65,535 characters, often used for storing longer text content.
A TEXT column with a maximum length of 65,535 (2¹⁶ - 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 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 valuesM characters long.
BLOB and TEXT columns can be assigned a value.
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 TEXT:
A can be created on a TEXT column.
Internally, this uses hash indexing to quickly check the values and if a hash collision is found, the actual stored values are compared in order to retain the uniqueness.
columns can be fully indexed. TEXT columns can only be indexed over a specified length.
Using TEXT or in a query that uses temporary tables for storing intermediate results will force the temporary table to be disk based (using the instead of the , which is a bit slower. This is not that bad as the caches the rows in memory. To get the benefit of this, one should ensure that the variable is big enough to hold most of the row and index data for temporary tables.
Internally the full length of the 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).
This page is licensed: GPLv2, originally from
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]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 |
+----------------+-------------------+CREATE TABLE text_example (
description VARCHAR(20),
example TEXT
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearerINSERT INTO text_example VALUES
('Normal foo', 'foo'),
('Trailing spaces foo', 'foo '),
('NULLed', NULL),
('Empty', ''),
('Maximum', RPAD('', 65535, 'x'));SELECT description, LENGTH(example) AS length
FROM text_example;+---------------------+--------+
| description | length |
+---------------------+--------+
| Normal foo | 3 |
| Trailing spaces foo | 9 |
| NULLed | NULL |
| Empty | 0 |
| Maximum | 65535 |
+---------------------+--------+TRUNCATE text_example;
INSERT INTO text_example VALUES
('Overflow', RPAD('', 65536, 'x'));ERROR 1406 (22001): Data too long for column 'example' at row 1