OceanBase Database supports the following large object (LOB) data types: binary large object (BLOB) and TEXT.
The BLOB data types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The TEXT data types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.
BLOB data types are used to store binary strings. BLOB values are compared and sorted based on the numeric value of each byte. TEXT data types are used to store non-binary strings. TEXT values are compared and sorted based on the collation of each character set. A TEXT data type with a specified BINARY character set is equivalent to a BLOB data type.
In non-strict mode, you can insert a string into a BLOB or TEXT column that exceeds the maximum length of the column. The string is truncated to fit the column. In strict mode, an error is reported when you insert an overlong string. If the excess part of the string consists of spaces, no error is reported in both strict mode and non-strict mode.
For a TEXT or BLOB column, trailing space padding is not performed during an insert and trailing space stripping is not performed during a query.
In most cases, a BLOB column can be considered as a VARBINARY column that can be as long as you like. In the same way, a TEXT column can be considered as a VARCHAR column. The difference is that a BLOB or TEXT column cannot have a default value, but a VARBINARY or VARCHAR column can.
Notice
You cannot create an index on a TEXT or BLOB column.
Among the TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT data types, only the TEXT data type allows you to explicitly specify the length. OceanBase Database can determine the appropriate data type to be used based on the length that you specify. These rules also apply to the BLOB types. Here is an example:
obclient> CREATE TABLE t(c1 TEXT(30), c2 TEXT(300), c3 TEXT(30000), c4 TEXT(10000000));
Query OK, 0 rows affected
obclient> DESC t;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1 | tinytext | YES | | NULL | |
| c2 | text | YES | | NULL | |
| c3 | mediumtext | YES | | NULL | |
| c4 | longtext | YES | | NULL | |
+-------+------------+------+-----+---------+-------+