The TEXT data type is used to store all types of text data.
Text types include four types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. They correspond to the four BLOB types and have the same maximum length and storage requirements.
TEXT values are considered non-binary strings. They have character sets other than binary and are sorted and compared based on the collation of their character set.
When the strict SQL mode is not enabled, if a value assigned to a TEXT column exceeds its maximum length, the excess part of the value will be truncated and an alert will be generated. When the strict SQL mode is enabled, if non-space characters are truncated, an error (instead of an alert) will occur and the insertion will be prohibited. Regardless of the SQL mode, an alert is always generated when trailing spaces are trimmed from a value inserted into a TEXT column.
TINYTEXT
TINYTEXT is a TEXT type with a maximum length of 255 bytes.
The syntax for TINYTEXT is as follows:
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
CHARACTER SET specifies the character set. If necessary, you can use the COLLATE attribute and other attributes to specify the collation for the character set. Specifying the binary attribute of CHARACTER SET will create the column as the corresponding binary string data type; for example, TEXT becomes BLOB.
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]
CHARACTER SET specifies the character set. If necessary, you can use the COLLATE attribute and other attributes to specify the collation for the character set. Specifying the binary attribute of CHARACTER SET will create the column as the corresponding binary string data type; for example, TEXT will become BLOB.
MEDIUMTEXT
MEDIUMTEXT is a TEXT type with a maximum length of 16,777,215 bytes.
The syntax for MEDIUMTEXT is as follows:
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
CHARACTER SET specifies the character set. If necessary, you can use the COLLATE attribute along with any other attributes to specify the collation for the character set. If specifying the binary attribute of CHARACTER SET results in creating a column as the corresponding binary string data type, TEXT will become BLOB.
In addition, OceanBase AI Database also supports the LONG data type, but it is recommended to use MEDIUMTEXT.
LONGTEXT
LONGTEXT is a TEXT type with a maximum length of approximately 4 TB (the strict upper limit is 4 TB - 1 bytes, or 4,398,046,511,103 bytes). A single LOB write can be up to approximately 512 MB; data exceeding 512 MB must be appended via the LOB_WRITEAPPEND stream.
The syntax for LONGTEXT is as follows:
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
CHARACTER SET specifies the character set. If necessary, you can use the COLLATE attribute and other attributes to specify the collation for the character set. Specifying the binary attribute of CHARACTER SET will create the column as the corresponding binary string data type; for example, TEXT will become BLOB.