MySQL表的约束

📟作者主页：慢热的陕西人

🌴专栏链接：MySQL

📣欢迎各位大佬👍点赞🔥关注🚓收藏，🍉留言

本博客主要内容介绍了mysql中约束的概念

1.表的约束的概念

表的约束（Database Table Constraints）是数据库管理系统中用来确保数据完整性的规则和限制。它们用于限制存储在表中的数据类型，以确保数据的准确性和可靠性。常见的表约束类型包括：

主键约束（Primary Key Constraint）：确保表中每一行都有唯一的标识符。每个表只能有一个主键，主键列中的值必须是唯一的，并且不能包含 NULL 值。

外键约束（Foreign Key Constraint）：用于在两个表之间建立关系，确保引用的表中的数据在主表中存在。这有助于维护数据的引用完整性。

唯一约束（Unique Constraint）：确保表中的所有行在指定的列或列组合中都有唯一的值。与主键不同，唯一约束可以接受 NULL 值。

检查约束（Check Constraint）：确保列中的数据满足指定的条件。例如，可以用检查约束来确保年龄列的值不小于18。

默认值约束（Default Constraint）：为表中的列指定默认值。如果在插入行时没有为该列提供值，则会自动插入默认值。

非空约束（NOT NULL Constraint）：确保列中的每一行都必须有值，换句话说，该列不能包含 NULL 值。

这些约束共同作用，保证了数据库中的数据是有效的、一致的，并满足业务规则的需要。

2.非空约束

2.1空属性

MySQL 中的空属性

MySQL 中的空属性表示一个字段没有明确的值。它与 NULL 不同，NULL 表示一个未知或不存在的值。

示例：
CREATE TABLE table_name (
  id INT NOT NULL,
  name VARCHAR(255),
  address VARCHAR(255) DEFAULT NULL
);
在这个例子中，name 字段允许空属性，而 address 字段通过 DEFAULT NULL 设置为默认为空属性，id设置为不能为空。

查询空属性：

可以使用 IS NULL 或 IS NOT NULL 运算符来查询空属性：
SELECT * FROM table_name WHERE name IS NULL;
插入空属性：

可以使用 NULL 关键字插入空属性：
INSERT INTO table_name (id, name, address) VALUES (1, NULL, NULL);

其中空属性意味着，我们表中的某些数据可以缺省的表示为NULL,但是有些必须要确定的信息，我们一般设置为NOT NULL。

2.2默认值

默认值：某一种数据会经常性的出现某一个值，可以在一开始就指定好，在需要真实的数据的时候，用户可以选择性的选择默认的值。

