【MySQL技术内幕】21-约束

最新推荐文章于 2023-06-10 22:04:51 发布

一直不懂

最新推荐文章于 2023-06-10 22:04:51 发布

阅读量384

点赞数

分类专栏： MySQL技术内幕

原文链接：https://book.douban.com/subject/24708143/

版权

MySQL技术内幕专栏收录该内容

64 篇文章 35 订阅

订阅专栏

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数据库用户才可以执行这条命令:

最多可以为一个表建立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存储引擎则完整支持外键约束。外键的定义如下:

用户可以在执行 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;

一直不懂

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【MySQL技术内幕】21-约束

1、数据完整性关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束( constraint)机制,该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说,数据完整性有以下三种形式:实体完整性保证表中有一个主键。在 InnoDB存储引擎表中,用户可以通过定义P...
复制链接

扫一扫