TEXT types are used to store text data.
TEXT types are classified into TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The four text types correspond to four BLOB types and have the same maximum length and storage requirements.
The values of the TEXT types are treated as non-binary strings. They have a character set other than binary, and are sorted and compared based on the collation of the character set.
When strict SQL mode is not enabled, if the value assigned to a TEXT column exceeds the maximum length of the column, the excess part of the value is truncated, and an alert is generated. In strict SQL mode, if non-space characters are truncated, an error instead of an alert occurs, and the insertion of the value is prohibited. When excess trailing spaces in the value inserted into a TEXT column are truncated, an alert is generated, regardless of the SQL mode.
TINYTEXT
The maximum length of a TINYTEXT column is 256 characters.
Each TINYTEXT value is stored with a one-byte prefix that indicates the number of bytes in the value. Syntax:
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If CHARACTER SET specifies the binary character set, the column is created as a binary string column, and the TEXT type changes to the BLOB type.
TEXT
The maximum length of a TEXT column is 65,536 characters.
Each TEXT value is stored with a two-byte prefix that indicates the number of bytes in the value. You can specify an optional length M for the TEXT type. Syntax:
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If CHARACTER SET specifies the binary character set, the column is created as a binary string column, and the TEXT type changes to the BLOB type.
MEDIUMTEXT
The maximum length of a MEDIUMTEXT column is 16,777,216 characters.
Each MEDIUMTEXT value is stored with a three-byte prefix that indicates the number of bytes in the value.
Syntax:
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If CHARACTER SET specifies the binary character set, the column is created as a binary string column, and the TEXT type changes to the BLOB type.
In addition, OceanBase Database supports the extended LONG type, but we recommend that you use MEDIUMTEXT.
LONGTEXT
The maximum length of a LONGTEXT column is 50,331,648 characters or48 MB. The maximum valid length of a LONGTEXT column also depends on the maximum packet size and available memory configured in the client/server protocol.
Each LONGTEXT value is stored with a four-byte prefix that indicates the number of bytes in the value. Syntax:
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
The CHARACTER SET attribute specifies the character set. If necessary, you can use COLLATE and other attributes to specify the collation of the character set. If CHARACTER SET specifies the binary character set, the column is created as a binary string column, and the TEXT type changes to the BLOB type.