CREATE TABLE

CREATE [ROWSTORE] [REFERENCE | TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] <table_name>
    (<create_definition>,...)
    [<table_options>]
    [[AS] SELECT ...]

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

<create_definition>:
    <column_name> { <column_definition> | AS <computed_column_definition> }
  | [CONSTRAINT [symbol]] PRIMARY KEY [<index_type>] (<index_column_name>,...)
      [<index_option>] ...
  | { INDEX | KEY } [<index_name>] [<index_type>] (<index_col_name>,...)
      [<index_option>] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [<index_name>] [<index_type>] (<index_column_name>,...)
      [<index_option>] ...
  | [CONSTRAINT [symbol]] SHARD KEY [<index_type>] (<index_column_name>,...) [METADATA_ONLY]
      [<index_option>] ...
  | SORT KEY (<index_column_name>,... [DESC]), 
  | COLUMN GROUP [column_group_name] ( * ),
  | FULLTEXT [<index_name>] (<index_column_name>,...)

<column_definition>:
    <data_type> [NOT NULL | NULL] [DEFAULT <default_value>] [ON UPDATE <update_value>]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [SPARSE] [SERIES TIMESTAMP]

<computed_column_definition>:
    computed_column_expression PERSISTED [data_type | AUTO]

<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]
  | DATETIME
  | DATETIME(6)
  | TIMESTAMP
  | TIMESTAMP(6)
  | DATE
  | TIME
  | CHAR[(<length>)]
      [CHARACTER SET <character_set_name>] [COLLATE <collation_name>]
  | VARCHAR(<length>)
      [CHARACTER SET <character_set_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_column_name>:
    <column_name> [(<length>)] [ASC | DESC]

<index_type>:
   | USING { BTREE | HASH }


<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 }
  | AUTOSTATS_CARDINALITY_MODE = {INCREMENTAL|PERIODIC|OFF}
  | AUTOSTATS_HISTOGRAM_MODE = {CREATE|UPDATE|OFF}
  | AUTOSTATS_SAMPLING = {ON|OFF}
  | COMPRESSION = SPARSE

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

If DEFAULT <default_value> is specified in <column_definition>, and no value is inserted in the column, then <default_value> will be placed in the column during an INSERT operation. If the column is of the type TIMESTAMP, TIMESTAMP(6), DATETIME , or DATETIME(6), then you can update <default_value> to one of the following values: CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(6), NOW(), or NOW(6). For more information, see Data Types.

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. If the column is of the type TIMESTAMP, TIMESTAMP(6), DATETIME , or DATETIME(6), then you can update <update_value> to one of the following values: CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(6), NOW(), or NOW(6).

ON UPDATE can be used with these TIMESTAMP/DATETIME[(6)] types only, and you can only use one of the time functions as the argument. For more information, see Data Types.

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, SingleStoreDB will automatically assign a value. It’s important to understand that AUTO_INCREMENT only guarantees that automatically-generated values are unique. In general, it does not guarantee that they:

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

If syntax such as CREATE TABLE table_1 SELECT * FROM table_2 is used to create table_1 where table_2 has an AUTO_INCREMENT column, it will be created as a non-auto-increment column in table_1.

See LAST_INSERT_ID for more information on AUTO_INCREMENT behavior.

If the AUTO_INCREMENT behavior described here does not satisfy your requirements you can create your own sequence generator using LAST_INSERT_ID. See the sequence generator stored procedure example.

These jumps are because each aggregator defines and manages its own range of values to start incrementing from to prevent collisions in a table. With each restart, a new batch of values is used. For sharded tables, the range of AUTO_INCREMENT values increases to the next 1,000,000 after each restart (e.g. 2,430,403 before restart -> 3,000,000 after). For reference tables, the batch size jumps to the next 1,000. And as with previous versions of SingleStoreDB, these values are also encoded with the aggregator ID, as described in the next section.

SELECT * FROM tb ORDER BY b;
****
+-------------------+------+------------+
| a                 | b    | c          |
+-------------------+------+------------+
|                 1 |    1 | from MA    |
|                 2 |    2 | from MA    |
|                 3 |    3 | from MA    |
|                 4 |    4 | from MA    |
|                 5 |    5 | from MA    |
| 13510798882111489 |    6 | from CA 96 |
| 13510798882111490 |    7 | from CA 96 |
| 13510798882111491 |    8 | from CA 96 |
| 13510798882111492 |    9 | from CA 96 |
| 13510798882111493 |   10 | from CA 96 |
| 14636698788954113 |   11 | from CA 20 |
| 14636698788954114 |   12 | from CA 20 |
| 14636698788954115 |   13 | from CA 20 |
| 14636698788954116 |   14 | from CA 20 |
| 14636698788954117 |   15 | from CA 20 |
|                 6 |   16 | from MA    |
| 15762598695796737 |   17 | from CA 17 |
| 13510798882111494 |   18 | from CA 96 |
|                 7 |   19 | from MA    |
| 14636698788954118 |   20 | from CA 20 |
+-------------------+------+------------+

