★ FEATURED ARTICLE 聊到MySQL基础操作很多人第一反应是“背几个SQL语句就行”但真到了实际项目里会发现连一个简单的分页查询都写不利索更别说遇到慢查询、字段乱码、误删数据这些事。我用MySQL少说六七年了从最开始在本地搭环境练手到后来给公司项目做数据迁移、优化慢SQL踩过的坑够写好几篇。这篇就把最常用、最核心的基础操作掰开揉碎讲一遍覆盖环境准备、建表规范、增删改查、索引优化和常见问题排查。适合刚接触数据库的同学照着操作也适合写了一段时间SQL但总感觉差点系统性理解的人查漏补缺。1. 环境准备与基础概念1.1 本机环境搭建五分钟跑起来不管你是Windows还是macOS本地装一个MySQL来练手都是最稳妥的方式。Windows用户去官网下载社区版MSI安装包安装时选Server only即可一路默认就可以唯一要注意的是安装过程中会让你设置root密码建议先设一个简单的好记的比如root123这种后面做实验方便生产环境再说强密码的事。macOS用户可以直接用Homebrew命令就一行brew install mysql brew services start mysql安装完之后默认root账号是空密码执行mysql -u root就能进去。这里有个小坑如果提示Cant connect to local MySQL server through socket多半是服务没起来先启动服务再连接。装好之后我强烈建议再装一个可视化工具不必非得用什么大型IDENavicat、DBeaver、DataGrip随便选一个顺手的使用就行。我个人更喜欢命令行操作因为上线排查问题时大部分场景都是命令行环境TablePlus这类工具适合看数据但练习阶段还是先熟悉命令行对理解SQL执行过程帮助更大。1.2 库、表、行的层级关系MySQL的逻辑结构其实特别生活化。你可以把数据库当成一个仓库仓库里有很多货架货架就是数据表货架上每一层格子存的就是一行数据。操作数据库时先选仓库USE语句再在指定货架上操作。SHOW DATABASES; USE test_db; SHOW TABLES;这三条命令就是入门三板斧。先看看有哪些库进入目标库看看库下有哪些表。我见过不少新手分不清“库”和“表”的关系在查询时忘了先用USE导致报错说表不存在——其实表就在隔壁库里你没进去而已。想要不切换库直接查也可以用库名.表名的完全限定写法比如SELECT * FROM test_db.users;。1.3 字符集和排序规则第一道坑建库建表时字符集的选择直接影响后面会不会出现中文乱码这件事几乎每个新手都会碰到。我负责过的一个系统早期库用了默认的latin1跑了一段时间后导入中文全部变成问号最后只能批量改字符集折腾得够呛。建库时最好显式指定CREATE DATABASE test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;为什么选utf8mb4而不是utf8因为MySQL的utf8是utf8mb3它只能存3字节的字符很多特殊符号比如emoji需要4字节直接插进去就报错Incorrect string value。utf8mb4才是真正意义的完整UTF-8。排序规则里的_general_ci表示大小写不敏感的比较方式常规业务场景选这个足够了如果对中文排序有特殊需求再考虑gbk系列之类的选择。提示生产环境表的字符集一定要在建表时就定好后面再改字符集不仅麻烦而且风险极高涉及锁表、数据转换等问题。2. 查询操作2.1 SELECT基础别只盯着SELECT和FROM查询语句是开发中写得最多的SQL但很多人写查询时的路径不对。最典型的错误是SELECT *满天飞查什么字段就写什么字段才是好习惯。一方面减少网络传输的数据量另一方面也避免代码里出现字段位置写错、语义不明确的问题。SELECT id, name, age, created_at FROM users WHERE age 18;WHERE条件决定了查询命中的行范围这里面有几个常见的坑需要留意字符串比较记得加引号不加引号时字段名会和字符串混淆。多个条件用优先级明确的括号包裹AND和OR混用时容易出逻辑错误。对字段做函数运算如WHERE YEAR(created_at) 2024会让索引失效应当尽量避免。这些都是我踩过的坑。某个报表接口本来查询200毫秒后来数据涨到百万级变成了3秒查了执行计划才发现WHERE条件里对时间字段用了DATE_FORMAT()函数索引完全没用上。改成了created_at BETWEEN 2024-01-01 AND 2024-12-31之后性能立刻恢复。2.2 ORDER BY和LIMIT分页的正确姿势排序和分页是查询操作里最常用的组合。排序的关键在于理解排序逻辑如果SELECT列表里有多个字段顺序是ORDER BY后面的字段决定的不是SELECT里的字段顺序。SELECT id, name, score FROM students ORDER BY score DESC, id ASC LIMIT 20;上面这条SQL的含义是先按score降序分数相同再按id升序。LIMIT 20表示只取前20条。分页查询的基本形态是LIMIT 偏移量, 行数或者LIMIT 行数 OFFSET 偏移量SELECT id, name, score FROM students ORDER BY id ASC LIMIT 20 OFFSET 40;这条SQL取第41到第60条数据。注意偏移量越大MySQL需要扫描并丢弃的行越多分页越深越慢这也是深分页问题的根源。数据量到几十万以后用WHERE id 上页最后一条id ORDER BY id LIMIT 20这种“基于游标”的方式性能会好很多。2.3 聚合函数与GROUP BY统计报表离不开聚合函数。COUNT、SUM、AVG、MAX、MIN这五个是最常用到的。使用上有三个容易犯迷糊的点COUNT(*)和COUNT(字段)的区别COUNT(字段)只统计该字段不为NULL的行。某次统计用户下单数时用错导致结果和订单明细对不上排查半天。GROUP BY和聚合函数必须成对使用SELECT里出现的非聚合字段必须出现在GROUP BY里否则MySQL虽然不报错但取出的值是随机的。HAVING用于对分组后的结果进行过滤WHERE是对分组前的行进行过滤。这个顺序关系要理顺。举个例子统计每个班级里及格人数超过30人的班级SELECT class_id, COUNT(*) AS pass_num FROM scores WHERE score 60 GROUP BY class_id HAVING pass_num 30;先有WHERE筛选再有分组聚合最后才是HAVING过滤分组。2.4 JOIN连接查询左右内连接怎么选真正工作后写复杂查询几乎绕不开多表关联。理解JOIN的关键在于想清楚“保留哪张表的全部数据”。-- 内连接只取两边匹配上的数据 SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id o.user_id; -- 左连接保留左表全部数据 SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id; -- 右连接保留右表全部数据 SELECT u.name, o.order_no FROM orders o RIGHT JOIN users u ON u.id o.user_id;实际项目中左连接用得最多因为通常主表是业务的出发点比如要展示所有用户及其订单没下过单的用户也要显示出来就用LEFT JOIN。内连接用于严格的匹配场景右连接写得少我也习惯把它改写成左连接便于维护阅读。连接查询性能最大的影响因素是连接字段是否走索引。连接字段上没索引即使两表各一千万数据也会出现灾难性的全表扫描一条查询拖垮整个库的例子我也遇到过。所以ON条件后面的字段必须建索引这是硬性要求。3. 数据增删改3.1 INSERT插入数据单条与批量INSERT是最直观的操作。基础语法很简单但生产环境中更常见的是批量插入一次插入几百行甚至上万行时性能差异非常明显。-- 单条插入 INSERT INTO users (name, age) VALUES (张三, 25); -- 批量插入 INSERT INTO users (name, age) VALUES (张三, 25), (李四, 30), (王五, 28);几条数据时感觉不出差异但插入一万行时一条一条插需要建立一万次连接和事务开销批量插入一条SQL就搞定了。实测中批量插入的耗时大约是逐条插入的十分之一以下。还有一个常用技巧是INSERT IGNORE和ON DUPLICATE KEY UPDATE。前者遇到唯一键冲突时直接跳过不报错适合做数据导入场景后者冲突时对指定字段做更新适合做数据同步场景。INSERT INTO users (id, name, age) VALUES (100, 李四, 31) ON DUPLICATE KEY UPDATE age VALUES(age);3.2 UPDATE更新数据永远记住WHEREUPDATE本身没什么技术含量但危害性极大。没写WHERE条件就意味着整张表的数据全部被更新。我身边真实发生过一次事故某同学在测试环境执行UPDATE users SET age 18;本来只想改某个人结果全表变18岁好在是测试环境生产环境出这种事是要背处分的。UPDATE users SET age 18 WHERE id 100;经验之谈执行UPDATE前先把WHERE条件用SELECT跑一遍看看命中哪些行确认无误再换成UPDATE执行。这一步花不了几秒钟但能避免绝大多数误操作。线上做更新操作时可以先查出影响行数再结合备份策略判断谨慎永远不会过分。3.3 DELETE和TRUNCATE的区别DELETE删除行可以加WHERE条件筛选TRUNCATE清空整张表不能加条件且重置自增ID。在业务系统里DELETE的误操作风险同样很高用前先SELECT确认命中范围是必须养成的习惯。另外一个细节是DELETE删除的数据如果后续想找回在开启了binlog的情况下理论上可以恢复但过程非常复杂需要钳时间点、做日志分析。更稳妥的做法是软删除设计也就是给表加一个deleted字段0表示正常1表示已删除查询时统一过滤既保留历史数据又方便恢复对审计也有好处。我经手的多数业务系统都采用软删除策略。大表删除还有个实际问题DELETE大批量数据时会锁表影响线上读写。经验做法是分批删除每次只删几千条加个LIMIT等一批删完再删下一批减少锁冲突。4. 索引与性能优化入门4.1 索引为什么能让查询变快索引的原理可以类比成书的目录。没有索引时找某段内容需要逐页翻有了索引就能直接定位到页码区域。MySQL的InnoDB引擎默认使用B树索引查询效率高而且叶子节点之间有指针串联范围扫描也非常顺畅。给字段加索引的语法很简单ALTER TABLE users ADD INDEX idx_age (age); ALTER TABLE users ADD UNIQUE KEY uk_email (email);创建索引的核心原则是查询的过滤字段、排序字段、连接字段才适合建索引。不要给每个字段都建索引索引会占用磁盘空间还会降低写入速度——每插一条数据都要同步更新所有索引。我见过某个表有几个七八个索引平时查询确实很快但写入出现了明显的性能瓶颈。后来根据慢日志分析删掉了几个没人用的索引写入快了近两成。4.2 联合索引最左前缀原则联合索引是提升复杂查询性能的法宝。举个例子ALTER TABLE orders ADD INDEX idx_user_time (user_id, created_at);这个索引同时覆盖了user_id和created_at两个字段的查询场景。联合索引遵循最左前缀原则理解为索引的使用必须从最左边字段开始跳过了第一个字段后面的字段就用不上索引了。WHERE user_id 100能使用索引WHERE created_at 2024-01-01无法使用联合索引因为最左边的user_id没出现在条件里。设计联合索引时把区分度高的字段放前面把范围查询的字段放后面效果最好。这个经验一定要记住后面看EXPLAIN结果的时候会非常有感觉。4.3 EXPLAIN看懂执行计划EXPLAIN能告诉我们一条SQL是怎么执行的MySQL优化器有没有用到索引、扫描了多少行全在这个输出结果里。EXPLAIN SELECT * FROM orders WHERE user_id 100;重点关注几个列type从好到差常见有system、const、eq_ref、ref、range、index、ALL。看到ALL说明全表扫描基本可以判定SQL需要优化。key实际用到的索引名如果显示NULL说明没有用任何索引。rows预估扫描的行数数值越大性能越差和实际行数对比越接近说明预估越准确。ExtraUsing filesort和Using temporary都是性能隐患出现了就要认真考虑优化SQL。每次写完一条慢查询SQL第一反应就应该开一条EXPLAIN看看执行路径。这个习惯越早养成后面踩的性能坑就越少。注意不要把SELECT *和EXPLAIN混在一起看只关注行列输出EXPLAIN的信息都在附加列上字段有没有走索引一眼便知。4.4 事务与控制MySQL的InnoDB支持事务ACID是事务的四个核心特性其实写成一句人话概括就是要么全成功要么全失败互相隔离持久可靠。START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;转账场景是解释事务的经典例子。第一步扣钱第二步加钱如果第二步失败而第一步已执行没有事务的话钱就凭空消失了。用事务包裹后只要出任何差错就ROLLBACK数据始终是安全的。事务使用时的几个建议事务要短不要在事务里做耗时的网络请求。并发高的场景要选择合适的隔离级别默认的REPEATABLE READ能满足大部分业务。悲观锁SELECT ... FOR UPDATE和乐观锁版本字段按并发控制的实际需求选型别一开始就上锁。5. 常见问题与排查技巧5.1 连接不上的排查流程MySQL连接失败报错是最常见的求助话题。出现连接不上按下面的顺序排查基本两分钟能定位问题服务是否启动执行systemctl status mysqlLinux或检查任务管理器服务列表Windows。端口是否监听netstat -an | grep 3306没有监听说明服务没起来。账号能否远程访问MySQL默认root只允许localhost登录想远程访问需要单独授权。防火墙是否放行端口云服务器还要检查安全组规则。授权远程访问的语句如下CREATE USER app_user% IDENTIFIED BY password; GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO app_user%; FLUSH PRIVILEGES;给账号只开最小权限是安全底线。比如只读账号就给SELECT权限不需要给ALL PRIVILEGES程序员能少犯错系统就安全一分。5.2 中文乱码的根治思路乱码问题十有八九是字符集不统一造成的。客户端、连接层、数据库、表、字段这五层中任何一层的字符集不一致中文就可能在某一环变质。排查乱码先确认四件事客户端工具连接时指定的字符集很多工具在连接参数里有encoding选项。数据库和表的默认字符集查SHOW CREATE TABLE 表名。连接参数是否加了characterEncodingutf8JDBC场景。数据在入库前本身是否已经乱码源头问题谁也救不了。一套命令可以设置当前会话的字符集SET NAMES utf8mb4;这行命令把客户端发送、接收以及连接层的字符集都统一成utf8mb4。在一台之前一直是乱码的服务器上执行后再查数据通常就正常了。5.3 误操作之后的补救思路误删数据、误更新数据是数据库操作中最让人头疼的事情。遇到这类问题保持冷静马上执行以下动作第一停止写入。如果还有业务在持续写入尽快暂停或切换流量避免新数据覆盖掉可恢复的信息。第二检查binlog日志是否开启根据show master status找到当前使用的binlog文件再用mysqlbinlog工具解析出事时间点前后的操作记录。第三根据日志解析出来的SQL做反向操作把数据补回来。mysqlbinlog --start-datetime2024-01-01 00:00:00 --stop-datetime2024-01-01 01:00:00 /var/lib/mysql/mysql-bin.000001理论上能找回但过程相当折腾而且依赖binlog_format配置ROW格式信息更全。最扎心的现实是如果没开binlog恢复几乎不可能。所以我反复强调两点生产环境必须开启binlog并且定期做全量备份。备份文件是你的最后一道保险。5.4 经验速查表我在下面整理了一张表把一些高频操作的核心要点浓缩在一起适合贴在工位旁边随时看。场景推荐做法禁忌建库建表utf8mb4字符集、显式指定引擎用默认latin1、不设置主键查询指定字段、WHERE走索引列滥用SELECT *、函数套字段分页LIMIT配合明确排序字段深偏移量无脑翻页更新删除WHERE先行SELECT验证无WHERE全表操作索引按查询场景建联合索引每个字段都建单独索引事务短事务、明确提交隐式提交、长事务备份开启binlog、定期全量备份裸奔不备份表里每一条都是实操中真金白银换来的经验。写SQL是数据库的基础能力但真正形成核心竞争力的是对这些细节的系统理解知道为什么这样写性能好知道出了问题从哪里排查知道如何从机制上避免事故。到这里MySQL基础操作的核心内容就全过了一遍。我个人实际使用中最大的体会是基础语法最多三天就能上手但每一条实践细节背后都对应着一个别人踩过的坑真正的高手不是会写复杂SQL的人而是能把简单SQL写对、写稳、写出性能的人。建议你装好环境后从建库开始把文中的每个例子亲手敲一遍再结合自己手头的数据集做几次查询练习坚持两周就能建立肌肉记忆。这个练手的过程本身就是最好的老师。
阅读完成 · 觉得有帮助？
