MySQL临时表使用注意事项
3,856 total views, 2 views today
创建临时表
|
1 |
mysql> create temporary table t(id int); |
创建的临时表只对当前会话可见，用于存放临时数据，关闭会话临时表自动删除。
show tables看不到表，但是可以通过show create table t\G 来查看表结构。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show tables; Empty set (0.00 sec) mysql> mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TEMPORARY TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
临时表的默认存储引擎，由 default_tmp_storage_engine 参数决定，默认是InnoDB。
|
1 2 3 4 5 6 7 |
mysql> show variables like 'default_tmp_storage_engine'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | default_tmp_storage_engine | InnoDB | +----------------------------+--------+ 1 row in set, 1 warning (0.00 sec) |
临时表表结构存放在tmpdir目录下；数据存放则存放在位于datadir的临时表空间中，由参数 innodb_temp_data_file_path决定其相关配置。
|
1 2 3 4 5 6 7 |
mysql> show variables like 'innodb_temp_data_file_path'; +----------------------------+-----------------------+ | Variable_name | Value | +----------------------------+-----------------------+ | innodb_temp_data_file_path | ibtmp1:12M:autoextend | +----------------------------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
临时表的名字和普通表的名字可以相同，默认查询的是临时表。
|
1 2 |
mysql> use hou; Database changed |
创建普通表并插入数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> create table t1(id varchar(100)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values('normal table'); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +--------------+ | id | +--------------+ | normal table | +--------------+ 1 row in set (0.00 sec) |
创建临时表
|
1 2 |
mysql> create temporary table t1(id varchar(100)); Query OK, 0 rows affected (0.00 sec) |
此时查询t1表，查询到没有数据，说明查询的是临时表。
|
1 2 |
mysql> select * from t1; Empty set (0.00 sec) |
如何解决临时表和普通表名字相同带来的混乱
1、创建临时表时，在表名前加前缀，如tmp_t1
2、创建临时表时，语句中加入判断表名是否存在
|
1 2 3 4 5 6 7 8 9 10 |
mysql> create temporary table if not exists t1(id varchar(100)); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +-------+------+---------------------------+ | Level | Code | Message | +-------+------+---------------------------+ | Note | 1050 | Table 't1' already exists | +-------+------+---------------------------+ 1 row in set (0.00 sec) |
临时表和数据库的关系很松散，或者说是两者就没有关系，两者并没有互相约束。
1、删除数据库，并不会连带删除临时。比如A会话有查询数据库DB1的权限，并创建了临时表t1。此时，其他会话删除数据库DB1，会话A仍然能继续操作和查询临时表t1。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
session A: use db1; create temporary table t1(id int); session B: drop database db1; session A: show create table db1.t1; mysql> show create table t\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TEMPORARY TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
2、临时表可以创建在不存在的库中。上面，已经将临时表t1所在库DB1删除。但是，仍可以通过制定库名的方式创建临时表t2.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> create temporary table db1.t2 (id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into db1.t2 values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from db1.t2; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) |