CREATE REFERENCE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t values();
INSERT INTO t values(5);
INSERT INTO t values();
SELECT id FROM t ORDER BY id;
****
+----+
| id |
+----+
|  1 |
|  5 |
|  6 |
+----+

UPDATE t SET id=9 WHERE id=5;
INSERT INTO t values();
SELECT id FROM t ORDER BY id;
****
+----+
| id |
+----+
|  1 |
|  6 |
|  9 |
| 10 |
+----+

DELETE FROM t;
INSERT INTO t values();
SELECT id FROM t order by id;
****
+----+
| id |
+----+
| 11 |
+----+

CREATE REFERENCE TABLE t (id int AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 7;

INSERT INTO t VALUES (), ();

SELECT * FROM t;
+----+
| id |
+----+
|  7 |
|  8 |
+----+

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

CREATE REFERENCE TABLE pages(
page_id INT PRIMARY KEY AUTO_INCREMENT,
page_url VARCHAR(1000)
);

CREATE TEMPORARY or CREATE ROWSTORE TEMPORARY (if default_table_type is set to columnstore, you must use the latter syntax) creates a table that will be deleted when the client session terminates.

CREATE TEMPORARY TABLE IF NOT EXISTS temp1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));

CREATE ROWSTORE TEMPORARY TABLE IF NOT EXISTS temp1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));

CREATE GLOBAL TEMPORARY or CREATE ROWSTORE GLOBAL TEMPORARY (if default_table_type is set to columnstore, you must use the latter syntax), creates a table that exists beyond the duration of a client session. If a failover occurs, a global temporary table loses data and enters an errored state; the global temporary table needs to be dropped and recreated.

CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS temp1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));

CREATE ROWSTORE GLOBAL TEMPORARY TABLE IF NOT EXISTS temp1 (id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, SHARD KEY(id));

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

Here is the basic syntax. You can create the new table and set shard keys, sort keys, and or other indexes:

