All Products
Search
Document Center

AnalyticDB for MySQL:CREATE TABLE AS SELECT (CTAS)

Last Updated:Mar 11, 2024

AnalyticDB MySQL allows you to execute the CREATE TABLE statement to create a table or execute the CREATE TABLE AS SELECT(CTAS) statement to write 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 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 on each column of the table.

Parameters

ParameterDescription
table_nameThe 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 db_name.table_name format to distinguish tables that share the same name across different databases.

IF NOT EXISTSDetermines whether the table that is specified by table_name exists. If the table exists, no table is created.
IGNOREOptional. 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.
REPLACEOptional. 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 primary key, distribution key, partition key, column data types, and default values 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)) 
  DISTRIBUTE 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)) 
  DISTRIBUTE 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;