CREATE TABLE

CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name LIKE source_table [ like_option [...] ]

CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | table_indexclause
        | LIKE source_table [ like_option [...] ] }
        [, ... ])
    [ AUTO_INCREMENT [ = ] value ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ]
    [ COMMENT {=| } 'text' ];
    [ create_option ]

其中create_option为：

        [ WITH ( {storage_parameter = value} [, ... ] ) ]
        [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
        [ COMPRESS | NOCOMPRESS ]
        [ TABLESPACE tablespace_name ]
        [ COMPRESSION [=] compression_arg ]
        [ ENGINE [=] engine_name ]
        [ COLLATE [=] collation_name ]
        [ [DEFAULT] { CHARSET | CHARACTER SET } [=] charset_name ]
        [ ROW_FORMAT [=] row_format_name ]
        [ AUTOEXTEND_SIZE [=] value ]
        [ AVG_ROW_LENGTH [=] value ]
        [ CHECKSUM [=] value ]
        [ CONNECTION [=] 'connect_string' ]
        [ {DATA | INDEX} DIRECTORY [=] 'absolute path to directory' ]
        [ DELAY_KEY_WRITE [=] value ]
        [ ENCRYPTION [=] 'encryption_string' ]
        [ ENGINE_ATTRIBUTE [=] 'string' ]
        [ INSERT_METHOD [=] { NO | FIRST | LAST } ]
        [ KEY_BLOCK_SIZE [=] value ]
        [ MAX_ROWS [=] value ]
        [ MIN_ROWS [=] value ]
        [ PACK_KEYS [=] value ]
        [ PASSWORD [=] 'password' ]
        [ START TRANSACTION ]
        [ SECONDARY_ENGINE_ATTRIBUTE [=] 'string' ]
        [ STATS_AUTO_RECALC [=] value ]
        [ STATS_PERSISTENT [=] value ]
        [ STATS_SAMPLE_PAGES [=] value ]
        [ UNION [=] (tbl_name[,tbl_name]...) ]
        [ TABLESPACE tablespace_name STORAGE DISK ]
        [ [TABLESPACE tablespace_name] STORAGE MEMORY ]

    除了WITH选项外允许输入多次同一种create_option，以最后一次的输入为准。

--创建表上索引
MogDB=# CREATE TABLE tpcds.warehouse_t24
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)                  ,
    key (W_WAREHOUSE_SK)                                    ,
    index idx_ID using btree (W_WAREHOUSE_ID)
);

--创建表上组合索引、表达式索引、函数索引
MogDB=# CREATE TABLE tpcds.warehouse_t25
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)                  ,
    key using btree (W_WAREHOUSE_SK, W_WAREHOUSE_ID desc)   ,
    index idx_SQ_FT using btree ((abs(W_WAREHOUSE_SQ_FT)))  ,
    key idx_SK using btree ((abs(W_WAREHOUSE_SK)+1))
);

--创建带INVISIBLE普通索引的表
MogDB=# CREATE TABLE tpcds.warehouse_t26
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)                  ,
    index idx_ID using btree (W_WAREHOUSE_ID) INVISIBLE
);

--包含index_option字段
MogDB=# create table test_option(a int, index idx_op using btree(a) comment 'idx comment');
--创建表格时对列指定字符集。
MogDB=# CREATE TABLE t_column_charset(c text CHARSET test_charset);
WARNING:  character set "test_charset" for type text is not supported yet. default value set
CREATE TABLE

--创建表格时对表格指定字符序。
MogDB=# CREATE TABLE t_table_collate(c text) COLLATE test_collation;
WARNING:  COLLATE for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表格时对表格指定字符集。
MogDB=# CREATE TABLE t_table_charset(c text) CHARSET test_charset;
WARNING:  CHARSET for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表格时对表格指定行记录格式。
MogDB=# CREATE TABLE t_row_format(c text) ROW_FORMAT test_row_format;
WARNING:  ROW_FORMAT for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定在表空间变满时扩展表空间大小。
MogDB=# CREATE TABLE t_autoextend_size(c text) AUTOEXTEND_SIZE 4M;
WARNING:  AUTOEXTEND_SIZE for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定表的平均行长度。
MogDB=# CREATE TABLE t_avg_row_length(c text) AVG_ROW_LENGTH 10;
WARNING:  AVG_ROW_LENGTH for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定是否维护所有行的实时校验和。
MogDB=# CREATE TABLE t_checksum(c text) CHECKSUM 0;
WARNING:  CHECKSUM for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定联合表的连接字符串。
MogDB=# CREATE TABLE t_connection(c text) CONNECTION 'connect_string';
WARNING:  CONNECTION for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定表数据数据和索引的存储目录。
MogDB=# CREATE TABLE t_data_directory(c text) DATA DIRECTORY 'data_directory';
WARNING:  DIRECTORY for TABLE is not supported for current version. skipped
CREATE TABLE
MogDB=# CREATE TABLE t_index_directory(c text) INDEX DIRECTORY 'index_directory';
WARNING:  DIRECTORY for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定是否延迟表的索引更新直到表关闭。
MogDB=# CREATE TABLE t_delay_key_write(c text) DELAY_KEY_WRITE 1;
WARNING:  DELAY_KEY_WRITE for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定表启用或禁用页面级数据加密。
MogDB=# CREATE TABLE t_encryption(c text) ENCRYPTION 'Y';
WARNING:  ENCRYPTION for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定主存储引擎的表属性。
MogDB=# CREATE TABLE t_engine_attribute(c text) ENGINE_ATTRIBUTE 'engine_attribute';
WARNING:  ENGINE_ATTRIBUTE for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定应将行插入到的表。
MogDB=# CREATE TABLE t_insert_method(c text) INSERT_METHOD NO;
WARNING:  INSERT_METHOD for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定索引键块的字节大小。
MogDB=# CREATE TABLE t_key_block_size(c text) KEY_BLOCK_SIZE 10;
WARNING:  KEY_BLOCK_SIZE for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定计划在表中存储的最大行数。
MogDB=# CREATE TABLE t_max_rows(c text) MAX_ROWS 20;
WARNING:  MAX_ROWS for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定计划在表中存储的最小行数。
MogDB=# CREATE TABLE t_min_rows(c text) MIN_ROWS 5;
WARNING:  MIN_ROWS for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定控制压缩索引的方式。
MogDB=# CREATE TABLE t_pack_keys(c text) PACK_KEYS DEFAULT;
WARNING:  PACK_KEYS for TABLE is not supported for current version. skipped
CREATE TABLE