CREATE [ROWSTORE] [REFERENCE | TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] <table_name_2> 
       (column_name(s), [SHARD KEY(column_name)] | [SORT KEY(column_name)] | [KEY(column_name)] 
       AS SELECT [*] | [column_name(s)] FROM table_name_1;

Here is an example of a CREATE TABLE AS SELECT command with a shard key, sort key and an index:

CREATE TABLE ctas_table (a BIGINT, b BIGINT, SHARD KEY(a), SORT KEY(b), KEY(a)) AS SELECT * FROM orig_table;

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 table_1 (PRIMARY KEY (a, b)) AS SELECT * FROM table_2;
CREATE TABLE table_1 (SORT KEY (a, b)) AS SELECT * FROM table_2;
CREATE TABLE table_1 (a int, b int) AS SELECT c, d FROM table_2;
CREATE TABLE table_1 (b AS a+1 PERSISTED int) AS SELECT a FROM table_2;

In the case that the old table (table_2 in the above examples) has an AUTO_INCREMENT column, it will be created as a non-auto-increment column in the new table (table_1).

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

INSERT INTO events VALUES('WRITE', NOW());

CREATE TABLE times(id INT AUTO_INCREMENT KEY);

SELECT time FROM events;

SELECT * FROM times;
****
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2016-03-25 15:38:12 |
+----+---------------------+

SELECT * FROM courses ORDER BY course_code, section_number;
****
+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              20 |
| CS-101      |              2 |              16 |
| CS-101      |              3 |              22 |
| CS-101      |              4 |              25 |
| CS-101      |              5 |              22 |
| CS-150      |              1 |              10 |
| CS-150      |              2 |              16 |
| CS-150      |              3 |              11 |
| CS-150      |              4 |              17 |
| CS-150      |              5 |               9 |
| CS-201      |              1 |              14 |
| CS-201      |              2 |              17 |
| CS-301      |              1 |               7 |
| CS-301      |              2 |              10 |
+-------------+----------------+-----------------+

CREATE TABLE IF NOT EXISTS distinct_courses (PRIMARY KEY(course_code))
    AS SELECT DISTINCT(course_code) FROM courses;

SELECT * FROM distinct_courses ORDER by course_code;
****
+-------------+
| course_code |
+-------------+
| CS-101      |
| CS-150      |
| CS-201      |
| CS-301      |
+-------------+

SingleStoreDB supports sparse data compression for rowstore tables. Nullable structured columns can use sparse data compression. The data types of these columns include numbers, dates, datetimes, timestamps, times, and varchars.

Columns that use sparse data compression only store non-NULL data values. Example 4 discusses an excellent sparse data compression use case, which also includes the query to retrieve actual memory usage of rowstore tables that use sparse data compression.

Sparse compression has the following limitations:

Refer to the Data Types topic for details.

CREATE ROWSTORE TABLE transaction(
    id BIGINT NOT NULL,
    explanation VARCHAR(70),
    shares DECIMAL(18, 2),
    share_price DECIMAL(18, 2),
    total_amount as shares * share_price PERSISTED DECIMAL(18,2),
    transaction_date DATE,
    dividend_exdate DATE,
    misc_expenses DECIMAL(18, 2),
    country_abbreviation CHAR(6),
    correction_date DATE,  
    settlement_date DATE
    ) COMPRESSION = SPARSE;

CREATE ROWSTORE TABLE transaction(
    id BIGINT NOT NULL,
    explanation VARCHAR(70) SPARSE,
    shares DECIMAL(18, 2) SPARSE,
    share_price DECIMAL(18, 2),
    total_amount as shares * share_price PERSISTED DECIMAL(18,2),
    transaction_date DATE,
    dividend_exdate DATE SPARSE,
    misc_expenses DECIMAL(18, 2) SPARSE,
    country_abbreviation CHAR(6),
    correction_date DATE SPARSE,  
    settlement_date DATE SPARSE
    );

SELECT column_name, is_sparse FROM information_schema.columns
WHERE table_name = 'transaction';
****
+----------------------+-----------+
| column_name          | is_sparse |
+----------------------+-----------+
| id                   | NO        |
| explanation          | YES       |
| shares               | YES       |
| share_price          | NO        |
| total_amount         | NO        |
| transaction_date     | NO        |
| dividend_exdate      | YES       |
| misc_expenses        | YES       |
| country_abbreviation | NO        |
| correction_date      | YES       |
| settlement_date      | YES       |
+----------------------+-----------+

CREATE ROWSTORE TABLE t (
  c1 double,
  c2 double,
  …
  c300 double) COMPRESSION = SPARSE;

SELECT table_name, SUM(memory_use) memory_usage FROM information_schema.table_statistics WHERE table_name = 't' GROUP BY table_name;
****
+-------------+--------------+
| table_name  | memory_usage |
+-------------+--------------+
| t           |         1.23 |
+-------------+--------------+

SingleStoreDB 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 LONGTEXTData Types

If you query a column cthat is part of a multi-column FULLTEXT index, where the query uses a FULLTEXT MATCH on c, the index on c will be applied.

This differs from a multi-column non-FULLTEXT index, where behavior is as follows: if you query column c that is part of index i, where the query uses an equality filter on c, the index on c will only be applied if c is the leftmost column in i.

Any column that is part of a FULLTEXT index can be queried, even if it is not the leftmost. Searches across FULLTEXT columns are done using the SELECT ... MATCH AGAINST syntax. For more information, see MATCH.

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT (title,body));

The USING HASH clause creates a hash index in a table.

If a rowstore or columnstore table is being created, the following applies:

If a columnstore table is being created, the following applies:

CREATE TABLE articles (
    id INT UNSIGNED,
    month int UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SHARD KEY(title),
    SORT KEY (id),
    KEY (id) USING HASH,     
    UNIQUE KEY (title) USING HASH,
    KEY (month,year) USING HASH);

CREATE TABLE t(a INT, b INT, c INT, KEY(a,b));

CREATE TABLE t(a INT, b INT, SORT KEY(), KEY(a,b) USING HASH);

For a column defined as type CHAR of length len, store the column as a VARCHAR of length len if len greater than or equal to the value of the engine variable varchar_column_string_optimization_length. If the value of the variable is 0, the column is not stored as a VARCHAR.

Storing a CHAR of length len as a VARCHAR of length len will allow the column to realize the performance benefit of a VARCHAR, in many cases. For example, CHARs use 3 bytes of memory, and VARCHARs use 1 byte. VARCHAR can have better performance and use less memory during query execution because SingleStoreDB does not need to allocate and process full-length strings with 3 bytes per char, as it does when using CHAR.

CREATE TABLE t(a CHAR(4));