Eine ausführliche Analyse der 6 gängigen Einschränkungstypen in MySQL-MySQL-Tutorial-php.cn

Eine ausführliche Analyse der 6 gängigen Einschränkungstypen in MySQL

Die wörtliche Bedeutung von Einschränkungen besteht darin, festzulegen oder einzuschränken, wie etwas getan werden soll. In MySQL dienen Einschränkungen dazu, Regeln für die Daten in der Datentabelle anzugeben, dh die Daten einzuschränken, um die Zuverlässigkeit sicherzustellen, z. B. nicht In einer bestimmten Spalte dürfen Nullwerte angezeigt werden. In der Praxis werden wir auf die folgenden Arten von Einschränkungen stoßen.

NOT NULL: Stellen Sie sicher, dass die Spalte keinen NULL-Wert haben darf

NOT NULL

CHECK ：确保列中的值满足特定条件
UNIQUE ：确保一列中的所有值都不同
PRIMARY KEY： NOT NULL和UNIQUE组合，唯一标识表中的每一行
FOREIGN KEY ：外键约束
DEFAULT ：如果未指定值，则为列设置默认值

【相关推荐：mysql视频教程】

约束

1.NULL

MySQL中通过使用NOT NULL确保列中不会出现Null值，创建表时候格式如下：

mysql> create table user(name varchar(255)not null);
Query OK, 0 rows affected (0.06 sec)

Nach dem Login kopieren

如果试图插入一个null值，则会抛出异常。

mysql> insert user values(null);
ERROR 1048 (23000): Column &#39;name&#39; cannot be null

Nach dem Login kopieren

或者在现有表上新增NOT NULL约束。

mysql> alter table user modify name varchar(255) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Nach dem Login kopieren

删除NOT NULL约束。

mysql> alter table user modify name varchar(255)  null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Nach dem Login kopieren

2.CHECK

如果想在列上定义条件约束，可以使用CHECK，比如下面，强制让年龄字段大于18，小于80，否则将会报错。

mysql> create table user(age int(11) check(age>18 and age <80));
Query OK, 0 rows affected, 1 warning (0.06 sec)

Nach dem Login kopieren

插入测试，可以发现9、81在插入的时候抛出异常。

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

mysql> insert user values(19);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(81);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql>

Nach dem Login kopieren

也可以进行多列约束，如年龄必须大于18，城市必须为中国。

mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city=&#39;中国&#39;));
Query OK, 0 rows affected, 1 warning (0.05 sec)

Nach dem Login kopieren

插入测试。

