MySQL DLL operation--------Temporary table storage Combat

1. Background

* temporary table is session-based, only visible on current connection

* When this connection (session) is closed, it will be automatically drop.

* two different connections (sessions) Use the same temporary table name and do not conflict with each other, or use a table that already exists, but not a table name for the staging table.

* when the table name already exists in this temporary table, the existing table is hidden, if the temporary table is drop, the existing table is visible

* create temporary table user must have create temporary table permission.

* after Mysql 5.7, temporary table data is stored in the Idbdata1 file.

2. MySQL 5.7 Temp Table related file storage

* View MySQL version

Mysql> select version (); +-----------+| Version () |+-----------+| 5.7.18 |+-----------+1 row in Set (0.01 sec)

* Create temporary table temp_1

mysql> CREATE Temporary TABLE temp_1 (ID BIGINT PRIMARY KEY not NULL auto_increment, data json-& Gt ) Engine=innodb Charset=utf8mb4; Query OK, 0 rows Affected (0.00 sec)

* Insert data on temporary table 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

* View temporary table temp_1 data

Mysql> SELECT * from temp_1;+----+---------------------------------------------+| ID |  Data |+----+---------------------------------------------+| 1 | {"Age": "+", "Sex": "Male", "name": "Tom"} |+----+---------------------------------------------+1 row in Set (0.00 sec)

* View TEMP variable [ temp File Store directory ]

Mysql> Show variables like ' tmpdir '; +---------------+-------+| variable_name | Value |+---------------+-------+| Tmpdir | /tmp |+---------------+-------+1 row in Set (0.02 sec)

* View temporary table structure definition file *.frm

　　 ' # ' represents a temporary table structure definition file

mysql> system ls-l/tmptotal 18srwxrwxrwx 1 mysql mysql 0 June 20:09 mysql.sock-rw-------1 mysql MySQL 5 June 20:09 mysql.sock.lock-rw-r-----1 mysql mysql 8586 June 22:41 #sql666_9_0. frm

* View DataDir variables [ data store directory ]

Mysql> Show variables like ' datadir '; +---------------+-------------------+| variable_name | Value |+---------------+-------------------+| DataDir | /data/mysql_data/|+---------------+-------------------+1 row in Set (0.01 sec)

* View temporary table data store file IBTMP1 [5.7 to store temporary table data in IBTMP1 file]

mysql> system ls-l/data/mysql_data/ibtmp1-rw-r-----1 mysql mysql 12582912 June 22:43/data/mysql_data/ibtmp1

3. MySQL 5.6 Temp Table Related file storage

* View MySQL version

Mysql> Show variables like ' version '; +---------------+--------+| variable_name | Value |+---------------+--------+| Version | 5.6.36 |+---------------+--------+1 row in Set (0.00 sec)

* Create temporary table temp_1

mysql> CREATE Temporary TABLE temp_1 (ID BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR (32 ) not NULL, the sex ENUM (' male ', ' female '), and age INT not null--) Engine=innodb charset=utf8mb4; Query OK, 0 rows affected (0.06 sec)

* Insert data on temporary table 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

* View temporary table temp_1 data

Mysql> INSERT into Temp_1 SELECT NULL, ' Tom ', ' Male ', 22; Query OK, 1 row affected (0.03 sec) records:1 duplicates:0 warnings:0mysql> SELECT * from temp_1;+----+------+----- -+-----+| ID | name | sex |  Age |+----+------+------+-----+| 1 | Tom |  Male | |+----+------+------+-----+1 row in Set (0.00 sec)

* View TEMP variable [ temp File Store directory ]

Mysql> Show variables like ' tmpdir '; +---------------+-------+| variable_name | Value |+---------------+-------+| Tmpdir | /tmp |+---------------+-------+1 row in Set (0.00 sec)

* View temporary table structure definition file *.frm and data file *.ibd

[MySQL 5.7 Before the temporary table all table structure definition files and data files are stored in Tmpdir]

mysql> system ls-l/tmptotal 116srwxrwxrwx 1 mysql mysql 0 June 22:53 mysql.sock-rw-rw----1 mysql mysql 8656 J Un 22:57 #sqla34_4_0. FRM-RW-RW----1 MySQL mysql 98304 June 22:58 #sqla34_4_0. IBD

4. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1943417

MySQL DLL operation--------Temporary table storage Combat