Mysql Data Types and Sizes for String/ Text
Category : How-to
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).
|CHAR(n)||255 characters||Fixed-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.|
|TINYTEXT||255 bytes||Variable-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)|
|ENUM||2 bytes||The 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.