Mariadb 简明教程

MariaDB - Temporary Tables

一些操作可能会从临时表中受益，因为速度或可处置数据。无论您是否通过命令提示符、PHP 脚本或客户端程序使用临时表，临时表的生命都将终止于会话终止。它也不会以典型方式显示在系统中。SHOW TABLES 命令不会显示包含临时表的列表。

Some operations can benefit from temporary tables due to speed or disposable data. The life of a temporary table ends at the termination of a session whether you employ them from the command prompt, with a PHP script, or through a client program. It also does not appear in the system in a typical fashion. The SHOW TABLES command will not reveal a list containing temporary tables.

Create a Temporary Table

CREATE TABLE 语句中的 TEMPORARY 关键字生成一个临时表。查看下面给出的示例：

The TEMPORARY keyword within a CREATE TABLE statement spawns a temporary table. Review an 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
);

在创建临时表时，您可以克隆现有表，这意味着使用类似 LIKE 子句的所有常规特征。用于生成临时表的 CREATE TABLE 语句不会因 TEMPORARY 关键字而提交事务。

In creating a temporary table, you can clone existing tables, meaning all their general characteristics, with the LIKE clause. The CREATE TABLE statement used to spawn the temporary table will not commit transactions as a result of the TEMPORARY keyword.

尽管临时表独立于非临时表并在会话结束时删除，但它们可能存在某些冲突：

Though temporary tables stand apart from non-temporary and drop at the end of a session, they may have certain conflicts −

  1. They sometimes conflict with ghost temporary tables from expired sessions.

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

Note - 允许临时表与现有的非临时表具有相同的名称，因为 MariaDB 将其视为不同的引用。

Note − Temporary tables are permitted to have the same name as an existing non-temporary table because MariaDB views it as a difference reference.

Administration

MariaDB 要求向用户授予创建临时表的权限。利用 GRANT 语句向非管理员用户授予此权限。

MariaDB requires granting privileges to users for creating temporary tables. Utilize a GRANT statement to give this privilege to non-admin users.

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

Drop a Temporary Table

尽管临时表在会话结束时基本上会被删除，但您可以选择删除它们。删除临时表需要使用 TEMPORARY 关键字，并且最佳实践建议在任何非临时表之前删除临时表。

Though temporary tables are essentially removed at the end of sessions, you have the option to delete them. Dropping a temporary table requires the use of the TEMPORARY keyword, and best practices suggest dropping temporary tables before any non-temporary.

mysql> DROP TABLE order;