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 255 bytes.
The syntax of TINYTEXT is as follows:
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,535 bytes.
You can specify an optional length M for the TEXT type. The syntax is as follows:
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,215 bytes.
The syntax of MEDIUMTEXT is as follows:
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 536,870,910 bytes. The maximum valid length of a LONGTEXT column also depends on the maximum packet size and available memory configured in the client/server protocol.
The syntax of LONGTEXT is as follows:
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.