简介本资源是B站知名编程讲师Mosh Hamedani《SQL三小时入门》课程的系统化学习笔记面向数据库初学者、转行新人及需快速掌握SQL核心语法的开发人员聚焦关系型数据库查询能力构建。笔记以PDF格式呈现共1个文件2.43MB内容高度凝练覆盖SQL基础概念、SELECT查询、WHERE条件筛选、AND/OR/NOT逻辑运算以及IN、BETWEEN、LIKE、REGEXP等关键操作符的语法、示例与使用场景还包含IS NULL、ORDER BY、LIMIT、各类JOIN及UNION等实用模块结构清晰、术语准确适合作为随查随用的速记手册与课后复盘依据。目前已有976人学习下载笔记源自Mosh官方YouTube教程7S_tz1z_5bA及配套Cheat Sheet语言简明、排版规范省去视频回看耗时助力高效掌握SQL核心查询技能。1. 这不是速成口诀是能直接贴进你 SQL 脚本里的实战备忘录Mosh 的三小时课程笔记为什么值得你花 20 分钟重读一遍你有没有过这种经历刚在 B 站刷完 Mosh 老师那节被标榜为「SQL 三小时速通」的视频关掉页面后打开自己的数据库敲SELECT * FROM users;都下意识加了个分号——结果报错不是 MySQL是 PostgreSQL不是本地测试库是公司生产环境里那个连LIMIT都要写成FETCH FIRST 3 ROWS ONLY的老系统。这节被千万人点开、收藏、又悄悄遗忘的课程真正值钱的从来不是“三小时”这个时间标签而是它把 SQL 从语法说明书还原成了工程师日常调试时手边那张油渍斑斑的 Cheat SheetWHERE state IN (VA, NY, CA)后面没写注释但你一眼就懂这是在筛高价值客户池REGEXP ^my|se不是炫技是某次清洗脏数据时批量捞出所有以 my 开头或含 se 的用户名的真实命令。它不教你怎么设计范式也不讲事务隔离级别但它确保你在接到「查下上月注册且积分超 1000 的用户」需求时5 秒内写出可跑通的第一版语句。适合谁刚转行的数据分析新人、需要临时补数据库能力的前端/测试、还有那些常年用 ORM 却某天被 DBA 叫去改慢查询的后端——你们缺的不是理论是一份能立刻抄、敢直接改、改完就能验证的“最小可用 SQL 手册”。2. 从SELECT *到精准取数为什么你的 WHERE 条件总在测试环境跑通上线就翻车2.1 SELECT 不只是“选所有”它是数据流的第一道闸门Mosh 在笔记开头就强调SELECT * FROM customers;是教学起点但生产环境里它是性能杀手。真实项目中我见过因SELECT *拉取了包含TEXT类型简历字段的整张用户表导致接口响应从 80ms 暴涨到 2.3s 的案例。正确姿势是明确投影列-- ✅ 推荐只取业务必需字段显式声明避免隐式类型转换风险 SELECT customer_id, first_name, last_name, email, points FROM customers WHERE state CA; -- ❌ 风险若 customers 表后续新增了 BLOB 字段如用户头像二进制此语句会无感知拖慢全链路 SELECT * FROM customers WHERE state CA;提示SELECT后的字段顺序直接影响客户端解析逻辑。比如 Python 的pandas.read_sql()默认按列序生成 DataFrame 列名若 SQL 中SELECT id, name和SELECT name, id返回结构不同下游代码可能因列索引错位而静默出错。2.2 WHERE 子句的四个隐形陷阱等号、空值、时区、大小写WHERE 是 SQL 的心脏但也是血泪最多的地方。Mosh 笔记里轻描淡写的state CA背后藏着至少四层现实水深等号 vs 安全等号MySQL 中允许NULL比较结果恒为UNKNOWN但 PostgreSQL 严格遵循三值逻辑。当state列允许为空时-- ✅ 安全写法显式处理 NULL兼容所有主流 RDBMS SELECT * FROM customers WHERE state CA OR state IS NULL; -- ❌ 危险写法在 PostgreSQL 中state NULL 永远返回 FALSE漏掉所有空状态记录 SELECT * FROM customers WHERE state NULL;字符串比较的隐式类型转换当points是INT类型但 WHERE 中写成points 1000MySQL 会自动转字符串再比对而 SQL Server 可能直接报错。Mosh 笔记里points 1000的写法本质是强制类型对齐的防御性编程。时区陷阱birthdate 1990-01-01看似简单但1990-01-01是无时区日期字面量。若数据库时区设为UTC8而应用层传入的是2024-01-01T00:00:00ZUTC 时间实际比对的是1990-01-01 00:00:0008vs1990-01-01 00:00:0000差 8 小时。解决方案是统一使用带时区的时间戳函数-- ✅ 显式指定时区避免隐式转换歧义 SELECT * FROM customers WHERE birthdate TIMESTAMP 1990-01-01 00:00:0000;大小写敏感性Mosh 笔记说 “SQL 不区分大小写”指的是关键字SELECT,WHERE。但字符串值是否敏感取决于列的排序规则Collation。MySQL 默认utf8mb4_0900_as_cs是大小写敏感而utf8mb4_0900_as_ci是不敏感。first_name LIKE b%在敏感排序下只会匹配小写 b 开头大写 B 会被过滤掉。生产环境必须查清排序规则-- 查看 customers 表 first_name 列的排序规则 SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME customers AND COLUMN_NAME first_name;2.3 逻辑运算符的执行优先级AND/OR/NOT 组合时括号不是可选项Mosh 笔记中WHERE birthdate 1990-01-01 AND points 1000看似直白但一旦加入OR优先级就成雷区。SQL 标准规定NOT AND OR但人类直觉常误判。例如-- ❌ 危险本意是“1990年后出生 OR 积分超1000”但实际执行为 “(1990年后出生 AND 积分超1000) OR 积分超2000” SELECT * FROM customers WHERE birthdate 1990-01-01 AND points 1000 OR points 2000; -- ✅ 必须加括号让意图 100% 显性化 SELECT * FROM customers WHERE (birthdate 1990-01-01 AND points 1000) OR points 2000;血泪经验我在某电商促销系统中修复过一个经典 Bug——活动规则配置表里存着is_vip 1 OR points 5000但开发人员忘了加括号导致is_vip 0 AND points 5000的用户也被错误发放了优惠券。上线前用EXPLAIN看执行计划发现OR导致索引失效最终强制拆成UNION优化。3. 模糊匹配的三种武器LIKE、REGEXP、BETWEEN何时该用哪一把3.1 LIKE简单模式匹配的黄金法则与性能红线Mosh 笔记中first_name LIKE b%是入门必学但它的性能代价常被低估。%在开头%b或中间%b%会导致索引失效因为 B-Tree 索引只能高效支持左前缀匹配。-- ✅ 高效利用索引扫描范围可控 SELECT * FROM customers WHERE first_name LIKE b%; -- ❌ 低效全表扫描大数据量时秒变卡顿 SELECT * FROM customers WHERE first_name LIKE %b; -- ⚠️ 折中方案若必须查后缀可建反向索引MySQL 5.7 CREATE INDEX idx_first_name_reverse ON customers ((REVERSE(first_name))); SELECT * FROM customers WHERE REVERSE(first_name) LIKE REVERSE(b%);参数说明_匹配单字符%匹配零或多个字符。注意ESCAPE子句处理转义字符-- 查询 first_name 中含下划线的用户_ 是通配符需转义 SELECT * FROM customers WHERE first_name LIKE %\_% ESCAPE \;3.2 REGEXP正则表达式的威力与跨数据库兼容性黑洞Mosh 笔记里REGEXP ^a展示了正则的简洁但REGEXP是 MySQL 特有语法PostgreSQL 用~SQL Server 用LIKE模拟或 CLR 函数SQLite 需加载扩展。跨数据库项目必须规避REGEXP。-- ✅ 兼容写法用标准 SQL 的 LIKE 模拟基础正则 -- 目标first_name 以 my 开头 或 包含 se -- MySQL REGEXP ^my|se 的等价标准写法 SELECT * FROM customers WHERE first_name LIKE my% OR first_name LIKE %se%; -- ✅ PostgreSQL 原生写法非标准但更强大 SELECT * FROM customers WHERE first_name ~ ^my|se; -- ❌ 绝对禁止在需要部署到多数据库的项目中使用 REGEXP -- 因为一旦换库整条 SQL 报错且无降级路径。避坑 / 常见问题 / 排查现象 1WHERE first_name REGEXP b[ru]在 MySQL 8.0 中返回空但在 5.7 中正常。原因MySQL 8.0 默认启用utf8mb4_0900_as_cs排序规则正则引擎对 Unicode 处理更严格[ru]可能因字符集边界匹配失败。解决显式指定字符集COLLATE utf8mb4_0900_as_cs或降级为LIKE组合。现象 2REGEXP ey$|on$在某些数据中漏匹配EY大写。原因正则默认大小写敏感而LIKE受列排序规则影响。若列是_cicase-insensitiveLIKE不敏感但REGEXP仍敏感。解决添加(?i)标志REGEXP (?i)ey$|on$。现象 3REGEXP查询响应时间从 50ms 暴涨到 3s。原因正则引擎无法利用索引且复杂模式如嵌套*触发回溯爆炸Catastrophic Backtracking。解决用EXPLAIN FORMATJSON查看是否type: ALL全表扫描将正则逻辑前置到应用层过滤或建生成列索引MySQL 5.7ALTER TABLE customers ADD COLUMN first_name_lower VARCHAR(50) GENERATED ALWAYS AS (LOWER(first_name)) STORED; CREATE INDEX idx_first_name_lower ON customers (first_name_lower); SELECT * FROM customers WHERE first_name_lower REGEXP ^(my|se);3.3 BETWEEN闭区间陷阱与日期边界精确控制BETWEEN 100 AND 200看似安全但BETWEEN是闭区间包含两端且对日期类型极易踩坑。Mosh 笔记中points BETWEEN 100 AND 200没问题但换成时间-- ❌ 危险本意查 2024-01-01 全天数据但 BETWEEN 包含 2024-01-01 00:00:00 和 2024-01-01 23:59:59.999999 -- 若订单时间精度为微秒2024-01-01 23:59:59.999999910位会被漏掉 SELECT * FROM orders WHERE order_date BETWEEN 2024-01-01 AND 2024-01-01; -- ✅ 推荐用半开区间精确到纳秒级无歧义 SELECT * FROM orders WHERE order_date 2024-01-01 AND order_date 2024-01-02;参数说明BETWEEN仅适用于连续值域数字、日期、字符串字典序。对字符串WHERE name BETWEEN Alice AND Bob会包含Alice到Bob字典序之间的所有值但Bob本身是否包含取决于数据库对字符串末尾空格的处理ANSI SQL 标准要求忽略尾部空格。4. JOIN 的五种形态从 INNER 到 CROSS一张表关联的底层逻辑拆解4.1 INNER JOIN数据交集的严格契约不是“默认选择”Mosh 笔记中JOIN orders o ON c.customer_id o.customer_id是最简写法但JOIN默认即INNER JOIN。新手常误以为“只要关联就用 JOIN”却不知它会主动丢弃无订单的客户。某次我帮某高校实验室做学生选课分析原始 SQL 写SELECT s.student_id, s.name, c.course_name FROM students s JOIN courses c ON s.major_id c.department_id;结果只返回了有专业归属的学生和对应课程而major_id为 NULL 的新生尚未分专业全部消失。修正为LEFT JOIN后才看到 237 名新生的course_name为 NULL这才是真实数据分布。-- ✅ LEFT JOIN保留左表全部右表无匹配则补 NULL SELECT s.student_id, s.name, c.course_name FROM students s LEFT JOIN courses c ON s.major_id c.department_id; -- ✅ RIGHT JOIN同理但极少用可读性差建议统一用 LEFT 表序调整 -- ❌ 避免无 ON 条件的 JOIN即笛卡尔积除非真需要全组合4.2 USING 子句当列名完全相同时的语法糖与隐式约束Mosh 笔记强调USING (customer_id)比ON c.customer_id o.customer_id更简洁。但USING有硬性前提两表中用于连接的列名必须完全相同且数据类型兼容。若customers表是customer_id INTorders表是customer_id BIGINTMySQL 会隐式转换但 PostgreSQL 可能报错。-- ✅ 安全列名、类型均一致 SELECT * FROM customers c JOIN orders o USING (customer_id); -- ❌ 危险若 orders 表列名为 order_customer_id则 USING 无效必须用 ON -- ✅ 替代显式 ON类型转换可控 SELECT * FROM customers c JOIN orders o ON c.customer_id CAST(o.order_customer_id AS SIGNED);注意USING会合并同名列结果集中customer_id只出现一次。而ON写法中c.customer_id和o.customer_id作为两个独立列存在。若后续要GROUP BY customer_idUSING写法更干净若需分别引用两表 ID 做计算ON更灵活。4.3 CROSS JOIN笛卡尔积的正当用途与性能核弹Mosh 笔记中CROSS JOIN sizes用于“组合所有颜色与尺寸”这是 CROSS JOIN 的典型场景——生成全排列。但它的危险在于若colors有 10 行sizes有 5 行结果就是 50 行若两表各 10 万行结果 100 亿行瞬间 OOM。-- ✅ 正当用途生成测试数据、枚举配置、计算组合权重 INSERT INTO product_variants (color_id, size_id, sku_code) SELECT c.color_id, s.size_id, CONCAT(c.code, -, s.code) FROM colors c CROSS JOIN sizes s; -- ❌ 绝对禁止无 WHERE 过滤的 CROSS JOIN 关联业务主表 -- 如CROSS JOIN users u WITH orders o —— 千万级用户 × 百万级订单 百万亿行DB 直接宕机避坑 / 常见问题 / 排查现象 1CROSS JOIN查询执行超时EXPLAIN显示rows: 1000000000。原因未加任何过滤条件且表数据量预估错误。解决立即加LIMIT 100测试结果集大小确认是否真需全组合否则改用INNER JOIN 关联条件。现象 2CROSS JOIN结果中出现大量重复 SKU。原因colors或sizes表自身存在重复主键如未设PRIMARY KEY导致组合爆炸。解决先SELECT COUNT(*) FROM colors; SELECT COUNT(DISTINCT color_id) FROM colors;检查去重率修复源表主键约束。现象 3CROSS JOIN在 MySQL 5.7 中快在 8.0 中慢 10 倍。原因MySQL 8.0 优化器对CROSS JOIN的物化策略变更可能触发临时表磁盘写入。解决强制使用内存临时表SET SESSION tmp_table_size 268435456;256MB或改用应用层双循环生成。5. 数据写入的确定性原则INSERT 的单行、多行、DEFAULT 与 NULL 的精确控制5.1 INSERT VALUES显式字段列表是生产环境的铁律Mosh 笔记中INSERT INTO customers(first_name, phone, points) VALUES (Mosh, NULL, DEFAULT)是规范写法。但新手常犯的错是省略字段列表直接INSERT INTO customers VALUES (...)。这在表结构稳定时可行但一旦customers表新增一列如created_at DATETIME DEFAULT CURRENT_TIMESTAMP旧脚本就会因值数量不匹配而报错。-- ✅ 铁律永远显式声明插入字段与 VALUES 一一对应 INSERT INTO customers (first_name, last_name, email, points, phone) VALUES (Mosh, Hamedani, moshcodewithmosh.com, 1000, NULL); -- ❌ 危险依赖表结构顺序脆弱且不可读 INSERT INTO customers VALUES (Mosh, Hamedani, moshcodewithmosh.com, 1000, NULL);参数说明DEFAULT关键字表示使用该列定义的默认值如points INT DEFAULT 0而非NULL。NULL是明确赋空值DEFAULT是调用列级约束逻辑。5.2 多值 INSERT批量写入的性能分水岭Mosh 笔记中INSERT ... VALUES (...), (...), (...)是 MySQL 批量插入的标准语法。其性能远超单条INSERT循环因为减少了网络往返和事务开销。但需注意最大包限制-- ✅ 推荐每批 1000 行以内平衡内存与网络 INSERT INTO customers (first_name, email, points) VALUES (Alice, aliceexample.com, 500), (Bob, bobexample.com, 800), (Charlie, charlieexample.com, 1200); -- ❌ 危险单条 INSERT 包含 10 万行触发 max_allowed_packet 限制默认 4MB -- 解决拆分为多批或调大参数需 DBA 权限 -- SET GLOBAL max_allowed_packet 64*1024*1024; -- 64MB5.3 NULL 与 DEFAULT 的语义鸿沟何时该用哪个Mosh 笔记中phone列插入NULLpoints列用DEFAULT这揭示了核心设计哲学NULL 表示“未知”或“不适用”DEFAULT 表示“采用系统预设值”。例如phone为NULL用户未填写手机号信息缺失points为DEFAULT用户注册时未获初始积分按规则给 0假设points INT DEFAULT 0。-- ✅ 语义清晰NULL 表示无电话DEFAULT 表示按规则给 0 分 INSERT INTO customers (first_name, phone, points) VALUES (Mosh, NULL, DEFAULT); -- ❌ 语义混淆用 0 代替 NULL无法区分“用户填了 0”和“用户没填” INSERT INTO customers (first_name, phone, points) VALUES (Mosh, 0, 0); -- phone0 是有效号码吗points0 是初始值还是扣减后避坑 / 常见问题 / 排查现象 1INSERT ... VALUES (Mosh, NULL, DEFAULT)报错 “Field points doesnt have a default value”。原因points列未定义DEFAULT约束或定义为NOT NULL且无默认值。解决检查表结构SHOW CREATE TABLE customers;添加默认值ALTER TABLE customers MODIFY points INT DEFAULT 0;。现象 2INSERT后SELECT发现phone列是空字符串而非NULL。原因应用层代码将空输入转为插入而和NULL在 SQL 中完全不同是字符串NULL是空值。解决在应用层校验空字符串统一转为NULL或数据库层用TRIGGER自动转换。现象 3DEFAULT插入后points值为NULL而非预期的0。原因DEFAULT仅在INSERT语句中显式使用时生效若列定义为points INT DEFAULT 0 NOT NULL但INSERT中写了points NULL则NULL会覆盖DEFAULT因NOT NULL约束会报错故实际是NULL被静默转为0。解决始终用DEFAULT关键字避免NULL字面量赋值给有默认值的列。6. 从笔记到生产如何用这份 Cheat Sheet 构建你的 SQL 防御性编程习惯6.1 建立“三查一测”工作流每次写 SQL 前的强制动作Mosh 的笔记不是让你背下来而是给你一套肌肉记忆。我给自己定的铁规是任何一条发往数据库的 SQL必须经过“三查一测”——这不是流程是保命线。查一查字段来源在SELECT后逐个确认每个字段是否真的来自目标表而非拼写错误的别名。曾有个线上事故SELECT user_id, user_name FROM ordersorders 表无user_name因 MySQL 的STRICT_TRANS_TABLES未开启它静默返回NULL导致下游报表用户姓名全为空。现在我的编辑器里装了 SQLLint 插件SELECT后自动高亮不存在的列。查二查 WHERE 边界对所有WHERE条件问三个问题这个字段是否允许NULL如果允许是否会漏掉NULL记录→ 改用IS NULL或COALESCE。这个值是用户输入吗如果是是否做了ESCAPE防注入→LIKE ? ESCAPE \\参数化绑定。这个条件会走索引吗→ 用EXPLAIN看key和rowsrows 10000就得优化。查三查 JOIN 类型看到JOIN立刻停顿 2 秒我要的是交集INNER、左表全集LEFT、还是全外连接FULL OUTER绝不容忍“先写JOIN后面再想”。某次我写JOIN关联用户和地址本意是查所有用户及其地址LEFT却写了JOIN导致 37% 的用户地址为空被过滤报表 GMV 少算 200 万。一测测 LIMIT 1任何SELECT语句上线前必须加LIMIT 1执行一次确认返回列数、类型、NULL 性是否符合预期EXPLAIN显示的type是const/ref好不是ALL坏响应时间 100ms慢查询阈值。这一步耗时 3 秒但能拦截 80% 的低级错误。6.2 构建你的个人 SQL 模板库从 Cheat Sheet 到可复用代码块Mosh 的笔记是种子你要把它长成自己的树。我维护一个sql-templates.md里面全是带注释的、可直接复制粘贴的片段### 【安全 SELECT】带字段检查与 LIMIT 的模板 sql -- [用途] 查询用户基本信息确保字段存在且索引有效 -- [检查] 1. customers 表是否有 first_name, email, points 列 2. state 列有索引 -- [执行] 先 LIMIT 1 测试再删 LIMIT 上线 SELECT customer_id, first_name, email, points FROM customers WHERE state CA AND points 1000 ORDER BY points DESC LIMIT 100;【防 NULL WHERE】处理可能为空的字段-- [用途] 查询有手机号的用户同时兼容 phone 为 NULL 或空字符串 -- [检查] phone 列类型是 VARCHAR是否允许 NULL SELECT * FROM customers WHERE COALESCE(TRIM(phone), ) ! ;【跨库 INSERT】兼容 MySQL/PostgreSQL 的批量插入-- [用途] 批量插入用户避免 REGEXP 和方言差异 -- [注意] PostgreSQL 用 $1, $2 占位符MySQL 用 ?此处用通用 ? 占位 INSERT INTO customers (first_name, email, points) VALUES (?, ?, ?); **血泪教训**三年前我因在 WHERE 中用了 REGEXP导致项目从 MySQL 迁移到 PostgreSQL 时27 个 SQL 文件全部报错加班 36 小时手动替换。从那以后我每次写 SQL 都强制问自己“这段代码能在 PostgreSQL、SQL Server、SQLite 上跑通吗” 如果答案是否定的立刻重构为标准 SQL 或封装到应用层。希望帮到你。 p a hrefhttps://download.csdn.net/download/weixin_55804389/77552081 stylecolor:#ec7500;font-size:14px; 本文还有配套的精品资源点击获取 /a img altmenu-r.4af5f7ec.gif srchttps://csdnimg.cn/release/wenkucmsfe/public/img/menu-r.4af5f7ec.gif stylewidth:16px;margin-left:4px;vertical-align:text-bottom;cursor:text; /p 
长河君 · 2026/10/13 23:29:56 · 阅读 1.2k SQL防错实战手册：WHERE陷阱、JOIN逻辑与跨库兼容写法
简介本资源是B站知名编程讲师Mosh Hamedani《SQL三小时入门》课程的系统化学习笔记面向数据库初学者、转行新人及需快速掌握SQL核心语法的开发人员聚焦关系型数据库查询能力构建。笔记以PDF格式呈现共1个文件2.43MB内容高度凝练覆盖SQL基础概念、SELECT查询、WHERE条件筛选、AND/OR/NOT逻辑运算以及IN、BETWEEN、LIKE、REGEXP等关键操作符的语法、示例与使用场景还包含IS NULL、ORDER BY、LIMIT、各类JOIN及UNION等实用模块结构清晰、术语准确适合作为随查随用的速记手册与课后复盘依据。目前已有976人学习下载笔记源自Mosh官方YouTube教程7S_tz1z_5bA及配套Cheat Sheet语言简明、排版规范省去视频回看耗时助力高效掌握SQL核心查询技能。1. 这不是速成口诀是能直接贴进你 SQL 脚本里的实战备忘录Mosh 的三小时课程笔记为什么值得你花 20 分钟重读一遍你有没有过这种经历刚在 B 站刷完 Mosh 老师那节被标榜为「SQL 三小时速通」的视频关掉页面后打开自己的数据库敲SELECT * FROM users;都下意识加了个分号——结果报错不是 MySQL是 PostgreSQL不是本地测试库是公司生产环境里那个连LIMIT都要写成FETCH FIRST 3 ROWS ONLY的老系统。这节被千万人点开、收藏、又悄悄遗忘的课程真正值钱的从来不是“三小时”这个时间标签而是它把 SQL 从语法说明书还原成了工程师日常调试时手边那张油渍斑斑的 Cheat SheetWHERE state IN (VA, NY, CA)后面没写注释但你一眼就懂这是在筛高价值客户池REGEXP ^my|se不是炫技是某次清洗脏数据时批量捞出所有以 my 开头或含 se 的用户名的真实命令。它不教你怎么设计范式也不讲事务隔离级别但它确保你在接到「查下上月注册且积分超 1000 的用户」需求时5 秒内写出可跑通的第一版语句。适合谁刚转行的数据分析新人、需要临时补数据库能力的前端/测试、还有那些常年用 ORM 却某天被 DBA 叫去改慢查询的后端——你们缺的不是理论是一份能立刻抄、敢直接改、改完就能验证的“最小可用 SQL 手册”。2. 从SELECT *到精准取数为什么你的 WHERE 条件总在测试环境跑通上线就翻车2.1 SELECT 不只是“选所有”它是数据流的第一道闸门Mosh 在笔记开头就强调SELECT * FROM customers;是教学起点但生产环境里它是性能杀手。真实项目中我见过因SELECT *拉取了包含TEXT类型简历字段的整张用户表导致接口响应从 80ms 暴涨到 2.3s 的案例。正确姿势是明确投影列-- ✅ 推荐只取业务必需字段显式声明避免隐式类型转换风险 SELECT customer_id, first_name, last_name, email, points FROM customers WHERE state CA; -- ❌ 风险若 customers 表后续新增了 BLOB 字段如用户头像二进制此语句会无感知拖慢全链路 SELECT * FROM customers WHERE state CA;提示SELECT后的字段顺序直接影响客户端解析逻辑。比如 Python 的pandas.read_sql()默认按列序生成 DataFrame 列名若 SQL 中SELECT id, name和SELECT name, id返回结构不同下游代码可能因列索引错位而静默出错。2.2 WHERE 子句的四个隐形陷阱等号、空值、时区、大小写WHERE 是 SQL 的心脏但也是血泪最多的地方。Mosh 笔记里轻描淡写的state CA背后藏着至少四层现实水深等号 vs 安全等号MySQL 中允许NULL比较结果恒为UNKNOWN但 PostgreSQL 严格遵循三值逻辑。当state列允许为空时-- ✅ 安全写法显式处理 NULL兼容所有主流 RDBMS SELECT * FROM customers WHERE state CA OR state IS NULL; -- ❌ 危险写法在 PostgreSQL 中state NULL 永远返回 FALSE漏掉所有空状态记录 SELECT * FROM customers WHERE state NULL;字符串比较的隐式类型转换当points是INT类型但 WHERE 中写成points 1000MySQL 会自动转字符串再比对而 SQL Server 可能直接报错。Mosh 笔记里points 1000的写法本质是强制类型对齐的防御性编程。时区陷阱birthdate 1990-01-01看似简单但1990-01-01是无时区日期字面量。若数据库时区设为UTC8而应用层传入的是2024-01-01T00:00:00ZUTC 时间实际比对的是1990-01-01 00:00:0008vs1990-01-01 00:00:0000差 8 小时。解决方案是统一使用带时区的时间戳函数-- ✅ 显式指定时区避免隐式转换歧义 SELECT * FROM customers WHERE birthdate TIMESTAMP 1990-01-01 00:00:0000;大小写敏感性Mosh 笔记说 “SQL 不区分大小写”指的是关键字SELECT,WHERE。但字符串值是否敏感取决于列的排序规则Collation。MySQL 默认utf8mb4_0900_as_cs是大小写敏感而utf8mb4_0900_as_ci是不敏感。first_name LIKE b%在敏感排序下只会匹配小写 b 开头大写 B 会被过滤掉。生产环境必须查清排序规则-- 查看 customers 表 first_name 列的排序规则 SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME customers AND COLUMN_NAME first_name;2.3 逻辑运算符的执行优先级AND/OR/NOT 组合时括号不是可选项Mosh 笔记中WHERE birthdate 1990-01-01 AND points 1000看似直白但一旦加入OR优先级就成雷区。SQL 标准规定NOT AND OR但人类直觉常误判。例如-- ❌ 危险本意是“1990年后出生 OR 积分超1000”但实际执行为 “(1990年后出生 AND 积分超1000) OR 积分超2000” SELECT * FROM customers WHERE birthdate 1990-01-01 AND points 1000 OR points 2000; -- ✅ 必须加括号让意图 100% 显性化 SELECT * FROM customers WHERE (birthdate 1990-01-01 AND points 1000) OR points 2000;血泪经验我在某电商促销系统中修复过一个经典 Bug——活动规则配置表里存着is_vip 1 OR points 5000但开发人员忘了加括号导致is_vip 0 AND points 5000的用户也被错误发放了优惠券。上线前用EXPLAIN看执行计划发现OR导致索引失效最终强制拆成UNION优化。3. 模糊匹配的三种武器LIKE、REGEXP、BETWEEN何时该用哪一把3.1 LIKE简单模式匹配的黄金法则与性能红线Mosh 笔记中first_name LIKE b%是入门必学但它的性能代价常被低估。%在开头%b或中间%b%会导致索引失效因为 B-Tree 索引只能高效支持左前缀匹配。-- ✅ 高效利用索引扫描范围可控 SELECT * FROM customers WHERE first_name LIKE b%; -- ❌ 低效全表扫描大数据量时秒变卡顿 SELECT * FROM customers WHERE first_name LIKE %b; -- ⚠️ 折中方案若必须查后缀可建反向索引MySQL 5.7 CREATE INDEX idx_first_name_reverse ON customers ((REVERSE(first_name))); SELECT * FROM customers WHERE REVERSE(first_name) LIKE REVERSE(b%);参数说明_匹配单字符%匹配零或多个字符。注意ESCAPE子句处理转义字符-- 查询 first_name 中含下划线的用户_ 是通配符需转义 SELECT * FROM customers WHERE first_name LIKE %\_% ESCAPE \;3.2 REGEXP正则表达式的威力与跨数据库兼容性黑洞Mosh 笔记里REGEXP ^a展示了正则的简洁但REGEXP是 MySQL 特有语法PostgreSQL 用~SQL Server 用LIKE模拟或 CLR 函数SQLite 需加载扩展。跨数据库项目必须规避REGEXP。-- ✅ 兼容写法用标准 SQL 的 LIKE 模拟基础正则 -- 目标first_name 以 my 开头 或 包含 se -- MySQL REGEXP ^my|se 的等价标准写法 SELECT * FROM customers WHERE first_name LIKE my% OR first_name LIKE %se%; -- ✅ PostgreSQL 原生写法非标准但更强大 SELECT * FROM customers WHERE first_name ~ ^my|se; -- ❌ 绝对禁止在需要部署到多数据库的项目中使用 REGEXP -- 因为一旦换库整条 SQL 报错且无降级路径。避坑 / 常见问题 / 排查现象 1WHERE first_name REGEXP b[ru]在 MySQL 8.0 中返回空但在 5.7 中正常。原因MySQL 8.0 默认启用utf8mb4_0900_as_cs排序规则正则引擎对 Unicode 处理更严格[ru]可能因字符集边界匹配失败。解决显式指定字符集COLLATE utf8mb4_0900_as_cs或降级为LIKE组合。现象 2REGEXP ey$|on$在某些数据中漏匹配EY大写。原因正则默认大小写敏感而LIKE受列排序规则影响。若列是_cicase-insensitiveLIKE不敏感但REGEXP仍敏感。解决添加(?i)标志REGEXP (?i)ey$|on$。现象 3REGEXP查询响应时间从 50ms 暴涨到 3s。原因正则引擎无法利用索引且复杂模式如嵌套*触发回溯爆炸Catastrophic Backtracking。解决用EXPLAIN FORMATJSON查看是否type: ALL全表扫描将正则逻辑前置到应用层过滤或建生成列索引MySQL 5.7ALTER TABLE customers ADD COLUMN first_name_lower VARCHAR(50) GENERATED ALWAYS AS (LOWER(first_name)) STORED; CREATE INDEX idx_first_name_lower ON customers (first_name_lower); SELECT * FROM customers WHERE first_name_lower REGEXP ^(my|se);3.3 BETWEEN闭区间陷阱与日期边界精确控制BETWEEN 100 AND 200看似安全但BETWEEN是闭区间包含两端且对日期类型极易踩坑。Mosh 笔记中points BETWEEN 100 AND 200没问题但换成时间-- ❌ 危险本意查 2024-01-01 全天数据但 BETWEEN 包含 2024-01-01 00:00:00 和 2024-01-01 23:59:59.999999 -- 若订单时间精度为微秒2024-01-01 23:59:59.999999910位会被漏掉 SELECT * FROM orders WHERE order_date BETWEEN 2024-01-01 AND 2024-01-01; -- ✅ 推荐用半开区间精确到纳秒级无歧义 SELECT * FROM orders WHERE order_date 2024-01-01 AND order_date 2024-01-02;参数说明BETWEEN仅适用于连续值域数字、日期、字符串字典序。对字符串WHERE name BETWEEN Alice AND Bob会包含Alice到Bob字典序之间的所有值但Bob本身是否包含取决于数据库对字符串末尾空格的处理ANSI SQL 标准要求忽略尾部空格。4. JOIN 的五种形态从 INNER 到 CROSS一张表关联的底层逻辑拆解4.1 INNER JOIN数据交集的严格契约不是“默认选择”Mosh 笔记中JOIN orders o ON c.customer_id o.customer_id是最简写法但JOIN默认即INNER JOIN。新手常误以为“只要关联就用 JOIN”却不知它会主动丢弃无订单的客户。某次我帮某高校实验室做学生选课分析原始 SQL 写SELECT s.student_id, s.name, c.course_name FROM students s JOIN courses c ON s.major_id c.department_id;结果只返回了有专业归属的学生和对应课程而major_id为 NULL 的新生尚未分专业全部消失。修正为LEFT JOIN后才看到 237 名新生的course_name为 NULL这才是真实数据分布。-- ✅ LEFT JOIN保留左表全部右表无匹配则补 NULL SELECT s.student_id, s.name, c.course_name FROM students s LEFT JOIN courses c ON s.major_id c.department_id; -- ✅ RIGHT JOIN同理但极少用可读性差建议统一用 LEFT 表序调整 -- ❌ 避免无 ON 条件的 JOIN即笛卡尔积除非真需要全组合4.2 USING 子句当列名完全相同时的语法糖与隐式约束Mosh 笔记强调USING (customer_id)比ON c.customer_id o.customer_id更简洁。但USING有硬性前提两表中用于连接的列名必须完全相同且数据类型兼容。若customers表是customer_id INTorders表是customer_id BIGINTMySQL 会隐式转换但 PostgreSQL 可能报错。-- ✅ 安全列名、类型均一致 SELECT * FROM customers c JOIN orders o USING (customer_id); -- ❌ 危险若 orders 表列名为 order_customer_id则 USING 无效必须用 ON -- ✅ 替代显式 ON类型转换可控 SELECT * FROM customers c JOIN orders o ON c.customer_id CAST(o.order_customer_id AS SIGNED);注意USING会合并同名列结果集中customer_id只出现一次。而ON写法中c.customer_id和o.customer_id作为两个独立列存在。若后续要GROUP BY customer_idUSING写法更干净若需分别引用两表 ID 做计算ON更灵活。4.3 CROSS JOIN笛卡尔积的正当用途与性能核弹Mosh 笔记中CROSS JOIN sizes用于“组合所有颜色与尺寸”这是 CROSS JOIN 的典型场景——生成全排列。但它的危险在于若colors有 10 行sizes有 5 行结果就是 50 行若两表各 10 万行结果 100 亿行瞬间 OOM。-- ✅ 正当用途生成测试数据、枚举配置、计算组合权重 INSERT INTO product_variants (color_id, size_id, sku_code) SELECT c.color_id, s.size_id, CONCAT(c.code, -, s.code) FROM colors c CROSS JOIN sizes s; -- ❌ 绝对禁止无 WHERE 过滤的 CROSS JOIN 关联业务主表 -- 如CROSS JOIN users u WITH orders o —— 千万级用户 × 百万级订单 百万亿行DB 直接宕机避坑 / 常见问题 / 排查现象 1CROSS JOIN查询执行超时EXPLAIN显示rows: 1000000000。原因未加任何过滤条件且表数据量预估错误。解决立即加LIMIT 100测试结果集大小确认是否真需全组合否则改用INNER JOIN 关联条件。现象 2CROSS JOIN结果中出现大量重复 SKU。原因colors或sizes表自身存在重复主键如未设PRIMARY KEY导致组合爆炸。解决先SELECT COUNT(*) FROM colors; SELECT COUNT(DISTINCT color_id) FROM colors;检查去重率修复源表主键约束。现象 3CROSS JOIN在 MySQL 5.7 中快在 8.0 中慢 10 倍。原因MySQL 8.0 优化器对CROSS JOIN的物化策略变更可能触发临时表磁盘写入。解决强制使用内存临时表SET SESSION tmp_table_size 268435456;256MB或改用应用层双循环生成。5. 数据写入的确定性原则INSERT 的单行、多行、DEFAULT 与 NULL 的精确控制5.1 INSERT VALUES显式字段列表是生产环境的铁律Mosh 笔记中INSERT INTO customers(first_name, phone, points) VALUES (Mosh, NULL, DEFAULT)是规范写法。但新手常犯的错是省略字段列表直接INSERT INTO customers VALUES (...)。这在表结构稳定时可行但一旦customers表新增一列如created_at DATETIME DEFAULT CURRENT_TIMESTAMP旧脚本就会因值数量不匹配而报错。-- ✅ 铁律永远显式声明插入字段与 VALUES 一一对应 INSERT INTO customers (first_name, last_name, email, points, phone) VALUES (Mosh, Hamedani, moshcodewithmosh.com, 1000, NULL); -- ❌ 危险依赖表结构顺序脆弱且不可读 INSERT INTO customers VALUES (Mosh, Hamedani, moshcodewithmosh.com, 1000, NULL);参数说明DEFAULT关键字表示使用该列定义的默认值如points INT DEFAULT 0而非NULL。NULL是明确赋空值DEFAULT是调用列级约束逻辑。5.2 多值 INSERT批量写入的性能分水岭Mosh 笔记中INSERT ... VALUES (...), (...), (...)是 MySQL 批量插入的标准语法。其性能远超单条INSERT循环因为减少了网络往返和事务开销。但需注意最大包限制-- ✅ 推荐每批 1000 行以内平衡内存与网络 INSERT INTO customers (first_name, email, points) VALUES (Alice, aliceexample.com, 500), (Bob, bobexample.com, 800), (Charlie, charlieexample.com, 1200); -- ❌ 危险单条 INSERT 包含 10 万行触发 max_allowed_packet 限制默认 4MB -- 解决拆分为多批或调大参数需 DBA 权限 -- SET GLOBAL max_allowed_packet 64*1024*1024; -- 64MB5.3 NULL 与 DEFAULT 的语义鸿沟何时该用哪个Mosh 笔记中phone列插入NULLpoints列用DEFAULT这揭示了核心设计哲学NULL 表示“未知”或“不适用”DEFAULT 表示“采用系统预设值”。例如phone为NULL用户未填写手机号信息缺失points为DEFAULT用户注册时未获初始积分按规则给 0假设points INT DEFAULT 0。-- ✅ 语义清晰NULL 表示无电话DEFAULT 表示按规则给 0 分 INSERT INTO customers (first_name, phone, points) VALUES (Mosh, NULL, DEFAULT); -- ❌ 语义混淆用 0 代替 NULL无法区分“用户填了 0”和“用户没填” INSERT INTO customers (first_name, phone, points) VALUES (Mosh, 0, 0); -- phone0 是有效号码吗points0 是初始值还是扣减后避坑 / 常见问题 / 排查现象 1INSERT ... VALUES (Mosh, NULL, DEFAULT)报错 “Field points doesnt have a default value”。原因points列未定义DEFAULT约束或定义为NOT NULL且无默认值。解决检查表结构SHOW CREATE TABLE customers;添加默认值ALTER TABLE customers MODIFY points INT DEFAULT 0;。现象 2INSERT后SELECT发现phone列是空字符串而非NULL。原因应用层代码将空输入转为插入而和NULL在 SQL 中完全不同是字符串NULL是空值。解决在应用层校验空字符串统一转为NULL或数据库层用TRIGGER自动转换。现象 3DEFAULT插入后points值为NULL而非预期的0。原因DEFAULT仅在INSERT语句中显式使用时生效若列定义为points INT DEFAULT 0 NOT NULL但INSERT中写了points NULL则NULL会覆盖DEFAULT因NOT NULL约束会报错故实际是NULL被静默转为0。解决始终用DEFAULT关键字避免NULL字面量赋值给有默认值的列。6. 从笔记到生产如何用这份 Cheat Sheet 构建你的 SQL 防御性编程习惯6.1 建立“三查一测”工作流每次写 SQL 前的强制动作Mosh 的笔记不是让你背下来而是给你一套肌肉记忆。我给自己定的铁规是任何一条发往数据库的 SQL必须经过“三查一测”——这不是流程是保命线。查一查字段来源在SELECT后逐个确认每个字段是否真的来自目标表而非拼写错误的别名。曾有个线上事故SELECT user_id, user_name FROM ordersorders 表无user_name因 MySQL 的STRICT_TRANS_TABLES未开启它静默返回NULL导致下游报表用户姓名全为空。现在我的编辑器里装了 SQLLint 插件SELECT后自动高亮不存在的列。查二查 WHERE 边界对所有WHERE条件问三个问题这个字段是否允许NULL如果允许是否会漏掉NULL记录→ 改用IS NULL或COALESCE。这个值是用户输入吗如果是是否做了ESCAPE防注入→LIKE ? ESCAPE \\参数化绑定。这个条件会走索引吗→ 用EXPLAIN看key和rowsrows 10000就得优化。查三查 JOIN 类型看到JOIN立刻停顿 2 秒我要的是交集INNER、左表全集LEFT、还是全外连接FULL OUTER绝不容忍“先写JOIN后面再想”。某次我写JOIN关联用户和地址本意是查所有用户及其地址LEFT却写了JOIN导致 37% 的用户地址为空被过滤报表 GMV 少算 200 万。一测测 LIMIT 1任何SELECT语句上线前必须加LIMIT 1执行一次确认返回列数、类型、NULL 性是否符合预期EXPLAIN显示的type是const/ref好不是ALL坏响应时间 100ms慢查询阈值。这一步耗时 3 秒但能拦截 80% 的低级错误。6.2 构建你的个人 SQL 模板库从 Cheat Sheet 到可复用代码块Mosh 的笔记是种子你要把它长成自己的树。我维护一个sql-templates.md里面全是带注释的、可直接复制粘贴的片段### 【安全 SELECT】带字段检查与 LIMIT 的模板 sql -- [用途] 查询用户基本信息确保字段存在且索引有效 -- [检查] 1. customers 表是否有 first_name, email, points 列 2. state 列有索引 -- [执行] 先 LIMIT 1 测试再删 LIMIT 上线 SELECT customer_id, first_name, email, points FROM customers WHERE state CA AND points 1000 ORDER BY points DESC LIMIT 100;【防 NULL WHERE】处理可能为空的字段-- [用途] 查询有手机号的用户同时兼容 phone 为 NULL 或空字符串 -- [检查] phone 列类型是 VARCHAR是否允许 NULL SELECT * FROM customers WHERE COALESCE(TRIM(phone), ) ! ;【跨库 INSERT】兼容 MySQL/PostgreSQL 的批量插入-- [用途] 批量插入用户避免 REGEXP 和方言差异 -- [注意] PostgreSQL 用 $1, $2 占位符MySQL 用 ?此处用通用 ? 占位 INSERT INTO customers (first_name, email, points) VALUES (?, ?, ?); **血泪教训**三年前我因在 WHERE 中用了 REGEXP导致项目从 MySQL 迁移到 PostgreSQL 时27 个 SQL 文件全部报错加班 36 小时手动替换。从那以后我每次写 SQL 都强制问自己“这段代码能在 PostgreSQL、SQL Server、SQLite 上跑通吗” 如果答案是否定的立刻重构为标准 SQL 或封装到应用层。希望帮到你。 p a hrefhttps://download.csdn.net/download/weixin_55804389/77552081 stylecolor:#ec7500;font-size:14px; 本文还有配套的精品资源点击获取 /a img altmenu-r.4af5f7ec.gif srchttps://csdnimg.cn/release/wenkucmsfe/public/img/menu-r.4af5f7ec.gif stylewidth:16px;margin-left:4px;vertical-align:text-bottom;cursor:text; /p