Create SyntaxΒΆ
MemSQL supports the following CREATE syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
The CREATE DATABASE ... DEFAULT CHARSET= syntax is accepted by MemSQL for compatibility with MySQL, but it has no effect. MemSQL gives all databases a default charset of utf8.
CREATE 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
| [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] ...
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
data_type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| TIMESTAMP
| DATETIME
| 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,...)
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| BUCKET_COUNT [=] value
| index_type
| COMMENT 'string'
table_options:
table_option [[,] table_option] ...
table_option:
AUTO_INCREMENT [=] value
| COMMENT [=] 'string'
Note
CREATE TABLE in MemSQL is slower than 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. See Indexes for more information. The only charset_name supported by MemSQL is ‘utf8’. See String Encoding and Collations for more information.
Some syntax is accepted by MemSQL but ignored. This includes:
- CREATE TABLE ... AUTO_INCREMENT=n: the value of n is currently ignored.
- KEY_BLOCK_SIZE [=] value: value is currently ignored.
The only MemSQL specific syntax that is not supported by MySQL is the BUCKET_COUNT hint. This hint only applies to a HASH index and specifies the number of hash buckets to create for the index. The value is rounded to the nearest power of 2 (so BUCKET_COUNT = 20000 will create a hash index with 16384 buckets). A best practice is to set BUCKET_COUNT to around 1/2 the maximum number of rows you expect to be in the table. Setting the value too low will reduce the seek performance of the HASH index. Setting the value too high will waste memory on empty hash buckets.
MemSQL supports the following CREATE INDEX syntax:
CREATE [UNIQUE] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type]
index_col_name:
col_name [ASC | DESC]
index_type:
USING {BTREE | HASH}
See Indexes for more information on indexes.
Note
Due to the dense interleaving of table and index row structures, CREATE INDEX will internally create a new version of the table (with the new index) while keeping an old version of the table in memory until the entire CREATE INDEX operation is completed. Make sure your MemSQL instance has enough free memory to store the new version of the table before running CREATE INDEX.