【MySQL技术内幕】21-约束

扫码关注公众号:Java 技术驿站

发送:vip
将链接复制到本浏览器,永久解锁本站全部文章

【公众号:Java 技术驿站】 【加作者微信交流技术,拉技术群】

1、数据完整性

关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束( constraint)机制,该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说,数据完整性有以下三种形式:
实体完整性保证表中有一个主键。在 InnoDB存储引擎表中,用户可以通过定义Primary Key或 Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。
域完整性保证数据每列的值满足特定的条件。在 InnoDB存储引擎表中,域完整性可以通过以下几种途径来保证:

  • 选择合适的数据类型确保一个数据值满足特定条件。
  • 外键( Foreign Key)约束。
  • 编写触发器。
  • 还可以考虑用 DEFAULT约束作为强制域完整性的一个方面。

参照完整性保证两张表之间的关系。 InnoDB存储引擎支持外键,因此允许用户定义外键以强制参照完整性,也可以通过编写触发器以强制执行。
对于 InnoDB存储引擎本身而言,提供了以下几种约束:

  • Primary Key
  • Unique Key
  • Foreign Key
  • Default
  • NOT NULL

2、约束的创建和查找

约束的创建可以采用以下两种方式:

  • 表建立时就进行约束定义
  • 利用 ALTER TABLE命令来进行创建约束

对 Unique Key(唯一索引)的约束,用户还可以通过命令 CREATE UNIQUE INDEX来建立。对于主键约束而言,其默认约束名为PRIMARY。而对于 Unique Key约束而言,默认约束名和列名一样,当然也可以人为指定 Unique Key约束的名字。 foreign Key约束
似乎会有一个比较神秘的默认名称。下面是一个简单的创建表的语句,表上有一个主键和一个唯一键:

    mysql> create table u(
        -> id int,
        -> name varchar(20),
        -> id_card char(18),
        -> primary key(id),
        -> unique key(name));
    Query OK, 0 rows affected (0.09 sec)

    mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS
        -> where table_schema='mytest' and table_name='u';
    +-----------------+-----------------+
    | constraint_name | constraint_type |
    +-----------------+-----------------+
    | PRIMARY         | PRIMARY KEY     |
    | name            | UNIQUE          |
    +-----------------+-----------------+
    2 rows in set (0.02 sec)

可以看到,约束名就如之前所说的,主键的约束名为 PRIMARY,唯一索引的默认约束名与列名相同。当然用户还可以通过 ALTER TABLE来创建约束,并且可以定义用户所希望的约束名,如下面这个例子:

    mysql> alter table u add unique keyuk_id_card (id_card);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u';
    +-----------------+-----------------+
    | constraint_name | constraint_type |
    +-----------------+-----------------+
    | PRIMARY         | PRIMARY KEY     |
    | name            | UNIQUE          |
    | keyuk_id_card   | UNIQUE          |
    +-----------------+-----------------+
    3 rows in set (0.01 sec)

接着来看 Foreign Key的约束。为了创建 Foreign Key,用户必须创建另一张表,例如在下面的示例中创建表p

    mysql> create table p(
        -> id int,
        -> u_id int,
        -> primary key(id),
        -> foreign key(u_id) references p(id));
    Query OK, 0 rows affected (0.03 sec)

    mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='p';
    +-----------------+-----------------+
    | constraint_name | constraint_type |
    +-----------------+-----------------+
    | PRIMARY         | PRIMARY KEY     |
    | p_ibfk_1        | FOREIGN KEY     |
    +-----------------+-----------------+
    2 rows in set (0.00 sec)

在上面的例子中,通过 information schema架构下的表 TABLE_CONSTRAINTS来查看当前 MySQL库下所有的约束信息。对于 Foreign Key的约束的命名,用户还可以通过查看表 REFERENTIAL_CONSTRAINTS,并且可以详细地了解外键的属性,如:

    mysql> select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='mytest'\G
    *************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: mytest
              CONSTRAINT_NAME: p_ibfk_1
    UNIQUE_CONSTRAINT_CATALOG: def
     UNIQUE_CONSTRAINT_SCHEMA: mytest
       UNIQUE_CONSTRAINT_NAME: PRIMARY
                 MATCH_OPTION: NONE
                  UPDATE_RULE: RESTRICT
                  DELETE_RULE: RESTRICT
                   TABLE_NAME: p
        REFERENCED_TABLE_NAME: p
    1 row in set (0.00 sec)

3、约束和索引的区别

在前面已经看到 Primary Key和 Unique Key的约束,有人不禁会问:这不就是通常创建索引的方法吗?那约束和索引有什么区别呢?
的确,当用户创建了一个唯一索引就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的,约束更是一个逻辑的概念,用来保证数据的完整性,而索引是一个数据结构,既有逻辑上的概念,在数据库中还代表着物理存储的方式。

4、对错误数据的约束

