1. 建库建表这一步决定了后面所有的坑多不多很多人知道CREATE TABLE这条语句怎么写就以为建表结束了实际上一张表的身板结不结实从建表那一刻就注定了。我在处理某教学管理模拟项目时数据库结构一开始没有认真设计后面改字段类型、调索引、跑批量导入每一步都在还债。今天这一篇是“MySQL数据的增删改查”系列的第一篇先聊透增删改查四个动作怎么落地它们是后面一切报表、接口、业务系统开发的地基。以这套模拟项目为例先建库建表。库名我习惯用业务领域比如school_db表名用复数或单数都行关键是一套标准走到底不要昨天用order、今天用ordersMySQL 里order还是保留字命名不规范直接给你语法错误。我用的是一套比较稳的写法CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE school_db; CREATE TABLE IF NOT EXISTS student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键自增, name VARCHAR(50) NOT NULL COMMENT 姓名, age TINYINT UNSIGNED NOT NULL COMMENT 年龄, gender ENUM(male, female, other) NOT NULL DEFAULT other COMMENT 性别, email VARCHAR(100) UNIQUE COMMENT 邮箱唯一, enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入学时间, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1-有效0-禁用 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT学生信息表;说一下为什么这么设计。字符集选utf8mb4是底线中的底线它会存表情符号utf8mb3在 MySQL 5.7 以后已经不够用了排序规则选_unicode_ci是为了让比较不区分大小写搜索时更宽容。引擎选InnoDB你如果还在用MyISAM事务、行锁、崩溃恢复一个都用不上2020 年后的应用场景基本可以告别它了。TINYINT UNSIGNED给年龄是刚好够用的0 到 255 岁不浪费一个字节状态字段用TINYINT存数字标记比字符串存储省很多空间。还有这个enrolled_at字段默认值设为CURRENT_TIMESTAMP好处是每次插入数据时不用手动管这个值MySQL 自动帮你盖上时间戳。很多人会问为什么不用DATE类型因为业务里往往需要精确到时分秒——比如凌晨 03:26 报名和上午 10:30 报名对运营分析来说是有区别的。用DATETIME留出扩展空间。这个阶段最容易被忽略的是COMMENT注释。给每个字段写注释短时间内看不出价值过了三个月回头看表结构你会非常感谢当时的自己。数据库管理员交接、迭代表结构、写接口文档全靠这些注释续命。还有一种很实用的小操作如果你不确定表结构是否正确可以用SHOW CREATE TABLE student;查看完整的建表语句。这个命令比DESC student信息量大得多它会把你表的所有属性原样打印出来排查建表问题、确认字段默认值一步到位。2. INSERT插入数据不只是往表里扔行还得考虑约束与效率2.1 单条插入与批量插入的取舍增删改查里的“增”对应 SQL 就是INSERT。基础的写法所有人都知道INSERT INTO student (name, age, gender, email) VALUES (张同学, 20, male, zhangexample.com);注意我在 SQL 里没有列出id、enrolled_at和status三个字段。理由很简单id是自增主键MySQL 自己负责生成enrolled_at有默认值status也有默认值1。这就是建表时设计默认值的好处插入语句精简得多也不容易漏字段。但实际项目里最常用的其实是批量插入INSERT INTO student (name, age, gender, email) VALUES (李同学, 21, female, liexample.com), (王同学, 19, other, NULL), (赵同学, 22, male, zhaoexample.com);批量插入比逐条INSERT快很多因为网络往返次数大幅减少InnoDB 引擎也能在同一个事务里更高效地写入数据。实测在局域网环境往一个 50 万行的表里插入 10 万条数据逐条插入与批量插入的耗时差距能达到几十倍。批量插入一次塞个几百条到一千条比较合适一次塞十万条反而会带来事务过大、锁持有时间过长的反效果。2.2 字符集与占位符两个最常踩的暗坑如果插入的中文乱码了先检查连接字符集不是表字符集的问题。大部分情况下是因为客户端连接 MySQL 时用了latin1或者默认字符集和表的utf8mb4不匹配。命令行里可以显式指定mysql --default-character-setutf8mb4 -u root -p在代码里用连接串时也要把characterEncodingutf8写清楚。另一个暗坑是通过代码操作数据库时不要直接拼 SQL 字符串而是用预编译占位符。以 Java 的 JDBC 为例参数用?代替通过PreparedStatement执行。这样做的好处不只是防注入更重要的是MySQL 会缓存相同的 SQL 模板执行效率比拼接字符串高得多。2.3 自增主键插入时的“意外成功”与“可见失败”当你插入数据时没有指定idMySQL 会按当前最大值加 1 生成。但你删除过数据再插入时会发现id不是连续的这是 InnoDB 自增的天然行为——它不会复用已删除的 id。我见过有人为此手动修改自增起始值想让 id 保持连续说实话没有意义主键只要求唯一不要求连续。强行去调连续性只会引入并发风险和不必要的维护成本。如果你确实需要某个字段不能重复比如email在表设计时已经加了UNIQUE约束那么插入重复邮箱时会直接报Duplicate entry。从业务层面看是一条报错从数据完整性看这是救命绳。千万不要在应用层写“先查一下有没有再决定插入”因为并发场景下两次查询和一次插入之间状态早就变了只有数据库的唯一索引能真正兜底。3. DELETE删错一行数据往往连后悔的机会都没有3.1 永远带着 WHERE 上路删除数据的语句是DELETE FROM student WHERE id 5;这句话看起来人畜无害问题都在“WHERE”上。没有WHERE的删除就是DELETE FROM student;这条会清空整张表的所有记录。列位这不仅危险而且它真的会被执行——不是只有新手会犯这个错工作多年的工程师深夜上线时复制错 SQL、忘了加条件按回车的情况也真实发生过。生产环境里为什么要有“操作前备份”这样的安全机制就是为了兜住这种瞬间的失误。我的实操建议是删除前先SELECT一下同样的条件看看会命中哪些行。比如SELECT * FROM student WHERE id 5;确认这个 id 对应的数据是对的再执行DELETE。有人觉得这一步多余实际上这是在给理智留出时间。3.2 为什么删除要用事务包裹如果一次要删多条数据别裸奔执行START TRANSACTION; DELETE FROM student WHERE id IN (10, 11, 12); -- 确认删除的行数符合预期后提交 COMMIT; -- 如果发现不对ROLLBACK;事务是数据库给的后悔药。开启事务以后删除操作只在当前会话里可见其他会话看不到执行完之后还没提交数据文件里其实没真正改。你觉得结果和自己预期一致才COMMIT否则ROLLBACK干净利落什么痕迹都不留。你可能会想删除性能怎么样DELETE其实不是物理删除。InnoDB 内部是把记录标记为删除真正的物理清理发生在后续的purge线程。所以一张大表频繁删除后会有大量“空洞”占据存储空间查询速度反而变慢。这是另一个话题涉及到碎片整理以后有机会单独展开。现在你只要记住频繁删除的表会“虚胖”需要定期用OPTIMIZE TABLE来整理或重建。3.3 TRUNCATE 和 DELETE 分清楚别用混TRUNCATE TABLE student;也是清空表但它有所不同它本质是重建表结构速度快得多不能加 WHERE不能回滚大部分情况下并且会重置自增主键。什么时候用TRUNCATE测试环境要清空一张表或者要处理历史数据且确定不要了用它合适生产环境对核心业务表请保持零容忍。另外注意TRUNCATE在 MySQL 里不管你开了多少事务它都会让该表的结构进行隐式提交这经常让不熟悉的人误以为数据丢了实际上是它的事务行为和 DELETE 完全不同。4. UPDATE你以为只改一行实际可能改了一堆4.1 修改语句的 WHERE 约束更严格更新数据的基本格式是UPDATE student SET age 21 WHERE id 5;理论上和 DELETE 一样必须用WHERE限制影响范围。但实际开发中有人写UPDATE student SET status 0;想禁用某几个学生结果把全表状态都改了这就属于事故现场。正确姿势是要么带上明确的WHERE条件要么先SELECT COUNT(*)评估一下影响行数。4.2 小心 UPDATE 与 SELECT 之间的时间差之前提过不要搞“SELECT 判断再 UPDATE”这种先查后改的姿势。并发场景下查和改之间的间隙就是问题窗口。举个例子你不用事务直接执行SELECT balance FROM account WHERE id 1; UPDATE account SET balance balance - 50 WHERE id 1;如果两个请求同时读到余额是 100然后各自减 50最终结果可能变成 50 而不是 0。这就是经典的丢失更新问题。正确处理方式是单条原子自减UPDATE account SET balance balance - 50 WHERE id 1;或者显式开启事务并配合行锁SELECT ... FOR UPDATE。对新手来说记住一条原则就够能在一条 SQL 里完成的逻辑不要拆成多条数据库的原子性比应用层的多步操作可靠得多。4.3 UPDATE 的真实代价行锁与事务膨胀很多人只关心 UPDATE 的语法没关心它背后是怎么运作的。InnoDB 默认使用行级锁。当一条 UPDATE 语句执行时它会锁定所有符合 WHERE 条件的行。这意味着如果一个事务更新了某一行但还没提交另一个事务想更新同一行就必须等待。生产环境里一个不恰当的批量 UPDATE 会把整张表堵住应用接口全部超时这是非常常见的线上故障原因。那么实际工作中批量更新应该怎么处理如果必须更新大量数据比如给一批学生统一修改状态分批是对的UPDATE student SET status 0 WHERE status 1 AND id BETWEEN 1 AND 1000;一次控制一千行左右观察耗时和锁等待情况。全部一把梭的后果往往是锁范围太大、事务时间太长直接拖垮其他读写请求。如果确实要更新的行非常多可以在低峰期分多次执行每次隔几秒再来一次避免长时间持锁。还有一个小细节UPDATE 语句里字段重新赋值为自身时比如SET age ageMySQL 可能认为该字段没变化而不真正更新记录。新版 MySQL 的binlog行为对这类无变化更新有不同的处理具体细节说实话需要结合版本查看但你要理解UPDATE 并非每次都会物理写一行这是 MySQL 的一个隐藏优化点。如果你需要记录“更新时间”可以在表里加一个updated_at字段用DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这样每次修改记录都会自动带上最新时间逻辑清晰也不需要应用层参与。5. SELECT查询不是拼出来就行要能读懂数据的含义5.1 基础查询与 WHERE 条件组合增删改查里查询的语法是最灵活的SELECT id, name, age, email FROM student WHERE status 1 AND age 18 ORDER BY age DESC LIMIT 20;它表达的意思是从student表里先过滤状态有效、成年的人再按年龄从大到小排序最后只取前 20 条。理解 SQL 的执行顺序对排查问题很重要——从逻辑层面看SQL 先读表(FROM)再过滤(WHERE)再做投影(SELECT的字段筛选)之后排序、分页。所以WHERE里不能用SELECT子句里定义的别名这是初学者最易迷惑的一个点。AND和OR优先级问题你也要上心WHERE a 1 OR b 1 AND c 1很多人以为是先算 OR 再算 AND实际上 MySQL 里AND优先级比OR高。要想明确分组加上括号不要炫技依赖默认优先级。5.2 聚合函数与分组从“看每一行”到“看一组”当你想统计每个年龄段的人数聚合查询就登场了SELECT CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 22 THEN 适龄 ELSE 超龄 END AS age_group, COUNT(*) AS cnt FROM student WHERE status 1 GROUP BY age_group ORDER BY cnt DESC;这条查询用CASE把年龄映射为分组标签然后统计每组人数。这里有个 MySQL 的大坑GROUP BY后面可以写SELECT里的别名age_group这在标准 SQL 里并不一定被允许但 MySQL 支持。不过不用去依赖这个特性——把同样的表达式写在GROUP BY后面更稳妥也利于跨数据库迁移。聚合函数里常用的除了COUNT还有SUM、AVG、MAX、MIN。它们在聚集时忽略NULL值——COUNT(*)数行数COUNT(age)只数 age 非空的行。这两个的语义差异在统计真实数据时经常导致报表数字对不上值得花时间确认需求到底是哪种。5.3 分页查询与性能隐患页面展示数据分页是躲不开的。简单写法是SELECT * FROM student ORDER BY id LIMIT 10 OFFSET 20;LIMIT 10 OFFSET 20表示跳过前 20 行取 10 行。第 1 页是OFFSET 0第 2 页是OFFSET 10第 n 页是OFFSET (n-1)*10。大表分页就不推荐了。OFFSET越大MySQL 需要扫描并丢弃的行越多。比如你要取第 100 万页的数据它实际上还是会先把前 1000 万行翻一遍性能可想而知。这种情况下改成分页键游标模式更实用SELECT * FROM student WHERE id 1000000 ORDER BY id LIMIT 10;也就是记住上一页最后一条数据的id下一页按大于这个 id 来查。这个方案的好处是能走主键索引数据量再大也能稳定快速。很多业务系统的列表接口翻到后面干脆禁用页码跳转用“加载更多”根本原因就在这里。5.4 表的连接查询CRUD 进阶的分水岭单表查询到一定阶段你会发现业务数据不会乖乖躺在一张表里。举例来说学生选了课程课程信息在course表选课关系在course_selection表。要查出某个学生的选课情况就得用JOINSELECT s.name, c.course_name FROM student s JOIN course_selection cs ON s.id cs.student_id JOIN course c ON cs.course_id c.id WHERE s.id 5;这段 SQL 把三张表串起来了。查询原理上先确定第一个表的一行然后根据关联条件去第二个表找匹配的行再以此类推。理解JOIN的关键在于它是通过主键、外键建立起的逻辑关系而不是拍脑袋随随便便把两张表堆在一起。自己在实验环境里敲一遍就会明白JOIN写多了会出现关联字段没有索引导致全表扫描的问题。关联字段上加索引是查询性能的第一道防线。另外什么时候用LEFT JOIN而不是JOIN当你需要把左表里那些在右表找不到匹配的记录也保留出来时LEFT JOIN就派上用场了。比如想查出所有学生的选课情况没选课的学生也要出现在结果里这时LEFT JOIN才是对的。6. 从零开始动手练一套帽子数据模型走一遍增删改查纸上谈兵终究隔一层。我建议在本地搭一个练习环境MySQL 版本 5.7 或 8.0 都行用查询练手远比看一百篇文章有效。建一张业务表模拟电商库存表CREATE TABLE IF NOT EXISTS product ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, category VARCHAR(50), price DECIMAL(10, 2) NOT NULL, stock INT NOT NULL DEFAULT 0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;先做增INSERT INTO product (product_name, category, price, stock) VALUES (机械键盘, 外设, 399.00, 50), (无线鼠标, 外设, 89.90, 120), (27寸显示器, 显示设备, 1299.00, 15), (USB-C扩展坞, 配件, 249.00, 0);这里DECIMAL(10, 2)是金额字段的标配千万别用FLOAT存价格浮点精度导致的对不上账会让你怀疑人生。stock默认 0正好演示零库存的场景。试试改-- 把扩展坞的库存补上价格调整到 229 元 UPDATE product SET stock 30, price 229.00 WHERE product_name USB-C扩展坞;再试试删-- 删除那个 0 库存的商品支撑演示 DELETE FROM product WHERE stock 0 AND category 配件;注意这里用了AND做了双重条件避免误伤其他 0 库存商品。如果你还是新手建议在这个步骤前后都执行SELECT * FROM product;看效果对比一下数据的变化。查一下最贵的商品SELECT product_name, price FROM product ORDER BY price DESC LIMIT 3;以及每个品类的库存平均值SELECT category, AVG(stock) AS avg_stock FROM product GROUP BY category HAVING avg_stock 0;HAVING和WHERE的区别是WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。这是新手特别容易混淆的一对建议亲手跑一遍感受两者的差异比背十遍定义都管用。7. 事务与错误处理别让数据操作变成事故7.1 事务的 ACID 没那么玄乎你已经看到了事务对删除和更新的保护作用现在再从原理上把它串起来讲。事务就是一组操作要么全部成功要么全部回滚。经典例子是转账A 扣钱、B 加钱任何一个失败都不能留一半操作在账上。START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; -- 如果余额不足应主动回滚 UPDATE account SET balance balance 100 WHERE id 2; COMMIT;MySQL 里 InnoDB 支持事务而 MyISAM 完全不支持。这也是我前面坚持要用 InnoDB 的最重要原因。事务有四个特性——原子性、一致性、隔离性、持久性理论上讲起来很枯燥但你在实操中不断遇到问题就会理解原子性对应“要么都做要么都不做”一致性对应“操作前后数据整体状态是合法的”隔离性对应“事务之间不能看到彼此的中间状态”持久性对应“提交了就不怕丢”。7.2 隔离级别的实战理解MySQL 默认的事务隔离级别是REPEATABLE READ也就是可重复读。它保证同一个事务里的多次查询结果一致。这个级别对大多数业务都够用能防住“幻读”的问题——在事务里两次查询同一个范围结果集不会凭空多出几行。如果你不确定当前级别可以执行SELECT transaction_isolation;或者老版本SELECT tx_isolation;我的建议是业务开发阶段保持默认级别不要轻易调整。隔离级别调低了可能出脏读调高了性能下降。除非你非常清楚自己在做什么否则默认值也不是什么丢人的选择。7.3 错误处理在代码里捕获数据库异常光靠 SQL 层面的约束还不够应用侧的异常处理也要跟上。以 Python 为例使用pymysql插入时碰到唯一键冲突要主动捕获import pymysql try: with connection.cursor() as cursor: sql INSERT INTO student (name, age, email) VALUES (%s, %s, %s) cursor.execute(sql, (陈同学, 20, chenexample.com)) connection.commit() except pymysql.err.IntegrityError as e: print(f数据插入失败违反唯一约束{e}) connection.rollback()IntegrityError专指唯一约束、外键约束之类的问题OperationalError则是连接丢失、语法错误这类数据库本身运行层面的问题。把它们分类捕获日志清晰人也清醒。建议所有写操作都包上 try/except并在异常分支调用rollback()这个动作常被忽略——一旦事务没有正常提交连接池里的连接可能会带着未结束的事务回到池子下个请求拿到这个连接时就会看到脏状态。8. 索引在增删改查里的隐形力量8.1 查询快不快先看索引对不对建索引这件事对增删改查的影响非常大尤其是查询。一个最简单的例子SELECT * FROM student WHERE email zhangexample.com;如果email没有索引MySQL 会从头到尾扫全表直到找到这个邮箱。几千行数据可能感知不强几百万行时你会明显感受到卡顿。给email建个唯一索引就完全不一样了ALTER TABLE student ADD UNIQUE INDEX idx_email (email);MySQL 直接通过索引 B 树查找跳过大片数据查询效率可以有数量级提升。8.2 索引不是越多越好索引不是免费午餐。它加快 SELECT但会拖慢 INSERT、UPDATE、DELETE因为每写一条记录索引结构也要同步维护。索引越多写入开销越大磁盘占得也越多。所以建索引的原则是优先给WHERE条件、ORDER BY、JOIN关联字段建索引频繁更新的字段不要盲目加索引。拿前面的场景举例status字段如果只有 0、1 两个值选择性很低单独建索引意义不大除非业务里绝大多数都是某个状态而需要快速筛选出少数状态。这种经验要靠实践积累但理解“索引是增加的存取路径”这一点就不会盲目建了。8.3 查询背后的索引优化技巧实际 SQL 调优时可以借助EXPLAIN看执行计划EXPLAIN SELECT * FROM student WHERE age 20;EXPLAIN输出的type列ALL表示全表扫描ref或const表示走了索引。把这个工具当成体检报告来看多跑几条查询你会慢慢建立起“数据库怎么执行我的语句”的直觉。这里有个最常见的坑对字段做函数运算会导致索引失效。比如SELECT * FROM student WHERE YEAR(enrolled_at) 2024;表面看起来没问题但一加上YEAR()函数enrolled_at上的索引基本就废了MySQL 不得不逐行计算再比。正确写法是用范围条件SELECT * FROM student WHERE enrolled_at 2024-01-01 AND enrolled_at 2025-01-01;这个写法不仅走得了索引语义也更清楚查整个 2024 年的数据。9. 实践收尾用一套小规则稳住日常操作节奏做增删改查这一套操作语法本身并没有多难真正体现水平的是在操作过程中有没有一套硬性的执行纪律。我把身边老工程师的一些习惯整理成几条小规则你们可以直接抄作业任何 UPDATE 和 DELETE 语句必须带 WHERE除非你写的就是一次性的整表清理脚本并且已经在测试环境验证过。写操作之前先跑同条件 SELECT 确认数据范围。多行数据变更务必使用事务养成COMMIT前核实影响行数的习惯。线上表格结构变更不要在业务高峰期执行。加索引、加字段、改类型都可能在执行时锁表或锁行。新增字段时优先考虑设置合理的默认值能避免大量历史数据回填的痛苦。具体到查询持续记住 SQL 的执行逻辑顺序FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。你写的每条 SELECT 都会按这个顺序被数据库理解你排错也按这个顺序来就不会抓瞎——为什么WHERE里不能引用别名因为WHERE的执行阶段在SELECT投影之前别名那时还没算出来。本期的内容是整个“MySQL数据的增删改查”系列的第一部分把增删改查各自的语法、常见误区和性能影响讲了一遍建表和索引也顺带带了一程。后续我会继续展开更复杂的多表关联、子查询优化、慢查询日志分析、存储引擎底层原理等内容。这个表我还没删实验环境还留在那里——实战过的东西永远比看过的更扎实。