MySql - Text Data Type

About

This page talks about character data type support for MySQL

Note

No national data type

MySql encodes all string ¹⁾ in unicode, therefore there is no national data type and we translate them:

NVARCHAR as VARCHAR
NCHAR as CHAR

Varchar Default precision of 2000

We set the VARCHAR_DEFAULT_PRECISION to 2000 by default to avoid the Row size too large error.

Example, 4 varchar with the maximum allowed of 21844 by varchar will yield this error:

create table `howto`.`characters` (
`last_name` varchar(21844),
`first_name` varchar(21844),
`birth_date` varchar(21844),
`wikipedia_page` varchar(21844)
 )

Row size too large: The maximum row size for the used table type,
not counting BLOBs, is 65535.
This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs

Why does MySql as a max size?

Because:

MySQL stores the entire row data together in fixed-size pages
All VARCHAR columns are counted toward this limit based on their maximum declared size

This applies to both MyISAM and InnoDB storage engines

Note that TEXT and BLOB columns don't count toward the row size limit because MySQL stores them separately from the main table data.

Text

MySQL uses TEXT types for storing large character data.

CLOB column	Maximum length in characters	Byte Length	Formula
TINYTEXT	255	8	(2**8 - 1)
TEXT	65,535 or 64kb	16	(2**16 - 1)
MEDIUMTEXT	16,777,215 or 16MB	24	(2**24 - 1)
LONGTEXT	4,294,967,295 or 4GB	32	(2**32 - 1)

TEXT is the default for XML column.

Clob not supported

MySQL does not have any standard CLOB type. It uses text types for storing large character data.

In a cross transfer, when a CLOB data type is found on the source database, the MEDIUMTEXT data type is chosen by default if the MySQL target table does not exist.

¹⁾

MySql String