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>By default, the CTAS statement is used in the same manner as the CREATE TABLE statement, and these two table creation methods share the same syntax. For example, regardless of the statement that is used to create a table, an index is automatically created for each column of the table.
Parameters
Parameter | Description |
| The name of the table. 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 underscore (_). Specify the table name in the |
| Determines whether the table that is specified by |
| 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. |
| 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
In the following examples, a table named new_customer is created from the customer table. The following code shows the schema of the customer table:
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'; Example 1
Read data of all columns from the customer table and write the data to the new_customer table so that the new_customer table has the same column names and data types as the customer table. Sample statement:
CREATE TABLE new_customer
AS
SELECT * FROM customer;Example 2
Read data of the customer_id and customer_name columns from the customer table and write the data to the new_customer table. Sample statement:
CREATE TABLE new_customer
AS
SELECT customer_id, customer_name
FROM customer;Example 3
Read data of the customer_id and login_time columns from the customer table and write the data to the new_customer table. In the new_customer table, define the customer_id and login_time columns as the primary key and the customer_id column as the distribution key. Sample statement:
CREATE TABLE new_customer (
PRIMARY KEY (customer_id,login_time))
DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id, login_time
FROM customer;Example 4
Read data of the customer_id and login_time columns from the customer table and write the data to the new_customer table. In the new_customer table, define the customer_id and login_time columns as the primary key and the customer_id column as the distribution key. Then, modify the data type of the login_time column. Sample statement:
CREATE TABLE new_customer (
login_time date,
PRIMARY KEY (customer_id,login_time))
DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id, login_time
FROM customer;Example 5
Read data of the customer_id, customer_name, and login_time columns from the customer table and write the data to the new_customer table. In the new_customer table, define the customer_id column as the inverted index column. Sample statement:
CREATE TABLE new_customer (
INDEX a_idx (customer_id))
AS
SELECT customer_id, customer_name, login_time
FROM customer;