STRING type

The STRING type is a text type that supports and migrates AP services. It also supports variable-length strings as primary keys.

Basic features

• When you create a table, you do not need to specify the length of the STRING type. The maximum length of data that can be stored is 16 MB (16777216 bytes).

• A STRING type column (hereinafter referred to as a String column) can be a primary key column or an index column. It can also be a partitioning key for the KEY, RANGE, or LIST partitioning types.

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

  • If a STRING column is not a primary key column, the data will be stored out of line when its length exceeds the LOB_INROW_THRESHOLD (the threshold for LOB columns).
  • If a STRING column is a primary key column, all data will be stored in-line, and the data length will be limited by the primary key length.

Limitations

• The default value of the STRING type only supports NULL.

• If a STRING column is not a primary key column, whether the data will be stored out of line depends on the LOB_INROW_THRESHOLD value.

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

• If a String column is a primary key or index key, an error will be returned when the length of the primary key or index key exceeds 16 KB during write or update.

• If a String column is part of an index, an error will be returned when you create the index if the String column in the primary table has already overflowed to out-of-line storage.

• If a String column has an index, an error will be returned when you write or update data in the String column if the data length exceeds the LOB_INROW_THRESHOLD value.

• If a String column is a partitioning key and the data to be written or updated is an overflowed LOB, an error will be returned when you write or update the data. (For example, in the INSERT INTO ... SELECT ... statement, the data selected by the SELECT statement is an overflowed LOB.)

• In direct load scenarios, if the target table has a String column with an index, an error will be returned when you import data to the target table if the String column needs to overflow to out-of-line storage (i.e., the data length exceeds the LOB_INROW_THRESHOLD value).

• In incremental direct load scenarios, if the target table has a String column with an index and the corresponding String column in the source table has already overflowed to out-of-line storage, an error will be returned when you import data.

Examples

1. Create a table named test_tbl1, which contains three String columns (col1, col2, and col3) and col1 is the primary key. The table is partitioned by the col1 column into four 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 returned 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 returned result is as follows:

   +------+------+------+------+
   | col1 | col2 | col3 | col4 |
   +------+------+------+------+
   | A2   | B2   | C2   | D2   |
   | A1   | B1   | C1   | D1   |
   +------+------+------+------+
   2 rows in set