OceanBase Database supports large object (LOB) data types, including 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.
The BLOB type stores binary strings, and the comparisons and sorting of BLOB values are based on the numerical value of each byte. The TEXT type stores non-binary strings, and the comparisons and sorting of TEXT values are based on the character sequence of the character set. If the BINARY character set is specified for a TEXT type, it is equivalent to a BLOB type.
In non-strict mode, users can insert strings into BLOB or TEXT columns that exceed the maximum length of the column. The extra part will be truncated. In strict mode, this scenario will result in an error. If the extra part consists only of spaces, regardless of whether strict mode is enabled or not, there will be no error.
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 | |
+-------+------------+------+-----+---------+-------+