Mysql虛表指的是什么意思

這篇文章主要講解了“Mysql虛表指的是什么意思”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Mysql虛表指的是什么意思”吧！

虛擬表是實(shí)際上并不存在（物理上不存在），但是邏輯上存在的表。在mysql中，存在三種虛擬表：臨時(shí)表、內(nèi)存表和視圖；而只能從select語句可以返回虛擬表的是視圖和派生表。視圖是為了方便多個(gè)表聯(lián)表查詢而設(shè)計(jì)的，所以視圖也是多個(gè)表中的字段由各個(gè)表中的關(guān)聯(lián)關(guān)系而創(chuàng)建的一種虛擬表。

虛擬表，就是實(shí)際上并不存在（物理上不存在），但是邏輯上存在的表。

在MySQL中，存在的虛擬表：臨時(shí)表、內(nèi)存表和視圖，派生表。

只能從select語句可以返回虛擬表的是視圖和派生表。

一、派生表

當(dāng)select語句的from子句中使用獨(dú)立子查詢時(shí)，就稱其為派生表。

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

與子查詢不同，派生表必須具有別名，以便稍后在查詢中引用其名字。

如果派生表沒有別名，則出錯(cuò)。

二、視圖

視圖是為了方便多個(gè)表聯(lián)表查詢而設(shè)計(jì)的，所以視圖也是多個(gè)表中的字段由各個(gè)表中的關(guān)聯(lián)關(guān)系而創(chuàng)建的一種虛擬表。

視圖創(chuàng)建后就保存了下來，以后可以隨時(shí)用，除非drop刪除視圖。

在數(shù)據(jù)庫中，只存放了視圖的定義，并沒有存放視圖的數(shù)據(jù)，數(shù)據(jù)還是存儲在原來的表中，

視圖的數(shù)據(jù)是依賴原來表中的數(shù)據(jù)的，所以原來表的數(shù)據(jù)發(fā)生了改變，那么顯示的視圖的數(shù)據(jù)也會隨著改變。

一般來說，我們只是利用視圖來查詢數(shù)據(jù)，不會通過視圖來操作數(shù)據(jù)。

1.創(chuàng)建視圖

-- 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.調(diào)用視圖

select * from other;

4.如果視圖不存在，則創(chuàng)建視圖；如果視圖存在，則修改視圖

create or replace view view_name as select 語句;

視圖創(chuàng)建一遍后會在navicat premium（MySQL可視化工具）保存下來，不可重復(fù)創(chuàng)建視圖，

所以想重復(fù)調(diào)試創(chuàng)建視圖，需先刪除已創(chuàng)建的視圖，在執(zhí)行創(chuàng)建視圖的SQL命令或者是修改視圖。

視圖作用：

• 提高了重用性，就項(xiàng)一個(gè)函數(shù)，創(chuàng)建一次，可重復(fù)調(diào)用。

• 對數(shù)據(jù)庫重構(gòu)，卻不影響原數(shù)據(jù)。

• 讓數(shù)據(jù)更加清晰。想要什么樣的數(shù)據(jù)，就創(chuàng)建什么樣的視圖。

視圖定義的存放位置：information_schema.views

查看視圖的基本信息：DESC view_name;

注意：

• 修改視圖的數(shù)據(jù)，將直接修改數(shù)據(jù)表（即原表）的真實(shí)數(shù)據(jù)。

• 刪除視圖，不會影響原表的數(shù)據(jù)，但是刪除視圖的數(shù)據(jù)，則會影響到原表。

即可以從select語句可以返回虛擬表，又可以通過構(gòu)建表結(jié)構(gòu)創(chuàng)建虛擬表的是臨時(shí)表和內(nèi)存表。

三、臨時(shí)表

MySQL臨時(shí)表在保存一些臨時(shí)數(shù)據(jù)時(shí)是非常有用的。

臨時(shí)表是建立在系統(tǒng)臨時(shí)文件夾中的表，使用得當(dāng)，完全可以像普通表一樣進(jìn)行各種操作。

臨時(shí)表的數(shù)據(jù)和表結(jié)構(gòu)都存儲在內(nèi)存中。

臨時(shí)表只在當(dāng)前MySQL連接可見，當(dāng)關(guān)閉連接時(shí)，MySQL會自動刪除表并釋放所有空間。

1.通過構(gòu)建臨時(shí)表結(jié)構(gòu)創(chuàng)建臨時(shí)表

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

select * from tmp_table;

2.直接將查詢結(jié)果導(dǎo)入臨時(shí)表

create temporary table tmp_table select * from other;

3.刪除臨時(shí)表

drop table tmp_table;

臨時(shí)表的應(yīng)用：

• 當(dāng)某一個(gè)SQL語句關(guān)聯(lián)的表在2張及以上，并且和一些小表關(guān)聯(lián)。可以采用將大表進(jìn)行拆分并且得到比較小的結(jié)果集合存放在臨時(shí)表中。

• 程序執(zhí)行過程中可能需要存放一些臨時(shí)數(shù)據(jù)，這些數(shù)據(jù)在整個(gè)程序的會話過程中需要重復(fù)使用。

• 臨時(shí)表默認(rèn)是MyISAM（存儲引擎），但是可以修改。

注意：

• 臨時(shí)表與存在的表名相同的時(shí)候，存在的表會被隱藏，當(dāng)臨時(shí)表被drop，存在的表就可見了。

