Execute the CREATE TABLE statement to create an IMCI - PolarDB

This topic describes how to create an in-memory column index (IMCI) when you create a table.

Prerequisites

After you add a read-only column store node and configure the cluster endpoint, you can connect to the cluster by using the cluster endpoint and execute the CREATE TABLE statement to create and manage an IMCI.

Read-only column store nodes are added. For more information, see Add a read-only column store node.
The cluster endpoint is configured. Manual and automatic request distribution solutions can be used to distribute requests to row store and column store nodes. You can select a request distribution solution based on your business requirements and configure the cluster endpoint. For more information, see Request distribution.
The database cluster is connected by using the cluster endpoint. For more information, see Connect to a cluster.

Create an IMCI

CREATE TABLE syntax
- To create an IMCI, you need to only add the COLUMNAR=1 string to the COMMENT field of the CREATE TABLE statement when you create a table. Other parameters remain unchanged and unaffected.
- You can separately add the COLUMNAR=1 string to the COMMENT field of a column, which is valid only for the column. You can also add the string to the COMMENT field at the end of the CREATE TABLE statement, which is valid for columns of all supported data types in the table.
  Note
  - For PolarDB for MySQL 8.0.1.1.25 and later, IMCIs support the BLOB and TEXT data types.
  - For PolarDB for MySQL 8.0.1.1.28 and later, IMCIs support the ENUM data type.
  - For PolarDB for MySQL 8.0.1.1.29 and later, you can create IMCIs on partitioned tables.
  - For PolarDB for MySQL 8.0.1.1.30 and later, IMCIs support the BIT, JSON, and Geo data types.
  - IMCIs do not support the SET data type.
Examples:
```
CREATE TABLE t1(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

CREATE TABLE t2(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';
```
In the preceding examples:
- When you create the t1 table, the IMCI valid for the col1 and col2 columns is created.
- When you create the t2 table, an IMCI valid for the table is created. The IMCI is valid for the col1, col2, and col3 columns.
CREATE TABLE LIKE syntax: If you execute the CREATE TABLE LIKE statement to create a table and the source table contains an IMCI, the destination table contains the same IMCI.
CREATE TABLE ... SELECT syntax: If you execute the CREATE TABLE ... SELECT statement to create a table, you can add the COLUMNAR=1 string to the COMMENT field to create an IMCI valid for all columns of the table. However, when you execute the CREATE TABLE ... SELECT statement to create a table, you cannot create an IMCI that is valid for a specified column.
Examples:
```
CREATE TABLE t3(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB;

CREATE TABLE t4 COMMENT 'COLUMNAR=1' SELECT col1, col2 FROM t3;
```
In the preceding examples:
The COMMENT 'COLUMNAR=1' string is valid for the t4 table. An IMCI that is valid for all columns of the t4 table is created, including the col1 and col2 columns, and subsequently added columns.

Note

When you add COLUMNAR=1 to the COMMENT field, take note of the following points: 1) COLUMNAR is not case-sensitive. 2) You do not need to use a delimiter between COLUMNAR=1 and the original part of the COMMENT field. 3) Adding COLUMNAR=1 does not affect the original part.

View the IMCI structure of a table

Syntax: You can execute the SHOW CREATE TABLE <tablename> FULL statement to view the IMCI structure of a table.

Examples:

SHOW CREATE TABLE test.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='columnar=1'

SHOW CREATE TABLE test.t1 FULL\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='columnar=1'

In the preceding examples:

Only COMMENT information, but not the COLUMNAR INDEX definition, is displayed when you execute the SHOW CREATE TABLE <tablename> statement.
To display the IMCI structure in the COLUMNAR INDEX field, you must execute the SHOW CREATE TABLE <tablename> FULL statement.