CREATE TABLE

Sintassi

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...) [table_options    ]... [partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)] [table_options   ]... [partition_options]
    select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   { LIKE old_table_name | (LIKE old_table_name) }
select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

Spiegazione

L'istruzione CREATE TABLE serve a creare una tabella con un dato nome. Occorre disporre del permesso CREATE sulla tabella o sul database per poter eseguire questa istruzione.

Nella sua forma più basilare, l'istruzione CREATE TABLE comprende un nome di tabella seguito da una lista di colonne, indici e vincoli. Per default, la tabella viene creata nel database selezionato. Si può specificare un database con la sintassi nome_db.nome_tab. Se si pone tra virgolette il nome della tabella, occorre virgolettare separatamente il nome del database e quello della tabella: `nome_db`.`nome_tab`.

Se una tabella con il nome specificato esiste già, viene generato un errore 1050. Con IF NOT EXISTS si sopprime questo errore e si riceve invece una nota. Con SHOW WARNINGS è possibile visualizzare le note.

Con la parola chiave TEMPORARY è possibile creare una tabella temporanea che sarà disponibile solo nella sessione corrente. Le tabelle temporanee vengono eliminate al termine della sessione. I nomi delle tabelle temporanee sono specifici all'interno della sessione. Possono entrare in conflitto con i nomi delle altre tabelle temporanee o nascondere i nomi delle tabelle non temporanee. Occorre disporre del permesso CREATE TEMPORARY TABLES sul database per poter creare una tabella temporanea.

E' possibile usare la clausola LIKE invece di una definizione completa per creare una tabella che avrà la stessa definizione di un'altra tabella, comprese le colonne, gli indici e le opzioni di tabella.

L'istruzione CREATE TABLE effettua automaticamente il commit della transazione corrente, a meno che non si usi la parola chiave TEMPORARY.

Definizione delle tabelle

create_definition:
    { col_name column_definition | index_definition | CHECK (expr) }
column_definition:
    data_type
      [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression) {VIRTUAL | PERSISTENT}
      [UNIQUE [KEY]] [COMMENT 'string']

Ogni definizione crea una colonna nella tabella oppure specifica un indice o un vincolo su una o più colonne. Si ceda Indexes sotto per ulteriori informazioni su come creare gli indici.

Le colonne si creano specificando un nome di colonna e un tipo di dato, opzionalmente seguito dalle opzioni di colonna. Si veda Data Types per la lista dei tipi ammessi in MariaDB.

Con le opzioni NULL e NOT NULL si specifica se i valori della colonna possono essere NULL o meno. Per default, i valori possono essere NULL.

E' possibile specificare un valore predefinito usando la clausola DEFAULT. Il valore predefinito viene usato se si inserisce una riga senza specificare il valore di una data colonna, o se si specifica DEFAULT come valore per quella colonna. Solitamente non è possibile utilizzare un'espressione o una funzione da valutare al momento dell'inserimento. Occorre indicare un valore predefinito costante. L'unica eccezione è la possibilità di specificare CURRENT_TIMESTAMP come valore predefinito per le colonne TIMESTAMP, in modo che utilizzino il timestamp relativo al momento dell'inserimento.

Use AUTO_INCREMENT to create a column whose value can can be set automatically from a simple counter. You can only use AUTO_INCREMENT on a column with an integer type. The column must be a key, and there can only be one AUTO_INCREMENT column in a table. If you insert a row without specifying a value for that column (or if you specify 0, NULL, or DEFAULT as the value), the actual value will be taken from the counter, with each insertion incrementing the counter by one. You can still insert a value explicitly. If you insert a value that is greater than the current counter value, the counter is set based on the new value. An AUTO_INCREMENT column is implicitly NOT NULL. Use LAST_INSERT_ID to get the AUTO_INCREMENT value most recently used by an INSERT statement.

Use UNIQUE KEY (or just UNIQUE) to specify that all values in the column must be distinct from each other. Unless the column is NOT NULL, there may be multiple rows with NULL in the column. Use PRIMARY KEY (or just KEY) to make a column a primary key. A primary key is a special type of a unique key. There can be at most one primary key per table, and it is implicitly NOT NULL.

Specifying a column as a primary or unique key creates an index on that column. Specifying a key in the column definition is equivalent to specifying a single-column key separately. See Indexes below.

