MySQL基础

MySQL 基础不是只背 SQL 语法，而是学会把业务对象设计成可靠的表结构。表结构一旦上线，后面再改字段类型、主键、唯一约束、索引都会有成本，所以基础阶段要先建立正确习惯。

这一页要解决四个问题：

SQL 分几类，各自负责什么。
建表时为什么要关注数据类型、主键、约束、默认值。
常用增删改查怎么写，哪些写法容易出问题。
初学者如何从一张表开始验证执行计划。

SQL 分类

类型	说明	示例	使用场景
DDL	定义数据库对象	`create`、`alter`、`drop`	建库、建表、改字段
DML	操作数据	`insert`、`update`、`delete`	写入、修改、删除行
DQL	查询数据	`select`	查询业务数据
DCL	权限控制	`grant`、`revoke`	分配账号权限
TCL	事务控制	`begin`、`commit`、`rollback`	控制一组操作的原子性

为什么要区分这些类型：

原因	说明
权限不同	线上通常不允许普通应用账号执行 DDL
风险不同	`drop table` 的风险远高于普通 `select`
锁影响不同	DDL 可能影响表结构和元数据锁
事务行为不同	DML 可以被事务控制，很多 DDL 会隐式提交

建表流程

mermaid

flowchart TD
    A["分析业务对象"] --> B["确定字段含义"]
    B --> C["选择数据类型"]
    C --> D["确定主键"]
    D --> E["添加唯一约束"]
    E --> F["设计必要索引"]
    F --> G["设置 not null 和默认值"]
    G --> H["补充字段注释"]
    H --> I["用 Demo SQL 验证查询"]

如果跳过这套流程，后果通常不是马上出错，而是在数据量增加后暴露：

跳过的步骤	常见后果
不分析字段含义	一个字段被多个业务复用，后期无法理解
随便选类型	存不下、排序错误、索引变大
没有主键	InnoDB 会生成隐藏主键，查询和复制都不友好
没有唯一约束	依赖代码防重，最终容易出现重复数据
不看查询设计索引	上线后慢 SQL 集中爆发
允许大量 `null`	查询条件复杂，索引统计和业务判断都容易混乱

数据类型怎么选

数值类型

场景	推荐类型	不推荐原因
主键 ID	`bigint`	`int` 容量有限，业务增长后可能不够
状态枚举	`tinyint`	用字符串保存状态会占空间，也不利于统一约束
金额	`decimal(10,2)` 或以分为单位的 `bigint`	`float/double` 有精度误差
计数	`int` 或 `bigint`	不要用 `varchar` 保存数字

金额为什么不能用 float：

sql

-- 浮点数是近似值，不适合做金额精确计算
create table wrong_amount (
  id bigint primary key,
  amount double not null
);

-- 更推荐
create table right_amount (
  id bigint primary key,
  amount decimal(10, 2) not null
);

字符串类型

类型	适合场景	注意点
`varchar`	长度变化的名称、标题、手机号	长度不是越大越好
`char`	固定长度编码，例如性别、固定短码	不适合长文本
`text`	大段内容	不适合频繁作为查询条件

varchar(50) 不是“只能存 50 字节”，而是最多 50 个字符，具体占多少字节与字符集有关。utf8mb4 下中文和 emoji 都可能占更多字节。

时间类型

类型	特点	建议
`datetime`	保存日期时间，不随会话时区转换	业务创建时间、更新时间常用
`timestamp`	与时区有关，范围相对小	需要时区转换时使用
`date`	只保存日期	生日、账期日期

大多数业务表可以使用：

sql

created_at datetime not null,
updated_at datetime not null

主键为什么重要

InnoDB 使用聚簇索引组织数据。主键索引的叶子节点保存整行数据，所以主键不只是“唯一标识”，还决定数据在 B+Tree 中如何组织。

mermaid

flowchart TD
    A["主键 id"] --> B["聚簇索引 B+Tree"]
    B --> C["叶子节点保存整行数据"]
    C --> D["通过主键查询路径最短"]

主键设计建议：

建议	原因	不这样会怎样
每张表都要有主键	InnoDB 表需要聚簇索引	没有显式主键时会选择唯一非空索引或隐藏 row_id
主键尽量短	二级索引叶子节点会保存主键值	主键越大，所有二级索引越大
主键尽量递增	新记录更容易追加到 B+Tree 末尾	随机主键容易页分裂和碎片
主键不要带业务含义	业务规则可能变化	主键变化会影响关联关系

约束不是可有可无

约束的价值是让数据库帮你兜底，而不是完全依赖应用代码。

