MySQL的学习之路：存储引擎、数据类型与表设计规范
我是程序员良夜，一个正在努力学习的小白，希望我的笔记能帮到同样在学习路上的你！
引言：学MySQL，很多人上来就学SQL语句，增删改查背得滚瓜烂熟。但面试官一问"InnoDB和MyISAM有什么区别""VARCHAR和CHAR怎么选""表该怎么设计才合理"，直接懵了。本文把3个容易被忽略但超级重要的基础知识点拆开：先从"两种仓库"理解存储引擎的区别，再用"选衣服尺码"类比讲清数据类型选择，最后用"盖房子"的思路讲透表设计规范，让你建表不再凭感觉。
1. 存储引擎（Storage Engine）—— MySQL的"两种仓库"
什么是存储引擎
你往MySQL里存数据，数据最终是存在硬盘上的。但怎么存、怎么读、怎么保证安全，不同方式就是不同的"存储引擎"。
打个比方：
存储引擎就像仓库的管理方式。
一种仓库叫铁皮仓库（InnoDB）：门上有锁，进出要登记，东西坏了能修，停电了记录也不会丢。安全可靠，但管理成本稍高。
另一种仓库叫简易仓库（MyISAM）：没锁，进出随便，存取速度快，但东西容易丢，坏了也没法修。便宜好用，但不安全。
MySQL支持很多存储引擎，但你99%的时间只需要关心两个：InnoDB和MyISAM。
InnoDB vs MyISAM 对比
| 特性 | InnoDB（铁皮仓库） | MyISAM（简易仓库） |
|---|---|---|
| 事务支持 | ✅ 支持（BEGIN/COMMIT/ROLLBACK） | ❌ 不支持 |
| 行级锁 | ✅ 支持（并发性能好） | ❌ 只有表锁（并发差） |
| 外键 | ✅ 支持 | ❌ 不支持 |
| 崩溃恢复 | ✅ 能自动恢复 | ❌ 容易丢数据 |
| 全文索引 | ✅（MySQL 5.6+支持） | ✅ 支持 |
| 读性能 | ⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ |
| 写性能 | ⭐⭐⭐⭐⭐ | ⭐⭐⭐ |
MySQL 5.5之后，默认存储引擎就是InnoDB。现在做项目，直接用InnoDB就对了。
什么时候还会用MyISAM？
说实话，现在已经很少了。但在以下场景还有人用：
| 场景 | 为什么用MyISAM |
|---|---|
| 只读或读多写少的日志表 | 读性能略高，不关心数据安全 |
| 全文检索（老版本MySQL） | 老版本InnoDB不支持全文索引 |
| 临时统计表 | 数据丢了重算就行，不需要事务 |
注意：如果你的项目用了MyISAM，而表里有重要数据（比如订单、用户信息），请立刻换成InnoDB。不支持事务意味着一旦出故障，数据可能直接损坏且无法恢复。
行级锁 vs 表级锁
这是InnoDB和MyISAM最大的实际区别：
-- 场景：两个用户同时修改不同的订单 -- InnoDB（行级锁）：互不影响 -- 用户A修改订单1 → 只锁订单1那一行 -- 用户B修改订单2 → 只锁订单2那一行 -- 两个操作可以同时进行 ✅ -- MyISAM（表级锁）：互相等待 -- 用户A修改订单1 → 锁住整张orders表 -- 用户B修改订单2 → 被迫等待，直到用户A操作完 -- 并发性能直接砍一半 ❌打个比方：
行级锁就像超市的自助结账——每个人只占一台机器，互不影响。
表级锁就像只有一个收银台——所有人排一条队，一个一个来。
💡小技巧：怎么查看表用的什么引擎？执行
SHOW TABLE STATUS LIKE '表名';，看Engine那一列。如果显示MyISAM，建议改成InnoDB：ALTER TABLE 表名 ENGINE = InnoDB;
2. 数据类型（Data Types）—— 像选衣服尺码一样选类型
选错类型的代价
很多同学建表的时候，不管什么字段都用VARCHAR(255)或者BIGINT，觉得"大一点总没错"。
但选错类型会有实际的性能和存储代价。
打个比方：
你买衣服，明明穿M码，非要买XXXL。能穿吗？能。舒服吗？不舒服。浪费布料吗？浪费。
数据类型也一样：字段能用INT就别用BIGINT，能用VARCHAR(50)就别用VARCHAR(255)，能用TINYINT就别用INT。类型选得越精确，占用空间越小，查询越快。
数字类型怎么选
| 类型 | 占用字节 | 范围 | 什么时候用 |
|---|---|---|---|
TINYINT | 1字节 | -128 ~ 127 | 状态码、性别、是否删除等 |
SMALLINT | 2字节 | -32768 ~ 32767 | 年龄、数量等小范围数字 |
INT | 4字节 | -21亿 ~ 21亿 | 大部分主键和普通数字字段 |
BIGINT | 8字节 | 超大范围 | 超大表的主键、雪花算法ID |
-- ❌ 不好的写法：什么都用BIGINT CREATE TABLE user ( id BIGINT, -- 主键用BIGINT没问题 age BIGINT, -- 年龄用BIGINT？太大了，TINYINT就够 is_deleted BIGINT, -- 是否删除用BIGINT？TINYINT(1)就行 status BIGINT -- 状态用BIGINT？TINYINT就够了 ); -- ✅ 合理的写法：按实际范围选类型 CREATE TABLE user ( id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键用INT age TINYINT UNSIGNED, -- 年龄，0~255足够 is_deleted TINYINT(1) DEFAULT 0, -- 0=未删除，1=已删除 status TINYINT DEFAULT 1 -- 状态：1=正常，2=禁用... );注意：
UNSIGNED表示"无符号"，就是不存负数。年龄不可能是负数，加UNSIGNED后范围从0~255变成0~255（TINYINT），更合理。
字符串类型：VARCHAR vs CHAR
这是面试最容易被问到的：
| 类型 | 存储方式 | 适用场景 | 例子 |
|---|---|---|---|
CHAR(N) | 固定长度，不够用空格补 | 长度固定的字段 | 手机号、身份证号、MD5值 |
VARCHAR(N) | 变长，只存实际长度 | 长度不固定的字段 | 用户名、地址、标题 |
-- CHAR(11)：手机号永远是11位，用CHAR最合适 phone CHAR(11) -- VARCHAR(50)：用户名长度不固定，用VARCHAR username VARCHAR(50) -- ❌ 错误示范：手机号用VARCHAR phone VARCHAR(11) -- 能用，但CHAR更合适，因为手机号长度固定 -- ❌ 错误示范：什么都用VARCHAR(255) address VARCHAR(255) -- 如果地址最多50个字，用VARCHAR(100)就够了打个比方：
CHAR就像一个固定大小的快递盒——不管你装多少东西，盒子大小不变。适合装大小固定的东西（比如手机壳）。
VARCHAR就像一个可以调节大小的袋子——东西多就撑大，东西少就缩小。适合装大小不一的东西（比如衣服）。
日期类型怎么选
| 类型 | 格式 | 适用场景 |
|---|---|---|
DATE | 2024-06-15 | 只需要日期，不需要时间 |
DATETIME | 2024-06-15 14:30:00 | 需要精确到秒 |
TIMESTAMP | 自动记录时间戳 | 记录创建/更新时间 |
-- 创建时间用DATETIME create_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 更新时间用TIMESTAMP，MySQL会自动更新 update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP注意：
TIMESTAMP有"2038年问题"——它只能存到2038年1月19日。如果你的数据需要存很久以后的日期，用DATETIME更安全。
💡小技巧：类型选精确一点还有一个好处——MySQL建索引时，数据越短，索引越小，查询越快。一个VARCHAR(50)的索引比VARCHAR(255)的索引小得多，内存里能缓存更多索引数据。
3. 表设计规范（Table Design）—— 像盖房子一样建表
为什么需要规范
我刚学数据库的时候，建表全凭感觉。字段名想怎么写怎么写，类型随便选，能跑就行。
直到项目越来越大，才发现之前的表设计简直是一团乱麻：
- 字段名看不懂（
a1、a2、tmp_col） - 数据冗余严重（同一个信息存了三遍）
- 改一个字段要动五六张表
- 查询慢得要命，加索引都救不了
表设计就像盖房子——地基没打好，楼盖得越高越危险。
规范1：命名要有意义
-- ❌ 看不懂的命名 CREATE TABLE t1 ( a1 INT, a2 VARCHAR(100), col3 DATETIME ); -- ✅ 清晰的命名 CREATE TABLE orders ( order_id INT PRIMARY KEY, user_name VARCHAR(100), create_time DATETIME );| 规则 | 说明 | 例子 |
|---|---|---|
| 表名用复数名词 | 表里存的是多条记录 | users、orders |
主键叫表名_id | 一眼就知道是主键 | user_id、order_id |
| 字名用下划线分隔 | 不用驼峰，MySQL习惯用下划线 | create_time、user_name |
布尔字段用is_开头 | 一看就知道是0/1 | is_deleted、is_active |
时间字段用_time结尾 | 一看就知道是日期 | create_time、update_time |
规范2：每个表必须有的字段
不管你建什么表，以下四个字段建议都加上：
CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自增 -- ... 你的业务字段 ... create_time DATETIME DEFAULT CURRENT_TIMESTAMP, -- 创建时间 update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间 is_deleted TINYINT(1) DEFAULT 0 -- 软删除标记 );| 字段 | 作用 | 为什么必须有 |
|---|---|---|
id主键 | 唯一标识每条记录 | 没有主键的表，InnoDB会自己生成一个隐藏主键，你查不到 |
create_time | 记录什么时候创建的 | 排查问题、数据审计全靠它 |
update_time | 记录什么时候改过的 | 数据同步、缓存失效判断 |
is_deleted | 软删除标记 | 不真删数据，只打标记，方便恢复和审计 |
注意：
is_deleted就是"软删除"。真正删除数据用DELETE是不可逆的，但用UPDATE SET is_deleted = 1打个标记，数据还在，随时可以恢复。
规范3：字段设计的"三不原则"
-- 原则1：字段不要有NULL → 给默认值 -- ❌ phone VARCHAR(20) NULL -- 允许NULL -- ✅ phone VARCHAR(20) DEFAULT '' -- 用空字符串代替NULL -- 原则2：不要存大文本/大图片 → 只存路径 -- ❌ avatar BLOB -- 把图片直接存进数据库（几百KB甚至几MB） -- ✅ avatar VARCHAR(255) -- 只存图片的URL路径 -- 原则3：不要用保留字做字段名 -- ❌ CREATE TABLE orders ( order VARCHAR(100), -- "order"是SQL保留字！ status VARCHAR(50), -- "status"也是保留字！ ... ); -- ✅ CREATE TABLE orders ( order_no VARCHAR(100), -- 改个名，加个后缀 order_status VARCHAR(50), -- 加个前缀，避免冲突 ... );总结
今天我们学习了MySQL中3个容易被忽略但非常重要的知识点，简单回顾一下：
- 存储引擎：直接用InnoDB就对了，支持事务、行级锁、崩溃恢复，MyISAM已基本淘汰
- 数据类型：按实际范围选类型，不要什么都用VARCHAR(255)，INT够用就别用BIGINT，手机号用CHAR(11)
- 表设计规范：每个表必须有主键、时间字段、软删除标记，命名要有意义，字段不要允许NULL
💡学习建议：光看不练等于白学！建议打开MySQL，把上面的建表语句亲手敲一遍，对比一下规范和不规范的写法。然后回去看看你之前项目里的表设计，有没有踩到这些坑——有的话趁早改，越往后改成本越高。
今天的内容就到这里啦，希望对你有帮助！