mysql> insert user values(81,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(8,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(20,&#39;中国1&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(85,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(9,&#39;中国&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

Nach dem Login kopieren

还可以让列值必须在指定集合中，如性别必须在男、女、未知、人妖集合中。

mysql> create table user(sex varchar(255) check (sex in (&#39;男&#39;,&#39;女&#39;,&#39;未知&#39;,&#39;人妖&#39;)));
Query OK, 0 rows affected (0.05 sec)

Nach dem Login kopieren

插入测试。

mysql> insert user values("男");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values("女");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values("人妖");
Query OK, 1 row affected (0.00 sec)

Nach dem Login kopieren

为约束命名并删除约束。

mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values(5);
ERROR 3819 (HY000): Check constraint &#39;CHK_AGE&#39; is violated.

mysql> alter table user drop check CHK_AGE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)

Nach dem Login kopieren

但是，这样的写法你见过吗？

猜猜下面的作用是什么。

这其实是一个case when条件判断，让其仅仅可以插入>=18，或者是在0-10之间的数。

CREATE TABLE `user` (`age` int(11) CHECK 
(((case when (`age` >=18) then 1 
else 
(case when age<10 and age >0 then 1 else 2 end) end) =1)));

Nach dem Login kopieren

3.UNIQUE

UNIQUE约束确保列中的没有重复的值，UNIQUE和 PRIMARY KEY约束都为一列值的唯一性提供保障，但是UNIQUE每个表可以出现多次，而PRIMARY KEY只能出现一个。

如下面name字段不能重复。

mysql> create table user (name varchar(255),unique(name));
Query OK, 0 rows affected (0.07 sec)

Nach dem Login kopieren

插入测试。

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name&#39;mysql>

Nach dem Login kopieren

对此约束进行起名，并删除。

mysql> create table user (name varchar(255),constraint name_un unique(name));
Query OK, 0 rows affected (0.07 sec)

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name_un&#39;
mysql> alter table user drop index name_un;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

Nach dem Login kopieren

插入后可以用以下语句查看创建语句。

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  UNIQUE KEY `name_un` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Nach dem Login kopieren

要删除UNIQUE约束，可以使用DROP INDEX或ALTER TABLE语句：

mysql> DROP INDEX name_un ON user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Nach dem Login kopieren

在现有表上添加。

mysql> alter table user add constraint name_un unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Nach dem Login kopieren

4.PRIMARY KEY

通常每个表中包含一个用于唯一标识每一行的值，这个列就被称为PRIMARY KEY。

mysql> create table user (id int(11) ,age int(11),primary key (id));
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert user values(1,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(1,2);
ERROR 1062 (23000): Duplicate entry &#39;1&#39; for key &#39;user.PRIMARY&#39;mysql>

Nach dem Login kopieren

5.FOREIGN KEY

FOREIGN KEY用于约束表中的一个字段必须是另一个表中某个字段所存在的值，但是在另一个表中，这个列不一定是主键，但必须是唯一性索引，否则会创建失败。

比如orders表中的userId必须参考user表中的id，如果插入的userId在user表中不存在，则无法插入。

mysql> create table orders (id int(11) primary key ,userId int(11) ,  FOREIGN KEY (userId) REFERENCES user(id) );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert orders values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))

mysql> insert orders values(1,1);
Query OK, 1 row affected (0.01 sec)

Nach dem Login kopieren

但是存在一个问题，如果主表(user)中记录被删除或者更新，那orders中的记录该怎么办？，如下面的例子，可以发现直接报错了。

mysql> update user set id =2 where id =1;

Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)

Nach dem Login kopieren

MySQL提供了几个约束可以帮助我们解决这类问题，比如在user表更新时，orders也相继更新。

RESTRICT：如果子表中有记录，则拒绝更新或删除父表中的记录。
CASCADE：更新或删除父表中的记录时，自动更新或删除子表中的记录。
SET NULL：在更新或删除父表记录时，将子表中字段的值设置为空。

可以发现，默认采用的是RESTRICT，下面来修改一下，让在更新时候也同样更新，在删除时候设置null。

mysql> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade on
delete set null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

Nach dem Login kopieren

测试更新

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> insert orders values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update user set id =2 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
+----+--------+
1 row in set (0.01 sec)

Nach dem Login kopieren

测试删除。

mysql> delete from user where id =2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |   NULL |
+----+--------+
1 row in set (0.00 sec)

Nach dem Login kopieren

6.DEFAULT

DEFAULTCHECK: Stellen Sie sicher, dass der Wert in der Spalte bestimmte Bedingungen erfüllt

UNIQUE: Stellen Sie sicher, dass alle Werte in einer Spalte unterschiedlich sind

PRIMARY KEY: Kombination aus NOT NULL und UNIQUE, eindeutiger Bezeichner für jede Zeile in der Tabelle
FOREIGN KEY: Fremdschlüsseleinschränkung
DEFAULT: Wenn kein Wert angegeben ist , legen Sie den Standardwert für die Spalte fest li>
【Verwandte Empfehlungen: MySQL-Video-Tutorial
】

Einschränkungen

1.NULL

Verwenden Sie in MySQL < code>NOT NULL. Stellen Sie sicher, dass beim Erstellen der Tabelle keine Nullwerte angezeigt werden. Das Format lautet wie folgt:

mysql> create table user(age int(11) default 18);
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values();
Query OK, 1 row affected (0.02 sec)

mysql> select * from user;
+------+
| age  |
+------+
|   18 |
+------+
1 row in set (0.00 sec)

Nach dem Login kopieren

Wenn Sie es versuchen Um einen Nullwert einzufügen, wird eine Ausnahme ausgelöst. rrreee🎜Oder fügen Sie eine neue NOT NULL-Einschränkung für die vorhandene Tabelle hinzu. 🎜rrreee🎜Entfernen Sie die NOT NULL-Einschränkung. 🎜rrreee
2.CHECK
🎜Wenn Sie bedingte Einschränkungen für die Spalte definieren möchten, können Sie CHECK wie folgt verwenden: um zu erzwingen, dass das Altersfeld größer als 18 und kleiner als 80 ist, andernfalls wird ein Fehler gemeldet. 🎜rrreee🎜Einfügetest, Sie können feststellen, dass 9 und 81 beim Einfügen Ausnahmen auslösen. 🎜rrreee🎜Sie können auch mehrspaltige Einschränkungen durchführen, z. B. das Alter muss größer als 18 sein und die Stadt muss China sein. 🎜rrreee🎜Test einfügen. 🎜rrreee🎜 Sie können auch angeben, dass der Spaltenwert im angegebenen Satz enthalten sein muss, beispielsweise muss das Geschlecht in den Sätzen „männlich“, „weiblich“, „unbekannt“ und „Shemale“ enthalten sein. 🎜rrreee🎜Test einfügen. 🎜rrreee🎜Benennen Sie Einschränkungen und löschen Sie Einschränkungen. 🎜rrreee🎜Aber haben Sie es jemals so geschrieben gesehen? 🎜🎜Raten Sie mal, was die Funktion unten ist. 🎜🎜Dies ist tatsächlich ein Fall einer bedingten Beurteilung, die nur das Einfügen von >=18 oder einer Zahl zwischen 0 und 10 ermöglicht. 🎜rrreee
3.UNIQUE
🎜UNIQUE-Einschränkung stellt sicher, dass die Spalte keine doppelten Werte enthält , Sowohl die Einschränkungen UNIQUE als auch PRIMARY KEY bieten Garantien für die Eindeutigkeit eines Spaltenwerts, aber UNIQUE kann in jeder Tabelle mehrmals vorkommen, während PRIMARY KEY erscheinen. 🎜🎜Zum Beispiel kann das Namensfeld unten nicht wiederholt werden. 🎜rrreee🎜Test einfügen. 🎜rrreee🎜Nennen Sie diese Einschränkung und löschen Sie sie. 🎜rrreee🎜Nach dem Einfügen können Sie die folgende Anweisung verwenden, um die Erstellungsanweisung anzuzeigen. 🎜rrreee🎜Um eine UNIQUE-Einschränkung zu löschen, können Sie die Anweisung DROP INDEX oder ALTER TABLE verwenden: 🎜rrreee🎜add auf einer vorhandenen Tabelle. 🎜rrreee
4.PRIMARY KEY
🎜Normalerweise enthält jede Tabelle einen Wert, der jede Zeile eindeutig identifiziert, und diese Spalte wird PRIMARY KEY genannt. 🎜rrreee
5.FOREIGN KEY
🎜FOREIGN KEY wird verwendet, um einzuschränken, dass ein Feld in der Tabelle vorhanden sein muss sei ein anderer Der Wert, der in einem bestimmten Feld in einer Tabelle vorhanden ist, aber in einer anderen Tabelle ist diese Spalte nicht unbedingt der Primärschlüssel, sondern muss ein eindeutiger Index sein, sonst schlägt die Erstellung fehl. 🎜🎜Zum Beispiel muss die Benutzer-ID in der Bestelltabelle auf die ID in der Benutzertabelle verweisen. Wenn die eingefügte Benutzer-ID nicht in der Benutzertabelle vorhanden ist, kann sie nicht eingefügt werden. 🎜rrreee🎜Aber es gibt ein Problem, wenn die Datensätze in der Haupttabelle (Benutzer) gelöscht oder aktualisiert werden. Was soll dann mit den Datensätzen in Bestellungen geschehen? , wie im Beispiel unten, können Sie feststellen, dass ein Fehler direkt gemeldet wird. 🎜rrreee🎜MySQL bietet mehrere Einschränkungen, die uns bei der Lösung dieser Art von Problemen helfen. Wenn beispielsweise die Benutzertabelle aktualisiert wird, werden auch Bestellungen nacheinander aktualisiert. 🎜
🎜🎜RESTRICT: Weigern Sie sich, Datensätze in der übergeordneten Tabelle zu aktualisieren oder zu löschen, wenn Datensätze in der untergeordneten Tabelle vorhanden sind. 🎜🎜🎜CASCADE: Datensätze in der untergeordneten Tabelle automatisch aktualisieren oder löschen, wenn Datensätze in der übergeordneten Tabelle aktualisiert oder gelöscht werden. 🎜🎜🎜SET NULL: Setzt den Wert des Felds in der untergeordneten Tabelle auf Null, wenn Datensätze der übergeordneten Tabelle aktualisiert oder gelöscht werden. 🎜
🎜Sie können feststellen, dass RESTRICT standardmäßig verwendet wird. Ändern Sie es so, dass es beim Aktualisieren auch aktualisiert wird und beim Löschen auf Null gesetzt wird. 🎜rrreee🎜Update testen 🎜rrreee🎜Löschung testen. 🎜rrreee
6.DEFAULT
🎜DEFAULT-Einschränkung wird verwendet, um einen Standardwert für eine Spalte festzulegen Wenn einem Feld ein Wert zugewiesen wird, fügt das System automatisch einen Standardwert für dieses Feld ein. Keine Zuweisung bedeutet, dass dieses Feld beim Einfügen von Daten nicht angegeben wird am Ende. 🎜rrreee🎜🎜Originaladresse: https://juejin.cn/post/7000352993572814885🎜🎜Autor: i Listen to the Wind Passing Night🎜🎜🎜Weitere Programmierkenntnisse finden Sie unter: 🎜Programmiervideo🎜! ! 🎜
Das obige ist der detaillierte Inhalt vonEine ausführliche Analyse der 6 gängigen Einschränkungstypen in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!