写下这篇笔记时我正在复习MySQL第34节课的内容创建数据库与运行各类SQL。说实话不少初学者在这个阶段会产生一种错觉——不就是CREATE DATABASE加几行SELECT吗有什么好学的但等你真正上手做项目、写业务代码、处理线上数据时才会发现库怎么建、字符集怎么选、权限怎么给、SQL怎么写才不踩坑每一步都有讲究。这篇笔记不是教科书式的语法汇总而是把我实操中真正用到的命令、踩过的坑、调整过的参数都记录在这里适合刚学完SQL基础语法、准备自己动手建库建表的初学者也适合想回头补齐数据库基本功的同学。内容从建库前的概念准备讲起一路到建库实操、各类SQL语句的实战、常见报错排查偏工程向不烧脑。1. 建库前必须清楚的三件事1.1 实例、数据库与表的关系MySQL服务跑起来之后我们面对的是一个实例。实例是内存里那套完整的运行机制负责监听端口、处理连接、管理存储引擎、维护缓冲池。而数据库只是实例下面的一种逻辑命名空间一个实例可以创建多个数据库库与库之间结构上是隔离的但共享同一个实例的CPU、内存和磁盘资源。打个比方实例是一栋写字楼数据库是楼里的房间表是房间里的文件柜文件柜里的抽屉才是真正放数据的地方也就是行。这个类比虽然粗糙但对理解权限和资源隔离很有用。很多人学习时会认为“数据库就是一个文件夹”这种说法其实不准确因为文件夹之间不会互相争抢内存而同一实例下的库一定会共享资源所以一个库的慢查询可能拖累同一实例上的另一个库。这也是为什么生产环境里重业务和高并发业务要拆到不同实例甚至不同服务器上。创建数据库的语句看起来只是划分了一个命名空间实际上还在实例的元数据表里登记了这个库的字符集、排序规则等属性并且决定了后续表默认的存储位置。理解这个层面之后建库就不再是“背命令”而是能解释为什么需要设计库名、为什么授权粒度是库级别而不是实例级。1.2 字符集与排序规则的选择建库时最容易被忽略、但影响最深远的参数就是字符集和排序规则。字符集决定数据存储时的编码方式排序规则决定字符串比较和排序时的行为。先给一个结论新项目建议直接用utf8mb4MySQL 8.0的默认值也是这个。老版本的utf8在MySQL里其实指utf8mb3最大只有3字节emoji和不少生僻字根本存不进去容易出现乱码或写入报错。utf8mb4是utf8的超集能完整覆盖Unicode这也是为什么几乎所有中文项目都会要求utf8mb4而不是简单的utf8。排序规则方面MySQL 8.0默认是utf8mb4_0900_ai_ciai表示不区分重音ci表示不区分大小写。如果你只需要普通英文和中文存储这个默认规则基本够用。但如果业务有特殊要求比如希望大小写敏感那就要选择后缀为cs或bin的排序规则再比如某些项目要求中文按拼音排序单靠这个排序规则并不总是符合预期可能需要在查询时用CONVERT函数做临时转换。建库时选错字符集后期想改就得对每张表、每个字段做ALTER线上操作成本极高所以第一步必须定准确。我见过有项目为了迁就旧系统的latin1编码硬着头皮存中文结果导出报表全是问号最后花了一整个迭代来清洗数据这种教训一次就够。1.3 权限模型决定建库方式MySQL的权限可以精确到“某个库.某张表”甚至到列和行。这意味着创建数据库时就应该顺便规划应用账号的权限边界。我见过不少团队为了图省事所有应用共享一个root账号后面出了问题根本没法排查是谁删了表。正确做法是为每个项目创建独立账号只授予这个项目库所需的权限。应用账号一般只需要DML权限也就是SELECT、INSERT、UPDATE、DELETEDDL权限像CREATE、ALTER、DROP应该留在DBA手里。这样即使应用被SQL注入攻破攻击者的破坏范围最多也就是增删改数据拿不到删库权限。建库不只是建一个库而是建一块“带边界的业务数据域”。库的划分同时决定了权限边界和未来的迁移边界多个不相关的业务塞进同一个库上线后想拆分应用要改连接、SQL要改前缀、授权要重新规划工程量非常大。我个人的实操习惯是“一项目一库一服务一账号”宁可在建库时多花几分钟设计名字和权限也不要给未来留一个无限扩容的麻烦。2. 创建数据库的完整实操2.1 命令行连接与图形化工具的选择创建数据库之前先要能连上MySQL。刚装好MySQL 8.0时我最推荐先用命令行验证基础连接执行mysql -h 127.0.0.1 -P 3306 -u root -p回车后输入密码。-h指定主机地址-P指定端口默认3306。Windows下如果服务没启动会报Cant connect to MySQL server on 127.0.0.1先去服务管理器里确认MySQL服务已经运行。连接中文环境时还可以加上--default-character-setutf8mb4避免客户端与服务器字符集不一致导致的乱码mysql --default-character-setutf8mb4 -h 127.0.0.1 -u root -p另一个高频问题是MySQL 8.0的认证插件。8.0默认使用caching_sha2_password老版本的客户端或驱动不认识会提示Authentication plugin caching_sha2_password cannot be loaded。解决方式有三种升级驱动到新版本在MySQL里创建用户时指定mysql_native_password或者显式开启SSL连接让认证过程兼容。具体操作我会在后面的报错排查部分展开。图形化工具方面Navicat和DBeaver是很多人习惯的选择。它们确实方便有可视化建表、ER图、数据导入导出等功能日常开发和调试效率很高。但我的建议是不要只依赖图形界面因为线上环境的故障排查很多时候只有命令行能用而且命令行能帮助你理解SQL到底是怎么被执行的图形工具反而会掩盖一些细节。两条腿走路是最稳的。2.2 CREATE DATABASE语法逐项拆解创建数据库的核心语法是CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name] [COLLATE collation_name];IF NOT EXISTS表示只有库不存在时才创建存在时跳过并且只产生一个警告。这在初始化脚本里非常实用因为脚本往往要重复执行不会因为库已存在而中断。如果不加这个参数重复执行会直接报ERROR 1007。字符集和排序规则可以省略省略时使用MySQL配置里的默认值但强烈建议显式写上不要依赖服务器全局配置否则同一套代码在不同环境可能得到不同的字符集。这里有个容易误解的点ALTER DATABASE可以修改库的字符集和排序规则但它只影响后续新建的表已经存在的表还是旧字符集必须逐表去改动。所以建库时选对参数好过事后补救。DEFAULT关键字在语法里只是可选修饰词写不写都不会改变结果但建议保留语义上更接近“默认字符集”的表达。另外数据库名的命名规则也需要注意Linux环境下库名区分大小写Windows和macOS默认不区分为了跨平台一致最好统一用小写字母加下划线命名比如shop_order、user_profile避免使用中文和保留字。2.3 一次完整的建库与授权实操下面是我在练习环境里经常执行的完整流程。先登录MySQLmysql -u root -p然后按顺序执行-- 查看当前实例里已有的库 SHOW DATABASES; -- 创建项目库 CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 确认库的创建信息 SHOW CREATE DATABASE shop\G; -- 切换到该库 USE shop; -- 验证当前所在库 SELECT DATABASE();建完库后紧接着做授权用独立账号来跑业务SQL而不是拿root顶上CREATE USER shop_app% IDENTIFIED BY Shop2026#App; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO shop_app%; FLUSH PRIVILEGES;为什么host写%因为应用服务器通常通过网络访问数据库不是本机。生产环境里我不建议用%最好限定应用服务器的IP比如192.168.1.10把暴露面缩小到具体来源。再补充一个小知识点CREATE USER和GRANT语句执行后其实不需要FLUSH PRIVILEGES权限缓存会自动刷新FLUSH PRIVILEGES主要是在你手动INSERT或UPDATE了mysql.user表之后才需要执行。脚本里保留它更多是养成习惯防止以后有手动改表的情况。完整流程跑完后还需要验证权限是否生效用新账号重新登录尝试在shop库里建一张表如果被拒绝说明DDL权限确实没有授予正好达到预期。2.4 建库报错对照表建库阶段常见的报错就那么几种我整理成了一张表方便对照排查报错信息常见原因处理方式ERROR 1007: Cant create database; database exists库已存在未加IF NOT EXISTS脚本里统一加IF NOT EXISTSERROR 1044: Access denied to user当前账号没有CREATE权限用管理账号或先授权ERROR 1115: Unknown character set字符集名称拼写错误核对utf8mb4等官方名称ERROR 1300: Invalid utf8 character string客户端连接字符集与库不一致连接时指定--default-character-setutf8mb4还有一个容易踩的问题创建数据库时指定的库名含有中横线比如my-dbMySQL会当成减号表达式而报语法错误。解决办法是给库名加反引号比如CREATE DATABASEmy-db;但从一开始就不用这种命名更省事。这一节内容虽然简单但基础打好了后面建表、导入数据时才不会来回折腾。3. 运行各类SQL语句的实战拆解3.1 DDL表结构的创建与调整DDL是对表结构本身的操作。创建表时除了字段定义还要选择存储引擎和字符集。MySQL 8.0默认InnoDB它支持事务、行级锁和崩溃恢复是绝大多数场景的正确选择。MyISAM虽然在某些读多场景下显得快但不支持事务而且表锁在并发写入时很容易变成瓶颈新项目我已经完全不考虑它了。建表语句的完整示例CREATE TABLE IF NOT EXISTS shop.orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;字段类型选择是一个值得展开的话题。金额我几乎只用DECIMAL因为FLOAT和DOUBLE在二进制存储时有精度丢失的问题0.1加0.2算出来不是0.3这在账务相关场景里是致命的。VARCHAR和CHAR的选择常规经验是长度固定且短比如性别、状态码可以用CHAR长度不确定比如用户名、备注用VARCHAR并给出合理的最大长度。VARCHAR(255)和VARCHAR(100)的存储开销差别不大但过长的VARCHAR在建立索引时可能超出键长度限制所以不要无脑拉长。日期时间类型也要注意只有年月日用DATE精确到秒用DATETIME需要带时区信息时用TIMESTAMP但TIMESTAMP在2038年会遇到溢出问题不少团队已经全面转向DATETIME。ALTER TABLE的常用三种形式ALTER TABLE shop.orders ADD COLUMN pay_time DATETIME NULL COMMENT 支付时间; ALTER TABLE shop.orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 0; ALTER TABLE shop.orders DROP COLUMN pay_time;MODIFY和CHANGE的区别是MODIFY改类型或属性不能改列名CHANGE可以同时改列名和类型。例如ALTER TABLE t CHANGE old_col new_col BIGINT NOT NULL;。还有一点需要注意大表上加索引在MySQL 8.0里可以用快速加索引的特性但修改列类型、修改约束仍然可能锁表线上操作要避开流量高峰或者用专门的在线DDL工具。TRUNCATE和DROP也是DDLTRUNCATE清空表数据但保留表结构DROP直接连结构一起删掉两者速度快但都无法回滚所以操作前必须确认清楚。3.2 DML增删改的细节控制INSERT语句最容易被忽视的是批量插入。逐行执行INSERT在导入几千上万条数据时非常慢因为每条写语句都有额外的网络往返和日志写入开销。更好的做法是拼接VALUESINSERT INTO shop.orders (order_no, user_id, status, amount) VALUES (A001, 1, 0, 99.00), (A002, 2, 1, 199.00), (A003, 3, 2, 399.00);一次提交几百行性能提升非常明显。还有一个习惯插入时永远显式写列名不要写INSERT INTO t VALUES(...)这种不带列名的写法因为表结构一变比如中间加了个字段无列名SQL就会错位或报错排查起来很痛苦。UPDATE和DELETE最大的坑是漏掉WHERE。非安全模式下不带WHERE的UPDATE和DELETE会作用于全表。我强烈建议把SQL_SAFE_UPDATES打开SET SQL_SAFE_UPDATES 1;开启后UPDATE和DELETE必须带WHERE或LIMIT否则拒绝执行。这个开关在本地和测试环境都很有用。大批量UPDATE在InnoDB里会产生行锁如果事务不提交锁会一直持有其他会话更新相同行就会被阻塞用户感受就是“数据库卡住”。我现在的大批量更新习惯是拆成小批次执行比如一次更新1万行就COMMIT一次避免长时间持有锁和制造过大的回滚日志。DELETE也类似如果一次删除几十万行建议分批删除配合LIMIT循环执行。3.3 DQL查询、去重、排序与分页SELECT的完整逻辑顺序我是按FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT来记的。理解这个顺序能解释很多低级错误比如WHERE里不能写聚合函数因为聚合是在GROUP BY阶段才发生的WHERE阶段行还没被分组所以“筛选总金额大于1000的用户”要用HAVING SUM(amount) 1000而不是WHERE SUM(amount) 1000。去重是学习SQL时的高频问题。DISTINCT的作用是对整个结果集去重不是单列去重。SELECT DISTINCT user_id和SELECT DISTINCT user_id, status是两个不同的语义后者是按“用户和状态的组合”去重可能返回同一个用户的多行。如果只是想知道有哪些不同用户用前者如果想知道不同状态下的用户组合用后者。COUNT(DISTINCT column)可以用来统计不同值的数量。DISTINCT在大数据量上代价较高因为它会使用临时表或文件排序如果是要去重后做统计尽量先缩小结果集再操作避免全表维度的大去重。排序方面ORDER BY默认升序降序加DESC。中文排序结果不符合预期时问题很可能出在排序规则上。utf8mb4_0900_ai_ci并不保证按拼音排序如果业务强依赖拼音顺序要么换支持拼音的排序规则要么在查询中用CONVERT(name USING gbk)这类临时转换。数据库层排序是默认路径理解这一点能避免行为不符合预期时到处排查。分页查询LIMIT offset, count是最常见的翻页写法但有个深坑offset越大MySQL要扫描和丢弃的行越多。比如LIMIT 100000, 20它会先找出前面的100020行再丢掉前100000行效率极低。优化方式常见有两种一是使用覆盖索引先只查主键列再关联原表取需要的字段二是改成基于上一页最大ID的查询在WHERE里加id 上次最大id LIMIT 20。两种方式都能把深分页的代价降下来具体选择取决于业务场景能否接受“按ID跳跃分页”的交互。JOIN是很多新手从入门到放弃的地方。INNER JOIN保留两边匹配的行LEFT JOIN保留左表所有行右表没有匹配则填NULLRIGHT JOIN用得少多数情况是调换两张表顺序用LEFT JOIN替代。写JOIN前先确认关联字段两边都有索引否则关联操作会对右侧表做全表扫描。多表关联时我习惯先跑EXPLAIN看执行计划确认驱动表和访问类型再决定是否需要调整连接顺序或改写子查询。3.4 事务与锁容易被忽略的运行前提运行DML语句时InnoDB默认是自动提交的每条INSERT、UPDATE、DELETE都被当成一个立刻提交的事务。遇到多步操作需要保证原子性时就必须手动开启事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;第二步一旦失败可以用ROLLBACK回滚让两个账户余额要么都变要么都不变这是事务ACID里一致性的直观体现。事务的隔离级别默认是REPEATABLE READ这个后面会深入讲解初学阶段只需要记住开事务容易忘关事务才是麻烦。没有COMMIT或ROLLBACK的事务会一直持有锁和连接严重时把连接池耗尽。判断当前是否关闭了自动提交可以用SELECT autocommit;0表示需要手动提交。存储过程也是很多人关注的话题。存储过程就是在数据库里预编译的一段SQL逻辑应用层通过名字来调用目的是减少网络往返、统一复杂逻辑。但它有两个明显缺点调试困难很难像应用代码那样打断点依赖数据库方言跨数据库迁移成本高。现在的工程实践里很多团队选择把复杂业务逻辑放在应用层数据库只负责基础读写所以初学者不必急着写一大堆存储过程先理解它的原理和使用边界就够了。4. 常见问题排查与避坑实录4.1 连接失败与登录报错连接阶段遇到的报错一半和认证机制有关。MySQL 8.0默认使用caching_sha2_password插件旧客户端和旧语言驱动不认识就会报认证插件无法加载。解决办法是按场景选择开发环境可以直接在用户侧指定mysql_native_password插件例如CREATE USER shop_app% IDENTIFIED WITH mysql_native_password BY Shop2026#App;如果有条件升级驱动就尽量升级因为这个插件方案在8.0后续版本中正在逐渐淡出推荐。另一个相关的问题是SSL连接报错客户端与服务端在SSL握手阶段版本不匹配时会出现SSL connection error。内网开发环境里可以在连接串中显式关闭SSL验证但生产环境建议保持SSL做内网加密传输。权限拒绝也是高频问题。看到Access denied for user先确认账号是否存在、host是否匹配、密码是否正确。host匹配是容易忽略的点一个账号可能同时存在rootlocalhost和root%两条记录客户端来源不同命中的记录就不同所以不要只查一条记录再看报错。密码重置可以用ALTER USER shop_app% IDENTIFIED BY 新密码;密码到期问题可以用ALTER USER ... PASSWORD EXPIRE NEVER;来消除策略限制但前提是确认这是公司安全策略允许的。4.2 重复建库与误删数据重复建库报错在前面已经提到解决方式就是加IF NOT EXISTS。这里更想强调的是误删数据的防错意识。DROP DATABASE不是可以轻描淡写的操作它会连库带表一起删除如果没备份数据基本只能靠binlog拉回来。我的习惯是所有建库删库操作必须写成脚本脚本里加上判断和打印并先在一个隔离环境里试跑。维护数据库最怕的是“人肉事件”所以从第一天起就要建立备份机制。最简单的备份就是mysqldumpmysqldump -u root -p --databases shop shop_backup.sql恢复时执行mysql -u root -p shop_backup.sqlmysqldump得到的SQL文件可读性强适合中小项目。大项目一般用物理备份比如xtrabackup但那需要单独部署和管理。无论用哪种方案定期验证备份能恢复比单纯备份更重要因为备份文件损坏的情况并不罕见。每周抽几分钟把备份恢复到本地环境里跑一遍这个成本换来的安心感非常高。4.3 慢SQL基本排查思路慢SQL优化是数据库运维里永不过时的话题。常规流程我总结为四步第一步打开慢查询日志把long_query_time设到1秒记录所有超过1秒的查询第二步拿到具体SQL后执行EXPLAIN看它的执行计划第三步检查type列如果出现ALL或者index基本就是在全表扫描或全索引扫描需要想办法加索引或改写SQL第四步确认索引是否真的生效注意常见的索引失效场景。哪些情况容易让索引失效我挑几个经常遇到的对索引列使用函数比如WHERE DATE(created_at) 2026-03-04会让created_at上的索引失效改成范围查询created_at 2026-03-04 AND created_at 2026-03-05更好隐式类型转换比如user_no是VARCHAR查询时却写WHERE user_no 123456MySQL会做类型转换索引失效正确写法是写成123456前导模糊查询LIKE %abc%无法用前缀索引只能全表扫如果业务确实需要考虑全文索引或外部搜索引擎。排查慢SQL时还有一个小技巧先看SQL是否真的需要返回那么多行。很多慢查询不是索引不行而是业务多取了根本用不到的数据。SELECT *满天飞然后把几百万行数据拉到应用层再用内存过滤这是最典型的浪费。先把查询结果集收敛到必需字段再谈优化索引。4.4 SQL注入的底线认知热搜词里出现“sql注入万能密码绕过”说明很多人对它有好奇心但这事值得认真对待。SQL注入的本质是用户输入被当作SQL代码的一部分拼进了语句改变了原本的语义。最经典的案例是登录框提交 OR 11如果后端直接拼接字符串SELECT * FROM users WHERE user_name OR 11条件恒为真于是绕过了密码校验这就是万能密码的原理。防御SQL注入最有效的手段是参数化查询。无论是Python的pymysql还是Java的PreparedStatement都用占位符代替字符串拼接# 反例字符串拼接 sql SELECT * FROM users WHERE user_name {}.format(name) cursor.execute(sql) # 正例参数化查询 sql SELECT * FROM users WHERE user_name %s cursor.execute(sql, (name,))参数化之后数据库会把输入当作纯数据而不是可执行代码注入就失效了。第二条防线是输入校验手机号、订单号这类格式化字段可以加白名单做格式校验。第三条防线是最小权限这也是我在建库部分坚持“应用账号只有DML权限”的原因之一。SQL注入防御是系统工程但从这三个点做起已经能挡住绝大多数常见攻击路径。5. 学习建议与实操习惯5.1 把建库和SQL练习放进一个可复现脚本我在学习阶段做了一件事自己都觉得很值把所有建库建表和练习SQL整理成一个可重复执行的脚本脚本开头放版本注释中间每步都写清楚目的。这样学到后面回看第34节内容时只需要执行一遍脚本就能快速重建整套练习环境不用靠记忆拼凑。脚本里统一使用IF NOT EXISTS和IF EXISTS保证幂等。这套思路在以后做自动化部署时也是同一个套路提前养成习惯能省不少事。5.2 每次连接先确认环境一个很小的习惯却非常实用每次在新的MySQL环境里第一件事不是急着跑业务SQL而是先查看当前环境信息SELECT VERSION(); SELECT character_set_database, collation_database; SHOW DATABASES;确认版本和字符集能避免很多莫名其妙的兼容性问题。我遇到过多次因为目标库是MySQL 5.7而自己按8.0语法写SQL的情况比如窗口函数、CHECK约束的支持差异提前确认环境就能少走弯路。数据库工作稳定比炫技重要这句话我越做越有体会。5.3 我对MySQL学习的一个建议最后再说一点个人体会。学MySQL最容易陷入的误区是背语法。但语法是查得到的真正值钱的是理解原理。创建数据库背后的字符集决策、权限设计运行SQL时的事务边界、索引失效场景这些东西靠背是背不出来的只有在一次次实操和排错中才能沉淀下来。如果只让我推荐一个重点我会说先学会看EXPLAIN的输出再去纠结其他优化技巧。有人写SQL写了很久却从未认真看过一次执行计划所有的优化判断都靠猜。把这条习惯建立起来之后你写的每一条SQL都会不一样。我也还在每天积累希望这篇笔记对正在经历第34节这个阶段的你有点用。