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

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，如需转载请自行联系原作者