SQL：临时表

ゝ一世哀愁。 2022-04-22 16:20 249阅读 0赞

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 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