MySQL DDL操作--------临时表存储实战

han8809

　　1. 背景
　　* 临时表是基于会话的(session)，只在当前连接可见
　　* 当这个连接(会话)关闭的时候，会自动drop。
　　* 两个不同的连接(会话)里使用相同的临时表名，并且相互不会冲突，或者使用已经存在的表，但不是临时表的表名。
　　* 当这个临时表表名已存在表的时候，存在的表被隐藏了，如果临时表被drop，存在的表就可见了
　　* 创建临时表用户必须有 create temporary table 权限。
　　* Mysql 5.7之后临时表数据存储于 ibtmp1 文件中.
　　2. MySQL 5.7临时表相关文件存储
　　* 查看 MySQL 版本
mysql> select version();　　
+-----------+
　　
| version() |
　　
+-----------+
　　
| 5.7.18    |
　　
+-----------+
　　
1 row in set (0.01 sec)
　　* 创建临时表 temp_1
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 插入数据
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 数据
mysql> SELECT * FROM temp_1;　　
+----+---------------------------------------------+
　　
| id | data                                        |
　　
+----+---------------------------------------------+
　　
|  1 | {"age": "25", "sex": "male", "name": "tom"} |
　　
+----+---------------------------------------------+
　　
1 row in set (0.00 sec)
　　* 查看 temp 变量　[ 临时文件存储目录 ]
mysql> show variables like 'tmpdir';　　
+---------------+-------+
　　
| Variable_name | Value |
　　
+---------------+-------+
　　
| tmpdir        | /tmp  |
　　
+---------------+-------+
　　
1 row in set (0.02 sec)
　　* 查看临时表结构定义文件 *.frm
　　'#'开头的代表临时表结构定义文件
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　变量 [ 数据存储目录 ]
mysql> show variables like 'datadir';　　
+---------------+-------------------+
　　
| Variable_name | Value             |
　　
+---------------+-------------------+
　　
| datadir       | /data/mysql_data/ |
　　
+---------------+-------------------+
　　
1 row in set (0.01 sec)
　　* 查看临时表表数据存储文件 ibtmp1　[ 5.7之后将临时表数据存储于ibtmp1文件中 ]
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 版本
mysql> show variables like 'version';　　
+---------------+--------+
　　
| Variable_name | Value  |
　　
+---------------+--------+
　　
| version       | 5.6.36 |
　　
+---------------+--------+
　　
1 row in set (0.00 sec)
　　* 创建临时表 temp_1
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 插入数据
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 数据
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 变量　[ 临时文件存储目录 ]
mysql> show variables like 'tmpdir';　　
+---------------+-------+
　　
| Variable_name | Value |
　　
+---------------+-------+
　　
| tmpdir        | /tmp  |
　　
+---------------+-------+
　　
1 row in set (0.00 sec)
　　* 查看临时表结构定义文件 *.frm和数据文件 *.ibd
　　[ MySQL 5.7之前临时表所有表结构定义文件和数据文件存储在 tmpdir中  ]
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. 总结
　　以需求驱动技术，技术本身没有优略之分，只有业务之分。