Creates a new table.

Syntax

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] [ON UPDATE update_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

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'

Remarks

• The first field of type TIMESTAMP or TIMESTAMP(6) has special behavior for insert and update operations, defaulting to the current timestamp value. Refer to the discussion of these types in the Data Types topic for more information.
• 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.
• Note that table comments are not supported in MemSQL.

ON UPDATE Behavior

If ON UPDATE update_value is specified in column_definition, and if any other column is updated but the specified column is not explicitly updated, then update_value will be placed in the column during an UPDATE operation. The update_value may be either CURRENT_TIMESTAMP or NOW().

AUTO_INCREMENT Behavior

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. The AUTO_INCREMENT column must be included in an index. Often it is natural to include it in the primary key, but a non-primary key is also allowed. Note that you should not insert data directly to the leaf nodes for a table with AUTO_INCREMENT.

If no value is specifically assigned to the AUTO_INCREMENT column, sequential numbers will be assigned automatically for rows inserted on the same aggregator. For example:

DROP TABLE IF EXISTS test_rowKey;
CREATE TABLE test_rowKey (
 rowKey BIGINT AUTO_INCREMENT,
 id VARCHAR(15),
 SHARD KEY `id` (`id`),
 KEY (rowKey)
);

INSERT INTO test_rowKey values (1, 'a-manual');
INSERT INTO test_rowKey values (NULL, 'b-auto');
INSERT INTO test_rowKey (id) values ('c-auto');
INSERT INTO test_rowKey value (NULL, 'd-auto');
INSERT INTO test_rowKey value (3, 'e-manual');
INSERT INTO test_rowKey value (NULL, 'f-auto');
INSERT INTO test_rowKey value (NULL, 'g-auto');

SELECT rowKey, id FROM test_rowKey ORDER BY id;
1	a-manual
1	b-auto
2	c-auto
3	d-auto
3	e-manual
4	f-auto
5	g-auto

To start with a value other than one, set AUTO_INCREMENT to the desired value, like this:

CREATE TABLE tbl_name AUTO_INCREMENT = 10;

That value will be assigned and the sequence will be reset from there so that the next value will follow sequentially from the largest value in the column. This sequence will apply to the individual aggregator to which it is applied, but will not span across multiple aggregators.

MySQL Compatibility

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

• 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 ...

The table will include a column for each column of the SELECT query. You can define indexes, additional columns, and other parts of the table definition in the create_definition. Some examples:

CREATE TABLE t2 (PRIMARY KEY (a, b)) AS SELECT * FROM t1;
CREATE TABLE t2 (KEY (a, b) USING CLUSTERED COLUMNSTORE) AS SELECT * FROM t1;
CREATE TABLE t2 (a int, b int) AS SELECT c, d FROM t1;

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!