CODEWITHSUNDEEP
mysqltemp-tables
TranQ
TranQ

Reputation: 139

How MySql distinguishes the different of temporary table between connections?

I read an article (https://www.red-gate.com/simple-talk/sql/t-sql-programming/temporary-tables-in-sql-server/) about how SQL Server distinguishes two temporary tables with the same name but is created in 2 different connection. They do it by adding a numeric string to each local temporary table name left-padded by underscore characters.

I wonder how MySQL can handle the situation above (make a differents of two temp tables with the same name in two local connections)? Is it use the same approach like SQL does?

Upvotes: 2

Views: 474

Answers (2)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4294

It depends on table engine, I have created two tables using InnoDB and MyISAM

MyISAM uses the temporary folder on your OS, check the system variable tmpdir in my local config is set to: /tmp, so when I create a temporary table three random named files are created:

ubuntu:~$ ll /tmp/#*
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_1.frm
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_1.MYD
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_1.MYI

That files contains the table definition file (frm) the table data and indexes (MYD and MYI)

InnoDB engine uses a single file that contains another engine internals, but the table definition also is created in tmpdir path:

ubuntu:~$ ll /tmp/#*
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_0.frm   <-- InnoDB
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_1.frm   <-- MyISAM
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_1.MYD   <-- MyISAM
-rw-r----- 1 mysql mysql  /tmp/#sql4c0_6_1.MYI   <-- MyISAM

The data and indexes are stored in /path/to/mysql/ibfileX , the path is related to system variable innodb_temp_data_file_path

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562701

MySQL handles temporary tables internally by generating unique pseudo-random filenames for tables. Here's an example with the MyISAM engine:

mysql> create temporary table test.foo (i int) engine=myisam;
mysql> exit

Now let's see what was created in MySQL's tmpdir:

$ ls /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/

#sql6f_200000013_2.MYD  #sql6f_200000013_2.MYI

I tried this test with a temp InnoDB table, but that storage engine does not create visible files (at least not in version 8.0.3 which is what I have installed right now). I guess the temp table is created in the system tablespace ibdata1.

MySQL has some internal mapping of logical table name test.foo to the respective physical table name. This way each session can have its own distinct table, even though other sessions have a table of the same name.

Upvotes: 2

Related Questions