MySQL教程
 MySQL Query查询 MySQL Insert记录 

MySQL 约束

MySQL 的约束用于指定允许或限制哪些值/数据将存储在表中的规则。它们提供了一种合适的方法来确保表内的数据准确性和完整性。它还有助于限制将插入表中的数据类型。如果约束和数据动作之间发生任何中断，则动作失败。

一、MySQL约束的类型

MySQL 中的约束分为两种类型：

  1. 列级约束：这些约束仅适用于限制特定列数据类型的单个列。
  2. 表级约束：这些约束应用于整个表，限制了整个表的数据类型。

 二、如何在 MySQL 中创建约束

我们可以在使用 CREATE TABLE 语句创建表期间定义约束。MySQL 还使用 ALTER TABLE 语句来指定现有表模式的情况下的约束。

语法

以下是在表中创建约束的语法：

CREATE TABLE new_table_name (  
    col_name1 datatype constraint,  
    col_name2 datatype constraint,  
    col_name3 datatype constraint,  
    .........  
);

三、MySQL 中常用的约束

以下是 MySQL 中最常用的约束：

  • NOT NULL非空约束
  • CHECK检测约束
  • DEFAULT默认值约束
  • PRIMARY KEY主键约束
  • AUTO_INCREMENT自增约束
  • UNIQUE唯一
  • INDEX索引约束
  • ENUM枚举约束
  • FOREIGN KEY外键约束

四、非空（NOT NULL）约束

此约束指定列不能有 NULL 或空值。下面的语句创建一个具有 NOT NULL 约束的表。 

mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));

执行下面列出的查询以了解其工作原理：

mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');  
  
mysql> INSERT INTO Student VALUES(2, NULL, 'Amanda', 'Florida');

输出结果为：

在上图中，我们可以看到第一个 INSERT 查询正确执行，但第二个语句失败并给出一个错误，指出列 LastName 不能为空。

五、唯一（UNIQUE）约束

此约束确保插入列中的所有值都是唯一的。这意味着列不能存储重复值。MySQL 允许我们在一个表中使用多个具有 UNIQUE 约束的列。下面的语句创建一个具有 UNIQUE 约束的表：

mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));

执行下面列出的查询以了解其工作原理：

mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantaloons', 38), (2, 'Cantabil', 40);  
  
mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymond', 38), (2, 'Cantabil', 40);

在下面的输出中，我们可以看到第一个 INSERT 查询正确执行，但第二个语句失败并给出错误消息： Duplicate entry 'Cantabil' for key BrandName。

六、检测（CHECK）约束

检测约束用于控制特定列中的值。它确保列中插入的值必须满足给定条件。换句话说，它确定与列关联的值在给定条件下是否有效。

在 8.0.16 版本之前，MySQL 使用此约束语法的受限版本，如下所示：

CHECK (expr)

在 8.0.16 版本之后，MySQL 对所有存储引擎使用 CHECK 约束，即表约束和列约束，如下所示：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

让我们了解一下 CHECK 约束在 MySQL 中是如何工作的。例如，以下语句创建一个表“Persons”，其中包含对“Age”列的 CHECK 约束。CHECK 约束确保列中的插入值必须满足给定条件，这意味着一个人的 Age 应该大于或等于 18：

mysql> CREATE TABLE Persons (  
    ID int NOT NULL,  
    Name varchar(45) NOT NULL,  
    Age int CHECK (Age>=18)  
);

执行列出的SQL以将值插入表中：

mysql> INSERT INTO Persons(Id, Name, Age)   
VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);  
  
mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);

在下面的输出中，我们可以看到第一个 INSERT 查询成功执行，但第二个语句失败并给出错误消息： CHECK 约束违反了键 Age。

七、默认（DEFAULT）约束

默认约束用于为我们未指定任何值的特定列设置默认值。这意味着该列必须包含一个值，包括 NULL。

例如，以下语句创建一个表“Persons”，其中包含对“City”列的 DEFAULT 约束。如果我们没有为 City 列指定任何值，它会插入默认值：

mysql> CREATE TABLE Persons (  
    ID int NOT NULL,  
    Name varchar(45) NOT NULL,  
    Age int,  
    City varchar(25) DEFAULT 'New York'  
);

执行列出的查询以将值插入表中：

mysql> INSERT INTO Persons(Id, Name, Age, City)   
VALUES (1,'Robert', 15, 'Florida'),   
(2, 'Joseph', 35, 'California'),   
(3, 'Peter', 40, 'Alaska');  
  
mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);

在下面的输出中，我们可以看到第一个包含所有字段的插入查询执行成功，而第二个插入语句不包含“City”列但也执行成功。这是因为它有一个默认值。

现在，执行以下语句来验证第 4 列的默认值：

mysql> SELECT * FROM Persons;

我们可以看到它完美地工作。这意味着默认值“New York”自动存储在“City ”列中。

八、主键（PRIMARY KEY）约束

主键约束用于唯一标识表中的每条记录。如果列包含主键约束，则它不能为 null 或空。一个表可能有重复的列，但它只能包含一个主键。它始终在列中包含唯一值。