You can provide a comment for each column using the COMMENT clause. Use the SHOW FULL COLUMNS statement to see column comments.

To define virtual columns (new in MariaDB 5.2), you can specify the virtual column's type: VIRTUAL or PERSISTENT. The default is VIRTUAL, which means that the column is calculated on the fly when a command names it; PERSISTENT means that the value is phisically stored in the table. The AS keyword makes clear that the column is virtual, but you can also use the GENERATED ALWAYS keyword. The expression must be deterministic. For a complete description about virtual columns and their limitations, see virtual columns.

Indexes

index_definition:
    {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
index_col_name:
    col_name [(length)] [ASC | DESC]
index_type:
    USING {BTREE | HASH | RTREE}
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

INDEX and KEY are synonyms.

Index names are optional, if not specified an automatic name will be assigned. Index name are needed to drop indexes and appear in error messages when a contraint is violated.

Each storage engine supports some or all index types. Different types are optimized for different kind of operations:

• BTREE is the default type, and normally is the best choice. It is supported by all storage engines. It can be used to compare a column's value with a value using the =, >, >=, <, <=, BETWEEN, and LIKE operators. BTREE can also be used to find NULL values. Searches against an index prefix are possible.
• HASH is only supported by the MEMORY storage engine. HASH indexes can only be used for =, <=, and >= comparisons. It can not be used for the ORDER BY clause. Searches against an index prefix are not possible.
• RTREE is the default for SPATIAL indexes, but if the storage engine does not support it BTREE can be used.

Index columns names are listed between parenthesis. After each column, a prefix length can be specified. If no lencth is specified, the whole column will be indexed. ASC and DESC can be specified for compatibility with are DBMS's, but have no meaning in MariaDB.

The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index.

For UNIQUE keys, PRIMARY KEYs and FOREIGN KEYs, you can specify a name for the contraint, using the CONSTRAINT keyword. That name will be used in error messages.

For the KEY_BLOCK_SIZE index option, see the table options below. This option can be specified at table level or at index level.

The WITH PARSER index option only applies to FULLTEXT indexes and contains the fulltext parser name. The fulltext parser must be an installed plugin.

Use the SPATIAL or FULLTEXT keywords to create geometric or fulltext indexes.

For FOREIGN KEYs, a reference definition must be provided. First, you have to specify the name of the target table and a column or a column list which must be indexed and whose values must match to the foreign key's values. The MATCH clause is accepted to improve the compatibility with other DBMS's, but has not meaning in MariaDB. The ON DELETE and ON UPDATE clauses specify what must be done when a DELETE (or a REPLACE) statements deletes a rows, and when an UPDATE statement modifies a rows, respectively, and a matching row exists in the other table. The following options are allowed:

• RESTRICT: The delete/update operation is not performed.
• CASCADE: The delete/update operation is performed in both tables.
• SET NULL: The fields matching to the foreign key are set to NULL, in the row which is in the other table.
• NO ACTION: The operation is performed normally, as if there was not foreign key.

The standard SET DEFAULT option is currently implemented only for the PBXT storage engine, which is disabled by default and no more mantained. It sets the other table's row fields to their DEFAULT value.

Table Options

For each individual table you create (or alter), you can set some table options. The general syntax for setting options is:

<OPTION_NAME> = <option_value>, [<OPTION_NAME> = <option_value> ...]

The equal sign is optional.

Some options are supported by the server and can be used for all tables, no matter what storage engine they use; other options can be specified for all storage engines, but have a meaning only for some engines. Also, engines can extend CREATE TABLE with new options.

table_option:    
    [STORAGE] ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PAGE_CHECKSUM [=] {0 | 1}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT|PAGE}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

[STORAGE] ENGINE specifies a storage engine for the table. If this option is not used, the default storage engine is used instead. That is, the storage_engine session option value if it is set, or the value specified for the --default-storage-engine mysqld startup options, or InnoDB. If the specified storage engine is not installed and active, the default value will be used, unless the NO_ENGINE_SUBSTITUTION SQL MODE is set (this is only true for CREATE TABLE, not for ALTER TABLE). For a list of storage engines that are present in your server, issue a SHOW ENGINES.

