企业官网建设流程全解析

很多人在学数据库的时候，都有一种感觉：SQL语句看着简单，好像就是"增删改查"四个字，但真到了写业务代码或者接手项目的时候，才发现自己连一条像样的查询都写不利索。尤其是MySQL，作为最流行的开源关系型数据库之一，搞清楚它最基础的INSERT、SELECT、UPDATE、DELETE，比背一堆高深理论有用得多。这篇我打算从实际开发的角度，把MySQL的增删改查（CRUD）完整拆开讲一遍，不绕弯子，直接说人话，给你一套能直接用到项目里的操作思路和避坑经验。

这篇文章适合谁看？刚入门数据库的学生、做后端开发但SQL基础不牢的工程师、以及那些用惯了ORM框架（比如MyBatis-Plus、Hibernate）却很少手写SQL的朋友。你放心，就算你之前完全没写过SQL，跟着这篇文章一步步操作，也能很快上手；如果你已经有经验，那里面关于WHERE条件陷阱、批量插入效率、误操作恢复的内容，也值得花几分钟扫一遍。

1. 先把地基打好：建库建表阶段的几个关键决定

增删改查的前提是你得有表，而表建得好不好，直接决定你后面写CRUD是舒服还是难受。很多新人喜欢拿到需求就写SQL，跳过设计这一步，结果数据冗余、查询缓慢、更新异常，最后全成了屎山。这里我分享一下我建表时心里默认过的一套流程。

1.1 数据库字符集和排序规则别乱选

建库的时候，字符集尽量用utf8mb4，排序规则用utf8mb4_unicode_ci或者utf8mb4_0900_ai_ci。为什么不用utf8？因为MySQL的utf8其实是阉割版，最多存3个字节，像一些生僻字、emoji表情根本存不进去，到时候往表里插数据报"Incorrect string value"错误，你查半天都不一定想到是字符集的问题。

我踩过一次坑：早期做一个小系统，建库用了utf8，上线后用户注册昵称带了个emoji，接口直接500，日志一看就是字符集不支持。后来把库、表、字段全部改成utf8mb4才解决。注意，改了库的默认字符集，原来已经建好的表不会自动跟着改，你需要ALTER TABLE手动去调整，这又是一个大坑。

1.2 主键和常用索引的规划

主键我几乎无条件推荐自增整数（INT UNSIGNED或BIGINT），业务字段做主键（比如身份证号、手机号）有时候听着合理，实际坑很多：一是身份证号涉及隐私，没必要全表到处带；二是字符串主键会让二级索引变大，检索性能下降。

索引也不是越多越好。很多新人喜欢在查询条件的每个字段上都建索引，但索引太多会拖慢写入速度。我一般的原则是：高频查询的WHERE条件字段、JOIN关联字段、ORDER BY排序字段优先建索引，其他的先不加，等真的出现慢查询再说。

1.3 字段类型够用就好，别什么都上大字段

能用TINYINT就别用INT，能用VARCHAR(50)就别用TEXT。字段类型太大，一方面浪费存储空间，另一方面InnoDB在内存里缓存的数据页就变少，间接影响查询性能。

举个例子，状态字段明明只有0、1、2三个值，用TINYINT就够了，有人非要建个VARCHAR(20)存字符串。MySQL的InnoDB对变长字段处理起来更复杂，等数据量上来之后，表空间膨胀得很明显。

我提供一个参考建表语句，你新建一个用户表可以照这个思路来：

