Description\n-----------\n\nUse the CREATE TABLE statement to create a table with the given name.\n\nIn its most basic form, the CREATE TABLE statement provides a table name followed by a list of columns, indexes, and constraints. By default, the table is created in the default database. Specify a database with db_name.tbl_name. If you quote the table name, you must quote the database name and table name separately as ` db_name.tbl_name . This is particularly useful for CREATE TABLE ... SELECT, because it allows creating a table in a database that contains data from other databases. See Identifier Qualifiers.\n\nIf a table with the same name exists, error 1050 results. Use IF NOT EXISTS to suppress this error and issue a note instead. Use SHOW WARNINGS to see notes.\n\nThe CREATE TABLE statement automatically commits the current transaction, except when using the TEMPORARY keyword.\n\nFor valid identifiers to use as table names, see Identifier Names.\n\nIf the default_storage_engine is set to ColumnStore, it needs to be set on all UMs. Otherwise, when the tables using the default engine are replicated across UMs, they will use the wrong engine. You should therefore not use this option as a session variable with ColumnStore.\n\nMicrosecond precision can be between 0-6. If no precision is specified, it is assumed to be 0, for backward compatibility reasons.\n\nPrivileges\n\nExecuting the CREATE TABLE statement requires the CREATE privilege for the table or the database.\n\nCREATE OR REPLACE\n\nIf the OR REPLACE clause is used and the table already exists, then instead of returning an error, the server will drop the existing table and replace it with the newly defined table.\n\nThis syntax was originally added to make replication more robust if it has to rollback and repeat statements such as CREATE ... SELECT on slaves.\n\n`sql\nCREATE OR REPLACE TABLE table_name (a INT);\n`\n\nis basically the same as:\n\n`sql\nDROP TABLE IF EXISTS TABLE_NAME;\nCREATE TABLE TABLE_NAME (a INT);\n`\n\nwith the following exceptions:\n\n If table_name was locked with LOCK TABLES, it will continue to be locked after the statement.\n Temporary tables are only dropped if the TEMPORARY keyword was used. (With DROP TABLE, temporary tables are preferred to be dropped before normal tables).\n\nThings to be Aware of With CREATE OR REPLACE\n\n The table is dropped first (if it existed), and after that, the CREATE is done. Because of this, if the CREATE fails, then the table will not exist anymore after the statement. If the table was used with LOCK TABLES, it will be unlocked.\n One can't use OR REPLACE together with IF EXISTS.\n Slaves will, by default, use CREATE OR REPLACE when replicating CREATE statements that don't use IF EXISTS. This can be changed by setting the variable slave-ddl-exec-mode to STRICT.\n\nCREATE TABLE IF NOT EXISTS\n\nIf the IF NOT EXISTS clause is used, then the table will only be created if a table with the same name does not already exist. If the table already exists, then a warning will be triggered by default.\n\nCREATE TEMPORARY TABLE\n\nUse the TEMPORARY keyword to create a temporary table that is only available to the current session. Temporary tables are dropped when the session ends. Temporary table names are specific to the session. They will not conflict with other temporary tables from other sessions, even if they share the same name. They will shadow names of non-temporary tables or views, if they are identical. A temporary table can have the same name as a non-temporary table, which is located in the same database. In that case, their name will reference the temporary table when used in SQL statements. You must have the CREATE TEMPORARY TABLES privilege on the database to create temporary tables. If no storage engine is specified, the default_tmp_storage_engine setting will determine the engine.\n\nROCKSDB temporary tables cannot be created by setting the default_tmp_storage_engine system variable or using CREATE TEMPORARY TABLE LIKE. If you try, an error is returned. Explicitly creating a temporary table with ENGINE=ROCKSDB has never been permitted.\n\nROCKSDB temporary tables cannot be created by setting the default_tmp_storage_engine system variable, or using CREATE TEMPORARY TABLE LIKE. They can be specified, but fail silently, and a MyISAM table is created instead. Explicitly creating a temporary table with ENGINE=ROCKSDB has never been permitted.\n\nReplicating temporary tables\n\nBy default, temporary tables are only created on the slave if the master is using the STATEMENT binary log format.\n\nThe new deterministic rules for logging of temporary tables are:\n\n The STATEMENT binlog format is used. The user can change this behavior by setting create_tmp_table_binlog_formats to MIXED, STATEMENT in which case the create is logged in statement format also in MIXED mode (as before).\n Changes to temporary tables are only binlogged if and only if the CREATE was logged. The logging happens under STATEMENT or MIXED. If binlog_format=ROW, temporary table changes are not binlogged. A temporary table that is changed under ROW is marked as 'not up to date in binlog' and no future row changes are logged. Any usage of this temporary table will force row logging of other tables in any future statements using the temporary table to be row logged.\n DROP TEMPORARY is binlogged only if the CREATE was binlogged.\n\nIn some contexts, temporary tables on the master and slave can become inconsistent.\\\nOne example is if a temporary table is updated with the value of a non deterministic function like UUID(), in which the change is never sent to the slave.\n\nIn some other contexts, while using MIXED mode, all changes will be logged in ROW mode while the user has any active temporary tables, even if the temporary tables are not used in the query. This depends on in which format some previous independent commands were logged.\n\nThere are many other pitfalls with logging temporary table to the slave.\n\nCREATE TABLE ... LIKE\n\nUse the LIKE clause instead of a full table definition to create an empty table with the same definition as another table, including columns, indexes, and table options. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.\n\nLIKE does not preserve the TEMPORARY status of the original table. To make the new table TEMPORARY as well, use CREATE TEMPORARY TABLE ... LIKE.\n\nLIKE does not work with views, only base tables. Attempting to use it on a view will result in an error:\n\n`sql\nCREATE VIEW v (mycol) AS SELECT 'abc';\n\nCREATE TABLE v2 LIKE v;\nERROR 1347 (HY000): 'test.v' is not of type 'BASE TABLE'\n`\n\nThe same version of the table storage format as found in the original table is used for the new table.\n\nCREATE TABLE ... LIKE performs the same checks as CREATE TABLE. So a statement may fail if a change in the SQL_MODE renders it invalid. For example:\n\n`sql\nCREATE OR REPLACE TABLE x (d DATE DEFAULT '0000-00-00');\n\nSET SQL_MODE='NO_ZERO_DATE';\n\nCREATE OR REPLACE TABLE y LIKE x;\nERROR 1067 (42000): Invalid default value for 'd'\n`\n\nCREATE TABLE ... SELECT\n\nYou can create a table containing data from other tables using the CREATE ... SELECT statement. Columns will be created in the table for each field returned by the SELECT query.\n\nYou 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:\n\n`sql\nCREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM\n    SELECT 5 AS b, c, d FROM another_table;\n`\n\nRemember that the query just returns data. If you want to use the same indexes or the same column attributes ([NOT] NULL, DEFAULT, AUTO_INCREMENT, CHECK constraints) in the new table, you need to specify them manually. Types and sizes are not automatically preserved if no data is returned by the SELECT that requires the full size, and VARCHAR could be converted into CHAR. The CAST() function can be used to force the new table to use certain types.\n\nAliases (AS) are taken into account, and they should always be used when you SELECT an expression (function, arithmetical operation, etc.).\n\nIf an error occurs during the query, the table will not be created at all.\n\nIf the new table has a primary key or UNIQUE indexes, you can use the IGNORE or REPLACE keywords to handle duplicate key errors during the query. IGNORE means that the newer values must not be inserted if an identical value exists in the index. REPLACE means that older values must be overwritten.\n\nIf the columns in the new table are more than the rows returned by the query, the columns populated by the query will be placed after the other columns. Note that if the strict SQL_MODE is on, and the columns that are not named in the query do not have a DEFAULT value, an error will be raised and no rows will be copied.\n\nConcurrent inserts are not used during the execution of a CREATE ... SELECT.\n\nIf the table already exists, an error similar to the following will be returned:\n\n`\nERROR 1050 (42S01): Table 't' already exists\n`\n\nIf the IF NOT EXISTS clause is used, and the table exists, a note will be produced instead of an error.\n\nTo insert rows from a query into an existing table, INSERT ... SELECT can be used.\n\nColumn Definitions\n\n<pre class="language-bnf"><code class="lang-bnf">create_definition:\n  { col_name column_definition | <a data-footnote-ref href="#user-content-fn-5">index_definition</a> | <a data-footnote-ref href="#user-content-fn-4">period_definition</a> | CHECK (expr) }\n\ncolumn_definition:\n  <a data-footnote-ref href="#user-content-fn-6">data_type</a>\n    [NOT NULL | NULL] [DEFAULT default_value | (expression)]\n    [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]\n    [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]\n    [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]\n    [COMMENT 'string'] [REF_SYSTEM_ID = value]\n    [<a data-footnote-ref href="#user-content-fn-5">reference_definition</a>]\n  | <a data-footnote-ref href="#user-content-fn-6">data_type</a> [GENERATED ALWAYS] \n  AS [ ROW {START|END} [NOT NULL ENABLE] [[PRIMARY] KEY]\n        | (expression) [VIRTUAL | PERSISTENT | STORED] ]\n      [INVISIBLE] [UNIQUE [KEY]] [COMMENT 'string']\n\nconstraint_definition:\n   CONSTRAINT [constraint_name] CHECK (expression)\n</code></pre>\n\nNote:\n\nMariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. For example:\n\n`sql\nCREATE TABLE b(for_key INT REFERENCES a(not_key));\n`\n\nMariaDB will attempt to apply the constraint. See Foreign Keys examples.\n\nMariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. For example:\n\n`sql\nCREATE TABLE b(for_key INT REFERENCES a(not_key));\n`\n\nEach definition either creates a column in the table or specifies an index or constraint on one or more columns. See Indexes below for details on creating indexes.\n\nCreate a column by specifying a column name and a data type, optionally followed by column options. See Data Types for a full list of data types allowed in MariaDB.\n\nNULL and NOT NULL\n\nUse the NULL or NOT NULL options to specify that values in the column may or may not be NULL, respectively. By default, values may be NULL. See also NULL Values in MariaDB.\n\nDEFAULT Column Option\n\nSpecify a default value using the DEFAULT clause. If you don't specify DEFAULT then the following rules apply:\n\n If the column is not defined with NOT NULL, AUTO_INCREMENT or TIMESTAMP, an explicit DEFAULT NULL will be added.\\\n  Note that in MySQL, you may get an explicit DEFAULT for primary key parts, if not specified with NOT NULL.\n\nThe default value will be used if you INSERT a row without specifying a value for that column, or if you specify DEFAULT for that column.\n\nCURRENT_TIMESTAMP may also be used as the default value for a DATETIME\n\nYou can use most functions in DEFAULT. Expressions should have parentheses around them. If you use a non deterministic function in DEFAULT then all inserts to the table will be replicated in row mode. You can even refer to earlier columns in the DEFAULT expression (excluding AUTO_INCREMENT columns):\n\n`sql\nCREATE TABLE t1 (a INT DEFAULT (1+1), b INT DEFAULT (a+1));\nCREATE TABLE t2 (a BIGINT PRIMARY KEY DEFAULT UUID_SHORT());\n`\n\nThe DEFAULT clause cannot contain any stored functions or subqueries, and a column used in the clause must already have been defined earlier in the statement.\n\nIt is possible to assign BLOB or TEXT columns a DEFAULT value.\n\nYou can also use DEFAULT (NEXT VALUE FOR sequence).\n\nAUTO_INCREMENT Column Option\n\nUse AUTO_INCREMENT to create a column whose value 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\\\nset 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.\n\nZEROFILL Column Option\n\nIf the ZEROFILL column option is specified for a column using a numeric data type, then the column will be set to UNSIGNED and the spaces used by default to pad the field are replaced with zeros. ZEROFILL is ignored in expressions or as part of a UNION, INTERSECT, or EXCEPT. ZEROFILL is a non-standard MariaDB and MySQL extension.\n\nPRIMARY KEY Column Option\n\nUse PRIMARY 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.\n\nSpecifying a column as a unique key creates a unique index on that column. See the Index Definitions section below for more information.\n\nUNIQUE KEY Column Option\n\nUse 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.\n\n`\n`\n\nWhen any inserts or updates occur in the table, reading the binlog shows the hidden column (@3). it causes confusion for the user; we can document these behaviours.\n\n`\nINSERT INTO securedb.t_long_keys\nSET\n@1=1 / INT meta=0 nullable=0 is_null=0 /\n@2='a' / VARSTRING(4073) meta=4073 nullable=1 is_null=0 /\n@3=580 / LONGINT meta=0 nullable=1 is_null=0 /\n`\n\n`sql\nCREATE TABLE t_long_keys (   a INT PRIMARY KEY,   b  VARCHAR(4073),   UNIQUE KEY uk_b (b) ) ENGINE=InnoDB;\nQuery OK, 0 r\n\n[Truncated — full content at mariadb.com/docs]