做视频网站视频放在哪里找/专业恶意点击软件

约束

1 数据完整性

  关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供约束(constraint)机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说，数据完整性有以下三种形式:

• **1.实体完整性**保证表中有一个主键。在InnoDB存储引擎表中，用户可以通过定义Primary Key和Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。
• **2.域完整性**保证数据每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径保证。
  • 选择合适的数据类型确保一个数据值满足特定条件
  • 外键(Foreign Key)约束
  • 编写触发器
  • 还可以考虑用default约束作为强制域完整性的一个方面
• **3.参照完整性**保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于InnoDB引擎本身，提供了以下几种约束：

• Primary Key
• Unique Key
• Foregin Key
• Default
• NOT NULL

2 约束的创建和查找

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

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

  对于Unique Key(唯一索引)的约束，用户还可以通过命令create unique index来建立。

Primary Key

## 1.创建表，id是Primary Key
mysql> create table constraint_test(-> id int,-> primary key (id)-> ) engine=innodb;
Query OK, 0 rows affected (0.05 sec)## 2.查询元数据信息可以发现
## Primary Key的约束名是Primary
mysql> select constraint_name,constraint_type-> from information_schema.table_constraints-> where table_schema='zxy' and table_name='constraint_test'\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY

Unique Key

• 方式一：alter table...add unique key... 添加

  # 1.添加字段name
  mysql> alter table constraint_test add column name varchar(25);
  Query OK, 0 rows affected (0.08 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  # 2.对name添加unique key，并指定约束名为uk_name
  mysql> alter table constraint_test add unique key uk_name (name);
  Query OK, 0 rows affected (0.09 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  # 3.查询元数据
  mysql> select constraint_name,constraint_type-> from information_schema.table_constraints-> where table_schema='zxy' and table_name='constraint_test'\G;
  *************************** 1. row ***************************
  constraint_name: PRIMARY
  constraint_type: PRIMARY KEY
  *************************** 2. row ***************************
  constraint_name: uk_name
  constraint_type: UNIQUE

• 方式二：create unique index ...添加

  mysql> alter table constraint_test add column sex varchar(25) ;
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0mysql> create unique index un_sex on constraint_test(sex);
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0mysql> select constraint_name,constraint_type-> from information_schema.table_constraints-> where table_schema='zxy' and table_name='constraint_test'\G;
  *************************** 1. row ***************************
  constraint_name: PRIMARY
  constraint_type: PRIMARY KEY
  *************************** 2. row ***************************
  constraint_name: uk_name
  constraint_type: UNIQUE
  *************************** 3. row ***************************
  constraint_name: un_sex
  constraint_type: UNIQUE
  3 rows in set (0.00 sec)
  

Foregin Key

## 1.创建外键表constraint_foreign
# id为primary key
# test_id为constraint_test的外键
mysql> create table constraint_foreign(-> id int ,-> test_id int ,-> name varchar(25) ,-> primary key (id),-> foreign key (test_id) references constraint_test(id)-> );# 2.查看元数据信息
mysql> select constraint_name,constraint_type-> from information_schema.table_constraints-> where table_schema='zxy' and table_name='constraint_foreign'\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: constraint_foreign_ibfk_1
constraint_type: FOREIGN KEY
2 rows in set (0.00 sec)# 3.用户还可以通过information_constraints查看表的外键属性
mysql> select *-> from information_schema.referential_constraints-> where constraint_schema='zxy' and table_name = 'constraint_foreign'\G;
*************************** 1. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: zxyCONSTRAINT_NAME: constraint_foreign_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: defUNIQUE_CONSTRAINT_SCHEMA: zxyUNIQUE_CONSTRAINT_NAME: PRIMARYMATCH_OPTION: NONEUPDATE_RULE: RESTRICTDELETE_RULE: RESTRICTTABLE_NAME: constraint_foreignREFERENCED_TABLE_NAME: constraint_test
1 row in set (0.00 sec)

3 约束和索引的区别

  我们可以看到在使用Primary Key和Unique Key的时候，就是正常创建索引的方法。那么约束和索引有什么区别呢？

  用户创建一个唯一索引就是创建了一个唯一的约束。但是约束和索引的概念有所不同，约束更像是一个逻辑的概念，用户保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

4 对错误数据的约束

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

  但是在插入非法的数据或不正确的数据的时候，会根据数据库的sql_mode来判断是报错还是警告。

# 1.查看sql_mode类型
mysql> select @@sql_mode\G;
*************************** 1. row ***************************
@@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)# 2.查看当前会话的sql_mode类型
mysql> select @@session.sql_mode\G;
*************************** 1. row ***************************
@@session.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)# 3.查看全局的sql_mode类型
mysql> select @@global.sql_mode\G;
*************************** 1. row ***************************
@@global.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)

  此时设置有sql_mode，因此插入非法或不正确的数据会直接报错

