官方文档：https://dev.mysql.com/doc/refman/5.7/en/create-view.html

  1. CREATE
  2.     [OR REPLACE]
  3.     [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  4.     [DEFINER = user]
  5.     [SQL SECURITY { DEFINER | INVOKER }]
  6.     VIEW view_name [(column_list)]
  7.     AS select_statement
  8.     [WITH [CASCADED | LOCAL] CHECK OPTION]

创建视图

  1. mysql> create table test_rank(id int, score int);
  2. Query OK, 0 rows affected (0.16 sec)
  4. mysql> insert into test_rank values(1, 10), (2, 20), (3, 30), (4, 30), (5, 40), (6, 40);
  5. Query OK, 6 rows affected (0.05 sec)
  6. Records: 6  Duplicates: 0  Warnings: 0
  8. mysql> select * from test_rank;
  9. +------+-------+
  10. | id   | score |
  11. +------+-------+
  12. |    1 |    10 |
  13. |    2 |    20 |
  14. |    3 |    30 |
  15. |    4 |    30 |
  16. |    5 |    40 |
  17. |    6 |    40 |
  18. +------+-------+
  19. 6 rows in set (0.00 sec)
  21. --
  22. -- 创建视图
  23. --
  24. mysql> create view view_rank as select * from test_rank;  -- 针对上面的test_rank创建一个视图
  25. Query OK, 0 rows affected (0.03 sec)
  27. -- 也可以对select结果增加条件进行过滤后，再创建视图
  29. mysql> show create table test_rank\G
  30. *************************** 1. row ***************************
  31.        Table: test_rank
  32. Create Table: CREATE TABLE `test_rank` (    -- 得到的是表结构
  33.   `id` int(11) DEFAULT NULL,
  34.   `score` int(11) DEFAULT NULL
  35. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  36. 1 row in set (0.00 sec)
  39. mysql> show create table view_rank\G  -- 他是以一张表的形式存在的，可通过show tables看到
  40. *************************** 1. row ***************************
  41.                 View: view_rank
  42.          Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_rank` AS select `test_rank`.`id` AS `id`,`test_rank`.`score` AS `score` from `test_rank`
  43.                       -- 和真正的表不同的是，这里show出来的是视图的定义
  44. character_set_client: utf8
  45. collation_connection: utf8_general_ci
  46. 1 row in set (0.00 sec)
  48. mysql> select * from view_rank;  -- 可以直接查询该视图得结果
  49. +------+-------+
  50. | id   | score |
  51. +------+-------+
  52. |    1 |    10 |
  53. |    2 |    20 |
  54. |    3 |    30 |
  55. |    4 |    30 |
  56. |    5 |    40 |
  57. |    6 |    40 |
  58. +------+-------+
  59. 6 rows in set (0.00 sec)
  61. -- 视图的作用是，可以对开发人员是透明的，可以隐藏部分关键的列
  62. -- 视图在mysql是虚拟表。根据视图的定义，还是去执行定义中的select语句。
  64. -- 只开放部分列
  65. mysql> create view view_rank_1 as select id from test_rank; -- 只开放id列
  66. Query OK, 0 rows affected (0.04 sec)
  68. mysql> select * from view_rank_1;  -- 即使 select * ，也只能看到id列，具有隐藏原来表中部分列的功能                        
  69. +------+
  70. | id   |
  71. +------+
  72. |    1 |
  73. |    2 |
  74. |    3 |
  75. |    4 |
  76. |    5 |
  77. |    6 |
  78. +------+
  79. 6 rows in set (0.00 sec)
  81. -- 不要取用select * from 去创建视图，因为mysql会把*逐个解析成列。
  82. -- 当原来的表结构发生变化时，视图的表结构是不会发生变化的，视图在创建的瞬间，便确定了结构。
  83. -- 比如，当你alter原来的表 增加列(add columns)时,再去查询该视图,新增加的列是不存在的。
  85. mysql> alter table test_rank add column  c int default 0; -- 增加一列名字为c，默认值为0
  86. Query OK, 0 rows affected (0.30 sec)
  87. Records: 0  Duplicates: 0  Warnings: 0
  89. mysql> select * from test_rank;  -- 查询原表，新的列c出现了
  90. +------+-------+------+
  91. | id   | score | c    |
  92. +------+-------+------+
  93. |    1 |    10 |    0 |
  94. |    2 |    20 |    0 |
  95. |    3 |    30 |    0 |
  96. |    4 |    30 |    0 |
  97. |    5 |    40 |    0 |
  98. |    6 |    40 |    0 |
  99. +------+-------+------+
  100. 6 rows in set (0.00 sec)
  102. mysql> select * from view_rank; -- 尽管view_rank用select * 创建，但当时没有列c，所以无法得到c列的值
  103. +------+-------+
  104. | id   | score |
  105. +------+-------+
  106. |    1 |    10 |
  107. |    2 |    20 |
  108. |    3 |    30 |
  109. |    4 |    30 |
  110. |    5 |    40 |
  111. |    6 |    40 |
  112. +------+-------+
  113. 6 rows in set (0.00 sec)

:::warning 注意：MySQL 中的视图都是虚拟表。不像 Oracle 可以物化成真实存在的表。每次查询视图，实际上还是去查询的原来的表，只是查询的规则是在视图创建时经过定义的。 :::

视图的算法

通过 show create table 命令查看视图定义的时候，可以看到一个字段：ALGORITHM，这个是用来定义视图算法的，视图的算法（ALGORITHM）有三种方式：

  • UNDEFINED：默认方式，由 MySQL 来判断使用下面的哪种算法。
  • MERGE：每次通过物理表查询得到结果，把结果 MERGE（合并）起来返回。
  • TEMPTABLE：产生一张临时表，把数据放入临时表后，客户端再去临时表取数据（不会缓存）。

TEMPTABLE 特点：即使访问条件一样，第二次查询还是会去读取物理表中的内容，并重新生成一张临时表，并不会取缓存之前的表。（临时表是 Memory 存储引擎，默认放内存，超过配置大小放磁盘）。

当查询有一个较大的结果集时，使用 TEMPTABLE 可以快速的结束对该物理表的访问，从而可以快速释放这张物理表上占用的资源。然后客户端可以对临时表上的数据做一些耗时的操作，而不影响原来的物理表。

所以一般我们使用 UNDEFINED，由 MySQL 自己去判断。

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/nzk6x8 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。