1. 写在前面为什么我还要写一篇“简单操作语句”先说实话MySQL这个主题都快被写烂了从入门到精通的书堆起来比人还高网上的教程更是车载斗量。但我在实际带新人、帮同事排查问题的过程中发现一个挺尴尬的现象很多人用Navicat、Workbench这类图形工具点得很溜可一旦离开图形界面面对黑乎乎的终端就麻爪。还有一部分人刚把MySQL装好连初始密码都找不到更别提写出一条能跑的UPDATE语句。这篇文章不打算讲什么高深原理就是围绕日常开发里最常用的增删改查把那些你真正会用到、真正容易踩坑的语句掰开揉碎说清楚。内容包括基础命令行操作、建库建表、数据操作、查询技巧、索引与执行计划以及几类高频报错的排查思路。适合三类人看一是刚装好MySQL、还处于“select 1都能报错”阶段的新手二是用图形工具多年、想补齐命令行短板的人三是准备面试、需要把常用SQL和底层逻辑串起来复习的同学。我会尽量用大白话讲涉及命令的地方直接给可复制的代码涉及原理的地方用生活化的类比带过保证你看完能照着敲敲完能真正用起来。2. 装好MySQL之后先把这几件事办了2.1 找到初始密码别卡在第一关很多新手装完MySQL兴冲冲在命令行敲mysql -uroot -p结果密码怎么输都不对。这里要记住从MySQL 5.7开始安装时已经不会让你自己设置密码了系统会随机生成一个临时密码存放在日志文件里。Windows下用压缩包方式安装的话先执行mysqld --initialize --console这一步会初始化数据目录并且把临时密码直接打印在控制台里。如果当时没留神错过了可以到数据目录下找*.err日志文件搜索“temporary password”关键字后面那一串就是初始密码。拿到临时密码后要做的第一件事就是改密码否则什么都干不了。登录进去后执行ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;改完记得刷一下权限FLUSH PRIVILEGES;注意MySQL默认的密码策略要求密码至少8位并且包含大小写字母、数字和特殊字符。如果你的测试环境不想搞这么复杂可以降低校验等级但生产环境千万别这么干。2.2 确认端口和服务的运行状态MySQL默认端口是3306这一点90%的场景都不用改。但我遇到过好几次“明明装好了却连不上”的问题最后排查下来都是服务根本没启动或者端口被其他程序占了。Linux下查看服务状态systemctl status mysqld如果没启动就执行systemctl start mysqld想开机自启就执行systemctl enable mysqld。Windows下可以在服务管理器里找到MySQL对应的服务手动启动或设置为自动。确认端口有没有在监听netstat -tlnp | grep 3306看到0.0.0.0:3306或者127.0.0.1:3306监听中说明服务正常。如果端口被占用需要去修改my.cnf或my.ini里的port配置改完重启服务。2.3 图形工具连接Workbench和Navicat的差异点命令行并不是唯一的选择实际开发中很多人还是习惯用图形工具。MySQL官方的Workbench免费开源功能也够用Navicat功能更强但商业授权不便宜。用Workbench连接时注意主机名、端口、用户名、密码四项填写正确即可。有次同事换了台电脑始终连不上测试库后来发现是防火墙拦了3306端口。如果你也在局域网里连接记得检查目标机器的防火墙设置必要时放行3306。这里多提一句连接池的概念。应用程序连接MySQL时一般不会每次请求都新建连接而是通过连接池复用。连接串中常见的connectTimeout、socketTimeout等参数本质上就是控制连接建立的等待时间和读写等待时间。排查线上“连接超时”问题时要从数据库侧的最大连接数和应用侧连接池配置两头看数据库报Too many connections就说明连接数打满了。3. 建库建表DDL语句里那些容易忽略的细节3.1 创建数据库字符集和排序规则怎么选创建数据库的语法很简单难的是字符集选择。很多初学者的库表直接用默认设置等存入中文发现乱码才回头改字符集麻烦得很。CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里有两个关键点一是utf8mb4才是真正的UTF-8MySQL的utf8字符集最多只支持3个字节像emoji这种4字节字符存不进去。二是排序规则utf8mb4_general_ci和utf8mb4_unicode_ci的区别简单说前者性能略好后者排序更准日常选general完全够用。想查看当前数据库的字符集SHOW CREATE DATABASE mydb;3.2 创建表字段类型、默认值、唯一约束建表语句是DDL操作的基石。以一张用户表为例CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL COMMENT 用户名, age int NOT NULL DEFAULT 0 COMMENT 年龄, email varchar(100) DEFAULT NULL COMMENT 邮箱, status tinyint NOT NULL DEFAULT 1 COMMENT 状态1启用0禁用, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;逐项拆解一下为什么这么写bigint做主键如果预期数据量不大int也够用但为了后期省事直接上bigint没有坏处。AUTO_INCREMENT自增主键避免业务层生成唯一ID的麻烦。DEFAULT 0很多字段建议设置默认值比如年龄默认0状态默认1这样插入数据时即使不传这些字段也不会报错。UNIQUE KEYemail加了唯一约束作用等于email字段不能重复。热词里有“mysql设置唯一已经有重复数据库”说的就是给已有重复数据的表加唯一索引时的报错这个问题我们后面专门讲。ENGINEInnoDB默认存储引擎支持事务和行级锁。COMMENT注释信息写在DDL里比单独维护文档靠谱得多。3.3 修改表结构加列、加索引、改默认值业务迭代过程中给已有表加字段是非常高频的操作。语法如下ALTER TABLE user ADD COLUMN phone varchar(20) DEFAULT NULL COMMENT 手机号 AFTER email;注意AFTER关键字可以控制新列的位置。如果不指定新列默认加在表末尾。给已有重复数据的表添加唯一索引这是热词里的一个高频场景。假设你的user表email字段已经有重复值了直接执行ALTER TABLE user ADD UNIQUE KEY uk_email (email);会报Duplicate entry错误。这时需要先找出重复数据处理掉之后再添加。查找重复数据的语句SELECT email, COUNT(*) FROM user GROUP BY email HAVING COUNT(*) 1;处理重复数据的策略无非两种删除多余的只留一条或者给重复数据加后缀改成不重复。处理完再执行加唯一索引就能成功了。修改字段默认值也是常见操作比如把status的默认值从1改成0ALTER TABLE user ALTER COLUMN status SET DEFAULT 0;3.4 DROP、TRUNCATE、DELETE三兄弟的区别这三个操作都能让数据消失但含义完全不同面试也经常被问到操作类型能否回滚是否走事务效率对自增ID影响DROPDDL否否快表直接没了TRUNCATEDDL否否快重置为1DELETEDML是是慢继续累加一句话总结DROP删表结构TRUNCATE清空表数据但保留结构DELETE按条件删行且可以回滚。生产环境删数据务必用DELETE并且先开事务确认无误再提交。4. 增删改DML操作的核心是“别玩脱”4.1 INSERT单条插入和批量插入的效率差异插入数据的语法非常直观INSERT INTO user (name, age, email) VALUES (张三, 28, zhangsanexample.com);一次插入多行逗号分隔即可INSERT INTO user (name, age, email) VALUES (李四, 22, lisiexample.com), (王五, 30, wangwuexample.com);批量插入比循环单条插入快得多这是有底层原因的每一条INSERT都是独立事务都要经过解析、优化、执行、提交的完整链路而批量插入只需一次解析、一次提交省掉的磁盘IO和日志同步开销非常可观。我实测过插入一万行数据逐条插入耗时大概在几十秒批量插入不到一秒。如果数据来自另一个表可以用INSERT ... SELECTINSERT INTO user_backup (name, age, email) SELECT name, age, email FROM user WHERE age 25;4.2 UPDATE单表更新、多表更新与同表更新的坑更新语句谁都会写但细节处最见真功夫。先看标准语法UPDATE user SET age 29 WHERE name 张三;多条字段更新用逗号分隔UPDATE user SET age 29, status 1 WHERE id 1001;多表关联更新是进阶操作。比如根据订单表的金额更新用户表的累计消费字段UPDATE user u JOIN order o ON u.id o.user_id SET u.total_amount u.total_amount o.amount WHERE o.status PAID;这种写法相当于把两张表先关联起来再统一更新比子查询效率高很多也更直观。热词里有“mysql中更新子查询”其实说的就是这类场景很多人先想到用子查询但MySQL对“更新同一张表”有限制。同表更新是个经典的坑。比如想把每行数据里的数值字段根据同表其他字段更新直接写子查询会报错。比如UPDATE user SET age (age 5) WHERE age 20;这其实没问题因为不涉及子查询。真正会踩坑的是UPDATE user SET name (SELECT name FROM user_temp WHERE user_temp.id user.id) WHERE id IN (SELECT id FROM user_temp);这种涉及多个表的写法在某些MySQL版本和sql_mode下会报“You cant specify target table for update in FROM clause”错误。解决办法是包一层派生表UPDATE user SET name (SELECT name FROM user_temp WHERE user_temp.id user.id) WHERE id IN (SELECT id FROM (SELECT id FROM user_temp) AS tmp);简单说MySQL不允许在更新一张表时直接去子查询里读同一张表但通过派生表绕一层就能骗过优化器。另一个必须警惕的是不带WHERE的UPDATEUPDATE user SET age 0;这条语句会把全表所有人的年龄改成0生产环境一旦误执行后果不堪设想。MySQL的Workbench和命令行交互模式默认开了安全更新模式执行不带主键条件的UPDATE会直接报错提示Error Code: 1175。这个报错让很多新手困惑但其实是保护机制。解决方案是确认安全后临时关闭SET SQL_SAFE_UPDATES 0;操作完记得改回来改成1。提醒任何生产环境的UPDATE先跑一遍等价的SELECT确认影响范围再执行UPDATE。这句话我重复多少遍都不嫌多。4.3 DELETE删数据前先数数DELETE的语法和UPDATE类似最大的建议同样是一个先SELECT再DELETE。SELECT COUNT(*) FROM user WHERE status 0;确认行数符合预期DELETE FROM user WHERE status 0;如果数据量特别大一次性DELETE全表可能会锁表很久影响线上业务。分批次删除是更好的选择DELETE FROM user WHERE status 0 LIMIT 1000;循环执行这条语句直到影响行数为0。每次删除一千条单次操作时间短锁范围小对线上影响可控。5. 查询操作从入门到进阶的必经之路5.1 SELECT基础列选择、WHERE与常用运算符查询是所有SQL操作中出现频率最高的语法结构是SELECT 字段列表 FROM 表名 WHERE 条件;最简单的全表查询SELECT * FROM user;实际开发中不建议写SELECT *除非你真的需要全部字段。原因有两点一是网络传输量更大二是如果表结构后来加了字段SELECT *的结果集会随之变化代码里按索引取字段的地方容易出问题。写清楚需要的列代码更健壮。WHERE后面可以接各种条件运算符SELECT * FROM user WHERE age 20 AND status 1; SELECT * FROM user WHERE age BETWEEN 20 AND 30; SELECT * FROM user WHERE email LIKE %example.com; SELECT * FROM user WHERE id IN (1001, 1002, 1003);注意LIKE的%通配符%匹配任意多个字符_匹配单个字符。LIKE %example%这种写法在数据量大时无法利用索引会触发全表扫描。非必要不这么写或者配合全文索引解决。热词里有个“mysql的or能去重吗”这里直接回答OR不会去重。比如SELECT * FROM user WHERE name 张三 OR name 李四;如果表里有多个叫“张三”的记录结果会全部返回不会自动合并。想去重就要加DISTINCTSELECT DISTINCT name FROM user WHERE name 张三 OR name 李四;5.2 排序与分页ORDER BY和LIMIT的配合排序用的ORDER BY默认升序ASC想倒序就加DESCSELECT * FROM user ORDER BY age DESC;多个字段排序优先按前面的字段排相同则按后面的排SELECT * FROM user ORDER BY age DESC, id ASC;分页查询是列表页最常见的功能语法是LIMIT后面跟两个参数第一个是偏移量第二个是返回行数SELECT * FROM user ORDER BY id LIMIT 0, 20;这段的意思是跳过0条返回20条即第1页。第2页就是SELECT * FROM user ORDER BY id LIMIT 20, 20;页码越深偏移量越大查询越慢。如果数据量达到百万级翻到第1000页时MySQL还是要先读前两万条再丢弃性能很差。优化方式是用“延迟关联”或者基于主键定位SELECT * FROM user WHERE id 20000 ORDER BY id LIMIT 20;前提是ID连续且按主键排序适合后台简单分页场景。5.3 分组与聚合GROUP BY、HAVING与聚合函数分组查询常用在统计报表场景。比如统计每个年龄段的人数SELECT age, COUNT(*) AS cnt FROM user GROUP BY age;HAVING是对分组后的结果做筛选相当于分组里的WHERESELECT age, COUNT(*) AS cnt FROM user GROUP BY age HAVING cnt 5;这条语句的含义是只返回人数超过5人的年龄段。注意WHERE和HAVING的区别WHERE是在分组前过滤行HAVING是在分组后过滤组所以HAVING里可以用聚合函数WHERE里不行。5.4 JOIN关联查询四种连接类型与使用场景关联查询是面试必问也是实际业务最常用的。假设有两张表user和order现在要查每个用户的订单信息SELECT u.name, o.order_no, o.amount FROM user u INNER JOIN order o ON u.id o.user_id;INNER JOIN只返回两边都匹配的行。如果某个用户没有订单这行就不出现。LEFT JOIN则左表全保留右表没有匹配就补NULLSELECT u.name, o.order_no, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id;这条语句会返回所有用户哪怕没有订单订单字段显示NULL。RIGHT JOIN方向相反右表全保留。FULL OUTER JOIN两边都保留多数业务用不上MySQL也不直接支持要用LEFT JOIN加UNION模拟。使用JOIN时有个细节关联字段上要有索引否则大表关联会慢到怀疑人生。比如order.user_id必须建索引否则每次关联都要扫描整张order表。建索引的语法后面会讲到。6. 索引与执行计划让慢查询原形毕露6.1 创建索引的语法和注意事项索引之所以重要是因为没有索引的查询就等于在图书馆里一本一本翻书有了索引就直接翻目录找到页码。主键索引就是主键本身此外还能额外建普通索引、唯一索引、联合索引。创建索引CREATE INDEX idx_age ON user(age); CREATE UNIQUE INDEX uk_email ON user(email);给已有表加唯一索引时如果遇到重复数据会报错这一点前面已经提过解决方案是先清理重复数据再建索引。普通索引则允许重复没有这个限制。联合索引是在多个字段上建一个索引CREATE INDEX idx_age_status ON user(age, status);联合索引遵循“最左前缀原则”也就是说(age, status)这个联合索引可以支持age单字段查询也可以支持age status组合查询但如果只查status用不上这个索引。这是面试高频题实际设计索引时要根据查询条件把最常用的字段放最前面。6.2 执行计划EXPLAIN怎么读懂type和key一条SQL跑得慢不要瞎猜先看执行计划。方法很简单在SQL前面加EXPLAINEXPLAIN SELECT * FROM user WHERE age 20;返回的结果里重点关注几个列type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。ALL就是全表扫描说明没走索引。key实际用到的索引名。如果为NULL说明没走索引。rows预估扫描行数数字越大越慢。ExtraUsing filesort表示需要额外排序Using temporary表示用到临时表这两种都要尽量避免。热词里有“mysql中int5”这里展开说下。在WHERE条件里对索引列做运算比如SELECT * FROM user WHERE age 5 25;这种写法会导致索引失效因为MySQL必须先算出每一行的age 5才能判断是否大于25等于是放弃了索引。正确写法是把运算移到条件右侧SELECT * FROM user WHERE age 20;同理还有在索引列上包函数、发生隐式类型转换都会让索引失效。记住一条原则不要让索引列参与任何运算和函数。6.3 隐式类型转换为什么varchar字段查数字会全表扫描隐式类型转换是索引失效的另一大元凶。举个例子phone字段是varchar类型存的是手机号创建一个索引执行SELECT * FROM user WHERE phone 13800138000;MySQL会把数字13800138000转成字符串还是把phone列转成数字答案是后面的情况即把phone列转成数字比较导致索引失效。如果你在建表时把手机号设计成了varchar查询时就必须写字符串SELECT * FROM user WHERE phone 13800138000;这是一个很恶心但又很常见的坑。排查的时候看到EXPLAIN结果里type是ALL、key是NULL同时查询条件里又涉及这种可能发生类型转换的字段八成就是这个问题。7. 日常运维与问题排查这些命令关键时刻能救命7.1 开启general_log查看执行记录排查生产问题时有时候你想知道某个时间点数据库到底执行了什么SQL。这时候可以打开通用查询日志general_log。SET GLOBAL general_log ON; SET GLOBAL general_log_file /var/log/mysql/general.log;打开之后所有客户端执行的SQL都会记录到这个文件里包括查询和更新。查找特定表的操作记录grep user /var/log/mysql/general.log | tail -n 50排查完记得关掉否则日志文件会以肉眼可见的速度膨胀把磁盘撑满。7.2 查看当前连接数和最大连接数数据库连不上时先看连接数SHOW VARIABLES LIKE max_connections; SHOW STATUS LIKE Threads_connected;如果Threads_connected接近max_connections说明连接池满了。处理方式有两种一是临时调大SET GLOBAL max_connections 500;二是排查应用侧看是不是连接池没释放连接。很多时候是代码里忘了关闭连接MySQL默认的wait_timeout是8小时空闲连接一直占着不释放连接数就这么被耗尽了。7.3 常用信息查询和帮助命令忘记表结构了DESC user;查看建表语句SHOW CREATE TABLE user;查看当前版本SELECT VERSION();查看所有数据库SHOW DATABASES;切换数据库USE mydb;这些命令不常用但关键时候能救急。7.4 高频报错速查表错误提示原因解决方案ERROR 1045 (28000): Access denied用户名或密码错误核对账号密码root被限定了localhost时指定-h 127.0.0.1ERROR 1049 (42000): Unknown database数据库不存在确认库名拼写或先建库ERROR 1146 (42S02): Table doesnt exist表名不存在确认表名注意大小写ERROR 1062 (23000): Duplicate entry唯一键冲突检查唯一索引列的数据是否重复ERROR 1175 (HY000): Safe update mode安全更新模式拦截确认条件无误后临时关闭安全模式ERROR 1410: You are not allowed to create a user with GRANT权限不足用root或等权限的账号执行这六类报错基本覆盖了新手90%以上的踩坑点。遇到陌生报错先用SHOW ENGINE INNODB STATUS;查看最近一次事务状态或者直接搜索错误码比瞎猜快很多。8. 最后的几点个人看法MySQL入门最大的障碍不是语法而是心态。很多人一上来就背各种命令、看各种原理结果真到了命令行前面反而不知道敲什么。我的建议是挑一个真实的小项目比如给班级做一个通讯录管理系统从建库开始一步步写完增删改查再试着加索引、看执行计划最后模拟几个并发场景。走完这一轮你对MySQL的体感会完全不一样。另外网上很多教程喜欢把命令写得特别全几十上百条铺满一屏但实际工作中真正高频用的就那么二三十条。先把这些用到条件反射再按需扩展比一开始就追求大而全要高效得多。遇到不会的语法多看看官方文档多动手验证很多问题卡住你半小时的往往只是一个引号或者一个空格的事。
