Xem định nghĩa View trong MySQL với SHOW CREATE VIEW

Lệnh SHOW CREATE VIEW view_name hiển thị câu lệnh CREATE VIEW gốc dùng để tạo view đó.

Tạo bảng gốc

mysql> CREATE TABLE DemoTable (StudentName VARCHAR(20));
Query OK, 0 rows affected (0.56 sec)

mysql> INSERT INTO DemoTable VALUES('Chris'), ('Robert'), ('David');
Query OK, 3 rows affected (0.14 sec)

Tạo View

mysql> CREATE VIEW view_DemoTable AS SELECT StudentName FROM DemoTable;
Query OK, 0 rows affected (0.12 sec)

Xem định nghĩa View

mysql> SHOW CREATE VIEW view_DemoTable;

Kết quả (rút gọn):

+-------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View              | Create View                                                                                                                      | character_set_client | collation_connection |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_DemoTable    | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_DemoTable` AS SELECT `DemoTable`.`StudentName`... | utf8                 | utf8_general_ci      |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

Ứng dụng: Debug view, sao chép view sang database khác, kiểm tra thuật toán (ALGORITHM), quyền định nghĩa (DEFINER), security context.