MySQL View 檢視

老马啸西风發表於2024-04-09
MySqlView

擴充閱讀

MySQL View

MySQL truncate table 與 delete 清空表的區別和坑

MySQL Ruler mysql 日常開發規範

MySQL datetime timestamp 以及如何自動更新，如何實現範圍查詢

MySQL 06 mysql 如何實現類似 oracle 的 merge into

MySQL 05 MySQL入門教程（MySQL tutorial book）

MySQL 04- EMOJI 表情與 UTF8MB4 的故事

MySQL Expression 1 of ORDER BY clause is not in SELECT list,references column

Mysql View

醜話說在前面，對於企業級專案是不推薦使用檢視的。但是東西用在合適的地方就是最好的。

參考資料：

  • mysql檢視學習總結

  • mysql之檢視詳解

檢視簡介

一、為何使用檢視

  1. 安全

使用者許可權與檢視繫結。實際上可以透過shiro或者spring-security控制。

  1. 查詢效能提高

  2. 虛擬表不用修改表結構，可以完成某些業務需求。這一點比較實用。

二、檢視的工作機制

  • 檢視的工作機制

當呼叫檢視的時候，才會執行檢視中的sql，進行取資料操作。檢視的內容沒有儲存，而是在檢視被引用的時候才派生出資料。這樣不會佔用空間，由於是即時引用，檢視的內容總是與真實表的內容是一致的。

  • 如此設計的優點

節省空間。維護好真實表的內容，就可保證檢視的完整性。

CRUD

為了測試。建立一個user表。DDL如下：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵,自增',
  `mobile` varchar(20) NOT NULL COMMENT '手機號',
  `password` varchar(64) NOT NULL COMMENT '密碼',
  `salt` varchar(128) NOT NULL COMMENT '密碼鹽',
  `nickname` varchar(32) NOT NULL DEFAULT '' COMMENT '暱稱',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否刪除 {0:未刪除, 1:已刪除}',
  `created_time` datetime NOT NULL COMMENT '建立時間',
  `updated_time` datetime NOT NULL COMMENT '最後更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `mobile_UNIQUE` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者表'

資料初始化：

INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13062666053', 'a7097b4e5fa3c1d1165e66b2d72a2d060f288d64', '649f6afc93874cf8', '某小寶', 0, '2016-09-17 14:08:38', '2016-09-17 14:08:38');
INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13012345678', '67917009d2faccc292171bb16084d7410616cdcb', '7c3186569813f1c3', '某寶', 0, '2016-09-17 14:08:38', '2016-09-17 14:08:38');
INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13111111111', '847100b326a0574c0cdcc22483e13cb39accdefd', '48c3e9cfac9ac472', '11', 0, '2016-09-17 18:29:25', '2016-09-17 18:29:25');
INSERT INTO `user` (mobile, password, salt, nickname, is_deleted, created_time, updated_time) VALUES ('13111111112', '1d4fe693d850b2e5de5f348c69bfe15a78249022', 'c1d2a40cd8db1a88', '12', 0, '2016-09-17 18:30:10', '2016-09-17 18:30:10')
  • Create
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

假設我們只關心使用者的資訊，如下：

CREATE VIEW v_user_info AS SELECT nickname, mobile, is_deleted FROM `user`;
  • Read

檢視建立成功之後，可以像普通表一樣對待。

mysql> select * from v_user_info;
+-----------+-------------+------------+
| nickname  | mobile      | is_deleted |
+-----------+-------------+------------+
| 某小寶    | 13062666053 |          0 |
| 某寶      | 13012345678 |          0 |
| 11        | 13111111111 |          0 |
| 12        | 13111111112 |          0 |
+-----------+-------------+------------+
4 rows in set (0.00 sec)

mysql> desc v_user_info;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nickname   | varchar(32) | NO   |     |         |       |
| mobile     | varchar(20) | NO   |     | NULL    |       |
| is_deleted | tinyint(4)  | NO   |     | 0       |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • Update

對檢視的資料修改會直接反應在真實表之上。

mysql> select * from user where nickname='11';
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
| id | mobile      | password                                 | salt             | nickname | is_deleted | created_time        | updated_time        |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
|  5 | 13111111111 | 847100b326a0574c0cdcc22483e13cb39accdefd | 48c3e9cfac9ac472 | 11       |          0 | 2016-09-17 18:29:25 | 2016-09-17 18:29:25 |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update v_user_info set mobile='10123456789' where nickname='11';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where nickname='11';
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
| id | mobile      | password                                 | salt             | nickname | is_deleted | created_time        | updated_time        |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
|  5 | 10123456789 | 847100b326a0574c0cdcc22483e13cb39accdefd | 48c3e9cfac9ac472 | 11       |          0 | 2016-09-17 18:29:25 | 2016-09-17 18:29:25 |
+----+-------------+------------------------------------------+------------------+----------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

我們也可以修改檢視的結構，就像修改表結構一樣。比如我們不想關心is_deleted了。

mysql> alter view v_user_info AS SELECT mobile, nickname FROM user;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_user_info;
+-------------+-----------+
| mobile      | nickname  |
+-------------+-----------+
| 13062666053 | 某小寶    |
| 13012345678 | 某寶      |
| 10123456789 | 11        |
| 13111111112 | 12        |
+-------------+-----------+
4 rows in set (0.00 sec)
  • Drop
DROP VIEW VIEW_NAME;

比如我們不想要這張檢視了

mysql> drop view v_user_info;
Query OK, 0 rows affected (0.00 sec)

相關文章