[MySQL初阶]MySQL（3）表的约束

标题：[MySQL初阶]MySQL（3）表的约束
@水墨不写bug

• 其实，《[MySQL初阶]MySQL（2）数据类型精讲&&静态类型和动态类型的对比》的数据类型精讲中，我们就已经知道了数据类型本质就是mysql的一种约束，但是这还不够，MySQL的表中一定需要各种约束，让插入数据库中的数据是符合预期的。

约束的本质：
通过技术手段，倒逼程序员插入正确的数据。反过来，站在MySQL的角度，凡是插入的数据，一定是符合约束的正确的数据。
约束的目的：
保证数据的完整性和可预见性。

1. NULL / NOT NULL

• 作用：控制字段是否允许存储空值（NULL）。
  • NULL：允许为空（默认）。
  • NOT NULL：字段必须包含值，否则插入会报错。
• 业务场景：
  • 必填字段：如用户表的 username 和 password 必须为 NOT NULL，避免数据不完整。
  • 可选字段：如用户表的 address 可为 NULL，允许用户暂时不填写。

示例：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL, -- 必填
    email VARCHAR(100) NOT NULL,    -- 必填
    phone VARCHAR(20) NULL,          -- 可选
    other VARCHAR(20)          -- 可选默认为null
);

2. DEFAULT

• 作用：为字段设置默认值。当插入数据未指定该字段时，自动填充默认值；如果用户指定了数据，则填充用户指定的数据。
• 业务场景：
• 状态字段：如订单状态默认为 pending。
• 时间字段：如记录的创建时间默认为当前时间 CURRENT_TIMESTAMP。

示例：

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending', -- 默认状态
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- 自动记录时间
);

null\not null 与default辨析？

总的来说：not null和default相互补充，并不冲突。

• null、not null表示表中的数据是否可以为null；
• default表示不管这一列数据时，如果设置了默认值，使用默认值；如果没有设置默认值，则报错。
• 如果在建表的时候没有设置default，sql会设置default为NULL；
• not null 约束除了不允许插入null。但是空串‘’!=null
• 一般而言，not null和default不会同时出现。

3. COMMENT

• 作用：为字段或表添加注释，提高代码可读性。（类似于C++的//后面的注释，是给开发者看的）
• 业务场景：
  • 字段说明：解释字段用途，如加密字段、特殊格式要求等。

示例：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2) COMMENT '税前工资，单位为人民币', -- 注释说明
    password_hash VARCHAR(100) COMMENT '使用SHA-256加密的密码'
);

如何查看注释？

运行sql语句：

show create table tb_name\G;

• 这个sql语句通过显示历史创建表时的语句来显示comment。
• \G 选项可以让显示更加整洁。
• sql语句不区分大小写，通常而言，小写更适合书写和阅读。

4. ZEROFILL

• 作用：对数值类型字段用零填充显示（不影响实际存储值）。例如，INT(5) ZEROFILL 存储 18 会显示为 00018。注意不是二进制显示，比如a=2，int（5）zerofill显示出来是00002，而不是00010！！
• 显示的总位数（补0后为10，因为int（n）n默认为10,2 ^ 32 为 10^10级别;此外，int unsigned（n）n默认为11，因为需要多一位存储符号。
• 业务场景：
  • 固定长度编码：如会员号 member_no INT(6) ZEROFILL，显示为 000123。

示例：

CREATE TABLE members (
    member_id INT PRIMARY KEY,
    member_no INT(6) ZEROFILL -- 显示为6位，不足补零
);

5. PRIMARY KEY

• 作用：唯一标识表中的每一行，确保字段值唯一且非空（NOT NULL），自动创建索引。一张表最多只能有一个主键，主键所在的列通常为整形。
• 业务场景：
• 唯一标识符：如用户ID、订单ID。

示例：

CREATE TABLE products (
    product_id INT PRIMARY KEY, -- 主键
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);

主键约束注意事项：

• 相同主键（主键冲突）无法插入；
• 丢弃主键：

alter table tb_name drop primary key;

• 添加某一列为主键：

alter table tb_name add primary key(col_name);--列名称

• 一张表最多只能有一个主键，但是这不意味着一个表中的主键，只能添加给一列！一个主键可以被添加到一列或者多列上（复合主键）；
• 对于复合主键，两个或者多个列数据同时和历史数据一样，才会冲突。
• 设置复合主键：

create table if not exists tb_name(
	id int unsigned,
	course_id int unsigned comment '课程编号',
	score tinyint unsigned comment '课程成绩',
	primary key(id,course_id) --设置复合主键
	);

6. AUTO_INCREMENT

• 作用：自动为字段生成递增的唯一值，通常与 PRIMARY KEY 结合使用。
• 注意：如果没有指定起始值，则从1开始自增长。
• 被指定的字段在插入的时候不必考虑，这个字段会自动+1并填充给新插入的行。
• 中途插入新值，则会从当前字段已有的最大值开始+1并填充。
• 业务场景：
• 自增主键：如文章的 article_id 自动生成。

示例：

CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
    title VARCHAR(200) NOT NULL,
    content TEXT
);

自增长字段注意：

• 任意一个字段要做自增长，前提是其本身就是索引（主键/唯一键等）换句话说，key一栏要有值；
• 一张表只能有一个自增长；
• 可以获取最后一次插入的ID：

select last_insert_id();

索引的本质

多用部分空间（内存）来存储数据的位置信息（类似于书的目录）。 本质是：以时间换空间。

7. UNIQUE KEY

• 作用：确保字段值在表中唯一，允许存在 NULL 值（除非结合 NOT NULL）。其与主键相互补充，满足非主键类型数据的唯一性。这就类似于我们已经设置了ID为主键，但是用户电话号码也不能冲突，所以可以把用户电话号码设置为unique唯一键。
• 业务场景：
• 防重复数据：如用户邮箱、手机号、身份证号等需唯一。

示例：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE, -- 唯一且非空
    phone VARCHAR(20) UNIQUE            -- 唯一但可为空
);

唯一键字段注意：

• 唯一键，可为null，但是可手动设置为not null；主键，不可为null；
• 主键更多是标识唯一性；
• 唯一键更多是保证在业务上，不要和别的信息出现重复。
• 唯一键可以有多个，主键只能有一个。

综合业务案例

用户表设计：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户唯一ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（唯一）',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱（唯一）',
    password VARCHAR(32) NOT NULL COMMENT '加密后的密码',
    status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '用户状态',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    member_no INT(6) ZEROFILL COMMENT '6位编号'
);

订单表设计：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL COMMENT '关联用户ID',
    order_no VARCHAR(20) NOT NULL UNIQUE COMMENT '唯一订单号',
    amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

根据以上两个简单的表的设计，我们就可以在此基础上实现订单和骑手送餐的代码逻辑，这就是mysql在实际业务中的应用。

完·~
转载请注明出处