下面的语句创建了一个“Person”表，并更清楚地解释了这个主键的使用：

CREATE TABLE Persons (  
    ID int NOT NULL PRIMARY KEY,   
    Name varchar(45) NOT NULL,   
    Age int,   
    City varchar(25));

接下来，使用插入查询将数据存储到表中：

INSERT INTO Persons(Id, Name, Age, City)   
VALUES (1,'Robert', 15, 'Florida') ,   
(2, 'Joseph', 35, 'California'),   
(3, 'Peter', 40, 'Alaska');  
  
INSERT INTO Persons(Id, Name, Age, City)   
VALUES (1,'Stephen', 15, 'Florida');

在下面的输出中，我们可以看到第一个插入查询成功执行。而第二个插入语句失败并给出错误提示：主键列的条目重复。

九、自增（AUTO_INCREMENT）约束

每当我们向表中插入新记录时，此约束会自动生成一个唯一编号。通常，我们将此约束用于表中的主键字段。

我们可以通过以下示例来理解它，其中 id 列将在 Animal 表中自动递增：

mysql> CREATE TABLE Animals(  
id int NOT NULL AUTO_INCREMENT,   
name CHAR(30) NOT NULL,   
PRIMARY KEY (id));

接下来，我们需要将值插入到“Animals”表中：

mysql> INSERT INTO Animals (name) VALUES   
('Tiger'),('Dog'),('Penguin'),   
('Camel'),('Cat'),('Ostrich');

现在，执行以下语句以获取表数据：

mysql> SELECT * FROM Animals;

在输出中，我们可以看到我没有为自增列指定任何值，因此 MySQL 会自动为该字段生成一个按顺序排列的唯一编号。

十、枚举（ENUM ）约束

MySQL 中的 ENUM 数据类型是一个字符串对象。它允许我们在创建表时限制从列规范中的允许值列表中选择的值。它是枚举的缩写，这意味着每一列可能具有指定的可能值之一。它使用数字索引（1、2、3…）来表示字符串值。

下图创建了一个名为“shirts”的表，其中包含三列：id、name 和 size。列名“size”使用包含 small、medium、large 和 x-large 大小的 ENUM 数据类型。

mysql> CREATE TABLE Shirts (    
    id INT PRIMARY KEY AUTO_INCREMENT,     
    name VARCHAR(35),     
    size ENUM('small', 'medium', 'large', 'x-large')    
);

接下来，我们需要使用以下语句将值插入“Shirts”表：

mysql> INSERT INTO Shirts(id, name, size)     
VALUES (1,'t-shirt', 'medium'),     
(2, 'casual-shirt', 'small'),     
(3, 'formal-shirt', 'large');

现在，执行 SELECT 语句以查看插入到表中的值：

mysql> SELECT * FROM Shirts;

我们将得到以下输出：

十一、索引（INDEX）约束

索引约束允许我们非常快速轻松地从表中创建和检索值。可以使用一列或多列创建索引。它以将它们插入表中的方式为每一行分配一个 ROWID。

下图创建了一个名为“shirts”的表，其中包含三列：id、name 和 size。

mysql> CREATE TABLE Shirts (    
    id INT PRIMARY KEY AUTO_INCREMENT,     
    name VARCHAR(35),     
    size ENUM('small', 'medium', 'large', 'x-large')    
);

接下来，我们需要使用以下语句将值插入“shirts”表：

mysql> INSERT INTO Shirts(id, name, size)     
VALUES (1,'t-shirt', 'medium'),     
(2, 'casual-shirt', 'small'),     
(3, 'formal-shirt', 'large');

现在，执行这条语句来创建索引：

mysql> CREATE INDEX idx_name ON Shirts(name);

我们可以使用下面的查询来使用索引列检索数据：

mysql> SELECT * FROM Shirts USE INDEX(idx_name);

输出结果为：

十二、外键（Foreign Key）约束

外键约束用于将两个表链接在一起。它也称为引用键。外键列匹配另一个表的主键字段。这意味着一个表中的外键字段是指另一个表的主键字段。

让我们考虑一下这些表的结构：Persons 和 Orders。

Persons表：

CREATE TABLE Persons (  
    Person_ID int NOT NULL PRIMARY KEY,   
    Name varchar(45) NOT NULL,   
    Age int,   
    City varchar(25)  
);

Orders表：

CREATE TABLE Orders (  
    Order_ID int NOT NULL PRIMARY KEY,  
    Order_Num int NOT NULL,  
    Person_ID int,  
    FOREIGN KEY (Person_ID) REFERENCES Persons(Person_ID)  
);

在上述表结构中，我们可以看到“Orders”表中的“Person_ID”字段指向“Persons”表中的“Person_ID”字段。“Person_ID”是“Persons”表中的 PRIMARY KEY，而“Orders”表的“Person_ID”列是 FOREIGN KEY。

我们的表包含以下数据：

 MySQL Query查询 MySQL Insert记录 

热门文章

优秀文章

全部教程