Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to create and delete temporary tables in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. Introduction:

MySQL temporary table, which belongs to the session level, is deleted when session exits. Temporary tables are allowed to have the same name as other tables and are maintained separately in the structure of the thd; therefore, different session can create temporary tables with the same name and operate only the temporary tables they own

The syntax for creating a temporary table is simple:

Root@test 03:26:44 > show create table tmp1\ G

* * 1. Row *

Table: tmp1

Create Table: CREATE TEMPORARY TABLE `tmp1` (

`a`int (11) NOT NULL AUTO_INCREMENT

`b` int (11) DEFAULT NULL

`c`int (11) DEFAULT NULL

PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

When a temporary table is created, two files are generated under the tmp folder:

# sql3e95_1a_0.frm

# sql3e95_1a_0.ibd

So how exactly does MySQL itself create and delete temporary tables?

two。 Create

Perform SQL:

CREATE TEMPORARY TABLE `tmp1` (`a` int (11) NOT NULL AUTO_INCREMENT, `b` int (11) DEFAULT NULL, `c` int (11) DEFAULT NULL, PRIMARY KEY (`a`))

1) breakpoint: ysql_execute_command

_ execute_command:

2205 switch (lex- > sql_command) {

(gdb)

2532 if (! (lex- > create_info.options & HA_LEX_CREATE_TMP_TABLE))

False in the (gdb) p lex- > create_info.options-if statement

$2 = 1

Create_table_precheck- checks whether the table has permission to create a table and whether the table name already exists on the global linked list (temporary tables do not need to be checked)

Append_file_to_dir-Fix names if symlinked tables

If (select_lex- > item_list.elements)-when it is a statement like create.... select, select_lex- > item_list.elements is a non-zero value, so we only consider simple cases here.

If ((result= new select_create))

Res= handle_select (thd, lex, result, 0)

Else

(1) mysql_create_like_table-create table like... A similar statement

(2) mysql_create_table-mainly analyze this function

2) breakpoint: mysql_create_table

Mysql_create_table

Mysql_create_table_no_lock

Check_engine

File = get_new_handler

3842 set_table_default_charset (thd, create_info, (char*) db)

3844 if (mysql_prepare_create_table (thd, create_info, alter_info)

3854 path_length= build_tmptable_filename (thd, path, sizeof (path));-create temporary table file name: # sql {process id} _ {thread_id} _ {current thread's temporary table integer identity thd- > tmp_table}

3978 rea_create_table-create frm file and ibd file

3986 open_temporary_table--- Open temporary Table

(1) construct table and table_share structures.

(2) add the table structure to the thd- > temporary_tables linked list

4009 error= write_create_table_bin_log- writes to binlog

3. Delete temporary table

Execute drop table tmp1 manually

Mysql_execute_command

Case SQLCOM_DROP_TABLE:

Mysql_rm_table

Mysql_rm_table_part2

For (table= tables; table; table= table- > next_local)

Drop_temporary_table-- looks up temporary tables from thd- > temporary_tables

Call close_temporary_table to close, delete the temporary table file, and delete the corresponding node from thd- > temporary_tables

If (! drop_temporary)-- when a non-temporary table is deleted, execute the following logic

-

4. When session exits.

Look at the stack:

Breakpoint 16, rm_temporary_table (base=0xc8c560, path=0x1427c10 "/ u01/mysql-5148.stock/tmp/#sql3e95_1d_0") at sql_base.cc:5634

5634 bool rm_temporary_table (handlerton * base, char * path)

(gdb)

5641 strmov (ext= strend (path), reg_ext)

(gdb) bt

# 0 rm_temporary_table (base=0xc8c560, path=0x1427c10 "/ u01/mysql-5148.stock/tmp/#sql3e95_1d_0") at sql_base.cc:5641

# 1 0x00000000005f6eaa in close_temporary (table=0x1427030, free_share=true, delete_table=true) at sql_base.cc:1928

# 2 0x00000000005f725f in close_temporary_tables (thd=0x14065f0) at sql_base.cc:1549

# 3 0x0000000000592d9b in THD::cleanup (this=0x14065f0) at sql_class.cc:967

# 4 0x00000000005a3579 in unlink_thd (thd=0xc8c560) at mysqld.cc:1858

# 5 0x00000000005a35dc in one_thread_per_connection_end (thd=0xc8c560, put_in_cache=16) at mysqld.cc:1945

# 6 0x00000000005ac208 in handle_one_connection (arg=0x14065f0) at sql_connect.cc:1141

# 7 0x0000003e638064a7 in start_thread () from / lib64/libpthread.so.0

# 8 0x0000003e630d3c2d in clone () from / lib64/libc.so.6

# 9 0x0000000000000000 in? ()

At the end of the session, THD::cleanup is called to clean up the temporary table.

On how to create and delete temporary tables in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.