mysql视图之确保视图的一致性（with check option）操作详解

本文实例讲述了mysql视图之确保视图的一致性（with check option）操作。分享给大家供大家参考，具体如下：

我们有的时候，会创建一个视图来显示表的部分数据。我们知道，简单视图是的，因此可以更新通过视图不可见的数据，但是此更新会使的视图不一致。为了确保视图的一致性，在创建或修改视图时使用with check option可更新子句。我们来看下with check option可更新子句的语法结构：

create or replace view view_name 
as
 select_statement
 with check option;

我们要注意，将分号(;)放在with check option子句的末尾，而不是在select语句的末尾来定义视图。完事我们来尝试根据employees表创建一个名为vps的视图，以显示其职位为vp的员工，例如vp marketing和 vp sales：

create or replace view vps as
  select 
    employeenumber,
    lastname,
    firstname,
    jobtitle,
    extension,
    email,
    officecode,
    reportsto
  from
    employees
  where
    jobtitle like '%vp%';

接下来，我们使用以下语句从vps视图中查询数：

mysql> select * from vps;
+----------------+----------+-----------+--------------+-----------+----------------------+------------+-----------+
| employeenumber | lastname | firstname | jobtitle   | extension | email        | officecode | reportsto |
+----------------+----------+-----------+--------------+-----------+----------------------+------------+-----------+
|      1056 | hill   | mary   | vp sales   | x4611   | [e-mail] | 1     |   1002 |
|      1076 | firrelli | jeff   | vp marketing | x9273   | [e-mail] | 1     |   1002 |
+----------------+----------+-----------+--------------+-----------+----------------------+------------+-----------+
2 rows in set

因为vps是一个简单的视图，因此它是可更新的，所以，我们通过vps视图将一行员工数据信息插入：

insert into vps(employeenumber,firstname,lastname,jobtitle,extension,email,officecode,reportsto)
values(1703,'lily','bush','it manager','x9111','[e-mail]',1,1002);

我们要注意，新创建的员工通过vps视图不可见，因为她的职位是it经理，而不是vp。使用以下select语句来验证它：

select * from employees where employeenumber=1703;

执行上面语句，得到以下结果：

+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeenumber | lastname | firstname | extension | email         | officecode | reportsto | jobtitle       |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
|      1703 | bush   | lily   | x9111   | [e-mail] | 1     |   1002 | it manager      |
|      1702 | gerard  | martin  | x2312   | [e-mail]   | 4     |   1102 | sales rep      |
|      1625 | kato   | yoshimi  | x102   | [e-mail]    | 5     |   1621 | sales rep      |
|      1621 | nishi   | mami   | x101   | [e-mail]   | 5     |   1056 | sales rep      |

但这可能不是我们想要的，因为通过vps视图暴露vp员工，而不是其他员工，所以，为了确保视图的一致性，用户只能显示或更新通过视图可见的数据，则在创建或修改视图时使用with check option：

create or replace view vps as
  select 
    employeenumber,
    lastname,
    firstname,
    jobtitle,
    extension,
    email,
    officecode,
    reportsto
  from
    employees
  where
    jobtitle like '%vp%' 
with check option;

我们要注意在create or replace语句的结尾处加上with check option子句，完事再次通过vps视图将一行插入employees表中，如下所示：

insert into vps(employeenumber,firstname,lastname,jobtitle,extension,email,officecode,reportsto)
values(1704,'john','minsu','it staff','x9112','[e-mail]',1,1703);

这时mysql会拒绝插入并发出以下错误消息：

error code: 1369 - check option failed 'luyaran.vps'

我们可以通过vps视图将一个职位为svp marketing的员工插入employees表，看看mysql是否允许这样做：

insert into vps(employeenumber,firstname,lastname,jobtitle,extension,email,officecode,reportsto)
values(1704,'john','minsu','svp marketing','x9112','[e-mail]',1,1076);

mysql发出1行受影响(query ok, 1 row affected)，我们可以通过根据vps视图查询数据来再次验证插入操作：

select * from vps;

如上查询结果所示，它的确按预期工作了：

mysql> select * from vps;
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
| employeenumber | lastname | firstname | jobtitle   | extension | email             | officecode | reportsto |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
|      1056 | hill   | mary   | vp sales   | x4611   | [e-mail]      | 1     |   1002 |
|      1076 | firrelli | jeff   | vp marketing | x9273   | [e-mail]      | 1     |   1002 |
|      1704 | minsu  | john   | svp marketing | x9112   | [e-mail] | 1     |   1076 |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
3 rows in set

好啦，本次记录就到这里了。