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

This article mainly shows you "what are the problems with mysql temporary tables". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "what are the problems with mysql temporary tables".

Problems with mysql temporary tables:

Mysql 5.7

Temporary tables can only be MEMORY, MyISAM, MERGE, or InnoDB.

Mysql cluster does not support temporary tables

2 you cannot use the same temporary table multiple times in the same statement, nor in the same stored procedure.

The 3 show tables statement does not display the temporary table because it does not have a frm file in the corresponding directory

4 cannot be renamed using rename, you can use alter table rename

Mysql > rename table pp to p

ERROR 1017 (HY000): Can't find file:'. / tt/pp.frm' (errno: 2-No such file or directory)

Mysql > alter table pp rename to p

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

5 Temporary tables created outside stored functions and referred to across multiple calling and callee

Functions might result in this error:

ERROR 1137: Can't reopen table: 'temp_table'

6 in the master-slave environment, if the slave service is restarted in the process of using the temporary table when binlog_format=statement or row is used (there is no problem with restarting sql_thread only), a master-slave synchronization error will occur. Because all operations of the temporary table are recorded during binlog_format=statement and the server is shut down, the temporary table is also lost.

All operations of the temporary table will not be recorded when binlog_format=row

There is a second situation today:

Mysql > insert into big_table select * from big_table

ERROR 1137 (HY000): Can't reopen table: 'big_table'

Mysql > show create table big_table

Big_table | CREATE TEMPORARY TABLE `big_ table` (

The above is all the contents of the article "what are the problems with mysql temporary tables". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.