ve 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.

ROCKSDB temporary tables cannot be created by setting the
default_tmp_storage_engine system variable, or using CREATE TEMPORARY TABLE
LIKE. Before MariaDB 10.7, they could be specified, but would silently fail,
and a MyISAM table would be created instead. From MariaDB 10.7 an error is
returned. Explicitly creating a temporary table with ENGINE=ROCKSDB has never
been permitted.

CREATE TABLE ... LIKE
---------------------

Use 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.

LIKE does not preserve the TEMPORARY status of the original table. To make the
new table TEMPORARY as well, use CREATE TEMPORARY TABLE ... LIKE.

LIKE does not work with views, only base tables. Attempting to use it on a
view will result in an error:

CREATE VIEW v (mycol) AS SELECT 'abc';

CREATE TABLE v2 LIKE v;
ERROR 1347 (HY000): 'test.v' is not of type 'BASE TABLE'

The same version of the table storage format as found in the original table is
used for the new table.

CREATE 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:

CREATE OR REPLACE TABLE x (d DATE DEFAULT '0000-00-00');

SET SQL_MODE='NO_ZERO_DATE';

CREATE OR REPLACE TABLE y LIKE x;
ERROR 1067 (42000): Invalid default value for 'd'

CREATE TABLE ... SELECT
-----------------------

You 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.

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 and sizes are not
automatically preserved if no data returned by the SELECT requires the full
size, and VARCHAR could be converted into CHAR. The CAST() function can be
used to forcee the new table to use certain types.

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 the 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 columns in the new table are more than the rows returned by the query,
the columns populated by the query will be placed after other columns. Note
that if 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.

If the table already exists, an error similar to the following will be
returned:

ERROR 1050 (42S01): Table 't' already exists

If the IF NOT EXISTS clause is used and the table exists, aˆ9Ex