Mysql Data Types and Sizes for String/ Text

Mysql Data Types and Sizes for String/ Text

Category : How-to

Get Social!

MySQL or MariaDB has several data types for handling text-based characters. There are several data types for handling smaller strings such as CHAR and VARCHAR data types. For larger text-based strings there are BLOB based data types such as TEXT.

It’s worth noting at this point that the below-quoted sizes do not necessarily represent the number of characters they can hold. In addition, more recent versions of MySQL (version 5 and 8) counts characters when defining the length, however, prior to these versions byres were used.

The below table shows the ‘size’ of each data type – notice that some data types are mentioned in characters, and others in bytes. The number of characters are always used when defining a string data type in your DDL statement – for example, VARCHAR(10).

Data TypeSizeDescription
CHAR(n)255 charactersFixed-length character field. Rows are padded with whitespace to the defined length.
VARCHAR(n)65,535 bytes *Variable-length character field with no manipulation on INSERT or SELECT.
TINYTEXT255 bytesVariable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
TEXT(n)65,535 bytes Variable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
MEDIUMTEXT(n)16,777,215 bytes (16MB)Variable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
LONGTEXT(n)4,294,967,295 bytes (4GB)Variable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
ENUM2 bytesThe ENUM doesn’t need a size definition but can hold up to 65,535 values.

* The maximum row length in MySQL is 65,535 bytes – your total row size cannot exceed this byte value. Keep in mind that some character sets consume more than one byte per character. For example, utf8mb4 can take up to 4 bytes per character and therefore the maximum VARCHAR is approximately one-quarter of the maximum row size.


Related posts:


Leave a Reply

Visit our advertisers

Quick Poll

Do you use GlusterFS in your workplace?

Visit our advertisers