AnalyticDB for MySQL allows you to execute the CREATE TABLE statement to create a table or execute the CREATE TABLE AS SELECT (CTAS) statement to write the queried data to a new table.

Syntax

CREATE TABLE [IF NOT EXISTS] <table_name> [table_definition]
[IGNORE|REPLACE] [AS] <query_statement>
Note By default, the CTAS statement is consistent with the CREATE TABLE statement and these two table creation methods support the same syntax. For example, regardless of the statement that you use to create a table, an index is automatically created on each column of the table.

Parameters

Parameter Description
table_name The name of a table to be created.

The table name must be 1 to 127 characters in length and can contain letters, digits, and underscores (_). The table name must start with a letter or an underscore (_).

Specify the table name in the db_name.table_name format to distinguish the tables that have the same name across different databases.

IF NOT EXISTS Determines whether the table that is specified by table_name exists. If the table exists, no table is created.
IGNORE Optional. Before a new record is written to the table, the system checks whether the table contains an existing record that has the same primary key as the new record. If a match is found, the new record is not written to the table.
REPLACE Optional. Before a new record is written to the table, the system checks whether the table contains an existing record that has the same primary key as the new record. If a match is found, the new record overwrites the existing record.

Examples

CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT 'Customer ID',
customer_name varchar NOT NULL COMMENT 'Customer name',
phone_num bigint NOT NULL COMMENT 'Phone number',
city_name varchar NOT NULL COMMENT 'City',
sex int NOT NULL COMMENT 'Gender',
id_number varchar NOT NULL COMMENT 'ID card number',
home_address varchar NOT NULL COMMENT 'Home address',
office_address varchar NOT NULL COMMENT 'Office address',
age int NOT NULL COMMENT 'Age',
login_time timestamp NOT NULL COMMENT 'Logon time',
PRIMARY KEY (login_time,customer_id,phone_num)
 )
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Customer information table';                   
INSERT INTO 
customer values
(002367,'Tom','13678973421','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00'),(002368,'Alex','13878971234','Hangzhou',0,'987300','West Lake','Cloud Town',28,'2018-08-01 11:00:00'),(002369,'Eric','13968075284','Hangzhou',1,'987300','West Lake','Cloud Town',35,'2018-09-12 08:11:00');                

Execute the following SQL statements to read data from the table customer and write the data to the table new_customer:

CREATE TABLE new_customer AS SELECT * FROM customer;
CREATE TABLE new_table AS SELECT a, b FROM base_table
CREATE TABLE new_table (PRIMARY KEY (a)) DISTRIBUTE BY HASH (b)
AS SELECT a, b FROM base_table
CREATE TABLE new_table (c varchar, PRIMARY KEY (a)) DISTRIBUTE BY HASH (b)
AS SELECT a, b, c FROM base_table
CREATE TABLE new_table (INDEX a_idx (a))
AS SELECT a, b, c FROM base_table