折腾MySQL这些年最常被新手问到的还是那句数据库怎么建SQL怎么写正好翻到之前整理的学习笔记里面完整记录了从创建数据库到跑通各类SQL的过程就干脆把这份笔记重新梳理了一遍。这篇内容不是教科书式的语法罗列而是我当时在真实环境里一个字段一个字段敲出来、一条语句一条语句跑通的实操总结。目标是让刚接触MySQL的朋友看完就能动手把库建起来、把表建好、把数据填进去、把想要的结果查出来顺便躲开那些官方文档里不会写清楚的小坑。先说结论MySQL的建库建表并不难难点在于建库之前的决策——字符集选什么、排序规则怎么定、存储引擎用什么这些决定一旦做错后期改起来非常痛苦。SQL语句的难点则在于场景同样的查询需求写法不同执行效率可能差出几个数量级。这篇文章就把这些决策逻辑和常见写法一次讲清楚。1. 动手建库之前先把几个关键参数定下来1.1 字符集和排序规则为什么不能拍脑袋选很多初学者第一次创建数据库时直接执行CREATE DATABASE test;就完事了。这样建出来的库用的是MySQL默认配置版本不同默认值也不同MySQL 8.0默认字符集是utf8mb4而5.7及更早版本有的默认是latin1。一旦数据写入后才发现字符集不对中文乱码、排序错乱、字段长度计算偏差就会接踵而来。字符集的选择上我建议直接锁定utf8mb4不要用utf8。这是个老生常谈的坑了MySQL里的utf8最多只能存3个字节的字符像emoji表情以及部分生僻汉字都需要4个字节用了utf8存这些字符直接报错或者变问号。utf8mb4才是真正意义上完整的UTF-8编码兼容性最好。至于utf8mb3这个历史遗留名称现在8.0版本已经明确标记为废弃新项目不要碰。排序规则Collation是跟着字符集走的它决定了字符串比较和排序的规则。MySQL 8.0默认的是utf8mb4_0900_ai_ci其中ai表示不区分重音ci表示不区分大小写。5.7时代最常用的是utf8mb4_general_ci性能尚可但规则比较粗糙。如果你有精确匹配、区分大小写的需求要选utf8mb4_bin或带cs区分大小写的规则。这里有个实际经验在做登录校验时用户名到底区分不区分大小写直接受排序规则影响。如果业务要求用户名不区分大小写那在查询的时候可以不依赖排序规则直接WHERE LOWER(username) LOWER(?)更保险因为排序规则在DBA手里换了环境行为可能就变了。1.2 存储引擎InnoDB和MyISAM怎么权衡存储引擎决定了数据怎么存、怎么锁、怎么恢复。MySQL 8.0里InnoDB是默认引擎也是绝大多数场景下的正确选择。核心差异可以看这张表对比项InnoDBMyISAM事务支持支持ACID、提交、回滚不支持锁粒度行级锁表级锁外键约束支持不支持崩溃恢复支持redo log恢复依赖repair table全文索引8.0已支持较早支持使用场景绝大多数OLTP业务只读报表、数据仓库等我在早些年的项目里见过把日志表建在MyISAM上的做法理由是查询快、占用小。但一旦服务崩溃表损坏的概率远高于InnoDB而且写操作会把整张表锁住并发一上来就非常难受。现在MyISAM基本只剩下历史包袱的角色新表一律InnoDB不需要犹豫。1.3 本地练习环境的版本和工具选型练习环境建议直接装MySQL 8.0的最新稳定版别在5.7上花太多时间。8.0的窗口函数、CTE公共表表达式、更好的优化器都是现代SQL开发绕不开的东西学了5.7再切8.0虽然不至于重学但很多特性边用边补效率太低。如果电脑上已经装了5.7又想体验8.0用Docker跑一个容器最省事docker run --name mysql8 -e MYSQL_ROOT_PASSWORDyourpassword -p 3306:3306 -d mysql:8.0图形客户端的话Navicat、DBeaver、DataGrip都是主流选择选正版或开源版本即可。我个人的做法是命令行为主、图形工具为辅命令行能让你更清楚地感知SQL本身在做什么排查问题时不依赖工具图形工具用来快速查看表结构、导出数据比较方便。两者配合效率最高。2. 用SQL创建数据库和数据表从语句到参数逐行拆解2.1 CREATE DATABASE里的隐藏细节创建数据库的语法看起来就一句话但里面藏着几个值得注意的点。推荐写法是CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci;很多人不理解IF NOT EXISTS存在的意义。其实在写自动化脚本、初始化项目的时候脚本可能被执行多次有了这个判断就不会因为数据库已存在而报错。字符集和排序规则显式写出来也很重要这等于把环境的决定权从服务器默认配置手里抢回来避免同样的脚本在不同的MySQL版本上跑出不同结果。建完之后建议执行一下SHOW CREATE DATABASE shop;看返回的结果是不是和预期一致。这个习惯很多人没有但实际很有用——比如排查线上数据库字符集问题时第一步就是看库、表、字段三级各自的字符集因为MySQL的字符集是可以逐级覆盖的表层配置会被字段层覆盖任何一级不一致都会出问题。2.2 设计一张用户表的完整建表SQL建表是整个数据库设计里含金量最高的环节。我用一张电商系统里的用户表来拆解。这是一版经过多次调整的建表语句CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, username VARCHAR(64) NOT NULL COMMENT 用户名, email VARCHAR(128) NOT NULL COMMENT 邮箱, phone VARCHAR(32) DEFAULT NULL COMMENT 手机号, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态 1-正常 0-禁用, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, last_login_at DATETIME DEFAULT NULL COMMENT 最后登录时间, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username), UNIQUE KEY uk_email (email), KEY idx_phone (phone), KEY idx_status_created (status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户表;逐个字段说说设计逻辑。主键id用BIGINT UNSIGNED而不是INT原因是INT最大只能到21亿多对用户表这种增长快、合并数据频繁的场景BIGINT能避免换主键类型的痛苦。自增配合主键在InnoDB下还有性能意义插入时按主键顺序写入聚簇索引能减少页分裂。username和email都加了唯一索引这是业务上明确的唯一约束。很多人只在前端判断“这个用户名有没有被注册”然后才插入这样在并发场景下必然会有漏网之鱼。唯一索引才是最终防线数据库层面的约束永远比应用层判断可靠。phone字段没有加唯一索引因为业务允许手机号为空而唯一索引对NULL值的处理是“多个NULL不冲突”这个特性可以用但要心里有数。status字段用TINYINT而不是VARCHAR存active之类的字符串一是省空间二是查询更快。TINYINT在MySQL里只占1字节范围-128到127UNSIGNED可到255存状态值绰绰有余。balance字段用DECIMAL(10,2)而不是FLOAT或DOUBLE这是硬性教训浮点数在二进制里不精确0.10.2算出来可能是0.30000000000000004涉及钱一分都不能错。DECIMAL是定点数按十进制存储和运算精确无误。created_at和updated_at这两个字段强烈建议每个表都加。MySQL 8.0支持了DATETIME类型的DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP插入时自动写当前时间更新时自动刷新这是审计和定位数据问题的基础设施没有它排查线上脏数据会非常痛苦。索引设计这块idx_status_created是联合索引对应“按状态查创建时间排序的用户列表”这类高频场景。注意联合索引的列顺序是有讲究的最左前缀原则下前面的列要放区分度高的、等值查询条件优先的列。status区分度不高但因为查询通常固定带status条件所以放前面不会浪费后面的created_at用来排序或范围查询。2.3 改表结构的ALTER TABLE操作要点建表之后再改表是常态业务逻辑变了字段就得跟着变。常见的操作-- 添加字段 ALTER TABLE users ADD COLUMN avatar_url VARCHAR(255) DEFAULT NULL COMMENT 头像地址; -- 修改字段类型 ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 修改字段名注意类型和约束要重新写全 ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20) DEFAULT NULL; -- 删除字段 ALTER TABLE users DROP COLUMN avatar_url; -- 添加索引 ALTER TABLE users ADD INDEX idx_email (email); -- 删除索引 ALTER TABLE users DROP INDEX idx_email;这些语法本身不难难的是对大表做结构变更的时机。公司的核心表动辄上千万行直接执行ALTER TABLE会锁表导致线上写入全部阻塞这种事故我踩过一次之后形成了条件反射改表前先看数据量超过百万行的表做结构变更必须走在线DDL工具或者至少评估好低峰期窗口别在上班时间手一抖就执行。另一个容易忽略的点是CHANGE COLUMN修改字段名时如果不把类型、约束、注释完整重现一遍原来字段的属性会全部丢失。很多人只写了新列名结果发现默认值、注释全没了等于静默改了表结构。3. 数据操纵与查询把各类SQL真正跑起来3.1 INSERT插入数据单条、批量、冲突处理数据插入是最基础的操作但写法不同效率和安全性差别很大。先看几种常用形式-- 单条插入 INSERT INTO users (username, email, phone, status) VALUES (zhangsan, zhangsanexample.com, 13800000000, 1); -- 批量插入推荐 INSERT INTO users (username, email, phone, status) VALUES (lisi, lisiexample.com, 13800000001, 1), (wangwu, wangwuexample.com, 13800000002, 1); -- 冲突时更新ON DUPLICATE KEY UPDATE INSERT INTO users (username, email, status) VALUES (zhangsan, zhangsanexample.com, 1) ON DUPLICATE KEY UPDATE last_login_at NOW();批量插入的效率远高于循环单条插入。原因在于每一条INSERT都是一次事务都要走一次SQL解析、权限校验、事务提交的完整流程批量插入把这些开销压缩到了一次。实测向一张空表插入一万条数据单条循环可能要好几秒批量插入几十毫秒就能完成。ON DUPLICATE KEY UPDATE是非常实用的语法它依赖于唯一索引或主键冲突来触发更新。典型场景是登录状态记录用户每天登录都写一条记录如果已经有今天的记录就只更新登录时间一条SQL搞定“没有就插入、有就更新”的逻辑不用先查一遍再决定是INSERT还是UPDATE。3.2 UPDATE和DELETE带条件的修改与删除修改和删除都离不开WHERE条件但永远要记住UPDATE和DELETE的WHERE可以省略省略意味着全表操作。最稳妥的习惯是写UPDATE之前先写一条相同WHERE条件的SELECT确认范围再改成UPDATE执行。这不是能力问题是纪律问题。-- 安全的更新 UPDATE users SET status 0 WHERE id 123; -- 更新时带上版本号/时间条件避免覆盖并发修改 UPDATE users SET balance balance - 200.00 WHERE id 123 AND balance 200.00;上面第二个例子是一个经典的防超扣写法。余额扣减在并发场景下如果先SELECT余额再判断够不够再UPDATE中间就有竞态窗口。把判断条件写进WHERE里balance 200.00不满足的话更新影响行数为0逻辑上天然串行化既简洁又安全。DELETE同理有条件就放心删没条件的时候想清楚到底要做什么。如果只是想清空表数据DELETE FROM和TRUNCATE TABLE的选择也不同TRUNCATE是DDL操作直接重建表速度快且不逐行走事务日志但无法按条件删除且会重置自增IDDELETE是DML操作可以带条件、可以配合事务回滚。考虑清楚业务需求再选。3.3 SELECT查询从简单到复杂的实际场景查询是SQL里最灵活的部分也是面试和日常开发中拉开差距的地方。我从单表到多表逐层展开。单表查询的基本结构-- 条件过滤 排序 分页 SELECT id, username, email, created_at FROM users WHERE status 1 AND created_at 2024-01-01 ORDER BY created_at DESC LIMIT 20 OFFSET 0;这个写法里有两个点值得说。一个是WHERE的字段顺序SQL优化器会自己调整但原理上要理解索引能否命中取决于你用的是哪个字段作为过滤条件。另一个是LIMIT深分页问题LIMIT 100000, 20这种写法MySQL会先把前面十万行全查出来再丢掉越翻越慢。更优雅的替代方案是用游标式翻页-- 基于上一页最后一条记录的id翻页 SELECT id, username, email, created_at FROM users WHERE status 1 AND id 100020 ORDER BY id LIMIT 20;这个写法在数据量大时性能差距是数量级的。聚合查询用于统计SELECT status, COUNT(*) AS user_count, SUM(balance) AS total_balance, AVG(balance) AS avg_balance FROM users GROUP BY status HAVING COUNT(*) 100;注意GROUP BY之后的条件过滤用HAVING而不是WHERE语义上WHERE是先过滤再分组HAVING是先分组再过滤两者执行顺序完全不同。COUNT()和COUNT(字段)也有区别COUNT()统计行数不会跳过NULLCOUNT(字段)只统计字段值不为NULL的行。统计用户数量时混用这两个结果可能对不上。多表连接是SQL进阶的必经之路-- 内连接只返回两表匹配的行 SELECT u.username, o.order_no, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id WHERE o.amount 100; -- 左连接左表全部行保留右表没有匹配的为NULL SELECT u.username, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id;内连接解决的是“两边都存在”的查询比如查下了单的用户左连接解决的是“左边为主、右边可有可无”比如查所有用户包括从未下单的用户。理解这个语义差别比背语法重要得多。子查询在MySQL 8.0里性能已经优化得不错但能用JOIN表达的场景优先用JOIN。一个经验法则是能写成JOIN的不要写子查询能写一条SQL的不要写两条不到万不得已不用SELECT *。去重的两种姿势-- 方式一DISTINCT SELECT DISTINCT status FROM users; -- 方式二GROUP BY SELECT status FROM users GROUP BY status;两个结果一样但语义不同DISTINCT是“对结果去重”GROUP BY是“按组聚合”。如果只需要去重不涉及统计两个都可以如果还要统计其他数据GROUP BY是唯一选择。DISTINCT在多列场景下是对多列的联合去重别只盯着一列看。模糊查询LIKE有个性能陷阱-- 前缀匹配能用到索引 WHERE username LIKE zhang%; -- 模糊匹配无法使用索引 WHERE username LIKE %zhang%;%zhang%中间带通配符的写法索引直接失效从头扫到尾。业务如果确实需要中间匹配考虑全文索引或搜索引擎兜底而不是硬扛SQL。4. 事务、视图与存储过程进阶SQL的实用补充4.1 事务保证多步操作原子性事务是数据库区别于文件系统最重要的能力之一。典型的转账场景A账户扣100B账户加100两步必须同时成功或同时失败。在MySQL里这样写START TRANSACTION; UPDATE accounts SET balance balance - 100.00 WHERE id 1; UPDATE accounts SET balance balance 100.00 WHERE id 2; -- 检查以上两步影响行数确认无误 COMMIT; -- 有任何一步异常 ROLLBACK;事务的ACID特性原子性、一致性、隔离性、持久性是它的理论基础。实际开发中默认autocommit是开的每条语句自动提交。当你需要多语句的原子性时显式写START TRANSACTION并把COMMIT延后到所有操作成功再执行。隔离级别决定了一个事务能看到另一个事务的哪些未提交数据。MySQL默认是REPEATABLE READ也就是可重复读事务期间多次读取同一条数据结果一致。修改隔离级别的语法是SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;隔离级别越高并发能力越差级别越低数据一致性风险越大。多数业务系统用默认级别就够了不需要在这个层面过度设计。4.2 视图把复杂查询封装成“虚拟表”视图本质上是一条保存下来的SELECT语句不存数据每次查询时动态执行。它的价值在于复用和安全。CREATE VIEW v_user_order_summary AS SELECT u.id, u.username, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_amount FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.username;创建之后就能当表一样查SELECT * FROM v_user_order_summary WHERE total_amount 1000;。好处是业务层不需要知道底层表结构只面对一个语义清晰的视图。另一个用途是权限控制给低权限角色授权访问视图而不是底层表让视图只暴露必要的字段。4.3 存储过程什么时候值得用存储过程是一堆SQL语句打包成一个可调用的单元。先看一个简单示例DELIMITER // CREATE PROCEDURE sp_create_user_with_order( IN p_username VARCHAR(64), IN p_email VARCHAR(128), IN p_order_amount DECIMAL(10,2) ) BEGIN DECLARE v_user_id BIGINT; INSERT INTO users (username, email, status) VALUES (p_username, p_email, 1); SET v_user_id LAST_INSERT_ID(); INSERT INTO orders (user_id, amount) VALUES (v_user_id, p_order_amount); END // DELIMITER ;调用方式CALL sp_create_user_with_order(test, testexample.com, 99.00);这里用到了LAST_INSERT_ID()来获取刚插入的自增主键是存储过程里最常见的写法。但我的实际建议是能用应用层代码完成多步操作的不要轻易写存储过程。存储过程的劣势很明显版本控制不方便、调试困难、数据库压力上移、不同数据库迁移成本高。真正适合的场景是批处理任务、复杂的报表计算、以及对事务一致性要求极高的内部流程。一句话就是“具体问题具体分析”别为了用存储过程而用存储过程。5. 常见问题与排查技巧实录5.1 连接和初始化阶段的报错ERROR 1045 (28000): Access denied for user rootlocalhost——这个报错几乎每个新手都见过。原因大多是密码错误或root账号的host限制。解决思路是对应着检查如果确实忘了密码MySQL 8.0可以跳过授权表启动来重置如果只是host不对用root%这种通配就不该写进生产环境。线上账号原则上是主机白名单不是通配符。ERROR 2026 (HY000): SSL connection error——这是热词里出现过的典型报错。MySQL 8.0默认开启SSL要求客户端和服务器TLS版本不匹配或校验失败就会出现。日常连本地开发库时在连接参数里显式禁用SSL即可命令行加--ssl-modeDISABLEDJDBC加useSSLfalseallowPublicKeyRetrievaltrue。生产环境当然要启用SSL但本地开发别让TLS挡了调试的路。Table xxx doesnt exist——先别急着认为是建表失败优先确认数据库选没选对。这个错误经常是忘了执行USE database或者连到了另一个库上。执行SHOW TABLES;看看当前库里到底有哪些表比反复看SQL语句更快。5.2 SQL执行中的字符集与默认值问题中文显示乱码或变成???——大概率是连接层字符集的问题。检查链路库字符集 → 表字符集 → 字段字符集 → 连接字符集四级任何一个不一致都乱。MySQL 8.0的SET NAMES utf8mb4;可以一次性设置客户端、连接、返回结果的字符集开发时先执行这句能排除掉大部分乱码因素。ERROR 1364: Field xxx doesnt have a default value——这个报错在MySQL 5.7和8.0有严格模式的版本里常见。原因是不允许插入没有默认值且未填写的NOT NULL字段。解决方法有两个方向要么建表时给字段显式指定DEFAULT要么在插入语句里补齐该字段。麻烦的地方在于线上已经存在的表严格模式变了老代码没跟上这就需要在应用层把这个字段的插入逻辑补完善而不是去关掉sql_mode——关闭严格模式是给自己埋雷数据完整性和类型校验都会被破坏。Unknown column xxx in field list——列名拼写错误或用了关键字。MySQL对大小写敏感的库Linux下里列名大小写写错也一样报这个错。建议写SQL时让字段名和建表语句保持一致统一小写加下划线能避免绝大多数这种问题。5.3 数据查询慢的排查思路SQL变慢先不要盲目加索引按顺序排查效果更好。第一件事是看能不能用EXPLAIN排查执行计划EXPLAIN SELECT * FROM users WHERE username zhangsan;重点看type字段const、eq_ref、ref是命中索引的优秀级别ALL表示全表扫描说明索引没生效。还有个关键指标是rows估算扫描行数能看出数据访问量的量级。常见索引失效场景我整理成了速查表场景原因解决方式WHERE中使用函数WHERE YEAR(created_at) 2024改写为范围条件隐式类型转换VARCHAR列对数值查询保证条件类型一致前导通配符LIKE %abc%改为前缀匹配或全文索引联合索引跳过前列只查第二个索引列调整索引顺序或另建索引OR关联非索引列a1 OR b2拆分为两条SQL用UNION慢SQL优化的核心思路是减少扫描行数而不是盲目加索引。索引是空间换时间的手段每个索引都会拖慢写入速度所以索引数量宁缺毋滥。5.4 一个安全习惯SQL注入的防范只要有SQL拼接的地方就有注入风险用户在文本框输入 OR 11 --这种内容如果直接拼进SQL条件就恒真了整张表的数据都能被拖出来。根本解法是参数化查询让SQL结构和数据分离。Java的PreparedStatement、Python的%s占位符、Go的?占位符这些方式都强制了数据和代码之间的边界。# 错误的拼接方式 sql SELECT * FROM users WHERE username username # 正确的参数化方式 cursor.execute(SELECT * FROM users WHERE username %s, (username,))除了应用层参数化数据库账号权限也得按最小化原则分配业务账号只授它能用得到的库表权限不轻易给GRANT ALL。密码用强密码并定期轮换生产库的root账号更是要谨慎使用。5.5 几个写在最后的小习惯操作生产库之前建议先开启一个只读事务或者用EXPLAIN确认影响行数UPDATE和DELETE前先跑同条件SELECT。新环境初始化时检查下全局变量SHOW VARIABLES LIKE sql_mode;和SHOW VARIABLES LIKE character_set%;看一眼只要几秒钟能省掉后面一晚上的排查时间。备份这事不要等到出事才做mysqldump定时任务加上binlog日志一起配合才能保证数据安全。写到这里想起之前整理这批笔记时的感受MySQL的入门门槛并不高CREATE TABLE的语法十几分钟就能背下来真正分出高下的是对这个数据库运行机制的理解——字符集怎么流转、索引为什么失效、事务在并发下会怎样表现。这些经验没有捷径只能在一次次建表、改表、优化慢查询的过程里慢慢沉淀。希望这篇笔记能让你少踩几个我已经踩过的坑快速把建库和SQL操作这件事吃透。