Product Solutions Support
Try MemSQL

CREATE TABLE

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

Info

MemSQL supports primary/unique keys only if the key contains all columns in the shard key. For more information about the shard key, see Distributed SQL.

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.

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;
Info

If you are selecting from computed column, ensure that you 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 useful?