> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/data-types/string-data-types/text.md).

# TEXT

Complete TEXT type reference: TEXT(M) syntax, 65,535 byte maximum, 2-byte length prefix, DEFAULT value support, and indexing constraints rules.

## Syntax

```bnf
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
```

## Description

{% hint style="info" %}
If you are handling large text data that exceeds the `max_allowed_packet` limit, you can stream the data in chunks using specialized API functions like `mysql_stmt_send_long_data()` or `setCharacterStream()`. See [Handling Large Data via APIs](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types.md#handling-large-data-via-apis) for more details.
{% endhint %}

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 [MEDIUMTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/mediumtext.md) 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.

`BLOB` and `TEXT` columns can be assigned a [DEFAULT](https://mariadb.com/docs/server/server-usage/tables/create-table.md#default-column-option) value.

## Examples

### Trailing spaces:

```sql
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 |
+----------------+-------------------+
```

### Example of TEXT

```sql
CREATE TABLE text_example (
   description VARCHAR(20),
   example TEXT
) DEFAULT CHARSET=latin1; -- One byte per char makes the examples clearer
```

```sql
INSERT INTO text_example VALUES
   ('Normal foo', 'foo'),
   ('Trailing spaces foo', 'foo      '),
   ('NULLed', NULL),
   ('Empty', ''),
   ('Maximum', RPAD('', 65535, 'x'));
```

```sql
SELECT description, LENGTH(example) AS length
   FROM text_example;
```

```sql
+---------------------+--------+
| description         | length |
+---------------------+--------+
| Normal foo          |      3 |
| Trailing spaces foo |      9 |
| NULLed              |   NULL |
| Empty               |      0 |
| Maximum             |  65535 |
+---------------------+--------+
```

### Data too Long

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`:

```sql
TRUNCATE text_example;

INSERT INTO text_example VALUES
   ('Overflow', RPAD('', 65536, 'x'));
```

```sql
ERROR 1406 (22001): Data too long for column 'example' at row 1
```

## Indexing

A [unique index](https://mariadb.com/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide.md#unique-index) 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.

## Difference between VARCHAR and TEXT

* [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar.md) columns can be fully indexed. `TEXT` columns can only be indexed over a specified length.
* Before MariaDB 13.2, using `TEXT` or [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob.md) in a [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md) query that uses temporary tables for storing intermediate results forces the temporary table to be disk-based, using the [Aria storage engine](https://mariadb.com/docs/server/server-usage/storage-engines/aria/aria-storage-engine.md) instead of the [MEMORY storage engine](https://mariadb.com/docs/server/server-usage/storage-engines/memory-storage-engine.md). From MariaDB 13.2, such temporary tables are created in memory as well, and are converted to Aria tables only when they grow too large. See [MEMORY: Internal Temporary Tables](https://mariadb.com/docs/server/server-usage/storage-engines/memory-storage-engine.md#internal-temporary-tables). Disk-based temporary tables are cached in the Aria page cache, so make sure [aria\_pagecache\_buffer\_size](https://mariadb.com/docs/server/server-usage/storage-engines/aria/aria-system-variables.md#aria_pagecache_buffer_size) is big enough to hold most of their row and index data.

### For Storage Engine Developers

* Internally the full length of the [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar.md) 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).

## See Also

* [BLOB and TEXT Data Types](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types.md)
* [MEDIUMTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/mediumtext.md)
* [Data Type Storage Requirements](https://mariadb.com/docs/server/reference/data-types/data-type-storage-requirements.md)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}
