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)
Contents
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
.
MariaDB starting with 5.3
Microsecond precision è un valore tra 0 e 6. Se non viene specificato è 0, per ragioni di compatibilità all'indietro.
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_definitionindex_col_name: col_name [(length)] [ASC | DESC]
index_type: USING {BTREE | HASH | RTREE}
index_option: KEY_BLOCK_SIZE [=] value
|
index_type|
WITH PARSER parser_namereference_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
, andLIKE
operators.BTREE
can also be used to findNULL
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 theORDER BY
clause. Searches against an index prefix are not possible.RTREE
is the default forSPATIAL
indexes, but if the storage engine does not support itBTREE
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 NULL
s. An error will raise if you try to insert duplicate values in a UNIQUE
index.
For UNIQUE
keys, PRIMARY KEY
s and FOREIGN KEY
s, 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 KEY
s, 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 toNULL
, 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 INSERT
s 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 anINTEGER
number. Thus, it is possible to use theHASH
method on an integer column, or on functions which accept integer columns as an argument. However,VALUES LESS THAN
andVALUES IN
clauses can not be used withHASH
. 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 toHASH
, 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
andVALUES IN
clauses can not be used withKEY
.RANGE
partitions the rows using on a range of values, using theVALUES LESS THAN
operator.VALUES IN
is not allowed withRANGE
. 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 toRANGE
, butVALUES IN
must be used for at least 1 columns, andVALUES 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 PARTITION
s 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 INSERT
s are not used during the execution of a CREATE ... SELECT
.