做后端开发的几乎没人能绕开 MySQL。不管你是刚入行的 Java 开发、做数据分析的还是自己搭个人项目的独立开发者数据库的“创建、插入、查询”这三板斧都是每天要摸的东西。很多人上来就背 SQL 语法背完就忘遇到报错也不知道怎么查根本原因在于没有把这几个操作连成一条线去理解。这篇内容就用一条完整的主线——创建、插入、查询——把 MySQL 最基础也最核心的操作讲透配合实际场景、参数选择和踩坑记录让你看完能直接在自己的机器上跑一遍下次遇到问题也知道该怎么排查。这篇文章适合的读者很明确刚接触 MySQL 的新手、自学数据库但总觉得知识点零散的人、以及想系统过一遍基础操作的开发同学。有经验的 DBA 可以跳过基础部分但后面关于防重复插入和 IN 查询的坑也值得扫一眼。1. 为什么从“创建、插入、查询”三条主线入门1.1 三条主线的顺序逻辑MySQL 的操作看着很多其实绕不开三个动作先有地方存数据再把数据放进去最后把数据取出来。对应到 SQL 上就是 CREATE、INSERT、SELECT 三类语句。这个顺序不是随便排的。我见过不少人一上来就研究复杂的 JOIN 和子查询结果连表都没建对字段类型选错后面插数据各种报错查询结果也不对。创建是基础插入是入口查询才是最终目的。一条线走下来你对整个数据流转过程就有感觉了。另外还有个容易忽略的点这三类操作的顺序其实反映了数据库设计的基本流程。先根据业务需求设计表结构再往里面填充数据最后通过查询验证数据和发现洞察。如果你每次都从创建开始一步步走到查询思路是顺的。反过来上来就写 SELECT往往连表里有什么字段都不知道直接卡死。1.2 基础知识准备装好环境再说在谈任何操作之前先把环境准备好。从 MySQL 官网下载社区版即可选 MSI 安装包安装时记住几个关键配置端口默认 3306字符集建议选 utf8mb4root 密码要记牢。很多初学者卡在安装这一步其实大部分问题出在没理解安装界面上的选项。比如“Developer Default”是推荐安装模式会带上 Workbench 和各类驱动日常学习足够。安装完成后推荐同时掌握两种操作方式命令行和 Workbench。命令行让你理解 SQL 本质Workbench 提供可视化反馈两者配合效率最高。注意安装完先用mysql --version验证一下环境变量配置是否生效避免后续命令行打不开的尴尬。2. 创建操作库、表、索引一个都不能少2.1 建库与选字符集创建数据库是最简单的操作语法就一行CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;但这里面藏着两个新手容易掉的坑一个是字符集选错一个是排序规则选错。字符集强烈建议用utf8mb4不要用utf8。因为utf8在 MySQL 里最多支持 3 字节存不了 emoji 表情和部分生僻字遇到就报错。utf8mb4是完整的 4 字节 UTF-8能覆盖所有 Unicode 字符现在已经是绝对主流。排序规则utf8mb4_unicode_ci和utf8mb4_general_ci的区别简单说就是前者对字符排序更精确、更符合 Unicode 标准后者性能略好但排序规则不够严谨。日常业务用utf8mb4_unicode_ci就够不用纠结太多。2.2 建表时字段类型怎么选建表是创建操作里最核心的部分字段类型选错了后面改起来要命。以最常见的用户表为例CREATE TABLE IF NOT EXISTS user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL DEFAULT , email VARCHAR(64) NOT NULL DEFAULT , age TINYINT UNSIGNED DEFAULT 0, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个选型的思路整型选INT UNSIGNED年龄用TINYINT UNSIGNED足够金额用DECIMAL(10,2)千万别用 FLOAT 和 DOUBLE浮点会有精度丢失问题时间字段直接用DATETIME并且用DEFAULT CURRENT_TIMESTAMP自动填充插入时间。主键设计这块多说一句自增整型主键是绝大多数业务的首选简单、快、范围可控。UUID 主键适合分布式场景但会导致 B 树索引频繁分裂单机规模不大时没必要。业务主键比如用身份证号做唯一键在数据校验上有优势但如果业务变更就非常痛苦。我的建议很简单普通业务表老老实实用自增 ID再加几个业务字段的唯一索引。2.3 索引的创建时机与方式索引是数据库中一个非常关键的概念直接影响查询速度。可以在建表时就定义好索引也可以用 ALTER TABLE 追加ALTER TABLE user ADD INDEX idx_username (username);创建索引有两个时机建表时和业务运行后。建表时添加索引主要针对确定的高频查询字段比如订单表的订单号、用户表的邮箱等。业务运行后添加索引通常是发现某个查询很慢用EXPLAIN分析后发现全表扫描才补上的。索引不是越多越好。每个索引都会增加插入和更新时的开销因为每次写数据都要同步维护索引结构。一个常见的误区是给每个字段都加上索引结果插入速度慢得离谱查询优化器反而不一定用你的索引。联合索引多列索引和单个索引的选择也是一门学问(a, b)联合索引能覆盖a和a, b两种查询条件但查b单独的条件用不上它。3. 插入操作单条、批量、防重复3.1 INSERT 语法与自增主键处理插入的基础语法很简单INSERT INTO user (username, email, age) VALUES (张三, zhangsanexample.com, 25);这里有个细节指定字段列表插入和全字段插入的差别。全字段插入要求 VALUES 里的值和表字段顺序完全一致一旦表结构变了比如加了字段SQL 就报错。指定字段插入则稳定得多推荐大家都写字段列表别偷懒不然上线时表结构一变更旧的 SQL 全挂。自增主键的 ID 在你插入时不指定MySQL 会自动分配。如果你插入后需要立刻用到这个 ID用LAST_INSERT_ID()函数获取注意这个函数是当前连接级别的不会受其他连接插入影响INSERT INTO user (username, email) VALUES (李四, lisiexample.com); SELECT LAST_INSERT_ID();3.2 批量插入的提速技巧单条插入本身没什么好说的实际工作中一个更大的问题是批量插入。你可能会遇到向数据库灌大量数据的场景比如定时任务同步数据、ETL 流程等。如果一条一条 INSERT速度非常慢因为每次插入都是一个独立事务要写日志、刷磁盘、维护索引开销极大。批量插入的正确写法是用一条语句多个 VALUESINSERT INTO user (username, email) VALUES (王五, wangwuexample.com), (赵六, zhaoliuexample.com), (孙七, sunqiexample.com);这种写法在数据量从几百到几万时都有明显提升。更大的数据量几十万上百万可以用LOAD DATA INFILE直接导入文件比 INSERT 还要快一个量级。但要注意批量插入不是越大越好尤其涉及索引维护时建议分批提交每批几千条到几万条视字段数量和索引导航而定避免单次事务过大导致锁表和回滚段膨胀。还有一个实用技巧用事务包裹批量插入。在 InnoDB 引擎下显式开启事务多条 INSERT 后统一提交可以大幅度减少磁盘同步的次数START TRANSACTION; INSERT INTO user (username, email) VALUES (a, aexample.com); INSERT INTO user (username, email) VALUES (b, bexample.com); -- 更多插入... COMMIT;如果没有显式事务每条 INSERT 都是自动提交相当于每次都要把数据写进磁盘的 redo log 并同步耗时差距可以达到几十倍。我之前给业务导入 50 万条历史数据时单条插入的方式跑了快半小时改成事务加批量插入后不到两分钟就完成了。3.3 重复数据不插入的几种写法这个场景非常常见接口重复提交、数据同步任务重复跑、消息队列重复消费等。对应热词里的“重复则不插入”我在工程里最常用的有三种写法INSERT IGNORE、ON DUPLICATE KEY UPDATE 和 REPLACE INTO。-- 方式一INSERT IGNORE忽略冲突不报错 INSERT IGNORE INTO user (username, email) VALUES (张三2, zhangsanexample.com); -- 方式二ON DUPLICATE KEY UPDATE冲突时更新 INSERT INTO user (username, email) VALUES (张三3, zhangsanexample.com) ON DUPLICATE KEY UPDATE username VALUES(username); -- 方式三REPLACE INTO冲突时先删再插 REPLACE INTO user (username, email) VALUES (张三4, zhangsanexample.com);三种方式的差异用一个表说明方式冲突时的行为自增ID变化适用场景INSERT IGNORE忽略新数据保留旧数据不变化只想要不报错保留原记录ON DUPLICATE KEY UPDATE更新指定字段不变化需要更新部分字段幂等写入REPLACE INTO删除旧记录插入新记录会变化完全替换整行数据实际业务里我最常用的是 ON DUPLICATE KEY UPDATE因为它的语义最可控你可以决定冲突时更新哪些字段其他字段保留原值并且不会造成自增 ID 膨胀。REPLACE INTO 有个坑它本质是先 DELETE 再 INSERT会触发额外的写日志和索引维护而且自增 ID 每次都会变如果其他表引用了这个 ID会导致外键关系错乱建议慎用。这些方式要生效前提是冲突判断基于唯一键或主键也就是表里必须有能“识别重复”的字段约束。没有唯一键的话这些 SQL 也不会报错但重复数据照样插入。4. 查询操作从简单查询到优化思路4.1 基础查询与条件过滤查询是 MySQL 操作里的重头戏也是涵盖内容最多的一部分。基础语法大家都懂但有些坏习惯必须改。第一个就是SELECT *。不是说绝对不能用而是在日常开发中尽量别用尤其是字段多、数据量大的表。SELECT *不仅浪费 IO 和网络传输还可能导致优化器无法使用覆盖索引。你需要什么字段就查什么字段。条件过滤的经典写法SELECT id, username, email, created_at FROM user WHERE age 18 AND email ! ORDER BY created_at DESC LIMIT 20;WHERE 条件里有几个开关要熟悉、、、、BETWEEN ... AND ...、LIKE、IN、IS NULL和IS NOT NULL。特别注意NULL的判断不能用 NULL只能用IS NULL。这个错误很多人不止一次犯。另外一个细节点LIKE模糊查询如果写法是LIKE %abc或LIKE %abc%前置通配符会导致索引失效全表扫描。只有LIKE abc%这种前缀匹配才有机会走索引。业务上不得已要用模糊搜索的话可以考虑全文索引或者外部搜索引擎不过这是后话。4.2 IN 与 EXISTS 的选择先看报错场景。热词里有“in查询语句报错”我遇到过的典型状况有两种第一种是 IN 列表里的值和字段类型不匹配比如字段是 INT结果 IN (1, abc)MySQL 会做隐式类型转换转换失败或者结果不对第二种是 IN 子查询返回了太多数据导致 SQL 语句过长或者性能急剧下降。先说一个基础结论小数据量下IN 和 EXISTS 的性能差距不大优化器会自动做等价改写。但在特定场景下两者的选择还是有讲究的-- IN 写法先执行子查询再在主查询中过滤 SELECT * FROM order WHERE user_id IN (SELECT id FROM user WHERE status 1); -- EXISTS 写法逐行判断主查询每条记录是否满足子查询条件 SELECT * FROM order o WHERE EXISTS (SELECT 1 FROM user u WHERE u.id o.user_id AND u.status 1);一般来说如果子查询的结果集很小而主查询的表很大用 IN 更合适。如果子查询表的数据量很大但主查询每次只取少量行用 EXISTS 可能更好因为 EXISTS 只要找到一条满足条件的记录就会停止扫描。不过 MySQL 优化器的能力比想象中强很多情况下会自动选择更优的执行计划。真正判断用哪个最终还是得靠EXPLAIN看执行计划而不是拍脑袋。4.3 子查询与关联查询的踩坑子查询的灵活运用让 SQL 的表达能力非常强但也带来了不少性能陷阱。最常见的坑是“子查询作为被驱动表”时优化器对派生表的处理MySQL 5.6 之前的版本会把派生表物化成一个临时表没有索引一旦数据量大就会非常慢。MySQL 5.7 之后引入了派生表条件下推优化情况好很多但也不能完全依赖。实际工作中我更推荐用 JOIN 来替代某些子查询。举个例子查“有订单的用户信息”SELECT DISTINCT u.* FROM user u INNER JOIN order o ON u.id o.user_id;JOIN 的语义更直白优化器也更擅长处理 JOIN 的情况。特别是 INNER JOIN通常会走哈希连接或嵌套循环连接走索引的情况下性能很有保障。LEFT JOIN 适合需要主表全量数据、不管有没有匹配的情况但要注意 LEFT JOIN 后如果 WHERE 条件写错容易退化成类似 INNER JOIN 的效果匹配不到的记录直接被过滤掉。INNER JOIN 一个比较难踩的坑是关联字段的字符集必须一致。如果两个表关联字段一个用了 utf8mb4一个用了 latin1MySQL 虽然不会报错但会因为隐式字符集转换导致索引失效。这个是在排查慢 SQL 时发现的当时查了很久才找到原因。4.4 排序、分组与分页排序和分组是查询中常用到的功能SELECT status, COUNT(*) AS cnt FROM order WHERE created_at 2024-01-01 GROUP BY status HAVING cnt 100 ORDER BY cnt DESC;这里有两个点容易被新手遗漏HAVING和WHERE的区别是WHERE 在分组前过滤行HAVING 在分组后过滤组。还有GROUP BY使用了分组字段后SELECT 里的非聚合字段要依赖 MySQL 的 ONLY_FULL_GROUP_BY 模式设置默认情况下会报错需要把 SELECT 字段都加到 GROUP BY 里或用聚合函数包裹。分页在数据量小的时候很无感数据量大了就暴露问题。LIMIT 100000, 20这种深分页写法MySQL 实际上要读取前 100020 条然后丢弃前面的非常浪费。一个常用的优化思路是用自增主键或者创建时间做游标-- 传统分页 SELECT * FROM order ORDER BY id LIMIT 100000, 20; -- 优化基于主键定位 SELECT * FROM order WHERE id 100000 ORDER BY id LIMIT 20;原理其实不复杂第二种方式用了主键索引直接定位到目标位置跳过了前面数据的扫描和排序。这是处理大数据量分页的核心思路面试和实践中都经常遇到。5. 实操记录一次完整的建库-建表-插数-查询流程5.1 完整 SQL 脚本演示光讲理论没意思这里用一个“用户购物订单”的小场景走一遍从创建到查询的完整流程。打开你的 MySQL 客户端跟着敲一遍-- 1. 建库 CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE shop; -- 2. 建用户表 CREATE TABLE IF NOT EXISTS user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL DEFAULT , email VARCHAR(64) NOT NULL DEFAULT , created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 3. 建订单表 CREATE TABLE IF NOT EXISTS order ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 4. 插入用户数据 INSERT INTO user (username, email) VALUES (张三, zhangsanexample.com), (李四, lisiexample.com), (王五, wangwuexample.com); -- 5. 插入订单数据 INSERT INTO order (user_id, amount, status) VALUES (1, 199.00, 1), (1, 299.00, 0), (2, 59.90, 1), (3, 999.00, 1); -- 6. 查询下单用户的订单总金额 SELECT u.username, SUM(o.amount) AS total_amount, COUNT(*) AS order_count FROM user u INNER JOIN order o ON u.id o.user_id WHERE o.status 1 GROUP BY u.id, u.username HAVING order_count 1 ORDER BY total_amount DESC;这个流程走完你就把创建、插入、查询串起来跑通了。关键点提示第 3 步建表时给user_id加上了普通索引因为订单表查某个用户的所有订单是高频操作给created_at加索引是为了支持时间范围查询。第 6 步的查询用了 INNER JOIN 关联用户表和订单表用 GROUP BY 做用户级汇总再通过 ORDER BY 把消费额最高的用户排在最前面。5.2 用 Workbench 观察执行效果如果安装了 MySQL Workbench可以更加直观地看到操作结果。Workbench 左侧的 SCHEMAS 面板里能实时看到建好的shop库和表执行查询后下方结果格子直接显示返回的数据。Workbench 还有一个非常好用的功能点击查询结果上方的“Explain”按钮可以看到这条查询的执行计划。以下面这条 SQL 为例EXPLAIN SELECT u.username, SUM(o.amount) AS total_amount FROM user u INNER JOIN order o ON u.id o.user_id GROUP BY u.id;执行计划里会显示访问类型type、key实际使用的索引、rows估计扫描行数等关键信息。你会看到user表走 PRIMARY 索引order表走idx_user_id索引连接类型为 ref说明关联查询高效利用了索引。学会看这些信息是排查慢 SQL 的基本功。6. 常见问题与排查技巧实录6.1 字段名与关键字冲突一个很容易踩的坑表或字段名正好叫order、group、desc这类 MySQL 保留字。比如你建了一个订单表叫order直接查询就会报语法错误。解决办法有两个一是建表时给字段加反引号二是干脆换个名字。我的建议是尽量用反引号兜底同时命名时也主动避开保留字双保险。6.2 中文乱码问题中文乱码的根源基本都是字符集不一致。有“三层字符集”要统一数据库/表字符集、客户端连接字符集、应用程序连接字符串里配置的字符集。命令行连接时执行SET NAMES utf8mb4;可以临时设置客户端字符集。Java 应用则在 JDBC 连接 URL 上加上characterEncodingutf8参数。经验法则只要建库建表时统一用 utf8mb4连接参数也统一乱码概率基本为零。6.3 插入速度慢插入慢的排查方向有这几种第一种是表上有多个索引每次插入都要同步维护索引索引多到一定程度写性能就会下降可以用EXPLAIN和SHOW INDEX查看索引使用情况把冗余索引删掉第二种是单条插入自动提交的事务开销改造成事务批量提交第三种是磁盘 IO 瓶颈比如机械硬盘写入速度本身受限这个只能靠硬件升级或者运维层优化解决。对应热词里的“es插入数据”虽然说的是 Elasticsearch但排查思路是类似的先看有没有锁再看是不是批量再看是不是索引和磁盘问题。6.4 IN 查询报错IN 查询报错我做过一个简单的排查清单报错现象可能原因解决办法SQL 语法错误IN 列表漏了括号或逗号检查 SQL 括号匹配java.sql.SQLException: Data too longIN 列表太长超过 max_allowed_packet分批查询或调大 max_allowed_packet查询结果和预期不符字段类型不匹配隐式转换导致索引失效检查字段类型去掉隐式转换子查询性能极差IN 子查询结果集太大改为 JOIN 或 EXISTS特别要提一下max_allowed_packet这个参数默认通常是 4MB 或 64MB。之前线上有一个生产故障就是因为传入的 ID 列表包含了几十万个 ID导致 SQL 语句长度超过 mysqld 允许的最大包长度直接被服务端拒绝。后来改成分批查询 IN 列表限制在几千以内问题彻底解决。最后分享一个我自己的习惯每个 SQL 在提交到业务代码之前先用 EXPLAIN 跑一下。哪怕查询结果一样执行计划也可能天差地别。养成这个习惯很多性能问题在开发阶段就被拦下了不会等到上线后让 DBA 来救火。基础操作不难难的是把每个操作的细节和背后的原理想清楚这才是从一个会写 SQL 的人变成一个懂数据库的人的分水岭。