约束	作用	示例
`primary key`	唯一标识一行	`id bigint primary key`
`unique key`	防止业务唯一字段重复	用户名、订单号
`not null`	防止未知值	状态、金额、创建时间
`default`	给字段默认值	`status tinyint default 1`
`foreign key`	维护引用完整性	互联网业务中不一定强依赖，需结合场景

唯一约束 Demo：

sql

create table account_user (
  id bigint primary key auto_increment,
  username varchar(50) not null,
  email varchar(100) not null,
  unique key uk_username (username),
  unique key uk_email (email)
) engine = InnoDB default charset = utf8mb4;

如果没有唯一约束，只在代码里先查再插：

sql

select id from account_user where username = 'tom';
insert into account_user(username, email) values ('tom', 'tom@example.com');

两个请求并发执行时，可能都先查不到，然后都插入成功。唯一约束可以在数据库层阻止这种重复数据。

建表示例

用户表：

sql

create table user_profile (
  id bigint primary key auto_increment comment '主键 ID',
  username varchar(50) not null comment '用户名',
  email varchar(100) not null comment '邮箱',
  status tinyint not null default 1 comment '状态：1启用，0禁用',
  last_login_at datetime null comment '最后登录时间',
  created_at datetime not null comment '创建时间',
  updated_at datetime not null comment '更新时间',
  unique key uk_username (username),
  unique key uk_email (email),
  key idx_status_created_at (status, created_at)
) engine = InnoDB default charset = utf8mb4 comment = '用户资料表';

为什么这样设计：

设计	原因
`id bigint auto_increment`	递增主键适合 InnoDB 聚簇索引
`username/email unique`	数据库兜底防止重复账号
`status tinyint`	状态字段小、固定，适合数字枚举
`created_at/updated_at`	方便排序、审计、排查问题
`idx_status_created_at`	支持按状态筛选并按创建时间分页

增删改查 Demo

插入：

sql

insert into user_profile(username, email, status, created_at, updated_at)
values ('tom', 'tom@example.com', 1, now(), now());

查询：

sql

select id, username, email
from user_profile
where status = 1
order by created_at desc
limit 20;

更新：

sql

update user_profile
set status = 0,
    updated_at = now()
where id = 1;

删除一般分两类：

sql

-- 物理删除：数据真的删除
delete from user_profile where id = 1;

-- 逻辑删除：增加 deleted 字段，用状态表示删除
update user_profile
set status = 0,
    updated_at = now()
where id = 1;

业务系统通常更常用逻辑删除，因为审计、恢复、排查问题都需要历史数据。但逻辑删除会让表持续变大，需要配合归档策略。

常用查看命令

sql

-- 查看数据库
show databases;

-- 使用数据库
use demo;

-- 查看表
show tables;

-- 查看表结构
desc user_profile;

-- 查看建表语句
show create table user_profile;

-- 查看索引
show index from user_profile;

-- 查看当前连接
show processlist;

基础阶段最容易犯的错

错误	后果	建议
用字符串保存数字	排序和比较可能不符合预期	数字用数值类型
金额用 `double`	精度误差	用 `decimal` 或整数分
主键用很长字符串	二级索引膨胀	用短且稳定的主键
字段大量允许 `null`	业务判断复杂，查询容易漏条件	非必要字段尽量 `not null`
没有唯一约束	并发下出现重复数据	业务唯一字段加 `unique key`
只建单列索引	多条件查询不一定高效	根据高频 SQL 设计联合索引
大表随意 `alter table`	可能锁表或长时间变更	上线前评估变更方式和窗口

练习

建一张 orders 表，包含订单号、用户 ID、状态、金额、创建时间。
给订单号加唯一索引。
给 (user_id, created_at) 加联合索引。
写一条查询某个用户最近 20 条订单的 SQL。
用 explain 验证是否使用了联合索引。

参考：

sql

create table orders (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  status tinyint not null default 1,
  amount decimal(10, 2) not null,
  created_at datetime not null,
  updated_at datetime not null,
  unique key uk_order_no (order_no),
  key idx_user_created_at (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

explain
select id, order_no, amount
from orders
where user_id = 1001
order by created_at desc
limit 20;

下一步建议阅读存储结构和索引知识点。基础页解决“表怎么设计”，索引页解决“为什么这样查会快”。

JDK 8/11/17/21 LTS演进

线程池

MySQL基础 ​

SQL 分类 ​

建表流程 ​

数据类型怎么选 ​

数值类型 ​

字符串类型 ​

时间类型 ​

主键为什么重要 ​

约束不是可有可无 ​

建表示例 ​

增删改查 Demo ​

常用查看命令 ​

基础阶段最容易犯的错 ​

练习 ​