MogDB=# CREATE TABLE t_password(c text) PASSWORD 'password';
WARNING:  PASSWORD for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定开启事务模式。
MogDB=# CREATE TABLE t_start_transaction(c text) START TRANSACTION;
WARNING:  START TRANSACTION for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定辅助存储引擎的表属性。
MogDB=# CREATE TABLE t_secondary_engine_attribute(c text) SECONDARY_ENGINE_ATTRIBUTE 'secondary_engine_attribute';
WARNING:  SECONDARY_ENGINE_ATTRIBUTE for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定是否自动重新计算表的持久统计信息。
MogDB=# CREATE TABLE t_stats_auto_recalc(c text) STATS_AUTO_RECALC DEFAULT;
WARNING:  STATS_AUTO_RECALC for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定是否为表启用持久统计信息。
MogDB=# CREATE TABLE t_stats_persistent(c text) STATS_PERSISTENT DEFAULT;
WARNING:  STATS_PERSISTENT for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定估计索引列的基数和其他统计信息时要采样的索引页数。
MogDB=# CREATE TABLE t_stats_sample_pages(c text) STATS_SAMPLE_PAGES 1;
WARNING:  STATS_SAMPLE_PAGES for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时访问一组相同的表作为一个表。
MogDB=# CREATE TABLE t_union(c text) UNION(a, b);
WARNING:  UNION for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定表存储在磁盘。
MogDB=# CREATE TABLESPACE test ADD DATAFILE 'data.ibd';
WARNING:  Suffix ".ibd" of datafile path detected. The actual path will be renamed as "data_ibd"
CREATE TABLESPACE
MogDB=# CREATE TABLE t_tablespace_storage_disk(c text) TABLESPACE test STORAGE DISK;
WARNING:  TABLESPACE_OPTION for TABLE is not supported for current version. skipped
CREATE TABLE

--创建表时对表指定表存储在内存。
MogDB=# CREATE TABLESPACE test ADD DATAFILE 'data.ibd';
WARNING:  Suffix ".ibd" of datafile path detected. The actual path will be renamed as "data_ibd"
CREATE TABLESPACE
MogDB=# CREATE TABLE t_tablespace_storage_memory(c text) TABLESPACE test STORAGE MEMORY;
WARNING:  TABLESPACE_OPTION for TABLE is not supported for current version. skipped
CREATE TABLE

--创建兼容MySQL全文索引语法的表。前提是兼容模式为B的数据库。
MogDB=# CREATE TABLE test (
MogDB(# id int unsigned auto_increment not null primary key,
MogDB(# title varchar,
MogDB(# boby text,
MogDB(# name name,
MogDB(# FULLTEXT (title, boby) WITH PARSER ngram
MogDB(# );
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
MogDB=# drop table if exists articles;
NOTICE:  table "articles" does not exist, skipping
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT INDEX ngram_idx(title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "ngram_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# drop table if exists articles;
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT INDEX (title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "articles_to_tsvector_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# drop table if exists articles;
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT KEY keyngram_idx(title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "keyngram_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# drop table if exists articles;
DROP TABLE
MogDB=# CREATE TABLE articles (
MogDB(# ID int,
MogDB(# title VARCHAR(100),
MogDB(# FULLTEXT KEY (title)WITH PARSER ngram
MogDB(# );
CREATE TABLE
MogDB=# \d articles
       Table "fulltext_test.articles"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 ID     | integer                |
 title  | character varying(100) |
Indexes:
    "articles_to_tsvector_idx" gin (to_tsvector('ngram'::regconfig, title::text)) TABLESPACE pg_default

MogDB=# create table table_ddl_0154(col1 int,col2 varchar(64), FULLTEXT idx_ddl_0154(col2));
CREATE TABLE