提起MySQL很多刚入行的同学首先接触的就是CRUD、约束和基本类型。这三个词听起来基础却几乎决定了你后续写SQL的质量和数据库整体的“健康度”。CRUD对应增删改查是业务操作的主体约束负责守住数据的完整性防止脏数据混进来基本类型则决定了字段能存什么、怎么存、占多少空间。三者合在一起就是一张表从设计到日常使用的完整框架。这篇文章适合那些已经会写简单SELECT、正准备系统补齐数据库基础的朋友也适合干了几年业务、想回头把地基夯实的开发者。我会直接从实际项目视角出发把概念、语法、选型思路和踩坑点一起讲透。1. 从一张学生表说起CRUD、约束、类型各自扮演什么角色1.1 三个基础概念之间的关系很多人以为CRUD、约束、类型是三个独立的知识点实际它们在设计表结构时是一体的。举个例子你要设计一张学生表得先想清楚学号用数值还是字符串来存——这是类型问题学号不能为空、不能重复——这是约束问题把学生信息写入、修改、查询出来——这是CRUD问题。可以这样类比类型是“容器”的规格约束是“容器”的规则CRUD是“容器”里的日常搬砖。没有类型的字段数据存进去就是一团乱麻没有约束的表重复、空值、不合法的数据会悄悄溜进来而CRUD则是你每天都要重复的流程。三者并不是平行的三条线而是层层嵌套的关系建表时定好类型和约束之后CRUD都会遵守这套“合同”。1.2 为什么新手常在这块栽跟头我见过很多同学写CRUD非常熟练但一让他建表就露怯。最常见的问题有三个。一是类型乱选。明明存一个年龄可以用TINYINT偏要用BIGINT白白浪费空间存手机号有人用INT结果溢出加报错最后不得不改成VARCHAR。类型选错轻则浪费存储重则直接丢精度或者插入失败。二是约束缺失。很多业务表连主键都不设或者所有字段都允许NULL导致同一批数据出现多条完全相同的重复记录程序里统计数字一塌糊涂。外键约束更是被很多人直接放弃结果子表里的记录指向一个不存在的父记录报表联查全靠碰运气。三是不理解CRUD背后的代价。DELETE一下能删得很爽但忘带WHERE会把整张表清空UPDATE不带条件会全表更新INSERT大批量数据不注意效率事务大到把连接拖死。这些坑都不是语法问题而是对机制理解不深。2. MySQL基本数据类型选型决定成败2.1 数值型INT、DECIMAL、FLOAT各管一段MySQL数值类型从TINYINT、SMALLINT到INT、BIGINT区别主要在于存储字节和取值范围。TINYINT只占1字节范围是-128到127INT占4字节范围约正负21亿BIGINT占8字节常用于雪花ID或订单号。业务里用INT存用户ID、状态码、计数等都没问题但要注意“无符号”和“自增”的搭配。浮点和定点是另一个重点。FLOAT和DOUBLE属于浮点类型存储小数时会有精度损失。比如存金额0.1反复计算后可能变成0.100000000001。如果你要给商品价格、账户余额算钱永远用DECIMAL。DECIMAL是定点数会精确保存数字比如DECIMAL(10,2)表示总长度10位、小数2位适合金额、税率等场景。实际项目里我通常这样选状态、年龄、计数用TINYINT或INT主键用BIGINT或者INT自增金额一律DECIMAL(10,2)或DECIMAL(12,2)后面要算报表才放心。2.2 字符串型CHAR、VARCHAR、TEXT别混用CHAR是指定固定长度的字符串比如CHAR(10)存5个字符也会占10个字符的空间但读取速度快。VARCHAR是可变长度存多少占多少最多还占用额外的长度标记空间适合长度差异大的场景。TEXT用来存大文本比如文章正文、备注。一个常见误区是很多人以为VARCHAR(255)是最大值其实在MySQL 5.7以后VARCHAR长度可以很大但受行大小限制。一般来说短号码、固定编码用CHAR用户名、邮箱、地址用VARCHAR超过几千字符的大段文本用TEXT。要注意VARCHAR括号里的是字符数而不是字节数如果一个字段存中文VARCHAR(20)可以存20个汉字但若存的是一串英文也能存20个实际占用字节跟字符集有关。这里有一个实战提醒不要把手机号存成数值类型。手机号是取值范围巨大的号码用INT会溢出用BIGINT虽然不溢出但你没法保存前导零将来做手机号段匹配也会非常别扭。正确做法是用VARCHAR(20)。2.3 日期时间型DATETIME、TIMESTAMP、DATE选型MySQL的日期时间类型有DATE年月日、TIME时分秒、DATETIME年月日时分秒和TIMESTAMP时间戳。DATE适合生日、节日DATETIME适合记录创建时间、更新时间TIMESTAMP实际存储的是UTC时间戳存储范围到2038年比较适合跨时区场景。很多人纠结DATETIME和TIMESTAMP。DATETIME不带时区信息存进去是什么就是什么TIMESTAMP会自动根据会话时区转换适合全球业务但它的取值范围小。我平时做业务系统默认用DATETIME除非明确要做时区转换才用TIMESTAMP。创建时间和更新时间建议由数据库自动维护MySQL 5.6之后可以用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP省去应用层赋值。2.4 类型选择的常见误区与最佳实践第一个误区是“什么字段都用VARCHAR”。VARCHAR很方便但索引效率低于定长类型数字的比较也是数值型更快。第二是使用BOOL类型。MySQL并没有真正的BOOL它用TINYINT(1)代替赋值时0是假非0是真。这个可以接受但不要被“BOOL是独立类型”误导。最佳实践可以总结成一句话能用整型绝不用字符串能用定长绝不用变长能用日期类型绝不用字符串硬凑。日期用字符串存是大忌排序会按字典序排错也无法使用日期函数。另外字段长度不要贪大VARCHAR(255)只是个通用选择如果你的业务里邮箱通常不超过50位那就设为VARCHAR(64)或VARCHAR(100)为索引留出更多余地。3. 约束体系撑起数据完整性的骨架3.1 主键约束与自增每一行数据的身份证主键PRIMARY KEY是唯一标识一行记录的字段它自动包含非空和唯一两个属性。一个表只能有一个主键但主键可以包含多个字段这叫联合主键。实际业务中我最推荐用无业务含义的自增主键即INT或BIGINT配合AUTO_INCREMENT因为自增主键插入有序、索引树稳定。主键不要选可变长业务字段比如身份证号、手机号因为一旦业务变动要更新这些字段会很痛苦。若确实需要业务唯一键可以在主键之外再加一个唯一索引。主键一旦建立就不要轻易修改因为它关系到所有外键引用和索引结构。3.2 非空约束与默认值给数据入口装闸门非空约束NOT NULL规定字段不能为空默认值DEFAULT则在不传值时给出一个兜底值。两者看似简单却是防止脏数据的第一道防线。我在设计表的时候凡是核心业务字段比如用户名、订单金额、状态都尽量NOT NULL。注意VARCHAR字符串的默认值常用空字符串而不是NULL因为NULL参与计算时往往带来麻烦比如SUM函数会忽略NULL。日期字段如果允许为空访问层也要做好空值判断。还有一个小技巧建表时给数值类型设置默认0、枚举状态设置默认初始值给时间字段设置默认当前时间这样应用层少传很多不必要的参数。3.3 唯一约束与业务逻辑唯一约束UNIQUE能够保证字段或字段组合在表里不重复但允许NULLNULL不参与唯一性判断。它的典型应用包括用户名、邮箱、订单流水号等。如果要保证两个字段组合不重复可以直接在建表时创建联合唯一索引。在业务里唯一约束不只是防呆更是数据一致性的兜底。比如你有一个领取优惠券的表同一个用户对同一个优惠券只能领取一次此时在user_id和coupon_id上建联合唯一索引最安全。如果只靠应用层先查后插在高并发下很容易出现重复记录数据库端唯一约束才是真正的最终防线。3.4 外键约束关联表之间的行业规矩外键FOREIGN KEY用于建立两张表之间的引用关系。比如订单表里有一个user_id应关联用户表的id。有了外键数据库会强制要求插入的user_id必须真实存在于用户表中不能插入孤立数据同时还能对父表删除、更新时做出限制比如不允许删除仍然被订单引用的用户。不过在互联网行业很多人会刻意不用外键。原因是高并发写入时外键约束检查会影响性能还可能造成级联操作的不可控。取而代之的是在应用层保证业务完整性表之间只保持逻辑关联。这个选择本身无所谓对错但你要知道如果不建外键就必须在代码里补齐一致性的检查如果建了外键就要承担对应的性能与维护成本。3.5 检查约束与MySQL的现状严格来说MySQL从8.0.16版本开始才真正支持了受强制执行的CHECK约束。在8.0.16之前CHECK约束在语法上允许却不被强制执行很多人建了等于没建。虽然现在可以用CHECK来做字段取值范围的校验比如年龄必须在0到150之间但我仍然建议把复杂的业务校验放在应用层数据库里的CHECK适合简单、稳定的规则。4. CRUD实操从建库建表到删改查的完整闭环4.1 建库建表时的细节字符集、引擎和字段顺序建库建表是整个CRUD的起点也是最容易埋雷的地方。建议第一步就明确字符集CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;为什么用utf8mb4因为utf8在MySQL里最多只能存3字节的字符很多生僻字和Emoji存不进去而utf8mb4是全量支持。排序规则选择utf8mb4_unicode_ci对大多数中英文业务都够用。引擎方面InnoDB是默认选择它支持事务、行锁、外键适合绝大多数OLTP场景。如果你的表只用来做日志、且不关心事务可以考虑MyISAM但在MySQL 8.0中MyISAM已经一步步边缘化了。所以我的习惯是无脑InnoDB。建表时建议把主键放第一列然后按字段的取用频率和大小合理排布。InnoDB的行存储在磁盘上的顺序并不完全依赖列顺序但主键和常用索引字段前置会让查询计划更清晰。4.2 增INSERT的几种写法与批量插入INSERT的核心是把数据写入表。最简单的写法是指定列名然后列出值。INSERT INTO user (name, age, email) VALUES (张三, 18, zhangsanexample.com);这里强烈建议总是显式指定列名而不是省略列名直接写VALUES。否则一旦表结构加了字段你的INSERT语句就会报错或错位。如果需要同时插入多条记录可以一条语句写多个VALUESINSERT INTO user (name, age, email) VALUES (李四, 20, lisiexample.com), (王五, 22, wangwuexample.com);这种方式比一条条执行要快很多因为它只发起一次网络往返。批量插入的规模也不是越大越好一般建议每次几百行到几千行具体取决于单行大小。如果数据来自文件或者异构系统优先用LOAD DATA INFILE更快。4.3 改UPDATE的语法与最容易踩的坑UPDATE用于修改已有记录UPDATE user SET age 19 WHERE id 1;所有的教训里最惨烈的一条是漏掉WHERE。一旦漏了整张表的数据都会被更新。而MySQL在高版本里默认不开启“安全更新模式”也就是没有WHERE的UPDATE和DELETE都能直接执行。我经常建议团队把我方环境的安全参数打开也可以在客户端工具里设置语句必须带条件。另外UPDATE时涉及多个字段用SET同时更新比逐条更新效率高。同时要注意如果字段上有索引频繁更新索引字段会带来额外开销。实际业务中更新前最好先SELECT确认条件能命中正确记录再执行UPDATE。4.4 删DELETE、TRUNCATE、DROP三兄弟的区别DELETE是逐行删除可加WHERE条件可以配合事务回滚。执行后自增ID不会重置默认情况下不会释放表存储空间。DELETE FROM user WHERE id 100;TRUNCATE是清空表数据一次性释放存储不能加WHERE也不能回滚。DELETE删除千万行时事务日志和回滚段压力很大而TRUNCATE几乎瞬间完成。如果你的需求是彻底清空表TRUNCATE可快得多。DROP是删除整张表结构数据、索引、约束全部消失。三者使用场景完全不同测试环境随便用生产环境一定要事先备份。安全删除的习惯是先SELECT看命中行数再改成DELETE。或者写DELETE语句时先把WHERE条件单独写在最前面反复确认后再补充DELETE开头。4.5 查SELECT的基础、条件、排序与分页SELECT是CRUD里最常用的也最值得打磨。基础语法很容易但性能取决于你是否会用索引、是否只查询必要的字段。SELECT id, name, age, email FROM user WHERE age BETWEEN 18 AND 30 AND name LIKE 张% ORDER BY age DESC LIMIT 20 OFFSET 40;这里LIKE左边不是%才可以利用索引ORDER BY排序字段尽量与WHERE条件中的索引方向一致LIMIT/OFFSET是分页的老搭档但OFFSET太大时会扫描大量偏移行。比如第1000000页用LIMIT 20 OFFSET 19999980会非常慢。这时可以用“上一页最后一条记录的id”来做定位SELECT id, name, age, email FROM user WHERE id 1000000 ORDER BY id ASC LIMIT 20;还有一点SELECT *在业务中要尽量避免。字段过多会增加网络传输和底层行拷贝的成本也容易由于字段顺序变动导致程序错乱。5. 实战案例图书管理系统的核心表操作5.1 需求分析与建表SQL假设我们要为一个非常简单的图书管理系统设计核心表。业务背景是读者可以借书还书一本书可以被多次借阅需要记录每次借阅流水。为了体现类型和约束这里建三张表读者表、图书表、借阅流水表。先建读者表CREATE TABLE reader ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, card_no VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1-正常0-停用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;card_no是借阅卡号要求业务唯一所以用UNIQUE。status用TINYINT节省空间并加NOT NULL以及DEFAULT。created_at直接用数据库当前时间。图书表CREATE TABLE book ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, isbn VARCHAR(20) NOT NULL, title VARCHAR(100) NOT NULL, author VARCHAR(50) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, stock INT UNSIGNED NOT NULL DEFAULT 0, UNIQUE KEY uk_isbn_title (isbn, title) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里isbn虽然业务上应该唯一但同一本书的不同版本可能ISBN相同所以我们用isbntitle做联合唯一约束防止完全重复的图书记录。price用DECIMAL存金额绝对不用FLOAT。借阅流水表CREATE TABLE borrow_record ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, book_id BIGINT UNSIGNED NOT NULL, reader_id BIGINT UNSIGNED NOT NULL, borrow_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, return_time DATETIME DEFAULT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (reader_id) REFERENCES reader(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有意保留外键约束用来展示关联关系。return_time默认NULL表示未归还。5.2 完整CRUD流程演示新增读者INSERT INTO reader (card_no, name, phone) VALUES (R0001, 小明, 13800000000);新增图书INSERT INTO book (isbn, title, author, price, stock) VALUES (9780000000001, MySQL入门, 张三作者, 59.00, 3);查询某本书的库存SELECT id, title, stock FROM book WHERE id 1;插入借阅记录时同时扣减库存INSERT INTO borrow_record (book_id, reader_id) VALUES (1, 1); UPDATE book SET stock stock - 1 WHERE id 1 AND stock 0;这里用stock 0作为条件可以在高并发下避免超卖。真正生产环境还需要结合事务和行锁。还书时更新归还时间并回补库存UPDATE borrow_record SET return_time NOW() WHERE id 1 AND return_time IS NULL; UPDATE book SET stock stock 1 WHERE id 1;5.3 约束如何在实际操作中生效我们刚才建的表已经设置了联合唯一约束现在尝试重复插入同样的记录INSERT INTO book (isbn, title, author, price, stock) VALUES (9780000000001, MySQL入门, 张三作者, 59.00, 1);数据库会返回类似“Duplicate entry”的错误这就避免了重复数据。再尝试插入一个不存在的借阅记录比如book_id999INSERT INTO borrow_record (book_id, reader_id) VALUES (999, 1);由于外键约束数据库会报“Cannot add or update a child row”错误。如果你在设计时放弃了外键那么程序里必须在插入前主动确认book_id和reader_id都存在否则就会产生脏数据。6. 常见问题与排查技巧实录6.1 乱码问题字符集惹的祸乱码几乎是每个MySQL新手都会遇到的。表现是数据看起来全是问号或火星文。原因往往出在三层数据库/表字符集、连接字符集、程序中的字符串编码。排查思路很简单先执行SHOW VARIABLES LIKE character_set_%;确认客户端连接字符集是否为utf8mb4。再确认表结构与列字符集。如果是已有表优先改表和列字符集但已存在的乱码数据可能无法自动恢复。新项目请从一开始就统一建库默认字符集为utf8mb4连接串里也加上characterEncodingutf8。这个细节能帮你省下大量排查时间。6.2 类型转换与隐式规则MySQL在比较不同数据类型时会发生隐式转换最常见的是字符串和数值比较。比如SELECT * FROM user WHERE phone 13800000000;phone是VARCHAR但这里会用数字去匹配。如果phone里有非数字字符匹配规则会变得很诡异若字段上建有索引隐式转换常常会导致索引失效。原因在于当字段类型是VARCHAR而比较值是数字时MySQL会把字段值转换为数字进行比较这等于对每一行都做一次转换无法使用索引。正确做法是查询条件里的类型与字段类型保持一致字符串就用引号数值就不加引号。这个习惯比任何技巧都重要。6.3 外键约束失败的原因分析外键约束失败通常有几种情况父表里没有对应主键、外键字段和引用字段的类型或字符集不一致、插入的子表记录违反外键指约束、父表删除时存在子表引用但外键动作设为了RESTRICT。排查时先看表结构SHOW CREATE TABLE borrow_record;对比外键字段的类型、字符集和父表主键类型是否严格一致。例如子表外键是BIGINT父表主键是INT就会报错。另外如果父表和子表某个字段的字符集不同也会无法创建外键。解决方法是统一字符集排序规则。生产中如果遇到外键被阻塞比如外键检查需要等待相关行锁释放可使用SHOW ENGINE INNODB STATUS;查看锁等待信息。6.4 一步步排查SQL语句的调试方法写出SQL后报错不要急着来回改。先在执行计划里看问题EXPLAIN SELECT ... ;EXPLAIN会返回type、key、rows等关键字段。如果type是ALL说明走了全表扫描如果key为NULL说明没用上索引。此时检查WHERE条件里的字段是否有索引或者条件写法是否破坏了索引。如果语句本身没问题但执行很慢可以开启慢查询日志找出哪些查询成本高。日志里记录执行时间、扫描行数和返回行数再结合EXPLAIN优化。整个过程的核心是先定位再改而不是凭感觉加索引。6.5 一些可以提高效率的小习惯最后分享几个我多年养成的习惯。第一建表时把所有常用查询字段的索引想明白但不要盲目给每个字段都建索引写操作会受影响。第二日期字段建议都用datetime类型不要用int存时间戳可读性太差查慢日志时非常痛苦。第三所有线上删除操作前先备份。简单的办法是先执行一句话把数据导出到一张临时表或者用CREATE TABLE xxx_bak AS SELECT * FROM ... WHERE ...快速备份。第四SQL关键字一律大写字段名一律小写加下划线能极大提升可读性。团队协作时代码评审的人扫一眼就能看清结构。我在实际排查问题中最大的体会是绝大多数故障都不是语法不会而是设计时没想清楚类型和约束或者忘了WHERE条件。如果你能把每一张表的字段都认真设计一遍把每条DELETE和UPDATE都习惯性带上条件数据库这块的底子就算稳了。后面有时间可以再深入聊聊索引优化和事务隔离级别那又是另一个值得写透的话题。