• show tables語句不會列舉臨時(shí)表，但是會列出內(nèi)存表。

• 臨時(shí)表在數(shù)據(jù)庫多個(gè)連接之間不能共享。

• 臨時(shí)表的最大所需內(nèi)存需要通過tmp_table_size=128MB設(shè)定。當(dāng)數(shù)據(jù)超過臨時(shí)表的最大值設(shè)定時(shí)，自動轉(zhuǎn)為磁盤表，此時(shí)因需要進(jìn)行IO操作，性能會大大下降，而內(nèi)存表不會，內(nèi)存表滿后，會提示數(shù)據(jù)滿錯(cuò)誤。

• 臨時(shí)表更多作用是系統(tǒng)自己創(chuàng)建或，組織數(shù)據(jù)以提升性能，如子查詢。

四、內(nèi)存表

表結(jié)構(gòu)建在磁盤上，數(shù)據(jù)在內(nèi)存里，當(dāng)停止服務(wù)后，表中的數(shù)據(jù)會丟失，而表的結(jié)構(gòu)不會丟失。

內(nèi)存表也可以被看作是臨時(shí)表的一種。

1.通過構(gòu)建內(nèi)存表結(jié)構(gòu)創(chuàng)建內(nèi)存表

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

select * from tmp_table;
show tables;

2.直接將查詢結(jié)果導(dǎo)入內(nèi)存表

create table tmp_table engine=memory 
　　select * from other;

3.釋放占用的內(nèi)存

-- 刪除數(shù)據(jù)
delete from tmp_table;
-- 清空表
truncate table tmp_table;
-- 刪除表
drop table tmp_table;

內(nèi)存表特征：

• 對于varchar等變長類型，內(nèi)存表使用固定的長度來存放。

• 內(nèi)存表可以有非唯一的鍵。

• 內(nèi)存表不能包含BLOB或TEXT列。

• 內(nèi)存表支持AUTO_INCREMENT列。

• 內(nèi)存表支持插入延遲，使讀取優(yōu)先。

• 當(dāng)臨時(shí)表變得很大時(shí)，MySQL會自動地把它轉(zhuǎn)化為在磁盤上存儲的表，而內(nèi)存表不會自動轉(zhuǎn)換。

• 在MySQL的主從服務(wù)器上，內(nèi)存表可以被復(fù)制。

• 內(nèi)存表最大的size受限于系統(tǒng)變量max_heap_table_size，默認(rèn)值是16MB，這個(gè)變量是可以修改的。

• 內(nèi)存表對所有用戶的連接都是可見的，使得它非常適合做緩存。

• 內(nèi)存表必須使用memory存儲引擎。

內(nèi)存表的注意事項(xiàng)：

1、heap不允許使用xxxTEXT和xxxBLOB數(shù)據(jù)類型；只允許使用=和<=>操作符來搜索記錄（不允 許& amp; lt;、>、<=或>=）；mysql4.1版本之前不支持auto_increment；只允許對非空數(shù)據(jù)列進(jìn)行索引（not null）。

注：操作符 “<=>” 說明：NULL-safe equal.這個(gè)操作符和“=”操作符執(zhí)行相同的比較操作，不過在兩個(gè)操作碼均為NULL時(shí)，其所得值為1而不為NULL，而當(dāng)一個(gè)操作碼為NULL時(shí)，其所得值為0而不為NULL。

2、內(nèi)存表可以通過max_heap_table_size = 2048M來加大使用的內(nèi)存。

3、內(nèi)存表必須使用memory存儲引擎

五、臨時(shí)內(nèi)存表

1.通過構(gòu)建表結(jié)構(gòu)創(chuàng)建臨時(shí)內(nèi)存表

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

select * from tmp_table;
show tables;

2.直接將查詢結(jié)果導(dǎo)入內(nèi)存表

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

臨時(shí)表使用注意事項(xiàng)：

（1）臨時(shí)表只在當(dāng)前連接可見，當(dāng)這個(gè)連接關(guān)閉的時(shí)候，會自動drop。這就意味著你可以在兩個(gè)不同的連接里使用相同的臨時(shí)表名，并且相互不會沖突，或者使用已經(jīng)存在的表，但不是臨時(shí)表的表名。(當(dāng)這個(gè)臨時(shí)表存在的時(shí)候，存在的表被隱藏了，如果臨時(shí)表被drop，存在的表就可見了)。

（2）臨時(shí)表只能用在 memory,myisam,merge,或者innodb引擎。

（3）臨時(shí)表不支持mysql cluster(簇)。

（4）在同一個(gè)query語句中，你只能查找一次臨時(shí)表。例如：下面的就不可用

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

　　如果在一個(gè)存儲函數(shù)里，你用不同的別名查找一個(gè)臨時(shí)表多次，或者在這個(gè)存儲函數(shù)里用不同的語句查找，這個(gè)錯(cuò)誤都會發(fā)生。

（5）show tables 語句不會列舉臨時(shí)表，但是會列出內(nèi)存表。

（6）你不能用rename來重命名一個(gè)臨時(shí)表。但是，你可以alter table代替：

mysql>ALTER TABLE orig_name RENAME new_name;

感謝各位的閱讀，以上就是“Mysql虛表指的是什么意思”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對Mysql虛表指的是什么意思這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！