As a text type, STRING can better support and migrate AP business, and also supports variable-length strings as primary keys.
Basic features
When creating a table, you do not need to specify the data length for the
STRINGtype. The maximum storable data length is 16 MB (16,777,216 bytes).Columns of the
STRINGtype (hereinafter referred to as string columns) can be used as primary key columns and index columns, and can also serve as the partitioning keys for the Key/Range columns/List columns partitioning types.The
STRINGtype is a special case of theLOBtype:- For the
STRINGtype, which is not a primary key column, data exceeding the length ofLOB_INROW_THRESHOLD(the threshold forLOBcolumns) will be stored out-of-row. - The
STRINGtype is the primary key column. All data is stored in-row, and the data length is limited by the primary key length.
- For the
Limitations
The default value for the
STRINGtype is onlyNULL.When the
STRINGtype is used for a non-primary key column, whether overflow data is stored externally is determined by the value ofLOB_INROW_THRESHOLD.For more information about
LOB_INROW_THRESHOLD, see LOB types and ob_default_lob_inrow_threshold.When a String column is used as the primary key or index key, its length is checked during write (or update) operations. If the length exceeds 16 KB, an error is returned.
When creating an index on a String column, if the String data on the primary table has already overflowed and requires external storage, an error will be returned indicating that the index cannot be created.
After an index is created on a String column, if the data written (or updated) to that column exceeds the row length (i.e., the data length is greater than
LOB_INROW_THRESHOLD), an error will be returned, indicating that the data cannot be written or updated.When a String column is used as the partitioning key, an error will be returned if the data to be written (or updated) is an overflowed LOB. (For example, in
INSERT INTO ... SELECT ..., where the data for theSELECTstatement is an overflowed LOB.)In a direct load scenario, if an index exists on the String column of the target table and the data length exceeds the maximum allowed for in-row storage (
LOB_INROW_THRESHOLD) after import, an error will be returned and the data cannot be imported.In an incremental direct load scenario, if an index exists on the String column of the target table and the corresponding String column in the source table has exceeded its row length and requires out-of-row storage, an error will be reported and data import will fail.
Usage examples
Create a table named
test_tbl1, wherecol1,col2, andcol3are string columns, andcol1is the primary key. The table is partitioned by KEY on the value ofcol1into four partitions.CREATE TABLE test_tbl1 (col1 STRING PRIMARY KEY, col2 STRING, col3 STRING, col4 VARCHAR(10)) PARTITION BY KEY(col1) PARTITIONS 4;Create an index named
idx_test_tbl1on thecol2column of the string type.CREATE INDEX idx_test_tbl1 ON test_tbl1(col2);View the definition of table
test_tbl1.SHOW CREATE TABLE test_tbl1;The return result is as follows:
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_tbl1 | CREATE TABLE `test_tbl1` ( `col1` string NOT NULL, `col2` string DEFAULT NULL, `col3` string DEFAULT NULL, `col4` varchar(10) DEFAULT NULL, PRIMARY KEY (`col1`), KEY `idx_test_tbl1` (`col2`) BLOCK_SIZE 16384 LOCAL ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 partition by key(col1) (partition `p0`, partition `p1`, partition `p2`, partition `p3`) | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setInsert test data into the
test_tbl1table.INSERT INTO test_tbl1 VALUES("A1", "B1", "C1", "D1"),("A2", "B2", "C2", "D2");View the data in the
test_tbl1table.SELECT * FROM test_tbl1;The return result is as follows:
+------+------+------+------+ | col1 | col2 | col3 | col4 | +------+------+------+------+ | A2 | B2 | C2 | D2 | | A1 | B1 | C1 | D1 | +------+------+------+------+ 2 rows in set