13.1.20.2 CREATE TEMPORARY TABLE Statement CREATE TABLE Statement You can use the  TEMPORARY  keyword when creating a table. A  TEMPORARY  table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non- TEMPORARY  table of the same name. (The existing table is hidden until the temporary table is dropped.) InnoDB  does not support compressed temporary tables. When  innodb_strict_mode  is enabled (the default),  CREATE TEMPORARY TABLE  returns an error if  ROW_FORMAT=COMPRESSED  or KEY_BLOCK_SIZE  is specified. If  innodb_strict_mode  is disabled, warnings are issued and the temporary table is created using a non-compressed row format. The  innodb_file_per-table  option does not affect the creation of  InnoDB  temporary tables. CREATE TABLE  causes an implicit commit, except when used with the  TEMPORARY  keyword. See Section 13.3.3, “Statements That Cause an Implicit Commit” . TEMPORARY  tables have a very loose relationship with databases (schemas). Dropping a database does not automatically drop any  TEMPORARY  tables created within that database. To create a temporary table, you must have the  CREATE TEMPORARY TABLES  privilege. After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as  DROP TABLE ,  INSERT ,  UPDATE , or  SELECT . One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the  CREATE TEMPORARY TABLES  privilege but is able to execute a definer-context stored procedure that executes with the privileges of a user who does have  CREATE TEMPORARY TABLES  and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it. You cannot use  CREATE TEMPORARY TABLE ... LIKE  to create an empty table based on the definition of a table that resides in the  mysql  tablespace,  InnoDB  system tablespace ( innodb_system ), or a general tablespace. The tablespace definition for such a table includes a  TABLESPACE  attribute that defines the tablespace where the table resides, and the aforementioned tablespaces do not support temporary tables. To create a temporary table based on the definition of such a table, use this syntax instead: Support for  TABLESPACE = innodb_file_per_table  and  TABLESPACE = innodb_temporary  clauses with  CREATE TEMPORARY TABLE  is deprecated as of MySQL 8.0.13; expect it be removed in a future version of MySQL. Use  CREATE TABLE ... LIKE  to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table: LIKE  works only for base tables, not for views. The copy is created using the same version of the table storage format as the original table. The  SELECT privilege is required on the original table. CREATE TABLE  new_tbl  LIKE  orig_tbl CREATE TEMPORARY TABLE  new_tbl  SELECT * FROM  orig_tbl  LIMIT 0; 13.1.20.3 CREATE TABLE ... LIKE Statement Note 2569