mysql如何使用临时表(MySQL中临时表的使用示例)
mysql如何使用临时表
MySQL中临时表的使用示例这两天事情稍微有点多,公众号也停止更新了几天,结果有读者催更了,也是,说明还是有人关注,利己及人,挺好。
今天分享的内容是MySQL中的临时表,对于临时表,之前我其实没有过多的研究,只是知道MySQL在某些特定场景下会使用临时表来辅助进行group by等一些列操作,今天就来认识下临时表吧。
1、首先。临时表是session级别的,当前session创建的表,在其他session中看不到。
session 1:
|
mysql> create temporary table test3 (id_tmp int )engine=innodb; Query OK, 0 rows affected (0.00 sec) |
session 2:
|
mysql> show create table test3\G ERROR 1146 (42S02): Table 'test.test3' doesn't exist |
2、临时表在session中,可以和正式的表重名。
|
mysql> create table test2 (id int )engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> create temporary table test2 (id_tmp int )engine=innodb; Query OK, 0 rows affected (0.00 sec) |
可以看到,创建同名的test2表的时候,并没有出现报错的情况。
3、当数据库中物理表和临时表的时候,使用show create table查看的是临时表的内容:
|
mysql> show create table test2\G *************************** 1. row *************************** Table : test2 Create Table : CREATE TEMPORARY TABLE `test2` ( `id_tmp` int (11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
4、临时表drop掉之后,show create table查看的是物理表的内容。
|
mysql> show tables like "test2" ; + ------------------------+ | Tables_in_test (test2) | + ------------------------+ | test2 | + ------------------------+ 1 row in set (0.00 sec) mysql> drop table test2; Query OK, 0 rows affected (0.00 sec) mysql> show tables like "test2" ; + ------------------------+ | Tables_in_test (test2) | + ------------------------+ | test2 | + ------------------------+ 1 row in set (0.00 sec) |
5、show tables命令,不能看到临时表。
6、不同的session中可以创建同名的临时表。
7、临时表保存方法
在MySQL中,使用.frm来保存表结构,而使用.ibd来保存表数据,.frm文件一般是放在tmpdir这个参数指定的目录下面的。台式机windows平台下MySQL的如下:
|
mysql> show variables like "%tmpdir%" ; + -------------------+-------------------------------------------------+ | Variable_name | Value | + -------------------+-------------------------------------------------+ | innodb_tmpdir | | | slave_load_tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\ Local \ Temp | | tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\ Local \ Temp | + -------------------+-------------------------------------------------+ 3 rows in set , 1 warning (0.01 sec) |
MySQL5.6版本下,会生成一个.ibd的文件来保存临时表。
MySQL5.7版本下,引入了临时文件表空间,专门用来存放临时文件的数据。
当我们使用不同的session来创建相同名称的临时表的时候,会发现临时表的目录下面存在不同名称的临时表文件:
这些临时表在内存中是通过链表的方式来表示的,如果一个session中包含两个临时表,MySQL会创建一个临时表的链表,将这两个临时表连接起来,实际的操作逻辑中,如果我们执行了一条SQL,MySQL会遍历这个临时表的链表,检查是否有这个SQL中指定表名字的临时表,如果有临时表,优先操作临时表,如果没有临时表,则操作普通的物理表。
8、临时表在主从复制中的注意点
临时表由于是session级别的,那么在session退出的时候,是会删除临时表的。但是主节点中并没有对临时表进行显示的操作,而是关闭session即可删除,那么从节点如何知道什么时候才能删除临时表呢?
假设主节点进行如下SQL:
|
crete table tbl; create temporary table tmp like tbl; insert into tmp values (0,0); insert into tbl select * from tmp; |
在binlog=statement/mixed模式下,如果不记录临时表相关操作的binlog,则最后一条insert语句会报错。因为找不到tmp这个表。这种情况下,MySQL的binlog中会记录临时表的操作,当主库的session关闭的时候,自动的在binlog中添加drop temporary table的SQL语句,从而保证主从数据的一致。
在binlog=row模式下,跟临时表有关的SQL,都不会记录到binlog里面,因为row模式下,数据的每个字段在binlog中都能找到,针对最后一个insert into select语句,binlog中会记录成往tbl表中插入(0,0)这条记录。
binlog=row模式下,当主库上主动使用drop table tmp的命令来删除临时表的时候,此时因为binlog中不记录临时表的相关操作,所以这条记录也会被忽略。
9、不同线程的同名临时表在从库上如何同时存在?
我们知道临时表是session级别的,而且不同session之间的临时表可以重名,在从库进行binlog回放的时候,从库是如何知道这些重名的临时表分别属于哪个事务的呢?
这个概念的理解可以参考函数中的形参和实参的概念,形参和实参可能有同样的名字,进行赋值的时候,二者的指针值是不一样的,所以同名的参数,对编译器来讲,由于指针值不一样,所以不会出现错误。
MySQL维护数据表,除了物理上要有文件外,内存里面也有一套机制区别不同的表,每个表都对应一个table_def_key。而这个table_def_key的值是由"库名字+表名字+server_id+thread_id"组成的,因为thread_id不同,所以在从库中进行操作的时候,是不会冲突的。
以上就是详解MySQL中的内存临时表的详细内容,更多关于MySQL 内存临时表的资料请关注开心学习网其它相关文章!
原文链接:https://cloud.tencent.com/developer/article/1651591
- 使用mysqldump命令来备份(linux使用mysqldump+expect+crontab实现mysql周期冷备份思路详解)
- windows 安装解压版 mysql5.7.28 winx64的详细教程(windows 安装解压版 mysql5.7.28 winx64的详细教程)
- mysql 用户权限配置(详解MySQL 用户权限管理)
- mysql中怎么删除整张表(MySQL如何优雅的删除大表实例详解)
- mysqlnull所占空间(详解mysql三值逻辑与NULL)
- docker 镜像mysql(解决docker拉取mysql镜像太慢的情况)
- mysqlbinlog怎么分析(MySQL中使用binlog时格式该如何选择)
- mysql 分库分表步骤(MySQL读多写少设计方案 - 分库分表还能这么做?)
- mysql中基本语句(MySQL中explain语句的基本使用教程)
- mac的mysql连接问题如何解决(MAC 中mysql密码忘记解决办法)
- mysql缓存和redis查询效率(浅谈MySQL与redis缓存的同步方案)
- mysql表锁行锁和分页锁(MySQL 不停机不锁表主从搭建)
- mysql单独导出表(mysql实现从导出数据的sql文件中只导入指定的一个表)
- mysql整体架构设计(MySQL 整体架构介绍)
- mysql如何删除外键约束数据(MySQL中外键的创建、约束以及删除)
- mysql模糊匹配语句(MySQL 数据库 like 语句通配符模糊查询小结)
- 前《iLOOK》时装总监 《快乐大本营》御用造型师上线(快乐大本营御用造型师上线)
- 释小龙晒杀青照片 多重身份惹观众期待(释小龙晒杀青照片)
- 《九牛之人降魔传》开机 演员祁高坤化身九牛之人除魔卫道(九牛之人降魔传开机)
- 王铲铲的致富之路无限金币卡法攻略教学(王铲铲的致富之路无限金币卡法攻略教学)
- 文明6金币太少怎么办 文明6无限刷钱教程(文明6金币太少怎么办)
- 开国中将,王牌军63军首任政委,两个连襟一个上将一个少将传为佳话(王牌军63军首任政委)
热门推荐
- harborjob设置(harbor修改配置文件后重启操作)
- 腾讯云轻量服务器怎么降低延迟(腾讯云星星海SA2云服务器配置规格性能实际评测)
- mysql 用户权限配置(详解MySQL 用户权限管理)
- dedecms设置轮播图(织梦dedecms网站地图改变生成目录的方法)
- python 串口图形化(python使用MQTT给硬件传输图片的实现方法)
- docker 加入k8s吗(k8s和Docker关系简单说明)
- sqlserver2014怎么重新激活(解决Windows 10家庭版安装SQL Server 2014出现.net 3.5失败问题)
- css图片水平旋转动画(css实现图片横向排列滚动效果)
- js判断浏览器的版本
- python集合类(Python数据类型之Set集合实例详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9