Create a table.

CREATE [REFERENCE | TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]

CREATE TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name { column_definition | AS computed_column_definition }
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | { INDEX | KEY } [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] SHARD KEY [index_type] (index_col_name,...)
      [index_option] ...

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']

computed_column_definition:
    computed_column_expression PERSISTED data_type

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED]
  | SMALLINT[(length)] [UNSIGNED]
  | INT[(length)] [UNSIGNED]
  | INTEGER[(length)] [UNSIGNED]
  | BIGINT[(length)] [UNSIGNED]
  | REAL[(length,decimals)] [UNSIGNED]
  | DOUBLE[(length,decimals)] [UNSIGNED]
  | DECIMAL[(length[,decimals])] [UNSIGNED]
  | NUMERIC[(length[,decimals])] [UNSIGNED]
  | TIMESTAMP
  | TIMESTAMP(6)
  | DATETIME
  | DATETIME(6)
  | DATE
  | TIME
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
  | TEXT [BINARY]
  | MEDIUMTEXT [BINARY]
  | LONGTEXT [BINARY]
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | JSON [COLLATE collation_name]
  | GEOGRAPHY
  | GEOGRAPHYPOINT

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING { BTREE | HASH | CLUSTERED COLUMNSTORE }

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | COMMENT 'string'

table_options:
    table_option [[,] table_option] ...

table_option:
    AUTO_INCREMENT [=] value
  | COMMENT [=] 'string'

Notes

• tbl_name is the name of the table to create in the MemSQL database.
• CREATE TABLE is slower in MemSQL than in MySQL. See Code Generation for more information.
• The BTREE index type creates a skip list index in MemSQL. This index has very similar characteristics to a BTREE index.
• The only charset_name supported by MemSQL is ‘utf8’.
• This command must be run on the master aggregator node (se Node Requirements for MemSQL Commands)
• computed_column_expression defines the value of a computed column using other columns in the table, constants, built-in functions, operators, and combinations thereof. For more information see Persistent Computed Columns
• Temporary tables, created with the TEMPORARY option, will be deleted when the client session terminates. For ODBC/JDBC, this is when the connection closes. For interactive client sessions, it is when the user terminates the client program.

AUTO-INCREMENT

AUTO_INCREMENT values in distributed tables are guaranteed to be unique across aggregators but not monotonically increasing. These values are monotonically increasing on each aggregator but not consecutive across the cluster. The AUTO_INCREMENT must be a BIGINT column because MemSQL uses the high 14 bits to encode the aggregator id and the bottom 50 bits for a per-aggregator consecutive value.

Reference tables must be created with an explicit primary key and distributed tables must be created with either a primary or shard key (See Distributed SQL). If your schema does not naturally have a primary or shard key, you can add an AUTO_INCREMENT column to the table. MemSQL does not require that the AUTO_INCREMENT column is the leading column of the primary key, so you can add it to an existing index or create a new one. Here is an example:

memsql> CREATE TABLE events (
    event_type VARCHAR(256),
    time TIMESTAMP,
    KEY (event_type)
)

This statement can be converted to the following:

memsql> CREATE TABLE events (
    event_type VARCHAR(256),
    time TIMESTAMP,
    id INT AUTO_INCREMENT,
    PRIMARY KEY (event_type, id)
)

MySQL Compatibility

MemSQL’s syntax differs from MySQL mainly in the datatypes and storage it supports, and some specific index hints.

• CREATE TABLE ... AUTO_INCREMENT=n : the value of n is currently ignored.
• KEY_BLOCK_SIZE [=] value : value is currently ignored.

Example

memsql> CREATE TABLE IF NOT EXISTS my_MemSQL_table(id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(10) NOT NULL);

memsql> CREATE REFERENCE TABLE pages(
 ->     page_id INT PRIMARY KEY AUTO_INCREMENT,
 ->     page_url VARCHAR(1000)
 -> );
 Query OK, 0 rows affected (0.21 sec)

CREATE TABLE … SELECT

CREATE TABLE ... SELECT (often referred to as CREATE TABLE AS SELECT) can create one table from results of a SELECT query.

CREATE TABLE [IF NOT EXISTS] tbl_name
    [create_definition,...]
    [AS] SELECT ...

Note

If select from computed column, you should specify column_definition.

Example

Extract time column from an event table to build a times table.

memsql> CREATE TABLE events(
    -> type VARCHAR(256),
    -> time TIMESTAMP
    -> );
Query OK, 0 rows affected (0.19 sec)

memsql> Insert into events values('WRITE', NOW());
Query OK, 1 row affected (0.08 sec)

memsql> CREATE TABLE times(
		-> id INT AUTO_INCREMENT KEY
    -> ) SELECT time from events;
Query OK, 1 row affected (0.19 sec)

memsql> select * from times;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2016-03-25 15:38:12 |
+----+---------------------+
1 row in set (0.06 sec)

Was this Article Helpful?

Thanks For Your Feedback!