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

Source: Internet
Author: User
Tags mysql version

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.