Examples of using temporary tables in MySQL

I've been a little busy these two days, and the official account has stopped updating for a few days. As a result, some readers urged me to update again. Well, it shows that there are still people paying attention to it. It benefits both myself and others, which is good.

What I want to share today is temporary tables in MySQL. I haven’t done much research on temporary tables before. I just know that MySQL uses temporary tables to assist in group by and other operations in certain scenarios. Let’s get to know temporary tables today.

1. First of all. Temporary tables are at the session level. Tables created by the current session cannot be seen in other sessions.

Session 1:

mysql> create temporary table test3 (id_tmp int)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

Session 2:

mysql> show create table test3\G
ERROR 1146 (42S02): Table 'test.test3' doesn't exist

2. In the session, a temporary table can have the same name as a formal table.

mysql> create table test2 (id int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table test2 (id_tmp int)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

It can be seen that no error occurred when creating the test2 table with the same name.

3. When there are physical tables and temporary tables in the database, use show create table to view the contents of the temporary table:

mysql> show create table test2\G
*************************** 1. row ***************************
    Table: test2
Create Table: CREATE TEMPORARY TABLE `test2` (
 `id_tmp` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4. After the temporary table is dropped, show create table checks the contents of the physical table.

mysql> show tables like "test2";
+------------------------+
| Tables_in_test (test2) |
+------------------------+
| test2 |
+------------------------+
1 row in set (0.00 sec)

mysql> drop table test2;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables like "test2";
+------------------------+
| Tables_in_test (test2) |
+------------------------+
| test2 |
+------------------------+
1 row in set (0.00 sec)

5. The show tables command cannot see temporary tables.

6. Temporary tables with the same name can be created in different sessions.

7. Temporary table saving method

In MySQL, .frm is used to save the table structure, and .ibd is used to save the table data. The .frm file is generally placed in the directory specified by the tmpdir parameter. MySQL on a desktop Windows platform is as follows:

mysql> show variables like "%tmpdir%";
+-------------------+-------------------------------------------------+
| Variable_name | Value |
+-------------------+-------------------------------------------------+
| innodb_tmpdir | |
| slave_load_tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
| tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
+-------------------+-------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

In MySQL 5.6, an .ibd file will be generated to save the temporary table.

In MySQL 5.7, a temporary file tablespace was introduced to store temporary file data.

When we use different sessions to create temporary tables with the same name, we will find that temporary table files with different names exist in the temporary table directory:

These temporary tables are represented in memory as linked lists. If a session contains two temporary tables, MySQL will create a linked list of temporary tables to connect the two temporary tables. In the actual operation logic, if we execute an SQL statement, MySQL will traverse the linked list of temporary tables to check whether there is a temporary table with the table name specified in the SQL statement. If there is a temporary table, the temporary table will be operated first. If there is no temporary table, the ordinary physical table will be operated.

8. Notes on temporary tables in master-slave replication

Since the temporary table is at the session level, it will be deleted when the session exits. However, the master node does not display the temporary table, but closes the session to delete it. So how does the slave node know when to delete the temporary table?

Assume that the master node executes the following SQL:

crete table tbl;
create temporary table tmp like tbl;
insert into tmp values ​​(0,0);
insert into tbl select * from tmp;

In binlog=statement/mixed mode, if the binlog of temporary table-related operations is not recorded, the last insert statement will report an error. Because the tmp table cannot be found. In this case, MySQL's binlog will record the operations on the temporary table. When the session of the master database is closed, the SQL statement of drop temporary table will be automatically added to the binlog to ensure the consistency of master-slave data.

In binlog=row mode, SQL statements related to temporary tables will not be recorded in binlog, because in row mode, every field of the data can be found in binlog. For the last insert into select statement, binlog will record the record of inserting (0,0) into the tbl table.

In binlog=row mode, when the master database uses the drop table tmp command to delete a temporary table, this record will be ignored because the binlog does not record operations related to the temporary table.

9. How can temporary tables with the same name from different threads exist simultaneously on the slave database?

We know that temporary tables are at the session level, and temporary tables between different sessions can have the same name. When the slave library replays binlog, how does the slave library know which transaction these temporary tables with the same name belong to?

To understand this concept, you can refer to the concept of formal parameters and actual parameters in a function. Formal parameters and actual parameters may have the same name, but when they are assigned, their pointer values ​​are different. Therefore, for parameters with the same name, no error will occur for the compiler because the pointer values ​​are different.

MySQL maintains data tables. In addition to physical files, there is also a mechanism in the memory to distinguish different tables. Each table corresponds to a table_def_key. The value of table_def_key is composed of "library name + table name + server_id + thread_id". Because the thread_id is different, there will be no conflict when operating from the library.

The above is a detailed explanation of the memory temporary table in MySQL. For more information about MySQL memory temporary table, please pay attention to other related articles on 123WORDPRESS.COM!