You are viewing an older version of this section. View current production version.
CREATE TABLE
Create a table.
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]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
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'
Notes
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.
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.
AUTO-INCREMENT
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.
Reference tables must be created with an explicit primary key and distributed tables must be created with either a primary or shard key (See Distributed SQL). If your schema does not naturally have a primary or shard key, you can add an AUTO_INCREMENT
column to the table. MemSQL does not require that the AUTO_INCREMENT
column is the leading column of the primary key, so you can add it to an existing index or create a new one. Here is an example:
memsql> CREATE TABLE events (
event_type VARCHAR(256),
time TIMESTAMP,
KEY (event_type)
)
This statement can be converted to the following:
memsql> CREATE TABLE events (
event_type VARCHAR(256),
time TIMESTAMP,
id INT AUTO_INCREMENT,
PRIMARY KEY (event_type, id)
)
MySQL Compatibility
MemSQL’s syntax differs from MySQL mainly in the datatypes and storage it supports, and some specific index hints.
CREATE TABLE ... AUTO_INCREMENT=n
: the value of n is currently ignored.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 ...
Note
If select from computed column
, you should 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)