牛牛逼逼叉叉
菜单

每个开发人员都需要了解的一个SQL技巧

2015年09月03日,分类《mysql》,作者:
博客捐助

对于数据过滤而言CHECK约束已经算是相当不错了。然而它仍存在一些缺陷,比如说它们是应用到表上面的,但有的时候你可能希望指定一条约束,而它只在特定条件下才生效。

113604hj6iiskjl4bebbai

使用SQL标准的WITH CHECK OPTION子句就能完成这点,至少Oracle和SQL Server都实现了这个功能。下面是实现方式:

  1. CREATE TABLE books (
  2. id NUMBER(10) NOT NULL,
  3. title VARCHAR2(100 CHAR) NOT NULL,
  4. price NUMBER(10, 2) NOT NULL,
  5. CONSTRAINT pk_book PRIMARY KEY (id)
  6. );
  7. /
  8. CREATE VIEW expensive_books
  9. AS
  10. SELECT id, title, price
  11. FROM books
  12. WHERE price > 100
  13. WITH CHECK OPTION;
  14. /
  15. INSERT INTO books
  16. VALUES (1, '1984', 35.90);
  17. INSERT INTO books
  18. VALUES (
  19. 2,
  20. 'The Answer to Life, the Universe, and Everything',
  21. 999.90
  22. );

正如你看到的那样,expensive_books 是那些价格大于100块的书。这个视图只会返回第二本书:

  1. SELECT * FROM expensive_books;

上述查询的输出是:

  1. ID TITLE PRICE
  2. -- ----------------------------------------- -------
  3. 2 The Answer to Life, the Universe, and ... 999.9

不过由于我们使用了CHECK OPTION,我们还能防止用户往”昂贵的书籍”中插入那些廉价的。比如说,我们运行下这个查询:

  1. INSERT INTO expensive_books
  2. VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);

它是无法生效的。你会看到:

ORA-01402: view WITH CHECK OPTION where-clause violation

我们也无法将贵的书更新成便宜的:

  1. UPDATE expensive_books
  2. SET price = 9.99;

这个查询也会报出同样的ORA-01402错误。

WITH CHECK OPTION内联

如果你需要局部防止脏数据被插入到表中,你可以使用WITH CHECK OPTION的内联子句:

  1. INSERT INTO (
  2. SELECT *
  3. FROM expensive_books
  4. WHERE price > 1000
  5. WITH CHECK OPTION
  6. ) really_expensive_books
  7. VALUES (3, 'Modern Enterprise Software', 999.99);

上述查询同样也会导到ORA-01402错误。

使用SQL转换来生成特殊约束

CHECK OPTION对于已存储的视图非常有用,它使得那些无权直接访问底层表的用户能够获得正确的授权,而内联的CHECK OPTION主要是在应用的SQL中间转换层来进行动态SQL的转换。

这个可以通过jOOQ的SQL转换功能来完成,比如说,你可以在SQL语句中对某个表进行约束,从根本上阻止了非法DML的执行。如果你的数据库没有本地提供行级别的安全性的话,这也是一个实现多租户的不错的方式。



发表评论

电子邮件地址不会被公开。 必填项已用*标注

【上一篇】

让你远离云计算安全问题的18个小贴士

【下一篇】

在RHEL/CentOS 7.0中安装LAMP