STRING type

The STRING type is a text type that supports and migrates AP services and can be used as an indefinite-length string for a primary key.

Basic features

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

• A column of the STRING type (hereinafter referred to as a String column) can be used as a primary key column or an index column, or as a partitioning key for Key/Range columns/List columns partitioning.

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

  • When a String column is not a primary key column, if its length exceeds the LOB_INROW_THRESHOLD (the threshold for LOB columns), the data will be stored outside the row.
  • When a String column is a primary key column, all data is stored within the row, and the data length is limited by the primary key length.

Limitations

• The default value for the STRING type is only NULL.

• When a String column is not a primary key column, whether the data overflows to be stored outside the row depends on the value of LOB_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 a primary key or an index key, an error is returned if the length of the primary key or index key exceeds 16K during write (or update) operations.

• If a String column in the main table has overflowed to be stored outside the row, an error is returned when you attempt to create an index on the String column in the main table.

• If a String column has an index and the data written (or updated) to the String column exceeds the LOB_INROW_THRESHOLD (i.e., the data length is greater than LOB_INROW_THRESHOLD), an error is returned when you attempt to write (or update) the data.

• If a String column is used as a partitioning key and the data written (or updated) to the String column has overflowed to be stored outside the row, an error is returned when you attempt to write (or update) the data. (For example, in the INSERT INTO ... SELECT ... statement, the data returned by the SELECT statement has overflowed to be stored outside the row.)

• In a direct load scenario, if an index exists on a String column of the target table and the data length of the String column exceeds LOB_INROW_THRESHOLD after the data is imported, an error is returned when you attempt to import the data.

• In an incremental direct load scenario, if an index exists on a String column of the target table and the corresponding String column in the source table has overflowed to be stored outside the row, an error is returned when you attempt to import the data.

Examples

1. Create a table named test_tbl1, where col1, col2, and col3 are String columns, and col1 is the primary key. The table is partitioned by the values of col1 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 String column col2.

   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