1. 别急着敲 SQL先把 MySQL 这层外壳摸清楚很多人第一次打开 MySQL 的命令行看到那个mysql提示符就开始发懵手指悬在键盘上不知道往哪落。我当初也一样跟着教程敲了几句 MySQL 基本操作结果报了一屏红字回头一看——压根没连上服务端。所以这篇东西我不打算上来就丢一堆 SQL 语法给你背而是按我当年从零摸过来的顺序把基本操作这四个字真正拆开讲一遍它到底包含哪些动作每个动作背后发生了什么哪些地方新手最容易翻车。适合完全没接触过数据库、或者只会照抄语句但不知道自己在干什么的人看。1.1 客户端和服务端到底谁在干活这是最容易含混的第一个概念。MySQL 不是一个程序它是两个角色一个是常驻在后台、真正管着数据文件的服务端进程名通常是mysqld另一个是你面前这个能打字、能看结果的客户端。命令行里的mysql命令、图形化的 Workbench、Navicat、DBeaver全都只是长得不一样的客户端而已它们自己一个字节的数据都不存。想通这一点很多莫名其妙的报错就解释得通了。比如ERROR 2002 (HY000): Cant connect to local MySQL server through socket ...这句话翻译成人话是客户端活着但它找不到那个该搭理它的服务端。跟你 SQL 写得好不好一点关系都没有。我见过太多新手在这里死磕语法其实是服务根本没启动。服务端和客户端的通信走的是网络协议本机走 Unix socket 或者 127.0.0.1 的 TCP 端口默认 3306。所以连不上这件事永远先查这三样服务在不在跑、端口对不对、账号密码对不对。这三样排完再去看 SQL。1.2 第一次连接时那几个绕不开的参数命令行连接的标准写法长这样mysql -h 127.0.0.1 -P 3306 -u root -p四个参数拆开看-h是主机地址-P是大写字母、指定端口小写-p是密码这两个大小写千万别搞反我当年就因为这个大小写被卡了十分钟-u是用户名-p后面不要直接跟密码回车后会单独提示你输入这样密码不会留在命令历史里。如果你在本机上操作-h可以直接省略客户端默认走本地。这一步有个新手常见的误解以为连上了就是进入 MySQL 了。其实你只是进入了一个会话session可以把它理解成我打开了一个对话窗口接下来我说的每句话都发给服务端执行。窗口关掉对话就结束了但数据还在服务端待着。登录成功后会看到一段欢迎信息和版本号比如Server version: 8.0.36 MySQL Community Server。记住这个版本号它后面会决定你很多语法能不能用。1.3 版本号和字符集这两个坑新手必踩MySQL 8.0 和 5.7 之间有不小的差异教程和教程之间经常对不上。最典型的就是默认字符集5.7 时代默认还是latin1或者utf8注意这个utf8是假的utf8只支持最多 3 字节而 8.0 默认已经是utf8mb4。为什么说 5.7 的utf8是假的因为标准 UTF-8 里一个字符最多可以用 4 个字节表示中文、日文这些常用字符 3 字节够用但像某些 emoji、生僻字、部分少数民族文字就需要 4 字节。MySQL 的utf8只给你留了 3 字节一存就报错或者变成问号。真正的 4 字节版本叫utf8mb4。所以你在建库的时候如果只是随手写CREATE DATABASE demo;在不改配置的情况下很可能得到一个拉丁字符集的库存中文就开始出乱码。稳妥的写法是显式指定CREATE DATABASE demo CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;这里COLLATE是排序规则决定了字符串比较和排序时怎么算大小。utf8mb4_0900_ai_ci里的0900是 Unicode 9.0 标准ai表示 accent insensitive重音不敏感ci表示 case insensitive大小写不敏感。如果你用的是 5.7没有0900一般用utf8mb4_general_ci或utf8mb4_unicode_ci。这个细节选题时看着烦但它决定了你后面WHERE name abc会不会把ABC也查出来。项目5.7 常见默认8.0 常见默认建议字符集latin1 / utf8utf8mb4一律 utf8mb4排序规则utf8_general_ciutf8mb4_0900_ai_ci8.0 用 0900_ai_ci认证插件mysql_native_passwordcaching_sha2_password老客户端连不上时注意最后一行那个认证插件值得多说一句。8.0 默认换成了caching_sha2_password一些老版本的客户端工具连不上报 Authentication plugin cannot be loaded。解决办法不是改客户端而是在创建用户时指定插件CREATE USER app% IDENTIFIED WITH mysql_native_password BY your_password;这些都属于环境层面的坑早踩早安心。2. 库和表建之前先想清楚的三件事2.1 建库动作用CREATE但真正的思考在命名和字符集数据库database在 MySQL 里更像一个命名空间或者文件夹用来把不同项目的表分开。基本操作就三条CREATE DATABASE IF NOT EXISTS myapp CHARACTER SET utf8mb4; SHOW DATABASES; USE myapp;IF NOT EXISTS是我强烈建议加上的因为脚本重复执行时没有它会直接报错中断加上它就变成有就跳过。USE是切换当前会话的默认库之后你写表名就不用每次都带上库名前缀了。我个人的命名习惯是库名全小写、用下划线分隔、不带空格和驼峰比如blog_system、order_center。原因很实际某些操作系统文件系统大小写敏感Linux你写MyApp和myapp可能是两个不同的东西跨平台迁移时容易出岔子。别给自己找麻烦。2.2 建表动作里字段类型选错最要命表的基本操作是CREATE TABLE、SHOW TABLES、DESC 表名、DROP TABLE。重点说创建。给你一个带注释的典型例子CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(120) NOT NULL, age TINYINT UNSIGNED DEFAULT NULL, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里每一行都有讲究我逐个说BIGINT UNSIGNED而不是INT。ID 用INT最多存到约 21 亿听起来很多但用户表、日志表这种写多读多的场景几年就跑满了。BIGINT一步到位代价是多 4 个字节完全值得。UNSIGNED是因为 ID 不可能为负白白浪费一半区间。金额用DECIMAL而不是FLOAT或DOUBLE。这是铁律。浮点数存 0.1 这种值本身就存不准会变成 0.0999999...。你算钱的时候差一分钱都算事故。DECIMAL(12,2)表示总共 12 位、小数 2 位精度是准的。时间用DATETIME还是TIMESTAMPTIMESTAMP只有 4 字节范围到 2038 年且会随时区变化DATETIME8 字节范围 1000~9999 年不随时区变。业务表我一般用DATETIME省心。VARCHAR的长度别乱给。有人图省事全写VARCHAR(255)觉得反正占不满。其实VARCHAR是变长存储的写多长占多长但长度上限会影响临时表和索引的可用性。挑一个贴合实际业务的上限比如用户名 50 足够了。2.3 约束不是摆设是帮你挡错的防线上面那段建表语句里出现了NOT NULL、DEFAULT、PRIMARY KEY、UNIQUE KEY这些统称约束。新手容易觉得这种限制烦能不加就不加其实大错。PRIMARY KEY主键一行记录的唯一身份证。InnoDB 里主键还有个特殊作用——它直接决定了数据的物理存储顺序这叫聚簇索引。所以主键最好选自增整数插入时永远往末尾追加不会引起数据页分裂性能最好。NOT NULL明确告诉数据库这个字段必须有值。很多人以为不写就是不允许空其实不写默认是允许NULL的。而NULL参与运算时结果也是NULLWHERE age ! 18这种条件会漏掉所有age为NULL的行坑很深。DEFAULT给个默认值插入时不给就自动填上。像created_at DEFAULT CURRENT_TIMESTAMP这种能让创建时间字段零成本自动维护。有个细节值得记NULL和空字符串是两回事。空字符串是有值值为空NULL是根本没值。统计非空值的时候要小心处理一般用IS NULL/IS NOT NULL而不是 NULL。3. 增删改查四条语句背后的执行逻辑3.1 INSERT 的两种玩法单行和多行插入数据的基本操作第一条INSERT INTO users (username, email, age) VALUES (zhangsan, zexample.com, 20);字段名可以省略但强烈建议写上。因为省略后值的顺序必须和表结构里字段的顺序完全一致哪天你给表加了个新字段又挪了位置老代码就全乱了。一次插多行比循环插单行快得多这不是一点点快是几倍到几十倍的差距INSERT INTO users (username, email) VALUES (alice, aexample.com), (bob, bexample.com), (carol, cexample.com);原因在于每次INSERT都会产生一次网络往返、一次事务提交、一次磁盘写入如果开了刷盘。批量插入把这些开销合并了。做数据初始化、批量导入的时候这个优化能省下大量时间。还有两个要点唯一键冲突。如果username有唯一约束插入重复值会报错。8.0 里可以用INSERT ... ON DUPLICATE KEY UPDATE做有则更新、无则插入或者用INSERT IGNORE直接跳过冲突行。后者有个副作用它会把所有错误都静默掉包括不该忽略的类型错误用的时候要留个心眼。3.2 SELECT 里 WHERE 条件怎么写决定后面要不要还债查询是最常用的操作基本结构是SELECT username, age FROM users WHERE age 18 AND username LIKE z% ORDER BY created_at DESC LIMIT 10;看着简单但几个点新手必须搞清楚WHERE里能用、!、、、BETWEEN、IN、LIKE、IS NULL等。LIKE z%里的%是通配符放在开头%z就没法走索引了因为它不确定从哪开始匹配只能全表扫描。数据量一大就慢得明显。这是性能层面最基础的一条。AND和OR混用要加括号。比如WHERE age 18 OR age 10 AND city BJ因为AND优先级高于OR实际执行是age 18 OR (age 10 AND city BJ)很可能跟你想要的不一样。NULL不能跟比较。age NULL永远返回空结果必须写age IS NULL。这个坑我见过不止一个同事踩排查半天以为是数据没插进去。3.3 UPDATE 和 DELETE执行前先 SELECT 一遍更新和删除是危险操作因为一旦提交就回不去了。我的基本纪律是先把WHERE条件拿去做一次SELECT确认要动的行数是对的再改成UPDATE。-- 第一步先看会命中哪些行 SELECT id, username, age FROM users WHERE username zhangsan; -- 第二步确认无误再更新 UPDATE users SET age 21 WHERE username zhangsan;背后是一个很实在的教训UPDATE users SET age 21;少了WHERE就把整张表所有人的年龄都改成 21 了。写的时候手指一抖很容易发生。而先 SELECT 再 UPDATE这个动作只要花几秒钟能救你一个下午。删除同理DELETE FROM users WHERE id 5;DELETE删的是行数据表结构还在。想清空整表用TRUNCATE TABLE users它更快直接重建表但同样不可回滚且会重置自增值。DROP TABLE是把整个表连结构一起删掉更彻底。这三个操作的危险等级递增DELETETRUNCATEDROP心里要有把尺子。还有一点很关键UPDATE和DELETE的WHERE条件尽量走索引否则就是全表逐行扫描加锁大表上可能直接卡住整个库。关于索引第 5 节单独讲。操作影响对象可回滚是否重置自增DELETE满足条件的行未提交可否TRUNCATE全部行否是DROP表结构数据否不适用4. 排序、分页和聚合把数据变成能看的结果4.1 ORDER BY 与 LIMIT 的经典组合查询结果默认顺序是不确定的想有稳定顺序就必须显式ORDER BYSELECT id, username FROM users ORDER BY created_at DESC, id DESC LIMIT 20;DESC是降序ASC是升序默认。上面这个写法有个实战意义时间相同时用id做第二排序键保证结果稳定。否则分页时可能出现同一条记录在第二页又冒出来一次的情况。LIMIT除了限制条数还能做分页。LIMIT 20 OFFSET 40表示跳过前 40 条、取 20 条。但这里有个深坑OFFSET越大越慢因为数据库必须先把前面那 40 条查出来再丢掉。线上分页到很深的页码时正确做法是用游标SELECT id, username FROM users WHERE id 1000 ORDER BY id LIMIT 20;用上一页最后一条的id做起点效率跟第一页一样。这是老手和新手在分页上的分水岭。4.2 聚合函数和 GROUP BY 的分组逻辑统计类需求靠聚合函数COUNT、SUM、AVG、MAX、MIN。SELECT city, COUNT(*) AS user_count FROM users GROUP BY city HAVING COUNT(*) 5 ORDER BY user_count DESC;GROUP BY是按某个字段把行归堆聚合函数在每一堆里算一个值。这里有两个新手友好度很低的点HAVING和WHERE的区别。WHERE是在分组之前过滤行HAVING是在分组之后过滤组。所以你能用HAVING COUNT(*) 5但不能用WHERE COUNT(*) 5因为分组还没发生COUNT无从算起。SELECT里出现的非聚合字段必须出现在GROUP BY里。这条在 8.0 默认配置下会直接报错开启了ONLY_FULL_GROUP_BY。5.7 有些环境下不报错但结果随机更危险因为它悄悄给你返回了一堆里随机的那个值。还有个常见误区COUNT(*)和COUNT(某字段)不一样。前者统计所有行后者跳过该字段为NULL的行。拿不准时候用COUNT(*)。4.3 多表关联 JOIN 的关键在于关联字段数据拆到多张表之后把它们接回来就靠JOINSELECT u.username, o.order_no, o.amount FROM users u JOIN orders o ON u.id o.user_id WHERE o.created_at 2024-01-01;JOIN不加修饰默认是INNER JOIN只返回两边都能匹配上的行。LEFT JOIN会保留左表全部行右表匹配不上就填NULL。这个区别在两个地方体现得特别明显想统计所有用户及其订单数没有订单的算 0就得用LEFT JOIN加COUNT(o.id)。注意这里要COUNT(o.id)而不是COUNT(*)因为COUNT(*)会把那张补了NULL的行也算作 1没订单的用户就变成 1 了数据全错。ON里的条件只影响关联匹配WHERE里的条件是对结果过滤。放在LEFT JOIN场景下把条件写进ON还是WHERE结果完全不同。这是 JOIN 里最容易错的一处写之前先在纸上画两张表比划一下。JOIN的关联字段最好是有索引的,不然就是两个大表做笛卡尔积慢到怀疑人生。下一节细说。5. 索引与 UPDATE 的边界入门阶段就该有的性能意识5.1 索引不是玄学就是一本字典的目录索引index的作用一句话说清让你不用翻完整本书就能找到目标那一页。没有索引查一行数据库要把整张表从头扫到尾全表扫描有了索引它先查目录定位再直接跳到目标位置。CREATE INDEX idx_users_email ON users (email);一般在高频出现在WHERE、ORDER BY、JOIN ON里的字段上建索引。但索引不是越多越好因为它会拖慢写入每次INSERT、UPDATE、DELETE数据库都得同步维护索引结构等于写一份数据要更新好几处。一张表上挂十几个索引写入性能会明显下降。还有一个新手常困惑的点主键自带索引UNIQUE约束也会自动创建唯一索引不用重复建。5.2 为什么我在 WHERE 里给字段套函数索引就失效了这是索引失效里最经典的一条。看这个-- 索引失效对字段做了函数计算 SELECT * FROM users WHERE YEAR(created_at) 2024; -- 索引可用改成范围条件 SELECT * FROM users WHERE created_at 2024-01-01 AND created_at 2025-01-01;原因很简单索引里存的是created_at的原始值。你写成YEAR(created_at)每一行都得先算一遍年份再比较数据库没法用索引去定位只能全表扫描。第二条把范围写出来数据库就能拿索引做区间查找。同样的道理WHERE id 1 100这种在字段上做运算的写法也走不了索引得反过来写成WHERE id 99。这就是把计算放到常数一侧别放到字段一侧的口诀。判断一个查询到底有没有用上索引用EXPLAINEXPLAIN SELECT * FROM users WHERE email zexample.com;重点看两个列type越接近const、ref越好出现ALL就是全表扫描和key实际用了哪个索引NULL就是没用上。EXPLAIN是排查慢查询的入门必备工具值得早点学会看。5.3 UPDATE 里的子查询MySQL 的一条特殊限制有一条限制很多人被坑过在 MySQL 里不能直接UPDATE或DELETE一张表同时又在子查询里SELECT同一张表。报错大概是You cant specify target table users for update in FROM clause。比如你想把和某个用户同名的人都改掉直接写会报错UPDATE users SET status 1 WHERE username IN (SELECT username FROM users WHERE id 5);绕过去的办法是套一层派生表让内层查询先落地成临时结果UPDATE users SET status 1 WHERE username IN ( SELECT username FROM (SELECT username FROM users WHERE id 5) AS tmp );内层SELECT的结果先被物化成tmp外层的UPDATE操作的是原表两者不再冲突。这个技巧记住就行遇到时不用现查。另外提醒一句UPDATE语句如果命中的行很多会长时间持有行锁其他事务想改同一批数据就得等。所以大表上批量更新建议分批做比如每次只更新 1000 行加个LIMIT别一口气改几十万行。6. 我在入门阶段踩过的几个典型坑6.1 密码、端口、连接三连问前面说过一大半连不上的问题都出在这三样上。我整理了一个排查顺序按这个来基本不会迷路服务端在跑吗本机用systemctl status mysql或看进程列表确认。端口对吗默认 3306如果改过或者用了容器那端口可能映射到别的值。账号密码对吗8.0 的默认 root 密码有时是安装时随机生成的藏在日志里不是空密码。很多人以为是空密码试了半天。命令行连上了再考虑图形客户端的问题。图形工具连不上多半是认证插件或者 SSL 设置的问题不是数据本身的问题。6.2 报错信息比我以为的有用刚入门那会儿我一看到报错就慌直接去搜错误全文。后来养成一个习惯先读报错本身。MySQL 的报错虽然英文但信息量其实很足。比如Unknown column xxx in field list字段名写错或者表里根本没这个字段。Table db.tbl doesnt exist库名或表名错了或者你忘了USE那个库。Duplicate entry xxx for key uk_username唯一键冲突说明这个用户名已经存在了。能读懂这几句一半的调试时间就省了。剩下的搜的时候把表名、字段名替换成占位符去搜别直接把你的字段名搜进去往往能搜到更通用的答案。6.3 几个真正省时间的小习惯最后分享几个我自己在用、也确实省过时间的习惯平时开着SHOW WARNINGS;的心思。有些语句执行完提示 1 row affected 但结果不对往往是它悄悄给了个 warning比如数据被截断了。执行完可疑语句顺手看一眼 warning能提前发现不少问题。建表脚本一定带IF NOT EXISTS操作前先备份。我现在改任何生产脚本第一步永远是CREATE TABLE xxx_bak AS SELECT * FROM xxx;几秒钟的事出了事能救命。给每张表都带上created_at和updated_at。这两个字段平时看着没用一旦要排查这条数据什么时候被改过那个问题从哪一刻开始出现,它们就是唯一的线索。updated_at记得设成ON UPDATE CURRENT_TIMESTAMP自动维护不用你在代码里手动改。入门阶段就把utf8mb4焊死在建库建表模板里。我见过太多项目上线后才发现存不下表情符号回头改库改表改连接成本比一开始就写对高得多。这不是什么高深技术就是一开始多写几个字符的事但能省掉后面一整轮的返工。