1. 背景
* 临时表是基于会话的(session)，只在当前连接可见
* 当这个连接(会话)关闭的时候，会自动drop。
* 两个不同的连接(会话)里使用相同的临时表名，并且相互不会冲突，或者使用已经存在的表，但不是临时表的表名。
* 当这个临时表表名已存在表的时候，存在的表被隐藏了，如果临时表被drop，存在的表就可见了
* 创建临时表用户必须有 create temporary table 权限。
* Mysql 5.7之后临时表数据存储于 ibtmp1 文件中.
2. MySQL 5.7临时表相关文件存储
* 查看 MySQL 版本
|
1
2
3
4
5
6
7
|
mysql>
select
version();
+-----------+
| version() |
+-----------+
| 5.7.18 |
+-----------+
1 row
in
set
(0.01 sec)
|
* 创建临时表 temp_1
|
1
2
3
4
5
|
mysql> CREATE TEMPORARY TABLE temp_1(
->
id
BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> data json
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
|
* 对临时表 temp_1 插入数据
|
1
2
3
|
mysql> INSERT INTO temp_1 SELECT NULL, JSON_OBJECT(
'name'
,
'tom'
,
'sex'
,
'male'
,
'age'
,
'25'
);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
|
* 查看临时表 temp_1 数据
|
1
2
3
4
5
6
7
|
mysql> SELECT * FROM temp_1;
+----+---------------------------------------------+
|
id
| data |
+----+---------------------------------------------+
| 1 | {
"age"
:
"25"
,
"sex"
:
"male"
,
"name"
:
"tom"
} |
+----+---------------------------------------------+
1 row
in
set
(0.00 sec)
|
* 查看 temp 变量 [ 临时文件存储目录 ]
|
1
2
3
4
5
6
7
|
mysql> show variables like
'tmpdir'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir |
/tmp
|
+---------------+-------+
1 row
in
set
(0.02 sec)
|
* 查看临时表结构定义文件 *.frm
'#'开头的代表临时表结构定义文件
|
1
2
3
4
5
|
mysql> system
ls
-l
/tmp
total 18
srwxrwxrwx 1 mysql mysql 0 Jun 27 20:09 mysql.sock
-rw------- 1 mysql mysql 5 Jun 27 20:09 mysql.sock.lock
-rw-r----- 1 mysql mysql 8586 Jun 27 22:41
#sql666_9_0.frm
|
* 查看 datadir 变量 [ 数据存储目录 ]
|
1
2
3
4
5
6
7
|
mysql> show variables like
'datadir'
;
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir |
/data/mysql_data/
|
+---------------+-------------------+
1 row
in
set
(0.01 sec)
|
* 查看临时表表数据存储文件 ibtmp1 [ 5.7之后将临时表数据存储于ibtmp1文件中 ]
|
1
2
|
mysql> system
ls
-l
/data/mysql_data/ibtmp1
-rw-r----- 1 mysql mysql 12582912 Jun 27 22:43
/data/mysql_data/ibtmp1
|
3. MySQL 5.6临时表相关文件存储
* 查看 MySQL 版本
|
1
2
3
4
5
6
7
|
mysql> show variables like
'version'
;
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.6.36 |
+---------------+--------+
1 row
in
set
(0.00 sec)
|
* 创建临时表 temp_1
|
1
2
3
4
5
6
7
|
mysql> CREATE TEMPORARY TABLE temp_1(
->
id
BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> name VARCHAR(32) NOT NULL,
-> sex ENUM(
'male'
,
'female'
),
-> age INT NOT NULL
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.06 sec)
|
* 对临时表 temp_1 插入数据
|
1
2
3
|
mysql> INSERT INTO temp_1 SELECT NULL,
'tom'
,
'male'
, 22;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
|
* 查看临时表 temp_1 数据
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> INSERT INTO temp_1 SELECT NULL,
'tom'
,
'male'
, 22;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM temp_1;
+----+------+------+-----+
|
id
| name | sex | age |
+----+------+------+-----+
| 1 | tom | male | 22 |
+----+------+------+-----+
1 row
in
set
(0.00 sec)
|
* 查看 temp 变量 [ 临时文件存储目录 ]
|
1
2
3
4
5
6
7
|
mysql> show variables like
'tmpdir'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir |
/tmp
|
+---------------+-------+
1 row
in
set
(0.00 sec)
|
* 查看临时表结构定义文件 *.frm和数据文件 *.ibd
[ MySQL 5.7之前临时表所有表结构定义文件和数据文件存储在 tmpdir中 ]
|
1
2
3
4
5
|
mysql> system
ls
-l
/tmp
total 116
srwxrwxrwx 1 mysql mysql 0 Jun 27 22:53 mysql.sock
-rw-rw---- 1 mysql mysql 8656 Jun 27 22:57
#sqla34_4_0.frm
-rw-rw---- 1 mysql mysql 98304 Jun 27 22:58
#sqla34_4_0.ibd
|
4. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943417，如需转载请自行联系原作者
