STRING types

The STRING type, as a text data type, provides better support analytical processing (AP) workloads and facilitates their migration. Additionally, it supports variable-length strings as primary keys.

Basic features

• When you create a table, you do not need to specify the length for the STRING type. The maximum length is 16 MB (16777216 bytes).

• Columns of the STRING type (referred to as string columns) can be used as primary key columns and index columns. They can also serve as partition keys for partition types such as key, range columns, and list columns.

• The STRING type is a special case of the LOB type:

  • If a STRING column is not a primary key and its length exceeds LOB_INROW_THRESHOLD (the size limit for storing LOB data within a row), the data will be stored externally instead of within the row.
  • If a STRING column is a primary key, all data will be stored inline, and the data length will be limited by the primary key length constraint.

Limitations

• The default value of a STRING column can only be NULL.

• When a STRING column is a non-primary key column, whether the data of the column is stored in the row or outside the row depends on LOB_INROW_THRESHOLD. When a STRING column is a primary key column, all data of the column is stored in the row.

  For more information about LOB_INROW_THRESHOLD, see LOB type and ob_default_lob_inrow_threshold.

• When a string column is used as a primary key or an index key, the length of the primary key or index key is checked during insertion or update. If it exceeds 16K, an error will be reported.

• When creating an index on a table containing a string column, an error will occur if the string column in the primary table has already overflowed to external storage.

• After creating an index on a string column, if data is inserted into (or updated in) the string column and the data needs to overflow to external storage (the data length exceeds LOB_INROW_THRESHOLD), an error will occur, and the data cannot be inserted or updated.

• If a string column is used as a partition key, and the inserted (or updated) data has already overflowed as a LOB, an error will occur, and the data cannot be inserted or updated. (For example, INSERT INTO ... SELECT ..., where the data in the SELECT statement is already overflowed.)

• In direct load scenarios, if the target table has an index on a string column and the imported data would cause the string column to overflow to external storage (the data length exceeds LOB_INROW_THRESHOLD), an error will occur, and the data cannot be imported.

• In incremental direct load scenarios, if the target table has an index on a string column and the corresponding string column in the source table has already overflowed to external storage, an error will occur, and the data cannot be imported.

Examples

1. Create a table named test_tbl1 with col1, col2, and col3 as string columns. Set col1 as the primary key. Partition the table by key using the values in col1 into 4 partitions.

   CREATE TABLE test_tbl1 (col1 STRING PRIMARY KEY,
       col2 STRING,
       col3 STRING,
       col4 VARCHAR(10))
       PARTITION BY KEY(col1) PARTITIONS 4;
   

2. Create an index named idx_test_tbl1 on the col2 column.

   CREATE INDEX idx_test_tbl1 ON test_tbl1(col2);
   

3. View the definition of the test_tbl1 table.

   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 set
   

4. Insert test data into the test_tbl1 table.

   INSERT INTO test_tbl1 VALUES("A1", "B1", "C1", "D1"),("A2", "B2", "C2", "D2");
   

5. View the data in the test_tbl1 table.

   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