AUTO_INCREMENT specifies the initial value for the AUTO_INCREMENT primary key. This works for MyISAM, Aria, InnoDB/XtraDB, MEMORY, and ARCHIVE tables. You can change this option with ALTER TABLE, but in that case the new value must be higher than the highest value which is present in the AUTO_INCREMENT column. If the storage engine does not support this option, you can insert (and then delete) a row having the wanted value - 1 in the AUTO_INCREMENT column.

AVG_ROW_LENGTH is the average rows size, and is only useful for tables using the FIXED format. MyISAM uses MAX_ROWS and AVG_ROW_LENGTH to decide the maximum size of a table (default: 256TB, or the maximum file size allowed by the system).

[DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to set a default character set for the table. This is the character set used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT is specified, database's default character set will be used.

CHECKSUM can be set to 1 to maintain a live checksum for all table's rows. This makes write operations slower, but CHECKSUM TABLE will be very fast. This option is only supported for MyISAM and Aria tables.

[DEFAULT] COLLATE is used to set a default collation for the table. This is the collation used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT is specified, database's default option will be used.

COMMENT is a comment for the table. Maximum length is 60 characters.

CONNECTION is used to specify a server name or a connection string for a Federated or FederatedX table.

DATA DIRECTORY and INDEX DIRECTORY is only supported for MyISAM and Aria, and specifies paths for data files and index files, respectively. If these options are omitted, the database's directory will be used to store data files and index files. Note that this table options do not work for partitioned tables (use the partition options instead), or if the server has been invoked with the --skip-symbolic-links startup option. To avoid the overwriting of old files with the same name that could be present in the directories, you can use the --keep_files_on_create option (an error will be issued if files already exist).

DELAY_KEY_WRITE is supported by MyISAM and Aria, and can be set to 1 to speed up write operations. In that case, when data are modified, the indexes are not updated until the table is closed. Writing the changes to the index file altogether can be much faster. However, note that this option is applied only if the delay_key_write server variable is set to 'ON'. If it is 'OFF' the delayed index writes are always disabled, and if it is 'ALL' the delayed index writes are always used, disregarding the value of DELAY_KEY_WRITE.

INSERT_METHOD is only used with MERGE tables. This option determines in which underlying table the new rows should be inserted. If you set it to 'NO' (which is the default) no new rows can be added to the table (but you will still be able to perform INSERTs directly against the underlying tables). FIRST means that the rows are inserted into the first table, and LAST means that thet are inserted into the last table.

KEY_BLOCK_SIZE is used to determine the size of key blocks, in bytes. However, this value is just a hint, and the storage engine could modify or ignore it. If KEY_BLOCK_SIZE is set to 0, the storage engine's default value will be used. Note that if you use this option for InnoDB/XtraDB tables using the ROW_FORMAT, the innodb_strict_mode option should be set; otherwise, the table might have different behavior than you intended.

MIN_ROWS and MAX_ROWS let the storage engine know how many rows you are planning to store as a minimum and as a maximum. These values will not be used as real limits, but they help the storage engine to optimize the table. MIN_ROWS is only used by MEMORY storage engine to decide the minimum memory that is always allocated. MAX_ROWS is used to decide the minimum size for indexes.

PACK_KEYS can be used to determine whether the indexes will be compressed. Set it to 1 to compress all keys. With a value of 0, compression will not be used. With the DEFAULT value, only long strings will be compressed. Uncompressed keys are faster.

PASSWORD non è usato.

RAID_TYPE è un'opzione obsoleta, perché il supporto a raid è stato disabilitato fin da MySQL 5.0.

ROW_FORMAT specifies the format for the data file. Possible values are engine-dependent. For MyISAM formats are: FIXED, DYNAMIC#; COMPRESSED can only be set by the myisampack command line tool. For Aria formats are: PAGE, FIXED, DYNAMIC#. For InnoDB/XtraDB, formats are: COMPACT, REDUNDANT, COMPRESSED, DYNAMIC. Other storage engines do not support this option.

STATS_AUTO_RECALC è disponibile solo a partire da MariaDB 10.0. Determina se le statistiche persistenti devono essere ricalcolate automaticamente (si veda STATS_PERSISTENT, sotto) per le tabelle InnoDB. Se è impostato a 1, le statistiche saranno ricalcolate quando più del 10% dei dati viene modificato. Se è impostato a 0, le statistiche saranno ricalcolate solo quando viene eseguita un'istruzione ANALYZE TABLE. Se è impostato a DEFAULT, od omesso, verrà utilizzato il valore della variabile di sistema innodb_stats_auto_recalc.

STATS_PERSISTENT è disponibile solo a partire da MariaDB 10.0. Determina se le statistiche di InnoDB create dall'istruzione ANALYZE TABLE debbano essere conservate sul disco o meno. Può essere impostato a 1 (su disco), 0 (non su disco, quindi il comportamento the pre-MariaDB 10), o DEFAULT (che è come omettere l'opzione), nel qual caso verrà utilizzato il valore della variabile di sistema innodb_stats_persistent. Le statistiche persistenti registrate su disco sopravvivono ai riavvii del server e permettono una maggiore stabilità dei piani delle query.

