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'
  | BUCKET_COUNT [=] value
  | WITH (index_kv_options)
  | UNENFORCED [RELY | NORELY]

index_kv_options:
    index_kv_option [, index_kv_option] ...

index_kv_option:
    RESOLUTION = value
  | COLUMNSTORE_SEGMENT_ROWS = value
  | COLUMNSTORE_FLUSH_BYTES = value

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

table_option:
    AUTO_INCREMENT [=] value
  | COMMENT [=] 'string'
  | AUTOSTATS_ENABLED = { TRUE | FALSE }
  | FULLTEXT [index_name] (index_col_name,...)

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 can be used to automatically generate a unique value for new rows. When you insert a new row, and the AUTO_INCREMENT field is DEFAULT, NULL, or 0, MemSQL will automatically assign a value. AUTO_INCREMENT only guarantees that automatically generated values are unique. It does not in general guarantee that they are consecutive or sequential, that they are monotonically increasing, that they start from any particular value, or that they are distinct from explicitly set values. If you explicitly set a value in an INSERT or UPDATE statement, it may collide with past or future automatically generated values.

A table can have only one AUTO_INCREMENT column. The AUTO_INCREMENT column must be included in an index (not necessarily a PRIMARY or UNIQUE key, a regular key is also allowed).

On a sharded (non-REFERENCE) table, AUTO_INCREMENT can only be used on a BIGINT column. AUTO_INCREMENT values in sharded tables are assigned using the high 14 bits to encode the aggregator ID and the bottom 50 bits for a per-aggregator sequential value. Therefore, inserts on an individual aggregator generate sequential values, but the automatically generated values from inserts across multiple aggregators are only unique, not sequential.

Here is an example to illustrate how AUTO_INCREMENT values are generated:

CREATE TABLE t (
 id BIGINT AUTO_INCREMENT,
 name VARCHAR(15),
 KEY (id)
);

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

SELECT id, name FROM t ORDER BY name;

One possible output is:

+----+----------+
| id | name     |
+----+----------+
|  1 | a-manual |
|  1 | b-auto   |
|  2 | c-auto   |
|  3 | d-auto   |
|  3 | e-manual |
|  4 | f-auto   |
|  5 | g-auto   |
+----+----------+

Another possible output is:

+------------------+----------+
| id               | name     |
+------------------+----------+
|                1 | a-manual |
| 6755399441055745 | b-auto   |
| 6755399441055746 | c-auto   |
| 6755399441055747 | d-auto   |
|                3 | e-manual |
| 6755399441055748 | f-auto   |
| 6755399441055749 | g-auto   |
+------------------+----------+

Note that the automatically generated values can differ depending on which aggregator you run the inserts on. Of course, if you ran some inserts on one aggregator and some inserts on another aggregator, you would get different automatically generated values. Also note that automatically generated values and explicitly set values can collide.

It is possible to override the starting AUTO_INCREMENT value for reference tables, by using the AUTO_INCREMENT option on a CREATE TABLE statement, like this:

CREATE REFERENCE TABLE t (...) AUTO_INCREMENT = 7;

For reference tables, inserts will automatically generate values starting from the chosen value and increasing sequentially. For example:

memsql> create reference table t (id bigint auto_increment primary key) auto_increment = 7;

memsql> insert into t values (), ();
Records: 2  Duplicates: 0  Warnings: 0

memsql> select * from t;
+----+
| id |
+----+
|  7 |
|  8 |
+----+

It is possible but not recommended to use this option on sharded tables. For sharded tables, AUTO_INCREMENT values will be generated sequentially starting from the chosen value for inserts on the master aggregator only; this option does not affect inserts into sharded tables on child aggregators. Also note that it is possible for automatically generated values to collide if you use this option to set the AUTO_INCREMENT starting value on the master aggregator to overlap with the range of values generated by a child aggregator. Therefore, this option is recommended only for REFERENCE tables, not sharded tables.

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

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. Persisted computed columns can also be specified this way. 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;
CREATE TABLE t2 (b AS a+1 PERSISTED int) AS SELECT a FROM t1;

Example

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

memsql> CREATE TABLE events(
    -> type VARCHAR(256),
    -> time TIMESTAMP
    -> );

memsql> Insert into events values('WRITE', NOW());

memsql> CREATE TABLE times(
		-> id INT AUTO_INCREMENT KEY
        -> ) SELECT time from events;

memsql> select * from times;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2016-03-25 15:38:12 |
+----+---------------------+

FULLTEXT behavior

MemSQL supports full text search across text columns in a columnstore table using the FULLTEXT index type. A full text index can only be added during CREATE TABLE and only on the text types CHAR, VARCHAR, TEXT, and LONGTEXT. Column data size is limited by the text type used. Size limitations for supported data types are documented in Data Types.

Warning

A FULLTEXT index cannot be dropped or altered after the table is created, and if the table is dropped, the index is deleted automatically.

Searches across FULLTEXT columns is done using the SELECT ... MATCH AGAINST syntax. For more information, see Full text search using MATCH.

Errors

These are the possible errors you may encounter when using FULLTEXT.

Error Error String
Invalid Type specified for column Invalid type specified for FULLTEXT
Specifying FULLTEXT keyword more than once in a CREATE TABLE statement FULLTEXT may only be specifed once in a CREATE TABLE statement
Specifying the same column multiple times Column may only be specified once in a FULLTEXT definition
Specifying a column that is not defined on the table Column not defined
Specifying FULLTEXT on a row store table Only column store tables may have a FULLTEXT index

Examples

This example creates an index for both the title column and the body column. The two columns may be searched independently.

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    KEY (id) USING CLUSTERED COLUMNSTORE, 
    FULLTEXT (title,body));
Was this article useful?