mysql> create table constraint_null (-> id int not null,-> date date not null-> );
Query OK, 0 rows affected (0.04 sec)mysql> insert into constraint_null select null,'2023-02-30';
ERROR 1048 (23000): Column 'id' cannot be null

  设置sql_mode为空，此时向NOT NULL的列插入了一个NULL值，同时向date列插入一个不合法的日期。这时候就没有报错信息，只是显示了警告(warning)。通过指令show warnings可以查看警告的信息。

mysql> set session sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> insert into constraint_null select null,'2023-02-30';
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 2mysql> show warnings\G;
*************************** 1. row ***************************Level: WarningCode: 1048
Message: Column 'id' cannot be null
*************************** 2. row ***************************Level: WarningCode: 1264
Message: Out of range value for column 'date' at row 1
2 rows in set (0.00 sec)mysql> select * from constraint_null;
+----+------------+
| id | date       |
+----+------------+
|  0 | 0000-00-00 |
+----+------------+
1 row in set (0.00 sec)

5 ENUM和SET约束

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

mysql> create table enum_test (-> id int ,-> sex enum('male','female')-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into enum_test values(1,'male'),(2,'female');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> insert into enum_test values(1,'test');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

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

6 触发器与约束

  触发器的作用是在执行INSERT、UPDATE、DELETE命令前后自动调用SQL命令或存储过程。

  触发器的创建命令是CREATE TRIGGER，只有具备Super权限的MySQL数据库用户才可以执行这条命令。

CREATE
[DEFINER = {user | current_user}]
TRIGGER trigger_name DEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW 

  最多可以为一个表建立6个触发器，即分别是INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。BEFORE和AFTER代表触发器发生的时间，表示在每行操作的之前还是之后发生。MySQL只支持行级触发器for each row,不支持语句级触发器for each statement。

  通过触发器，用户可以实现MySQL数据库本身并不支持的一些特性，比如对于传统check约束的支持，物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。

案例

  加入有张用户消费表，每次用户购买一样物品后其金额都是减的，若这时残生了负值的操作，这样用户的金额不减反增

mysql> create table usercash(-> userid int not null,-> cash int unsigned not null-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into usercash values(1,1000);
Query OK, 1 row affected (0.01 sec)mysql> update usercash-> set cash=cash-(-20)-> where userid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from usercash;
+--------+------+
| userid | cash |
+--------+------+
|      1 | 1020 |
+--------+------+
1 row in set (0.00 sec)

  如上所示，SQL语句对于数据库来说是没有任何问题的，可以正常的运行，不会报错。但是从业务逻辑上讲这是绝对错误的。所以可以通过触发器来约束这个行为：

# 1.创建存储错误业务数据表
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)# 2.创建触发器
mysql> delimiter &&
mysql> create trigger trigger_user_cash_updatebefore 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.01 sec)
mysql> delimiter ;# 3.业务数据负数情况
mysql> update usercash-> set cash = cash - (-20)-> where userid = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0# 4.查看错误业务数据是否影响业务
mysql> select * from usercash;
+--------+------+
| userid | cash |
+--------+------+
|      1 | 1020 |
+--------+------+
1 row in set (0.00 sec)# 5.查看错误日志表
mysql> select * from usercash_err_log;
+--------+----------+----------+----------------+---------------------+
| userid | old_cash | new_cash | user           | time                |
+--------+----------+----------+----------------+---------------------+
|      1 |     1020 |     1040 | root@localhost | 2023-03-09 16:44:36 |
+--------+----------+----------+----------------+---------------------+
1 row in set (0.00 sec)

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

7 外键约束

  外键用来保证参照完整性，MySQL数据库的MyISAM存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...)
REFERENCES tb1_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
referece_optio:
RESTRICT | CASCADE |SET NULL|NO ACTION

  用户可以在执行CREATE TABLE时就添加外键，也可以在表创建后ALTER TABLE命令来添加。一个简单的外键的创建示例如下：

mysql> create table parent(-> id int not null,-> primary key (id)-> ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)mysql> create table child(-> id int ,-> parent_id int,-> foreign key (parent_id) references parent(id)-> ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

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

• CASCADE

  CASCADE表示当父表发生DELETE或UPDATE操作时，对相应的子表中数据进行DELETE或UPDATE操作。

• SET NULL

  SET NULL表示当父表发生DELETE或UPDATE操作时，相应的子表中的数据被更新为NULL值。

• NO ACTION

  NO ACTION表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生

• RESTRICT

  RESTRICT表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。

  如果定义外键没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。

  在其他数据库中，如Oracle数据库，有一种称为延时检查(deferred check)的外键约束，即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查(immediate check)，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT功能都是相同的。

  在Oracle数据库中，对于建立外键的列，一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动对该列加一个索引，这和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=utf8
1 row in set (0.00 sec)

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

mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
...
加载数据
...
mysql> set foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)