SQL：临时表

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.24-0ubuntu0.18.10.1 |
+-------------------------+

临时表的性质

1、打开两个终端，同一个用户登陆

#终端A
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 333257
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 47     -- 当前 ID 
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show processlist   //当前终端执行
*************************** 3. row ***************************
     Id: 48
   User: root
   Host: localhost
     db: test
Command: Sleep
   Time: 25
  State: 
   Info: NULL

#终端B
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 333265
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 47
   User: root
   Host: localhost
     db: test
Command: Sleep
   Time: 8
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 48    -- 当前 ID 
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show processlist  

#终端A --- 不可以创建表但是可以查询表
mysql> show tables;

mysql> create temporary table  temp_a_56(a int);

mysql> show tables;

mysql> insert into temp_a_56 values(123);

mysql> show tables;

mysql> select * from temp_a_56;
+------+
| a    |
+------+
|  123 |
+------+

#终端B：不可以创建表也不可以查询表
mysql> show tables;

mysql> show tables;    -- 从其他终端登录的用户(session)无法看到临时表temp_a_56

mysql> select * from temp_a_56;
ERROR 1146 (42S02): Table 'test.temp_a_56' doesn't exist

• 临时表是SESSION级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的

2、临时表与普通表同名问题

mysql>  create table test_1 (a int);  -- 创建一张普通的表叫做 test_1

mysql>  insert into test_1 values(23),(24);

mysql> select * from test_1;
+------+
| a    |
+------+
|   23 |   -- 可以看到插入的数据
|   24 |
+------+

mysql> create temporary table test_1 (a int);   -- 创建一种和test_1 同名的临时表

mysql> insert into test_1 values(1000);  -- 插入一个 不一样的值

mysql> select * from test_1;
+------+
| a    |
+------+
| 1000 |   -- 只能搜索到 临时表中的数据
+------+

mysql> create temporary table if not exists table_name  (a int);  -- 使用if not exists进行判断

• 当临时表和普通表同名时，当前用户只能看到同名的临时表
• 创建表时带上if not exists进行表的存在性检查；同时建议在临时表的表名前面加上统一的prefix

临时表的作用

• 临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。
• 不要和SQL优化器在排序过程中内部帮你创建的临时表相混淆。

查看临时表的信息

mysql>  show variables like "default%tmp%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |   --临时表默认存储引擎就是 InnoDB
+----------------------------+--------+

mysql> show variables like "innodb_temp%";
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| innodb_temp_data_file_path  | ibtmp1:12M:autoextend |  --临时表结构放在tmpdir，而数据表数据放在datadir 
| innodb_temp_tablespaces_dir | ./#innodb_temp/       |
+-----------------------------+-----------------------+

mysql> show create table test_1\G  -- 表结构
*************************** 1. row ***************************
       Table: test_1
Create Table: CREATE TEMPORARY TABLE `test_1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>  desc test_1;  -- 表的描述,描述二维表信息
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    | NULL  |
+-------+---------+------+-----+---------+-------+

mysql>  show table status like "test_1"\G    -- 看表结构的元数据信息
*************************** 1. row ***************************
           Name: test_1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-01-13 23:01:50
    Update_time: 2019-01-13 23:02:02
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 

官方文档：https://dev.mysql.com/doc/refman/8.0/en/create-table.html