Mysql虚表是什么

大芳同学_7392

大芳同学_7392

2023-04-14

5143人浏览

原创

虚拟表是实际上并不存在(物理上不存在),但是逻辑上存在的表。在mysql中,存在三种虚拟表:临时表、内存表和视图;而只能从select语句可以返回虚拟表的是视图和派生表。视图是为了方便多个表联表查询而设计的,所以视图也是多个表中的字段由各个表中的关联关系而创建的一种虚拟表。

本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。

虚拟表,就是实际上并不存在(物理上不存在),但是逻辑上存在的表。

在MySQL中,存在的虚拟表:临时表、内存表和视图,派生表。

只能从select语句可以返回虚拟表的是视图和派生表。

一、派生表

当select语句的from子句中使用独立子查询时,就称其为派生表。

select column_list
from (
      select column_list
      from table_1
      ) derived_table_name
where derived_table_name.c1 > 0 ;

与子查询不同,派生表必须具有别名,以便稍后在查询中引用其名字。

如果派生表没有别名,则出错。

二、视图

视图是为了方便多个表联表查询而设计的,所以视图也是多个表中的字段由各个表中的关联关系而创建的一种虚拟表。

视图创建后就保存了下来,以后可以随时用,除非drop删除视图。

在数据库中,只存放了视图的定义,并没有存放视图的数据,数据还是存储在原来的表中,

视图的数据是依赖原来表中的数据的,所以原来表的数据发生了改变,那么显示的视图的数据也会随着改变。

一般来说,我们只是利用视图来查询数据,不会通过视图来操作数据。

1.创建视图

-- other
create view other as 
select a.name as username, b.name as goodsname from 
user as a, goods as b where a.id=b.id;

2.删除视图

drop view if exists other;

3.调用视图

select * from other;

4.如果视图不存在,则创建视图;如果视图存在,则修改视图

create or replace view view_name as select 语句;

视图创建一遍后会在navicat premium(MySQL可视化工具)保存下来,不可重复创建视图,

所以想重复调试创建视图,需先删除已创建的视图,在执行创建视图的SQL命令或者是修改视图。

视图作用:

  • 提高了重用性,就项一个函数,创建一次,可重复调用。
  • 对数据库重构,却不影响原数据。
  • 让数据更加清晰。想要什么样的数据,就创建什么样的视图。

视图定义的存放位置:information_schema.views

查看视图的基本信息:DESC view_name;

注意:

  • 修改视图的数据,将直接修改数据表(即原表)的真实数据。
  • 删除视图,不会影响原表的数据,但是删除视图的数据,则会影响到原表。

即可以从select语句可以返回虚拟表,又可以通过构建表结构创建虚拟表的是临时表和内存表。

三、临时表

MySQL临时表在保存一些临时数据时是非常有用的。

临时表是建立在系统临时文件夹中的表,使用得当,完全可以像普通表一样进行各种操作。

临时表的数据和表结构都存储在内存中。

临时表只在当前MySQL连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。

1.通过构建临时表结构创建临时表

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
create temporary table tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
);

select * from tmp_table;

2.直接将查询结果导入临时表

create temporary table tmp_table select * from other;

3.删除临时表

drop table tmp_table;

临时表的应用:

  • 当某一个SQL语句关联的表在2张及以上,并且和一些小表关联。可以采用将大表进行拆分并且得到比较小的结果集合存放在临时表中。
  • 程序执行过程中可能需要存放一些临时数据,这些数据在整个程序的会话过程中需要重复使用。
  • 临时表默认是MyISAM(存储引擎),但是可以修改。

注意:

  • 临时表与存在的表名相同的时候,存在的表会被隐藏,当临时表被drop,存在的表就可见了。
  • show tables语句不会列举临时表,但是会列出内存表。
  • 临时表在数据库多个连接之间不能共享。
  • 临时表的最大所需内存需要通过tmp_table_size=128MB设定。当数据超过临时表的最大值设定时,自动转为磁盘表,此时因需要进行IO操作,性能会大大下降,而内存表不会,内存表满后,会提示数据满错误。
  • 临时表更多作用是系统自己创建或,组织数据以提升性能,如子查询。

四、内存表

表结构建在磁盘上,数据在内存里,当停止服务后,表中的数据会丢失,而表的结构不会丢失。

内存表也可以被看作是临时表的一种。

1.通过构建内存表结构创建内存表

create table tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)ENGINE=MEMORY;

select * from tmp_table;
show tables;

2.直接将查询结果导入内存表

create table tmp_table engine=memory 
  select * from other;

3.释放占用的内存

-- 删除数据
delete from tmp_table;
-- 清空表
truncate table tmp_table;
-- 删除表
drop table tmp_table;

内存表特征:

  • 对于varchar等变长类型,内存表使用固定的长度来存放。
  • 内存表可以有非唯一的键。
  • 内存表不能包含BLOB或TEXT列。
  • 内存表支持AUTO_INCREMENT列。
  • 内存表支持插入延迟,使读取优先。
  • 当临时表变得很大时,MySQL会自动地把它转化为在磁盘上存储的表,而内存表不会自动转换。
  • 在MySQL的主从服务器上,内存表可以被复制。
  • 内存表最大的size受限于系统变量max_heap_table_size,默认值是16MB,这个变量是可以修改的。
  • 内存表对所有用户的连接都是可见的,使得它非常适合做缓存。
  • 内存表必须使用memory存储引擎。

内存表的注意事项:

1、heap不允许使用xxxTEXT和xxxBLOB数据类型;只允许使用=和操作符来搜索记录(不允 许& amp; lt;、>、=);mysql4.1版本之前不支持auto_increment;只允许对非空数据列进行索引(not null)。

注:操作符 “” 说明:NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作,不过在两个操作码均为NULL时,其所得值为1而不为NULL,而当一个操作码为NULL时,其所得值为0而不为NULL。 

2、内存表可以通过max_heap_table_size = 2048M来加大使用的内存。

3、内存表必须使用memory存储引擎

五、临时内存表

1.通过构建表结构创建临时内存表

create temporary table tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)ENGINE=MEMORY;

select * from tmp_table;
show tables;

2.直接将查询结果导入内存表

create temporary table tmp_table engine=memory 
  select * from other;

临时表使用注意事项:

(1)临时表只在当前连接可见,当这个连接关闭的时候,会自动drop。这就意味着你可以在两个不同的连接里使用相同的临时表名,并且相互不会冲突,或者使用已经存在的表,但不是临时表的表名。(当这个临时表存在的时候,存在的表被隐藏了,如果临时表被drop,存在的表就可见了)。

(2)临时表只能用在 memory,myisam,merge,或者innodb引擎。

(3)临时表不支持mysql cluster(簇)。

(4)在同一个query语句中,你只能查找一次临时表。例如:下面的就不可用

  mysql> SELECT * FROM temp_table, temp_table AS t2;
  ERROR 1137: Can't reopen table: 'temp_table'

  如果在一个存储函数里,你用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,这个错误都会发生。

(5)show tables 语句不会列举临时表,但是会列出内存表。

(6)你不能用rename来重命名一个临时表。但是,你可以alter table代替:

mysql>ALTER TABLE orig_name RENAME new_name;

【相关推荐:mysql视频教程】

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

20

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

20

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

20

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

20

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

120

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程