在某些默认设置下, MySQL数据库允许非法的或不正确的数据的插入或更新,又或者可以在数据库内部将其转化为一个合法的值,如向 NOT NULL的字段插人一个NULL值, My SQL数据库会将其更改为0再进行插入,因此数据库本身没有对数据的正确性进行约束。例如:

    mysql> show variables like 'sql_mode';
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Variable_name | Value                                                                                                                                     |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> set sql_mode='';
    Query OK, 0 rows affected, 1 warning (0.01 sec)

    mysql> create table a(
        -> id int not null,
        -> date date not null);
    Query OK, 0 rows affected (0.03 sec)

    mysql> insert into a select null,'2018-02-30';
    Query OK, 1 row affected, 2 warnings (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 2

    mysql> show warnings\G
    *************************** 1. row ***************************
      Level: Warning
       Code: 1048
    Message: Column 'id' cannot be null
    *************************** 2. row ***************************
      Level: Warning
       Code: 1264
    Message: Out of range value for column 'date' at row 1
    2 rows in set (0.00 sec)

在上述例子中,首先向 NOT NULL的列插入了一个NULL值,同时向列date插入了一个非法日期'2009-02-30。“奇怪”的是 MySQL数据库并没有报错,而是显示了警告( warning)。如果用户想通过约束对于数据库非法数据的插入或更新,即 MySQL数据库提示报错而不是警告,那么用户必须设置参数 sql mode,用来严格审核输入的参数,如:

    mysql> insert into a select null,'2018-02-30';
    ERROR 1048 (23000): Column 'id' cannot be null
    mysql> insert into a select 1,'2018-02-30';
    ERROR 1292 (22007): Incorrect date value: '2018-02-30' for column 'date' at row 1

5、ENUM和SET约束

MySQL数据库不支持传统的 CHECK约束,但是通过ENUM和SET类型可以解决部分这样的约束需求。例如表上有一个性别类型,规定域的范围只能是male或 female,在这种情况下用户可以通过ENUM类型来进行约束

    mysql> create table a(
        -> id int,
        -> sex enum('male','female'));
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into a select 1,'female';
    Query OK, 1 row affected (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 0

    mysql> insert into a select 2,'bi';
    ERROR 1265 (01000): Data truncated for column 'sex' at row 1

这次对非法的输入值进行了约東,但是只限于对离散数值的约束,对于传统CHECK约束支持的连续值的范围约束或更复杂的约,ENUM和SET类型还是无能为力,这时用户需要通过触发器来实现对于值域的约束。

6、触发器与约束

完整性约束通常也可以使用触发器来实现,因此在了解数据完整性前先对触发器来做一个了解。
触发器的作用是在执行 INSERT、 DELETE和 UPDATE命令之前或之后自动调用SQL命令或存储过程。MSQL5.0对触发器的实现还不是非常完善,限制比较多,而从My SQL5.1开始触发器已经相对稳定,功能也较之前有了大幅的提高。
创建触发器的命令是 CREATE TRIGGER,只有具备 Super权限的 MySQL数据库用户才可以执行这条命令:
2020011020021\_1.png

最多可以为一个表建立6个触发器,即分别为 INSERT、 UPDATE、 DELETE的BEFORE和 AFTER各定义一个。 BEFORE和 AFTER代表触发器发生的时间,表示是在每行操作的之前发生还是之后发生。当前 MySQL数据库只支持 FOR EACH ROW的触发
方式,即按每行记录进行触发,不支持像DB2的 FOR EACH STATEMENT的触发方式通过触发器,用户可以实现 MySQL数据库本身并不支持的一些特性,如对于传统CHECK约束的支持,物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。
假设有张用户消费表,每次用户购买一样物品后其金额都是减的,若这时有“不怀好意”的用户做了类似减去一个负值的操作,这样用户的钱没减少反而会不断增加,如:

    mysql> create table usercash(
        -> userid int not null,
        -> cash int unsigned not null);
    Query OK, 0 rows affected (0.03 sec)

    mysql> insert into usercash select 1,1000;
    Query OK, 1 row affected (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 0

    mysql> update usercash set cash=cash-(-20) where userid=1;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

上述运行的SQL语句对数据库来说没有任何问题,都可以正常的运行,不会报错。
但是从业务的逻辑上来说,这是绝对错误的。因为消费总是意味着减去一个正值,而不是负值,所以这时要通过触发器来约束这个逻辑行为,可以进行如下设置:

    mysql> create table usercash_err_log(
        -> userid int not null,
        -> old_cash int unsigned not null,
        -> new_cash int unsigned not null,
        -> user varchar(30),
        -> time datetime);
    Query OK, 0 rows affected (0.02 sec)

    mysql> delimiter $$
    mysql> create trigger tgr_usercash_update before update on usercash
        -> for each row
        -> begin
        -> if new.cash-old.cash > 0 then
        -> insert into usercash_err_log
        -> select old.userid,old.cash,new.cash,user(),now();
        -> set new.cash = old.cash;
        -> end if;
        -> end;
        -> $$
    Query OK, 0 rows affected (0.05 sec)

    mysql> delimiter ;

上述例子首先创建了一张表 usercash_err_log来记录错误数值更新的日志,然后创建了进行约束操作的触发器 tgr usercash update,其类型为 BEFORE。触发器首先判断新、旧值之间的差值,在正常情况下消费总是减的,新值应该总是小于原来的值,因此大于原值的数据被判断为非法的输入,将cash值设定为原来的值,并将非法的数据更新插入表 usercash_err_log。再次运行上述的SQL语句:

    mysql> delete from usercash;
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into usercash select 1,1000;
    Query OK, 1 row affected (0.01 sec)
    Records: 1  Duplicates: 0  Warnings: 0

    mysql> update usercash set cash=cash-(-20) where userid=1;
    Query OK, 0 rows affected (0.01 sec)
    Rows matched: 1  Changed: 0  Warnings: 0

    mysql> select * from usercash;
    +--------+------+
    | userid | cash |
    +--------+------+
    |      1 | 1000 |
    +--------+------+
    1 row in set (0.00 sec)

    mysql> select * from usercash_err_log;
    +--------+----------+----------+----------------+---------------------+
    | userid | old_cash | new_cash | user           | time                |
    +--------+----------+----------+----------------+---------------------+
    |      1 |     1000 |     1020 | root@localhost | 2018-10-16 18:24:19 |
    +--------+----------+----------+----------------+---------------------+
    1 row in set (0.01 sec)

可以看到这次对于异常的数据更新通过触发器将其保存到了 usercash_err_log。此外该触发器还记录了操作该SQL语句的用户及时间。通过上述的例子可以发现,创建触发器也是实现约束的一种手段和方法。

7、外键约束

外键用来保证参照完整性, MySQL数据库的 MyISAM存储引擎本身并不支持外键,对于外键的定义只是起到一个注释的作用。而 InnodB存储引擎则完整支持外键约束。外键的定义如下:
2020011020021\_2.png
用户可以在执行 CREATE TABLE时就添加外键,也可以在表创建后通过 ALTER TABLE命令来添加。一个简单的外键的创建示例如下:

    mysql> create table parent(
        -> id int not null,
        -> primary key (id));
    Query OK, 0 rows affected (0.04 sec)

    mysql> create table child( id int, parent_id int, foreign key (parent_id) references parent(id));
    Query OK, 0 rows affected (0.03 sec)

般来说,称被引用的表为父表,引用的表称为子表。外键定义时的 ON DELETE和 ON UPDATE表示在对父表进行DELETE和UPDATE操作时,对子表所做的操作,可定义的子表操作有:

  • CASCADE
  • SET NULL
  • NO ACTION
  • RESTRICT

CASCADE表示当父表发生 DELETE或 UPDATE操作时,对相应的子表中的数据也进行 DELETE或 UPDATE操作。 SET NULL表示当父表发生 DELETE或 UPDATE操作时,相应的子表中的数据被更新为NULL值,但是子表中相对应的列必须允许为NULL值。 NO ACTION表示当父表发生 DELETE或 UPDATE操作时,抛出错误,不允许这类操作发生。 RESTRICT表示当父表发生 DELETE或 UPDATE操作时,抛出错误,不允许这类操作发生。如果定义外键时没有指定 ON DELETE或 ON UPDATE,RESTRICT就是默认的外键设置。
在其他数据库中,如 Oracle数据库,有一种称为延时检查( deferred check)的外键约束,即检查在SQL语句运行完成后再进行。而目前 MySQL数据库的外键约束都是即时检查( immediate check),因此从上面的定义可以看出,在 MySQL数据库中NO ACTION和RESTRICT的功能是相同的。
在 Oracle数据库中,对于建立外键的列,一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动地对该列加一个索引,这和 Microsoft SQL Server数据库的做法一样。因此可以很好地避免外键列上无索引而导致的死锁问题的产生。例如在上述的例子中,表 child创建时只定义了外键,并没有手动指定 parent id列为索引,但是通过命令 SHOW CREATE TABLE可以发现 InnoDB存储引擎自动为外键约束的列parent_id添加了索引:

    mysql> show create table child\G
    *************************** 1. row ***************************
           Table: child
    Create Table: CREATE TABLE `child` (
      `id` int(11) DEFAULT NULL,
      `parent_id` int(11) DEFAULT NULL,
      KEY `parent_id` (`parent_id`),
      CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

对于参照完整性约束,外键能起到一个非常好的作用。但是对于数据的导入操作时,外键往往导致在外键约束的检查上花费大量时间。因为 MySQL数据库的外键是即时检查的,所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查,如:

    mysql> SET foreign key checks =0;
    mysql> LOAD DATA ...
    mysql> SET foreign key checks =1;

来源:https://blog.csdn.net/shenchaohao12321/category_8075653.html

赞(0) 打赏
版权归原创作者所有,任何形式的转载请联系博主:daming_90:Java 技术驿站 » 【MySQL技术内幕】21-约束

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