CREATE TABLE syntax¶
CREATE TABLE [IF NOT EXISTS] name [( create_definition,...)] [table_options]
CREATE TABLE [IF NOT EXISTS] name LIKE old_name
create_definition:
col_name data_type [data_type_options]
table_options:
table_option [ table_option] ...
table_option:
option_name = 'option_value'
CREATE TABLE creates a new index. Available for RT mode only.
Columns data types:
data_type | Config equivalent | Options | Alias |
---|---|---|---|
text | rt_field | indexed, stored | |
integer | rt_attr_uint | int uint | |
float | rt_attr_float | ||
multi | rt_attr_multi | ||
multi64 | rt_attr_multi64 | ||
bool | rt_attr_bool | ||
json | rt_attr_json | ||
string | rt_attr_string | ||
timestamp | rt_attr_timestamp | ||
bit(n) | rt_attr_uint |
The “text” type supports additional options. By default both “indexed” and “stored” are enabled.
Table options are the same as defined at Index configuration options and are supported by RT, PQ and distributed index types:
- access_plain_attrs
- access_blob_attrs
- access_doclists
- access_hitlists
- agent_connect_timeout
- agent_query_timeout
- agent_retry_count
- agent
- attr_update_reserve
- bigram_freq_words
- bigram_index
- blend_chars
- blend_mode
- charset_table
- docstore_block_size
- docstore_compression
- docstore_compression_level
- exceptions
- expand_keywords
- global_idf
- ha_strategy
- hitless_words
- html_index_attrs
- html_remove_elements
- html_strip
- ignore_chars
- index_exact_words
- index_field_lengths
- index_sp
- index_token_filter
- index_zones
- local
- min_infix_len
- min_prefix_len
- min_stemming_len
- min_word_len
- mirror_retry_count
- morphology
- morphology_skip_fields
- ngram_chars
- ngram_len
- overshort_step
- phrase_boundary
- phrase_boundary_step
- read_buffer_docs
- read_buffer_hits
- regexp_filter
- rt_mem_limit
- stopwords
- stopword_step
- stopwords_unstemmed
- type (plain is not supported)
- wordforms
The options must be separated by whitespace.
Default type is rt.
Optionally IF EXISTS can be used to not return error in case the index already exists.
mysql> CREATE TABLE test1 ( title text, tag integer );
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE test2 (t1 text indexed stored, t2 text indexed, t3 text indexed, gid int) rt_mem_limit='128k' min_infix_len='3';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE distr type='distributed' local='test1' local='test2';
Query OK, 0 rows affected (0.00 sec)
Another way of creating a table is by cloning the settings of an existing table:
mysql> CREATE TABLE test2 LIKE test1;
Query OK, 0 rows affected (0.00 sec)