CREATE TABLE `user` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', `username` VARCHAR(50) NOT NULL COMMENT '用户名', `nickname` VARCHAR(50) DEFAULT NULL COMMENT '昵称', `age` TINYINT UNSIGNED DEFAULT NULL COMMENT '年龄', `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0-正常，1-禁用', `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

这里有几个细节，你们在建表时可以先养习惯：

• create_time和update_time直接给默认值，这样插入时不写这两个字段也能自动填充，省去应用层手动处理的时间。
• status这类枚举型字段用数字而不是字符串，应用层再翻译成对应的业务含义，查询更快，也避免拼写不统一。

2. INSERT插入数据：从单行到批量，效率差在哪里

插入是CRUD的第一步。很多人写INSERT就只写最简单的单行插入，数据量小的时候没感觉，一旦要初始化几万条数据或者接口并发写入，才发现性能差得离谱。

2.1 基础插入语法和逻辑

单行插入的语法相信你眼熟：

INSERT INTO user (username, nickname, age, email) VALUES ('zhangsan', '张三', 25, 'zhangsan@example.com');

这里有几个细节，你们在建表时可以先养习惯：

• 强烈建议列出字段名再VALUES，不要直接INSERT INTO user VALUES (...)。一旦表结构中间加了个字段，不带字段名的SQL直接就崩了，而且阅读代码的人根本不知道每个值对应什么列。
• VALUES里的字符串注意别漏引号，数字可以不加引号，但建议都加上引号，避免隐式类型转换的问题。
• 插入时不要插入主键，让自增主键自己生成。有些新手喜欢在主键里填业务含义的数字，短时间没问题，时间一长ID就乱了，还会把自增游标搞乱。

2.2 批量插入的正确姿势

如果要插入多条数据，最直观的想法是一条一条INSERT，但在循环里拼命执行单条INSERT是很低效的做法。每一次INSERT都是一次独立的SQL操作，要经过连接、解析、优化、执行、提交这一整套流程，非常浪费。

批量插入就是把多条记录合并到一条SQL里：

INSERT INTO user (username, nickname, age, email) VALUES ('lisi', '李四', 26, 'lisi@example.com'), ('wangwu', '王五', 27, 'wangwu@example.com'), ('zhaoliu', '赵六', 28, 'zhaoliu@example.com');

一条SQL插几百条甚至上千条都可以，但也不是越大越好。单条SQL太大会导致网络传输包过大、锁持有时间过长、事务日志暴涨，我一般控制在500到1000条一批，分批提交。比如你要插一万条数据，可以分成10个批次，每批1000条，不要一口气全塞进去。

批量插入的原理说白了就是减少SQL解析和网络往返次数，把多次小事务合并成少数几次大事务。在MyBatis中，你可以用foreach标签动态拼接VALUES，也可以自定义一个executeBatch工具方法，效果都不错。

2.3 一个真实的批量插入性能对比

我做过一个数据迁移任务，旧的用户表有大概50万条数据要搬到新表。刚开始用最粗暴的逐条插入，程序跑了快半小时才插了5万条，这速度根本没法接受。后来改成批量插入，每批1000条，50万条数据总共也就几十秒。差别在哪？逐条插入相当于你我在工厂流水线上每加工一个零件就重新开一次机器，批量插入则是一次开机连续加工几十个零件，开销集中摊销了，自然快得多。

如果遇到大批量初始化数据的场景，还有几招可以用：

• 先删除索引再批量插入，最后重建索引，因为插入过程中维护索引有额外代价。
• 将事务手动提交改为自动一次性提交，减少fsync次数。
• 中间穿插SELECT COUNT(*)验证数据条数，防止部分失败。

3. SELECT查询数据：WHERE条件的执行逻辑和索引的默契

SELECT是增删改查里最常用也最容易写出花样的操作，但很多人对它的理解停留在"查出结果就行"，完全不考虑查询条件和索引的关系。这里我把最核心的查询逻辑拆开讲。

3.1 基础查询与列的取舍

最简单的查询是查全表：

SELECT * FROM user;

注意，SELECT *在开发调试时可以，上生产环境我是坚决反对的。为什么？因为你不可能需要一张表的所有字段。查出来的列越多，网络传输的数据量越大，内存和CPU的负担越重。并且如果表结构后续加了一个重量级字段（比如TEXT类型的简介），用SELECT *的应用层代码就无端背上这个包袱。

规范的做法是明确列出需要的字段：

SELECT id, username, nickname, age FROM user WHERE status = 0;

这个习惯在ORM框架里往往被忽略，因为写实体类映射的时候会自动把全部字段查出来。但手写SQL的时候要有这个意识，尤其是表字段多、数据量大之后，少查一个字段就少一分开销。

3.2 WHERE条件的底层执行顺序

WHERE条件的本质是从表中筛选出满足条件的行。这里有一个非常重要的知识点：SQL中WHERE条件的执行顺序不是按照你书写顺序来的，而是优化器决定的。很多人以为（虚构的例子）AND前面的条件先执行、后面的后执行，其实MySQL的优化器会基于统计信息选择它认为最优的执行路径。

举个例子：

SELECT * FROM user WHERE age > 20 AND username = 'zhangsan';

你可能会觉得age > 20先过滤再匹配username，但MySQL优化器评估后发现username有索引，单独用username = 'zhangsan'能快速定位到少量行，再在结果集里过滤age > 20，整体代价更低，于是执行计划就反过来了。所以不要自以为聪明地调整WHERE条件的顺序来"优化"SQL，优化器比你更懂数据分布。

三个常见的WHERE细节，经常有人在这里翻车：

• 字符串字段别和数字比较。WHERE mobile = 13800138000会让MySQL把字符串转成数字再比较，一旦字段里有非数字字符，结果可能不对，索引也用不上。
• NULL判断要用IS NULL或IS NOT NULL，不要写成= NULL，在MySQL里= NULL的结果永远是UNKNOWN，查出来的行数会和你预期差很多。我之前排查过一个数据对不上的问题，最后发现就是有人写了一整排xxx = NULL。
• IN里面的元素别太多，几百上千个IN条件会让优化器非常纠结，甚至放弃索引走全表扫描。

3.3 分页查询与排序

分页是项目里几乎躲不开的需求。MySQL最常用的分页写法是LIMIT：

SELECT id, username FROM user ORDER BY id LIMIT 10 OFFSET 20;

这个写法的小问题是偏移量大的时候很慢，比如LIMIT 100000, 10，MySQL依然要扫描前面十万行再跳过，性能是线性下降的。深分页优化我一般用延迟关联或基于游标的方式：

-- 先查主键，再回表查详情 SELECT u.id, u.username, u.nickname FROM user u INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 100000, 10 ) tmp ON u.id = tmp.id;

分页和排序是对好兄弟，但排序字段一定要有索引，否则数据量一大，MySQL就要建临时文件做filesort。一般我把排序字段放在索引的末尾，并注意排序方向和索引方向的匹配，这样order by就能直接走索引顺序，避免额外排序。

3.4 聚合查询和GROUP BY的常见坑

统计类需求离不开聚合函数和分组，最基础的是查数量、最大值、最小值、平均值：

SELECT status, COUNT(*) AS cnt, AVG(age) AS avg_age FROM user GROUP BY status;

写GROUP BY时，有一个非常容易踩的坑：SELECT后面出现的非聚合列，必须出现在GROUP BY里。在MySQL里如果关掉了ONLY_FULL_GROUP_BY这个SQL模式，你甚至可以SELECT一些没分组的列，这时返回的数据是随机的、不可控的。我建议在配置里保持ONLY_FULL_GROUP_BY开启，避免写出潜藏问题的SQL。另外，HAVING是分组后的过滤条件，它和WHERE不同，不能替代WHERE。能用WHERE提前过滤的数据，就尽量在WHERE里过滤，减少分组时的数据量。

4. UPDATE更新数据：影响行数和你想象中的不一样

更新操作是最容易出事故的环节。删库跑路只是段子，但一条忘加WHERE的UPDATE，绝对能让一个团队忙活一整天。

4.1 基础UPDATE语法和影响行数

UPDATE的基本写法：

UPDATE user SET age = 26 WHERE username = 'zhangsan';

这里我想强调"影响行数"这个概念。很多人在执行UPDATE之后，下意识以为返回的行数就是实际被修改的行数。但实际上，MySQL默认返回的是被匹配的行数，而不是真正发生变更的行数。如果age本来就是26，你执行上面这条SQL，MySQL会告诉你影响1行，但它内部其实什么都没改。

在一些ORM框架里，如果你拿这个影响行数来判定"更新是否成功"，会发现明明数据没变化，却返回成功。这种需求下你应该检查的是"实际变更行数"，MySQL可以通过在连接上设置s Found Rows或者你在SQL里做一些额外的判断来实现。最简单的方式是更新前先查一次旧值比对，成本高点，但逻辑清晰。

4.2 更新时最容易忽略的事务问题

UPDATE一旦出事，往往需要事务来回滚。MySQL的InnoDB引擎默认支持事务，但要注意，自动提交模式下每条SQL都是一个独立事务。如果你连续执行多条UPDATE，中间某条失败，前面的并不会自动回滚。

我建议在需要多条更新保持一致性的场景显式开启事务：

START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT;

如果第二条UPDATE报错，直接ROLLBACK，第一条的修改也会撤销。这个习惯在线上变更数据时尤为重要，千万别靠一条条单SQL执行后手动"补数据"去修复。

4.3 更新语句的防呆习惯

更新语句的防呆，说起来就一句：WHERE条件务必写严谨。但真到了手速快的时刻，谁都可能翻车。我见过最惨的一次，有人在测试环境执行UPDATE user SET status = 0 WHERE id = 100，结果id写成了1 = 1那种裸奔条件，整个表的用户全部被禁用，要不是有备份，数据根本救不回来。

我自己养成的一个习惯是：先SELECT确认要更新的数据范围，再写UPDATE。不是不信任自己，而是多花一秒钟确认，比出事之后花几天恢复要划算得多。

另外，UPDATE语句里尽量用主键或唯一索引字段做条件，这样能精确定位到目标行。如果只能用普通字段，先用一条SELECT看下这个条件下有多少行，确认是预期数量再执行更新，这招看起来笨，但真的很管用。

4.4 大批量更新时的分批技巧

如果需要更新几万行数据，一次性UPDATE全表会持有大量的行锁，长事务还会导致主从复制延迟和undo膨胀。我有一次批量更新配置表，直接UPDATE了几万行，结果从库延迟了快十分钟，业务查询都受到了影响。

后来我养成了分批更新的习惯：

UPDATE user SET status = 1 WHERE status = 0 AND id > 0 ORDER BY id LIMIT 1000;

一批一批地更新，每次只锁一小部分行，既避免了长事务，也让主从延迟控制在很小范围内。有些场景不能简单LIMIT，那就按ID区间循环，比如以1万为步长，每次更新一个ID区间，循环跑完为止。

5. DELETE删除数据：你以为的删除可能不是真删除

DELETE看起来比UPDATE安全，毕竟删错了从结果上就能看出来。但DELETE的坑在于它的执行机制和数据恢复难度。

5.1 DELETE语法与TRUNCATE的本质区别

DELETE的基础写法：

DELETE FROM user WHERE id = 100;

不加WHERE相当于清空全表：

DELETE FROM user;

很多人分不清DELETE和TRUNCATE的区别。DELETE是DML（数据操作语言），逐行删除、走事务、可以回滚、不会重置自增ID；TRUNCATE是DDL（数据定义语言），直接重建表、速度快得多、不可按行回滚（在事务里可以回滚，但通常不建议依赖）、会重置自增ID。

如果你只是想清空一张表的数据并让ID从1重新开始，TRUNCATE更合适；如果只想删掉其中的一部分数据或者需要保留删除记录以便恢复，用DELETE。

5.2 大表DELETE的效率问题和方案

大表DELETE有一个很恶心的点：即使你只删除其中20%的数据，如果表里数据量很大，这个DELETE可能执行得非常慢，还会导致主从延迟和大量磁盘碎片。这是因为DELETE不只是删数据，还要记录undo日志、维护二级索引、在数据页上打删除标记。

删除千万级大表的一部分数据，我一般不会直接一次性DELETE，而是先查出要删除的主键范围，然后分批DELETE：

DELETE FROM user WHERE id BETWEEN 100000 AND 200000;

每批删几千行，删完一批暂停几十毫秒，让主库和从库都有喘息的机会。这样总耗时会拉长，但对生产环境的影响最小。

还有一种做法是软删除，这也是大量业务系统的常态：加一个deleted字段（0表示存在，1表示已删除），删除操作变成UPDATE。这样做的好处很明显——数据还在表里，误删可以恢复；坏处是查询时所有SQL都要额外加WHERE deleted = 0，写起来烦，而且容易漏。

5.3 误删除之后的急救思路

如果不小心把一张表的数据删了，第一步是深呼吸，不要慌，然后立刻停止对这个表的一切写入操作，防止已删除的数据占用的数据页被后续写入覆盖，减少恢复难度。

第二步看备份。有备份的话，用备份把丢失的数据恢复到一个临时表，再通过INSERT ... SELECT把数据找回来。如果你用的是云数据库（比如RDS），通常有自动备份和按时间点恢复的功能，可以恢复到删除之前的那个时间点。

如果没有备份，那就要靠binlog了。前提是你开启了binlog（一般生产环境都要开），日志格式最好是ROW。你可以通过mysqlbinlog工具解析binlog，找到删除前的INSERT或UPDATE记录，把丢失的数据拼出来。整个过程比较费劲，但至少给了你一线生机。

我特别想强调：别把希望寄托在自己的手速和运气上，定期备份才是防止误删事故的根本手段。在生产环境，至少要做到每日全量备份加实时binlog增量备份，有条件的话做跨机房容灾。

6. 写CRUD时那些让我印象深刻的翻车现场

这一节我挑几个真实的坑来复盘。知道"正确的写法"是一回事，亲眼看一遍"错误是怎么产生的"，才能真正长记性。

6.1 忘了WHERE条件的UPDATE事故复盘

之前公司有个运营后台，运营同学需要给一批用户加积分，程序执行的SQL是：

UPDATE user_score SET score = score + 100 WHERE user_id = 12345;

看起来没问题对吧？但有一次一个开发同学在做数据订正时，复制了这条SQL，把WHERE给删了，变成：

UPDATE user_score SET score = score + 100;

结果全表几十万用户的积分都加了100。这件事因为涉及金额（积分可以兑换商品），最后动用了备份binlog一点点恢复，耗费了整整一个晚上。复盘下来的核心教训就是：生产环境执行UPDATE或DELETE，一定要先在测试环境试过完整的SQL，手写时先写WHERE再写SET。

6.2 字符集不一致导致的插入错误

两个系统对接，A系统的库是utf8mb4，B系统的库是latin1，往B系统插中文标题时直接插入失败。排查很久，最后发现是两张表的字符集不一样，数据在连接层做了错误的转码。解决方式是把两边统一成utf8mb4，并且连接字符串显式指定characterEncoding=utf8。

这个问题的隐蔽性在于，不是每条插入都报错，而是当字符串里出现了某种特殊字符时才报错，导致排查方向经常跑偏。

6.3 分页深翻页卡死线上服务

一个后台管理页面上有"全部用户"列表，管理员习惯性点最后一页，SQL是：

SELECT * FROM user ORDER BY id LIMIT 500000, 20;

数据量上了百万之后，这条查询直接把数据库CPU打满，整个库的查询全部被拖慢。后来我优化成先查主键再回表的延迟关联方案，同样翻到50万页，从原来的5秒多降到0.1秒不到。优化点不在于少查了数据，而在于避免了让数据库扫描并丢弃大量无关行。

7. 从手写SQL到ORM框架：思想相通，但别做甩手掌柜

现在很多项目用MyBatis-Plus、Hibernate这类ORM框架，写代码时根本不用手拼SQL。这里我想说：ORM让你省事，但不代表你可以不懂SQL。框架生成SQL的能力终究有限，一旦遇到复杂查询、性能问题，你还是要回到SQL层面去分析。

7.1 框架里的CRUD和手写SQL的对照

拿MyBatis-Plus举例，它内置的BaseMapper已经提供了selectById、insert、updateById、deleteById这些方法。你调用userMapper.selectById(100)的时候，本质上执行的等价SQL就是：

SELECT id, username, nickname, age, email, status, create_time, update_time FROM user WHERE id = 100;

也就是说，框架只是在帮你拼接SQL。如果你对底层SQL的执行逻辑没有概念，那当框架生成的SQL出现性能问题时，你连该从哪个索引入手都不知道。

7.2 框架生成SQL的典型性能陷阱

MyBatis-Plus的selectList方法很好用，但它默认会查出这个实体映射的所有字段。如果你的表字段很多，又不幸包含了几个大的TEXT字段，那么列表页每次查询都会携带大量无用数据。这时候我就建议你用select方法指定列，或者干脆写自定义SQL。

还有LambdaQueryWrapper里的like方法，很多新手拿来当模糊查询用，一查发现慢得离谱。原因很简单，LIKE '%keyword%'的前置通配符让索引直接失效，全表扫描。这种问题在框架里隐藏得尤其深，因为代码看着人畜无害。

7.3 建议：即使有框架，也要保持手写SQL的能力

我的建议并不是让你抛弃ORM，而是你要能看懂框架生成的SQL，能在必要时果断手写。手段可以是打印SQL日志，或者用工具截获执行的SQL语句，一行行拆解。这样你在用框架时，心里始终明白它背后到底做了什么，数据库有没有在帮你好好干活。

8. 增删改查的“道”与“术”：几条掏心窝的经验总结

走到这里，MySQL的基础增删改查其实讲得差不多了。我再分享几条这些年写SQL攒下的原则，纯个人经验，不是教科书上写的，但非常实用。

8.1 数据库不是给你跑全表扫描的

每次写查询之前，先问自己：这条SQL大概会扫描多少行？如果数据量到百万千万，这个写法还能扛住吗？好的SQL不是功能正确就完事，而是要在合理的数据量下，尽量走索引、减少扫描行数、避免无谓的排序和临时表。

检查索引的方式很简单，用EXPLAIN：

EXPLAIN SELECT * FROM user WHERE username = 'zhangsan';

看输出里type这一列，如果是ALL，说明是全表扫描，要考虑加索引；如果是ref、const这类，说明命中索引，心里就有底了。

8.2 每一条生产环境的CRUD都要有演练意识

在大公司，线上数据变更要有审批单、有执行窗口、有回滚预案。个人开发者可能没这么严格的流程，但至少有"先在测试库跑一遍、备份再做变更、变更后确认数据"的意识。

8.3 SQL可读性也是一种尊重

尽量用缩进和统一大小写来写SQL，别把一条几百行的SQL全部堆积在一行。这不是形式主义，而是未来维护你代码的人（很可能就是三个月后的你）会感激你。

8.4 关于MySQL增删改查的进阶方向

掌握了基础的CRUD之后，接着可以往这些方向走：索引优化、事务隔离级别、锁机制、SQL执行计划分析、慢查询优化、主从复制、分库分表。这些内容我后面的文章会逐步展开。

就当前这篇来说，你只要把INSERT、SELECT、UPDATE、DELETE这四类操作的语法、性能和习惯都理顺了，后面的进阶内容才有地基可扎。

最后再聊一个非常贴身的技巧。每次部署完一个系统，我会顺手往数据库里塞一条测试数据，然后用默认的CRUD接口走一遍：新增、查询、修改、删除，再直连数据库用SQL验证一遍。这个动作不是多余的，它能最快暴露字符集、自增主键、字段类型映射等基础问题，避免上线后让真实用户当小白鼠踩坑。数据库的功夫，永远不嫌多。