【MySQL数据库】数据类型与表约束

MySQL 设计表结构时，数据类型选得对、约束用得好，表结构就稳了一半。这篇文章把「数据类型」和「表约束」两大核心知识点讲透

1.MySQL 数据类型

MySQL 数据类型主要分四大类：数值类型、字符串类型、日期时间类型、枚举/集合类型。

1.1 数值类型

最常用： TINYINT、INT、BIGINT、FLOAT、DECIMAL

整数类型

• TINYINT：1字节，-128~127；无符号 0~255
• INT：4字节，常用作主键 ID
• BIGINT：8字节，超大数字用

-- 无符号 tinyint（0~255）
create table tt2(num tinyint unsigned);

小数类型

• FLOAT：单精度，4字节，精度一般
• DECIMAL(M,D)：高精度定点数，钱/金额必须用它

-- 高精度小数，推荐用 decimal
create table tt8(
    salary float(10,8), 
    salary2 decimal(10,8)
);

BIT 类型

• 存 0/1 最省空间
• 显示时按 ASCII 码展示

create table tt5(gender bit(1)); -- 只能存 0 或 1

1.2 字符串类型

CHAR(size)

• 固定长度，最大 255 字符
• 速度快，空间浪费
• 适合：身份证、手机号、MD5

name char(32); -- 永远占 32 字符空间

VARCHAR(size)

• 可变长度，用多少占多少
• 最大 65535 字节
• UTF8 下最大 21844 字符

name varchar(60); -- 变长，节省空间

CHAR vs VARCHAR 怎么选？

• 长度固定 → CHAR
• 长度不固定 → VARCHAR
• 定长浪费空间、效率高；变长省空间、效率稍低

1.3 日期时间类型

• DATE：yyyy-mm-dd
• DATETIME：yyyy-mm-dd hh:mm:ss
• TIMESTAMP：时间戳，更新行时自动刷新

create table birthday (
    t1 date, 
    t2 datetime, 
    t3 timestamp
);

1.4 ENUM 与 SET（单选/多选）

• ENUM：单选（男/女）
• SET：多选（游泳、篮球、登山）

create table votes(
    username varchar(30),
    hobby set('登山','游泳','篮球'),
    gender enum('男','女')
);

查询 SET 必须用 find_in_set

select * from votes where find_in_set('登山', hobby);

2.MySQL 表约束

约束 = 保证数据合法、不乱、不重复、不空。

2.1 NOT NULL 非空

字段不允许为 NULL，业务字段强烈建议加。

create table myclass(
    class_name varchar(20) not null,
    class_room varchar(10) not null
);

2.2 DEFAULT 默认值

插入时不填则自动用默认值。

age tinyint unsigned default 0,
sex char(2) default '男'

2.3 COMMENT 字段注释

必加！方便维护，通过 show create table 表名\G 可以看到该字段。

name varchar(20) not null comment '用户姓名';

2.4 ZEROFILL 补零

数字不足长度时前面补 0。

id int(5) unsigned zerofill;
-- 1 → 00001

2.5 PRIMARY KEY 主键

• 唯一 + 非空
• 一张表只能一个主键

id int unsigned primary key auto_increment

复合主键

create table tt14(
id int unsigned,
course char(10) comment '课程代码',
score tinyint unsigned default 60 comment '成绩',
primary key(id, course) -- id和course为复合主键
);

如果有多个字段作为主键，可以使用复合主键，多个字段组合起来必须唯一

当表创建好以后但是没有主键的时候，可以再次追加主键

alter table 表名 add primary key(字段列表)

删除主键

alter table 表名 drop primary key;

2.6 AUTO_INCREMENT 自增长

配合主键使用，自动 +1，自增长字段必须是整数，一张表最多只能有一个自增长。

id int primary key auto_increment

2.7 UNIQUE 唯一键

值不能重复，但允许 NULL。

email varchar(64) unique

2.8 FOREIGN KEY 外键

保证关联表数据合法性，防止无效关联。外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或 unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为 null

foreign key (class_id) references myclass(id)

建立外键的本质其实就是把相关性交给 mysql 去审核了，提前告诉 mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql 不允许你插入

希望读者们多多三连支持

小编会继续更新

你们的鼓励就是我前进的动力！