You are viewing an older version of this section. View current production version.
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
- For more information about the data types listed above, and for an explanation of
UNSIGNED
, refer to the Data Types topic. - The first field of type
TIMESTAMP
orTIMESTAMP(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. BUCKET_COUNT
is specific to theHASH
index type. It controls the bucket count of the hash table.- The
UNENFORCED
index option can be used on aUNIQUE
constraint to specify that the unique constraint is unenforced. See Unenforced Unique Constraints. RESOLUTION
is specific to index on geospatial columns. See Geospatial Guide) for more information.COLUMNSTORE_SEGMENT_ROWS
,COLUMNSTORE_FLUSH_BYTES
controls configuration variables specific to columnstore tables. See Advanced Columnstore Configuration Options) for more information.- The only
charset_name
supported by MemSQL is ‘utf8’. AUTOSTATS_ENABLED
controls if automatic statistics should be collected on this table. See Automatic Statistics) for more information.- This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands), with the exception of
CREATE TEMPORARY TABLE
, which can be run on any aggregator node. 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. - Keyless sharding distributes data across partitions uniformly at random but with the limitation that it does not allow single partition queries or local joins since rows are not assigned to specific partitions. Keyless sharding is the default for tables that do not have primary key or explicit shard key. You can explicitly declare a table as keyless sharded by specifying a shard key with an empty list of columns in the
SHARD KEY()
constraint in the table definition.
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.
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.
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. It’s important to understand that AUTO_INCREMENT
only guarantees that automatically-generated values are unique. In general, it does not 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).
See LAST_INSERT_ID for more information on AUTO_INCREMENT
behavior.
Restarting an aggregator, such as during upgrades or host machine maintenance, will introduce a large gap between any AUTO_INCREMENT
values inserted before the restart and any values inserted after. In the case of reference tables, this same behavior might also occur when a child aggregator is promoted to master aggregator. Depending on how often you restart your aggregators, you could see many jumps in values from a specific aggregator.
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 MemSQL, these values are also encoded with the aggregator ID, as described in the next section.
AUTO_INCREMENT in Sharded Tables
On a sharded (distributed) table, AUTO_INCREMENT
can only be used on a BIGINT
column (as they usually use the entire 64 bits). Each aggregator computes and tracks its own AUTO_INCREMENT
values and uses those values when new rows are added to a table. 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 unique value. The values on each aggregator are usually, but not always, sequential; therefore, inserts on an individual aggregator generate values which are unique and usually sequential. And because each aggregator manages its own AUTO_INCREMENT
values, the automatically-generated values from inserts across multiple aggregators are only unique, never sequential.
Here is an example to illustrate how AUTO_INCREMENT
values are generated across aggregators in a cluster as new rows are inserted into table tb
.:
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 |
+-------------------+------+------------+
As shown in the example above, automatically-generated AUTO_INCREMENT
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 in sharded tables.
AUTO_INCREMENT in Reference Tables
The AUTO_INCREMENT
value for a reference table is tracked by the master aggregator. It is guaranteed that the next AUTO_INCREMENT
value will always be greater than any value previously seen in this column. These generated values are usually sequential, but not always. Contrarily to the behavior for sharded tables, explicitly setting a value in an INSERT
or UPDATE
statement will not create a collision with future automatically generated values.
The next example shows some queries using AUTO_INCREMENT
fields on reference tables.
memsql> CREATE REFERENCE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY);
memsql> INSERT INTO t values();
memsql> INSERT INTO t values(5);
memsql> INSERT INTO t values();
memsql> SELECT id FROM t order by id;
+----+
| id |
+----+
| 1 |
| 5 |
| 6 |
+----+
memsql> UPDATE t SET id=9 WHERE id=5;
memsql> INSERT INTO t values();
memsql> SELECT id FROM t order by id;
+----+
| id |
+----+
| 1 |
| 6 |
| 9 |
| 10 |
+----+
memsql> DELETE FROM t;
memsql> INSERT INTO t values();
memsql> SELECT id FROM t order by id;
+----+
| id |
+----+
| 11 |
+----+
Setting AUTO_INCREMENT Starting Values
It is possible to override the starting AUTO_INCREMENT
value for reference tables by setting the AUTO_INCREMENT
option on a CREATE TABLE
statement.
The following example shows how to set the AUTO_INCREMENT
start value during table creation:
CREATE REFERENCE TABLE t (id int AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 7;
INSERT INTO t VALUES (), ();
SELECT * FROM t;
+----+
| id |
+----+
| 7 |
| 8 |
+----+
This syntax has no effect on sharded tables. It will not return an error, for compatibility with external tools, but it will explicitly present a warning and no operation will be done.
AUTO_INCREMENT During Replication
When replicating data between clusters, the secondary cluster has all the replicated AUTO_INCREMENT
values from the primary cluster. When you failover to a secondary cluster, MemSQL synchronizes the secondary cluster by looking for the maximum value in the range of AUTO_INCREMENT
values on every aggregator.
Examples
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.
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 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 specified 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));