Detailed explanation of the functions and example codes of MySQL temporary tables

MySQL temporary tables are very useful when we need to save some temporary data. Temporary tables are only visible in the current connection. When the connection is closed, Mysql will automatically delete the table and release all space.

Temporary tables were added in MySQL version 3.23. If your MySQL version is lower than version 3.23, you cannot use MySQL temporary tables. However, nowadays it is generally rare to use such a low version of the MySQL database service.

MySQL temporary table is only visible in the current connection. If you use a PHP script to create a MySQL temporary table, the temporary table will be automatically destroyed when the PHP script is executed.

If you use another MySQL client program to connect to the MySQL database server to create a temporary table, the temporary table will only be destroyed when the client program is closed. Of course, you can also destroy it manually.

Example

The following shows a simple example of using MySQL temporary table. The following SQL code can be applied to the mysql_query() function of PHP script.

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

When you use the SHOW TABLES command to display the data table list, you will not be able to see the SalesSummary table.

If you exit the current MySQL session and then use the SELECT command to read the originally created temporary table data, you will find that the table does not exist in the database because the temporary table has been destroyed when you exit. .

Delete MySQL temporary table

By default, the temporary table will be automatically destroyed when you disconnect from the database. Of course, you can also use the DROP TABLE command in the current MySQL session to manually delete the temporary table.

The following is an example of manually deleting a temporary table:

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

[Related recommendations]

1. Special recommendation： "php Programmer Toolbox" V0.1 version download

2. Free mysql online video tutorial

3. Those things about database design

The above is the detailed content of Detailed explanation of the functions and example codes of MySQL temporary tables. For more information, please follow other related articles on the PHP Chinese website!