UNION deve essere specificato quando si crea una tabella MERGE. Questa opzione contiene i nomi, separati da virgole, delle tabelle MyISAM che compongono la nuova tabella. La lista è racchiusa tra parentesi. Esempio: UNION = (t1,t2)

Partitions

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr) }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]
partition_definition:
    PARTITION partition_name
        [VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

If the PARTITION BY clause is used, the table will be partitioned. A partition method must be explicitly indicated for partitions and subpartitions. Partition methods are:

• [LINEAR] HASH creates a hash key which will be used to read and write rows. The partition function can be any valid SQL expression which returns an INTEGER number. Thus, it is possible to use the HASH method on an integer column, or on functions which accept integer columns as an argument. However, VALUES LESS THAN and VALUES IN clauses can not be used with HASH. An example:

CREATE TABLE t1 (a INT, b CHAR(5), c DATETIME)
    PARTITION BY HASH ( YEAR(c) );

[LINEAR] HASH can be used for subpartitions, too.

• [LINEAR] KEY is similar to HASH, but the index has an even distribution of data. Also, the expression can only be a column or a list of columns. VALUES LESS THAN and VALUES IN clauses can not be used with KEY.
• RANGE partitions the rows using on a range of values, using the VALUES LESS THAN operator. VALUES IN is not allowed with RANGE. The partition function can be any valid SQL expression which returns a single value.
• LIST assignes partitions based on a table's column with a restricted set of possible values. It is similar to RANGE, but VALUES IN must be used for at least 1 columns, and VALUES LESS THAN is disallowed.

Only HASH and KEY can be used for subpartitions, and they can be [LINEAR].

It is possible to define up to 1024 partions and subpartitions.

The number of defined partitions can be optionally specified as PARTITION count. This can be done to avoid specifying all partitions individually. But you can also declare each individual partition and, additionally, specify a PARTITIONS count clause; in the case, the number of PARTITIONs must equal count.

CREATE ... SELECT

You can create a table containing data from other tables using the CREATE ... SELECT statement. A columns will be created in the table for each field returned by the SELECT query.

You can also define some columns normally and add other columns from a SELECT. You can also create columns in the normal way and assign them some values using the query, this is done to force a certain type or other field characteristics. The columns that are not named in the query will be placed before the others. For example:

CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
    SELECT 5 AS b, c, d FROM another_table;

Remember that the query just returns data. If you want to use the same indexes, or the same columns attributes ([NOT] NULL, DEFAULT, AUTO_INCREMENT) in the new table, you need to specify them manually. Types size are not automatically preserved if not data returned by the SELECT requires the full size, and VARCHAR could be converted into CHAR.

Aliases (AS) are taken into account, and they should always be used when you SELECT an expression (function, arithmetical operation, etc).

If an error occurs during the query, the table will not be created at all.

If the new table has a primary key or UNIQUE indexes, you can use IGNORE or REPLACE keywords to handle duplicate key errors during the query. IGNORE means that the newer values must not be inserted an identical value exists in the index. REPLACE means that older values must be overwritten.

If the IF NOT EXISTS clause is used, and the table you are trying to CREATE already exists, the table will not be created, but MariaDB will try to insert the rows from the SELECT statement. If the rows in the table are more than the rows returned by the query, values will be inserted in the fields that are in the last positions, disregarding their names. Note that is the strict SQL_MODE is on, and the columns that are not names in the query do not have a DEFAULT value, an error will raise and no rows will be copied.

Concurrent INSERTs are not used during the execution of a CREATE ... SELECT.