Shulou(Shulou.com)06/01 Report--

This article is mainly about the operation steps of MySQL DDL temporary table storage, if you are interested, let's take a look at this article. I believe it is of some reference value to you after reading the operation steps of MySQL DDL temporary table storage.

1. Background

* temporary tables are session-based (session) and are visible only on the current connection

* use the same temporary table name in two different connections (sessions) and do not conflict with each other, or use an existing table, but not the table name of the temporary table.

* when the temporary table name already exists, the existing table is hidden. If the temporary table is drop, the existing table is visible.

* the user who creates a temporary table must have create temporary table permission.

* temporary table data after Mysql 5.7are stored in ibtmp1 files.

2. MySQL 5.7 temporary table related file storage

* View MySQL version

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

* create temporary table temp_1

Mysql > CREATE TEMPORARY TABLE temp_1 (- > id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> data json->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.00 sec)

* insert data into 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": "25" "sex": "male", "name": "tom"} | +-+-+ 1 row in set (0.00 sec)

* View the temp variable [temporary file storage directory]

Mysql > show variables like 'tmpdir';+-+-+ | Variable_name | Value | +-+-+ | tmpdir | / tmp | +-+-+ 1 row in set (0.02 sec)

The temporary table structure definition file that begins with'#'

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

* View the datadir variable [data storage directory]

Mysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / data/mysql_data/ | +-- -+ 1 row in set (0.01 sec)

* View the temporary table data storage file ibtmp1 [store the temporary table data in the ibtmp1 file after 5.7]

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

3. MySQL 5.6 temporary 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,-> sex ENUM ('male',' female'),-> age INT NOT NULL->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.06 sec)

* insert data into 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 | 22 | +-+ 1 row in set (0.00 sec)

* View the temp variable [temporary file storage directory]

Mysql > show variables like 'tmpdir';+-+-+ | Variable_name | Value | +-+-+ | tmpdir | / tmp | +-+-+ 1 row in set (0.00 sec)

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

Mysql > system ls-l / tmptotal 116srwxrwxrwx 1 mysql mysql 0 Jun 27 22:53 mysql.sock-rw-rw---- 1 mysql mysql 8656 Jun 27 22:57 # sqla34_4_0.frm-rw-rw---- 1 mysql mysql 98304 Jun 27 22:58 # sqla34_4_0.ibd

The above details about the operation steps of MySQL DDL temporary table storage are helpful to all of you. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.