如何创建MySQL5的视图

CHECK TABLE statement.

The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.

The tables named in the view definition must already exist.

You cannot associate a trigger with a view.

ORDER BY is allowed in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE, LOCK IN SHARE MODE, and PROCEDURE.

If you create a view and then change the query processing environment by changing system variables, that may affect the results you get from the view:

mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));

Query OK, 0 rows affected (0.00 sec)



mysql> SET NAMES 'latin1';

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT * FROM v;

 ------------------- --------------------- 

| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |

 ------------------- --------------------- 

| latin1            | latin1_swedish_ci   |

 ------------------- --------------------- 

1 row in set (0.00 sec)



mysql> SET NAMES 'utf8';

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT * FROM v;

 ------------------- --------------------- 

| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |

 ------------------- --------------------- 

| utf8              | utf8_general_ci     |

 ------------------- --------------------- 

1 row in set (0.00 sec)

The optional ALGORITHM clause is a MySQL extension to standard SQL. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if no ALGORITHM clause is present. The algorithm affects how MySQL processes the view.

For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.

For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.

For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.

A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

A view algorithm can be UNDEFINED three ways:

上一页 1 23 4 5 下一页

[打印] [关闭]

0

顶一下

相关文章

运用Mysql语句生成后门木马的具

Mysql数据库的安全配置、实用技

MySQL使用指南（一）

MySQL数据库中用GRANT语句增添

DBA解决MySQL服务器启动和关闭

提数据高分页效率

MySQL简易备份方法

如何安装并测试MySQL

如何恢复/修复MS SQL数据库的MD

通过查询分析器的调试功能进行

上一篇： 修改MySQL的默认密码
下一篇： 在FreeBSD下安装mysql5的注意点