MariaDB Temporary Table

Some operations may benefit from temporary tables due to speed or one-time data. T he life of the temporary table ends at the end of the session, whether from command prompts, PHP scripts, or through client programs. I t also does not appear in the system in a typical way. T he SHOW TABLES command does not display a list of temporary tables.

Create a temporary table

The TEMPORARY keyword in the CREATE TABLE statement generates a temporary table. S ee the example given below -

mysql>CREATE TEMPORARY TABLE order (
   item_name VARCHAR(50) NOT NULL
      , price DECIMAL(7,2) NOT NULL DEFAULT 0.00
      , quantity INT UNSIGNED NOT NULL DEFAULT 0
);

When you create a temporary table, you can clone an existing table using theLIKE clause, which means all of its general characteristics. T he CREATE TABLE statement used to generate the temporary table does not commit the transaction as a result of the TEMPORARY keyword.

Although temporary tables are detached from non-temporary tables at the end of a session, they may have some conflicts -

• They sometimes conflict with the host temporary table in an expired session.

• They sometimes conflict with shadow names of non-temporary tables.

Note - Temporary tables are allowed to have the same name as existing non-temporary tables because MariaDB treats them as differential references.

Administrative

MariaDB needs to grant the user permission to create temporary tables. U se the GRANT statement to grant this permission to a non-administrator user.

GRANT CREATE TEMPORARY TABLES ON orders TO 'machine122'@'localhost';

Delete the temporary table

Although temporary tables are essentially deleted at the end of the session, you can choose to delete them. Removing temporary tables requires the TEMPORARY keyword, and best practices recommend removing temporary tables before temporary tables.

mysql> DROP TABLE order;