mysql> create table if not exists t1(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> gender char(1) default '男');
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t1 (name, age, gender) values ('张三', 19, '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (name) values ('李四');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
| 李四   |   18 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

其中默认值和NOT NULL可以同时生效：

无默认值 + NOT NULL	插入数据必须对该列显式的插入数据
有默认值 + NOT NULL	插入数据的时候可以选择缺省，但是不能插入NULL
无默认值 + NULL	插入数据的时候不可以缺省，但是可以选择插入NULL
有默认值 + NULL	插入数据的时候可以选择缺省，也可以选择插入NULL

当我们建表的时候不设定NULL或者默认值，一般sql会设定为default NULL;

当我们设定NOT NULL的时候，sql不会帮我们设定一个默认的default;

3.列描述comment

概念：comment,没有实际的含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或者DBA来了解。

mysql> create table if not exists t2(
    -> name varchar(20) not null comment '用户的姓名',
    -> age tinyint unsigned default comment '用户的年龄'
    -> );
--注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

通过desc查看不到注释信息：

mysql> desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+

查看创建语句：

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name` varchar(20) NOT NULL COMMENT '用户的姓名',
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '用户的年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.zerofill

行为：

将数字值填充前导零，以达到指定的显示宽度。

仅影响值的显示，而不影响其存储或计算。

如果值比指定的显示宽度长，则不会将其截断。

不适用于负值。

语法：
CREATE TABLE table_name (
  column_name data_type(display_width) ZEROFILL
);
其中：

data_type 是数字数据类型（INT、BIGINT、DECIMAL 或 NUMERIC）。

display_width 指定值的显示宽度。

示例：

考虑以下表：
CREATE TABLE numbers (
  id INT(5) ZEROFILL
);
向该表插入以下值：
INSERT INTO numbers (id) VALUES (5), (123), (12345);
表中存储的值如下：

| id |
|—|—|
| 00005 |
| 00123 |
| 12345 |

用途：

ZEROFILL 属性可用于以下用途：

确保数字在显示或存储时具有统一的宽度，这在对齐数据或创建格式化报告时很有用。

方便比较字符串表示的数字，因为前导零可以使值按字典顺序排列。

注意：

ZEROFILL 仅适用于数字数据类型。

如果未指定显示宽度，则 MySQL 将使用列的默认宽度。

ZEROFILL 不影响值的排序或聚合。

5.主键

MySQL中的主键约束是一种数据库表级约束，用于唯一标识表中的每一行。这种约束有以下几个重要特性：

唯一性：主键中的值必须是唯一的。这意味着两行不能有相同的主键值。

非空性：主键字段不能为NULL。这是因为主键需要一个实际的值来唯一标识一行。

自动索引：在MySQL中，当你指定一个字段为主键时，系统会自动为该字段创建一个唯一索引。这有助于快速访问和检索数据，因为索引允许数据库软件快速找到具有特定主键值的行。

限制：一个表只能有一个主键。然而，主键可以包含多个字段，这称为复合主键。复合主键意味着两个或更多的字段联合起来唯一标识表中的一行。

自动递增：通常，主键字段被设置为自动递增，尤其是当主键是一个数字时。这意味着每当添加新行时，主键字段的值会自动递增。这是通过使用AUTO_INCREMENT属性实现的，确保了每个记录的唯一性而无需手动输入。

在MySQL中定义主键的一个示例语句如下：
CREATE TABLE example_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);
在这个例子中，id字段被定义为主键，具有AUTO_INCREMENT属性，保证了每次插入新记录时id都是唯一的。

主键约束是数据库设计中的一个基本元素，它确保了数据的完整性和唯一性。它对于关系型数据库来说尤其重要，因为它们经常依赖主键来执行快速数据检索和表之间的关联。

添加或删除主键：

在已经创建好的表中，你可以使用ALTER TABLE语句来删除现有的主键约束和添加新的主键约束。这些操作在数据库管理中是常见的，尤其是在调整数据库结构以满足应用需求变化时。

删除主键

要从MySQL表中删除主键约束，可以使用以下语句：
ALTER TABLE your_table_name DROP PRIMARY KEY;
这条命令会从名为your_table_name的表中删除现有的主键约束。需要注意的是，如果主键有AUTO_INCREMENT属性，你可能需要先删除该属性，因为直接删除主键可能会遇到错误。

添加主键

向表中添加主键约束，使用以下语句：
ALTER TABLE your_table_name ADD PRIMARY KEY (your_column_name);
如果你想要创建一个复合主键（即多列共同作为主键），可以在括号中包含多个列名，用逗号分隔：
ALTER TABLE your_table_name ADD PRIMARY KEY (column1, column2);
这会将column1和column2设置为联合主键。

示例

假设有一个students表，包含student_id和email两列，你想要删除现有的主键并将email设置为新的主键。你可以按照以下步骤操作：

删除现有的主键（假设是student_id）:
ALTER TABLE students DROP PRIMARY KEY;
将email列设置为新的主键:
ALTER TABLE students ADD PRIMARY KEY (email);
在执行这些操作时，需要确保将成为新主键的列满足主键的所有要求，即它们必须是唯一的且不包含NULL值。

注意事项

在进行这类操作之前，确保对数据库进行备份，以避免数据丢失。

修改主键会影响到依赖该主键的外键约束和索引，因此需要仔细考虑这些改动对数据库整体结构和性能的影响。

如果你的表很大，修改主键操作可能会耗费较长时间，因为MySQL需要重新构建索引。在执行这些操作时，请考虑到可能的性能影响。

复合主键：

假设你正在设计一个数据库来存储学生选课信息，其中每个学生可以选择多门课程，而一门课程也可以被多个学生选择。为了唯一标识每个学生和课程之间的关系，你需要使用到复合主键。复合主键由两个或多个列组成，这些列的组合值在表中必须是唯一的。

创建表并设置复合主键

下面是一个创建名为student_courses的表的例子，其中student_id和course_id两个字段联合作为复合主键：
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);
在这个例子中：

student_id列代表学生的唯一标识符。

course_id列代表课程的唯一标识符。

enrollment_date列记录了学生选课的日期。

PRIMARY KEY (student_id, course_id)定义了一个复合主键，意味着student_id和course_id的组合必须在student_courses表中唯一。

插入数据

向student_courses表插入数据时，需要确保每个student_id和course_id的组合是唯一的。以下是一些插入记录的例子：
INSERT INTO student_courses (student_id, course_id, enrollment_date) VALUES (1, 101, '2024-01-15');
INSERT INTO student_courses (student_id, course_id, enrollment_date) VALUES (1, 102, '2024-02-15');
INSERT INTO student_courses (student_id, course_id, enrollment_date) VALUES (2, 101, '2024-03-15');
在这些例子中，一个学生（例如，student_id为1）可以选多门课程（如course_id为101和102），但同一学生对同一课程的选课记录只能出现一次。这正是通过复合主键约束保证的。

注意事项

使用复合主键时，需要确保组成主键的每个列都不允许NULL值，因为主键的作用是唯一标识表中的每行数据，而NULL值在此上下文中是无意义的。此外，在设计具有复合主键的表时，应该仔细考虑哪些列真正需要包含在主键中，以确保数据的一致性和完整性。

5.1自增长

在MySQL中，自增长（AUTO_INCREMENT）是一个属性，它允许数据库为表中的每一行自动生成一个唯一的数字。只能用于主键列，确保每一行都有一个唯一的标识符。

以下是关于MySQL中自增长的一些关键点：
用途：

通常用于主键列，确保数据的唯一性。

无需手动为每行输入一个值，数据库会自动为你处理。
设置：

当你创建一个新表时，可以在列定义中使用AUTO_INCREMENT属性来指定哪个列应该自增长。
sql`CREATE TABLE example (  
    id INT NOT NULL AUTO_INCREMENT,  
    name VARCHAR(255),  
    PRIMARY KEY (id)  
);`
在已经存在的表上，你可以使用ALTER TABLE命令来添加一个自增长属性只能添加在主键上。
特性：

每次插入新行时，自增长列的值会自动增加。

默认情况下，自增长列的起始值为1，每次插入新行时增加1。但这些值都可以调整。

自增长列必须是索引（通常是主键）。

一个表只能有一个自增长列。
插入数据：

当插入新行时，如果省略了自增长列的值（或为其指定了NULL），则MySQL会自动为该列生成一个新的值。
复制代码

sql`INSERT INTO example (name) VALUES ('Alice');`
如果为自增长列指定了一个值，MySQL会检查该值是否唯一，并且大于当前的最大值。如果满足条件，MySQL会使用该值；否则，它会生成一个新的值。
重置和修改：

你可以使用ALTER TABLE命令来重置自增长列的当前值。
复制代码

sql`ALTER TABLE example AUTO_INCREMENT = 100;`
这会将下一个自增长值设置为100。
注意事项：

在某些情况下，手动插入特定的自增长值可能会导致数据冲突或不一致，因此应谨慎操作。

如果删除了表中的行，自增长计数器不会重置。如果你希望重置它，需要手动执行ALTER TABLE命令。
自增长属性在MySQL中是一个非常有用的功能，它简化了数据插入的过程，并确保每行都有一个唯一的标识符。然而，在使用时仍需要注意一些潜在的问题和限制。

6.唯一键

在 MySQL 中，唯一键（Unique Key）是一个非常重要的概念，它用于保证表中某一列或者多列组合的值是唯一的，以确保数据的准确性和完整性。

定义与特性

唯一性保证：唯一键约束确保了表中的每条记录在指定的列（或列的组合）上具有唯一的值。这意味着在这些列上，不允许有重复的值出现。

允许NULL值：唯一键允许有NULL值，这是它与主键的一个主要区别。标准SQL中，唯一约束允许每个唯一列有多个NULL值（即，你可以有多条记录其唯一键列为NULL，这在逻辑上被视为各不相同），但实际的实现可能依数据库具体而异。

多个唯一键：一个表可以有多个唯一键约束，这意味着可以有多组列需要保持其值的唯一性。

自动创建索引：当你为表定义一个唯一键时，MySQL 会自动为该唯一键创建一个唯一索引，以加快查找和排序的速度，同时确保数据的唯一性。

应用场景

业务逻辑的唯一性要求：在实际应用中，唯一键通常用于那些需要唯一性但不是主键的场景，如邮箱地址、身份证号码等。这些字段在业务逻辑上需要保证唯一，以防止数据重复或错误。

数据完整性和准确性：通过使用唯一键，开发人员可以确保数据库中的数据既符合业务规则，又保持了数据的准确性和完整性。

创建唯一键

创建唯一键的语法通常如下：
ALTER TABLE table_name
ADD UNIQUE (column_name);
或在创建表时直接指定：
CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);
如果是多列组合的唯一性约束，则：
ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);
或在创建表时：
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    UNIQUE (column1, column2, ...)
);
注意事项

性能考虑：虽然唯一键提供了数据唯一性的保障，但每个唯一键也是一个索引，太多的索引会影响插入和更新的性能，因为每次这些操作都需要更新索引。

设计原则：合理使用唯一键，确保数据的完整性和业务逻辑的需要，同时避免过度设计，以免影响数据库性能。

综上所述，唯一键在数据库设计中是确保数据唯一性的重要工具，合理使用可以大大提高数据质量和应用性能。

与主键的对比：

对比

存在原因：主键是为了唯一标识表中的每一行，而唯一键是为了防止特定列（或列的组合）出现重复值。

NULL 值：主键不允许 NULL 值，而唯一键允许（但具体到实现可能有所不同）。

数量限制：每个表只能有一个主键，但可以有多个唯一键。

作为外键引用：外键通常引用主键，尽管技术上也可以引用唯一键。

总的来说，虽然主键和唯一键都能确保数据的唯一性，但它们的使用场景和限制有所不同。主键用于唯一标识表中的每条记录，而唯一键则用于保证某一列（或多列）的值在所有记录中是唯一的。

7.外键

MySQL中的外键是一个非常重要的数据库概念，用于确保数据的一致性和完整性。外键是一个表中的字段，它是另一个表的主键的引用。使用外键可以创建两个表之间的关系，这有助于保持数据的一致性和避免数据冗余。

主要特点

引用完整性：外键约束确保了一个表中的数据引用另一个表中的有效数据。这意味着，如果表A中有一个字段是表B的外键，那么表A中该字段的每个值都必须在表B的主键字段中存在。

防止无效数据：通过使用外键约束，你可以防止在引用的表中添加不存在于主表中的值。

级联操作：MySQL支持通过外键约束自动进行级联更新和级联删除。例如，如果你删除了一个记录，那么所有引用该记录的表中的相关记录也可以被自动删除或更新，这取决于外键约束的定义。

使用方式

在创建表或之后添加外键约束的语法如下：
-- 在创建表时定义外键
CREATE TABLE 表名 (
    列名 数据类型,
    ...
    FOREIGN KEY (外键列) REFERENCES 主表(主键列)
);

-- 向现有表添加外键约束
ALTER TABLE 表名
ADD FOREIGN KEY (外键列) REFERENCES 主表(主键列);
注意事项

性能影响：虽然外键可以保证数据的一致性，但它们也可能对数据库的性能产生影响。每次插入、更新或删除操作时，数据库都需要检查外键约束，这可能会增加操作的时间。

设计选择：在一些情况下，可能会选择不使用外键约束以提高性能，特别是在高并发写入的场景中。这种情况下，应用层需要负责维护数据的一致性。

兼容性问题：并不是所有的MySQL存储引擎都支持外键。例如，MyISAM引擎就不支持外键，而InnoDB引擎支持。

在设计数据库时，适当使用外键非常重要，它有助于维护数据的完整性和减少错误数据的产生。然而，也需要考虑到外键的潜在性能影响，并根据具体情况做出合适的设计选择。