把MySQL装好的朋友恭喜你们跨过了最劝退的一步。上一篇我们把下载安装、初始化、启动服务这些事捋了一遍这一篇就进入真正有意思的部分往数据库里放数据再把数据查出来。内容不追求一次讲完所有语法而是带你完整走一遍“建库-建表-增删改查-改结构-事务”这条主线这也是你日后写业务代码、应付面试时最常用的那部分MySQL。这篇适合两类人看一类是刚装好MySQL、还没写过几条正经SQL的初学者另一类是写过增删改查但一直没搞清楚字符集、事务、索引这些底层逻辑的“半新手”。我会尽量把每个操作背后的为什么也讲清楚这样你下次遇到问题至少知道该往哪个方向排查。1. 装好MySQL之后先别急着建表字符集与存储引擎怎么选很多新手装完MySQL第一步就是打开命令行执行CREATE TABLE结果建出来的表乱码、排序规则不对、想用事务发现根本不支持然后一头雾水。这些问题十有八九出在字符集和存储引擎上而且是建库之前就该决定的。1.1 登录后先查这三条命令先用root登录有几条SQL建议你敲一遍心里有数mysql -u root -p SHOW DATABASES; SHOW VARIABLES LIKE character_set%; SHOW ENGINES;SHOW DATABASES可以看到系统自带的几个库information_schema和mysql这两个先别乱动。SHOW VARIABLES LIKE character_set%是查看当前字符集配置重点看character_set_server和character_set_database这俩决定了你新建库和新表的默认字符集。SHOW ENGINES则是列出当前MySQL支持的所有存储引擎支持情况和默认值一目了然。MySQL 8.0默认字符集是utf8mb4默认排序规则是utf8mb4_0900_ai_ci这套组合比老版本默认的utf8靠谱很多。为什么强调这个因为utf8在MySQL里不是真正的“全量UTF-8”它最多存3个字节像emoji表情和一些生僻汉字都存不进去。utf8mb4才是真正完整的UTF-8编码一个字最多占4个字节。所以建库建表时字符集我闭着眼都选utf8mb4。排序规则也很重要_ci结尾表示大小写不敏感cs结尾是大小写敏感bin是二进制比较。utf8mb4_0900_ai_ci这种新排序规则用的是Unicode 9.0标准比较更准确同时也意味着SELECT a A会返回1。如果业务上有严格区分大小写的需求比如用户名登录再单独给字段指定utf8mb4_bin。1.2 存储引擎为什么默认是InnoDB而不是MyISAM老一代MySQL开发者对MyISAM肯定不陌生它查询快、索引结构简单以前很多读多写少的站都用它。但MySQL从5.5开始把InnoDB设为默认引擎到现在8.0版本InnoDB几乎是一统天下的状态。原因很简单InnoDB支持事务、支持行级锁、支持外键还有崩溃后自动恢复的能力。这四项对业务系统来说是刚需尤其是事务和行级锁。能力InnoDBMyISAM事务支持不支持行级锁支持仅表锁外键支持不支持崩溃恢复支持不支持全文索引支持8.0后支持我的建议很简单不是特别明确的原因一律用InnoDB。以前有人为了让全文搜索快一点、或者追求那一点读性能去选MyISAM现在完全没必要MySQL 8.0的InnoDB已经支持全文索引而且性能差距在现代硬件上根本不明显。建库的时候顺手指定字符集能省掉后面一堆坑CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;用IF NOT EXISTS是防止你重复执行脚本时报错。这个习惯建议从第一天就养成后面写自动化脚本会频繁用。2. 建表SQL拆解从零定义第一张业务表数据库建好了接下来写第一张表。表结构设计是个大话题这里先讲最常用的建表语法、数据类型选择和几个约束的用法让你能看懂、能上手。2.1 先看一个完整的最小建表语句假设我们做一个简单的用户表包含自增主键、用户名、手机号、状态、创建时间。语句长这样USE shop; CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, name VARCHAR(64) NOT NULL COMMENT 用户名, mobile VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0-正常 1-禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户表;这段SQL基本涵盖了建表的核心语法。id用BIGINT UNSIGNED配AUTO_INCREMENT是为了避免将来数据量大了用INT不够。INT最大到21亿多看上去很多但很多业务表几年就到千万级再加上频繁的测试数据插入很容易触顶。BIGINT是INT的8倍空间做普通业务主键完全够。mobile这里给了一个唯一索引uk_mobile这是因为手机号天然应该唯一。唯一索引除了约束数据还能让手机号查询走索引一举两得。status用TINYINT而不是VARCHAR因为状态这种枚举值用整型更省空间、比较也更快。这里也呼应了热搜词里“mysql设置默认值为0”的需求DEFAULT 0就是给字段一个默认值防止插入时漏掉这个字段导致NULL。created_at用DATETIME加DEFAULT CURRENT_TIMESTAMP意思是插入数据时如果不专门传这个字段MySQL会自动填当前时间。这个写法省事且直观强烈推荐。2.2 数据类型选择的几个关键经验数据类型选错是新手最容易埋雷的地方。我见过有人把价格用FLOAT存结果对账对不上也见过有人把所有字符串统一用VARCHAR(255)导致索引膨胀、查询变慢。整数类型TINYINT(1字节)、SMALLINT(2字节)、INT(4字节)、BIGINT(8字节)。布尔值直接用TINYINT(1)0和1。小数类型金额、单价这种必须用DECIMAL比如DECIMAL(10,2)表示最长10位、小数点后2位。不要用FLOAT和DOUBLE它是近似存储算钱会出精度问题。字符串类型VARCHAR存可变长度字符串括号里的数字是最大字符数。VARCHAR(64)和VARCHAR(255)在InnoDB下对索引的效率影响不小不是越大越好。大段文本用TEXT。时间类型DATETIME不带时区适合绝大多数业务TIMESTAMP会随数据库时区转换适合需要统一UTC存储的场景。字段命名也有讲究。不要用order、desc、rank这类MySQL保留字做字段名不然每次查询都得加反引号很烦。就算偶尔要用也建议改成order_no、description这种带业务含义的名字。每张表的字段一定都写COMMENT注释半年后回来看你写的SQL会感谢当时的自己。2.3 约束不只是“限制”它也在保护数据NOT NULL、DEFAULT、PRIMARY KEY、UNIQUE KEY这些都是约束。刚学的时候容易觉得约束碍事——插入数据老报错实际上它们是在保护数据的质量。比如NOT NULL保证了关键字段不会被漏填唯一索引保证了不会出现两条重复的手机号。MySQL 8.0还支持CHECK约束可以在字段层面加简单的业务校验比如status IN (0,1)虽然InnoDB对它的执行力度有限但写上去至少能让表结构语义更清楚。3. 查询与排序的日常操作SELECT从简单到综合建表毕竟是少数时间日常开发里90%的SQL都是查询。很多人觉得SELECT简单其实恰恰是在查询里最容易写出慢SQL、错SQL。这一节把条件、排序、去重、分页串起来对应热搜词“mysql排序”“mysql常用sql语句”“mysql数据库命令大全”这些高频搜索需求。3.1 SELECT的基本骨架和条件过滤一条查询语句最简洁的骨架是SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 排序字段 LIMIT 行数;对应我们前面建的user表查所有正常用户并按照创建时间从新到旧排列SELECT id, name, mobile, created_at FROM user WHERE status 0 ORDER BY created_at DESC LIMIT 20;这句话的意思很直白只查状态正常的用户按创建时间倒序最多返回20条。WHERE里常用的运算符有、!、、、IN、LIKE、BETWEEN AND、IS NULL。比如查手机号以138开头的用户SELECT id, name, mobile FROM user WHERE mobile LIKE 138%;注意LIKE以通配符开头比如%138是没法走索引的数据量大时会全表扫描这种写法能避免就避免。3.2 排序不只是加一个ORDER BY排序的坑主要在数据类型的“隐性转换”上。比如手机号你在表里定义的是VARCHAR如果查询条件写成mobile 13800138000MySQL会把字段转成数字再比较一旦字段上有索引这个转换会让索引失效。所以字符串类型字段的等值查询一定要写成带引号的形式mobile 13800138000。多列排序的写法也有讲究SELECT id, name, status, created_at FROM user ORDER BY status ASC, created_at DESC;先按状态升序状态相同的再按创建时间倒序。这个逻辑在业务里很常见比如优先展示正常用户再按时间倒序排列。排序字段如果既不是主键也没有索引数据量大时会在临时文件里排序这也是慢查询的常见来源之一。碰到这种SQL优先考虑给排序字段加索引。3.3 去重、分页和写入操作的习惯DISTINCT用于去重比如统计所有用户的手机号段SELECT DISTINCT LEFT(mobile, 3) AS prefix FROM user;这里LEFT(mobile, 3)把手机号前3位截出来再加上DISTINCT去重。AS是起别名让查询结果更好看。分页是另一个高频操作SELECT id, name FROM user ORDER BY id LIMIT 10, 20;LIMIT 10, 20的意思是跳过前10条取后面20条。也就是第11条到第30条。偏移量一大这种分页就会慢因为MySQL要先扫描并丢弃前10条记录。深分页的优化思路一般是改成基于主键的游标查询比如WHERE id 10000 ORDER BY id LIMIT 20翻页时把上一页最后一条记录的id传进来这也是现在很多后台系统列表采用“加载更多”而不是传统页码的原因之一。插入和更新也有几个习惯。插入支持多行一次写入INSERT INTO user (name, mobile) VALUES (张三, 13800000001), (李四, 13800000002);批量插入比一条条插入快得多网络交互次数从N次降到1次。更新操作最关键的一条铁律UPDATE和DELETE一定要先想清楚有没有带WHERE。我见过不止一次有人在测试库执行UPDATE user SET status 1把全表状态改掉的惨案。生产环境执行更新前先跑一遍相同条件的SELECT确认要改哪些行再执行更新。DELETE和TRUNCATE的区别也提一下DELETE是逐行删除可以带WHERE、走事务删错了还能回滚TRUNCATE是清空整张表速度快但不可回滚而且会重置自增id。要不要用TRUNCATE取决于你是不是真的想“彻底清空而且不再反悔”。4. 表结构说改就改默认值调整、字段变更与索引创建业务跑起来以后表结构几乎没有不改的。今天加个字段明天调整一个默认值后天发现查询慢了要加索引。这一节对应热搜词“mysql数据库修改结构”“mysql设置默认值为0”“mysql创建索引”把这些日常结构变更操作讲透。4.1 ALTER TABLE 的三种常见操作增加字段是最常见的变更语法是ALTER TABLE user ADD COLUMN avatar_url VARCHAR(255) NOT NULL DEFAULT COMMENT 头像地址 AFTER mobile;这里的AFTER mobile表示新字段加在mobile后面。如果不写新字段默认加到最后。注意给已有大量数据的表增加字段时NOT NULL DEFAULT可以让MySQL在8.0里避免重建整张表属于比较稳妥的写法。修改字段用MODIFY它可以改类型、改默认值、改注释ALTER TABLE user MODIFY COLUMN name VARCHAR(32) NOT NULL DEFAULT COMMENT 用户名;改字段名用CHANGE它和MODIFY的区别是CHANGE要写两次字段名一次旧的一次新的ALTER TABLE user CHANGE COLUMN name nickname VARCHAR(32) NOT NULL DEFAULT COMMENT 昵称;删除字段就此打住能不用就不用。删掉容易想恢复就只能靠备份了。线上表结构变更还有一个大坑MySQL 8.0之前很多ALTER TABLE操作会锁表业务写入会直接卡住。即使8.0做了优化大数据量下改结构也要挑业务低峰期做或者借助在线变更工具。4.2 索引的创建和“最左前缀原则”查询变慢时第一个想到的优化就是加索引。创建索引的语法很简单CREATE INDEX idx_name ON user(name); CREATE UNIQUE INDEX uk_mobile ON user(mobile);普通索引允许重复值唯一索引不允许重复。如果业务上确认某个字段不能有重复值直接建唯一索引既约束数据又加速查询。更复杂一点的是联合索引比如经常按“状态创建时间”组合查询CREATE INDEX idx_status_created ON user(status, created_at);联合索引有个非常关键的“最左前缀原则”查询条件里如果包含联合索引的第一个字段索引就能生效如果跳过了第一个字段只查第二个字段索引就用不上。idx_status_created这个索引WHERE status 0能走索引WHERE status 0 AND created_at 2024-01-01也能走但WHERE created_at 2024-01-01就废了。索引不是越多越好。每个索引在写入时都要额外维护索引多了插入和更新变慢磁盘占用也变大。一个几百行的小表全表扫描比走索引还快压根不用建索引。判断一条SQL有没有走对索引用EXPLAIN看执行计划EXPLAIN SELECT id, name FROM user WHERE status 0 ORDER BY created_at DESC;重点关注type列和key列。type出现ALL说明是全表扫描key是NULL说明没用到索引这时候就该考虑加索引或者改SQL了。4.3 从MySQL表过渡到时序数据库的表结构思路热搜词里有“mysql表结构自动转tdengine超级表子表”这背后其实是一个很现实的需求业务数据量大了以后很多人会把监控、日志、物联网采集这类时序数据从MySQL迁到专用时序数据库。如果你是第一次做这种迁移记住一个核心思路把MySQL表拆成“标签字段时间字段量测字段”三段。比如一张设备采集表设备ID、地区、型号这些很少变化的字段就是标签采集时间是时间戳温度和湿度是量测字段。在时序数据库里标签用来分组过滤时间字段用来排序和聚合量测字段才是真正存数值的地方。如果你在建MySQL表时就把device_id、region、ts、temperature这种语义区分清楚后面无论同步到TDengine还是ClickHouse映射逻辑都会顺很多。5. 事务处理入门从“银行转账”理解Commit和Rollback热搜词里“mysql事务处理”“mysql存储过程”出现频率非常高可见这块是大家关心的重点。事务的概念其实不难难的是理解它到底解决什么问题以及不提交事务会带来什么后果。5.1 为什么转账必须是原子的拿最经典的银行转账举例A账户扣100元B账户加100元。如果第一条SQL执行成功、第二条SQL执行失败A的钱扣了但B没收到这账就平不了。事务的作用就是把这多条SQL打包成一个整体要么全部成功要么全部回滚。这也是事务ACID四个特性的核心价值原子性(Atomicity)多个操作要么全做要么全不做。一致性(Consistency)事务前后数据总量保持不变。隔离性(Isolation)事务之间互不干扰。持久性(Durability)一旦提交数据永久保存。用生活化的话说原子性就是“转账要么成功要么失败不存在转了一半的状态”隔离性就是“你付款的时候别人不会看到你余额忽多忽少”。5.2 手动事务的完整流程MySQL默认是自动提交的也就是说每执行一条SQL就自动提交一次。要手动控制需要这样写START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果第二步执行时发现余额不足或者干脆报错了就把COMMIT换成ROLLBACKROLLBACK;所有已执行的未提交变更都会被撤销。这里有一个新手极其容易踩的坑开启了事务执行了好几条UPDATE然后程序报错退出你以为是没生效实际上没提交的事务已经把相关行锁住了。其他连接里的更新会一直卡在等待锁状态表现就是“SQL执行卡住不动了”。遇到这种问题先查有没有未提交事务SELECT * FROM information_schema.innodb_trx\G找到事务的trx_mysql_thread_id可以KILL掉对应连接或者等它自己超时。5.3 隔离级别默认REPEATABLE READ到底是什么事务并发时会出现三种读问题隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED避免可能可能REPEATABLE READ避免避免可能InnoDB可避免SERIALIZABLE避免避免避免MySQL默认是REPEATABLE READ也就是可重复读。意思是同一个事务里多次读取同一条记录结果是一致的。它通过MVCC多版本并发控制解决不可重复读又通过间隙锁在绝大多数场景下避免了幻读。SERIALIZABLE最严格但并发性能最差一般不用。查看当前隔离级别SELECT transaction_isolation;需要理解的一点是隔离级别定得越高并发能力越低所以不要盲目把全局隔离级别改成SERIALIZABLE绝大多数业务用默认的REPEATABLE READ就够了。如果确实只需要已提交读可以在会话级别临时调整。6. 面试里常被问到的锁、连接池与存储过程最后一个部分把热搜词里出现的锁、连接池、存储过程、性能调优这些高频面试点串起来讲。这些内容不一定每天写业务都用到但面试和被同事拉去救火时一定会碰上。6.1 锁的分类表锁、行锁、间隙锁MySQL的锁可以分成两个维度理解。一个是粒度表锁和行锁。MyISAM只有表锁写的时候整张表锁住写并发能力很差InnoDB支持行锁不同行可以同时写这就是为什么InnoDB能扛并发。另一个维度是模式共享锁和排他锁。共享锁允许其他连接也加共享锁读同一行排他锁则独占这行谁都不能碰。InnoDB的行锁实际上有三兄弟记录锁(Record Lock)锁住具体某条记录。间隙锁(Gap Lock)锁住一个范围防止其他事务在这个范围插入数据。临键锁(Next-Key Lock)记录锁和间隙锁的组合既锁记录又锁范围。间隙锁是解决幻读的关键。举个例子事务里查WHERE id BETWEEN 10 AND 20如果这个范围没数据MySQL会锁住这个区间让别的事务没法往里面插入id为15的新记录从而保证事务内两次查询结果一致。死锁是另一个常见面试题。死锁发生时可以执行SHOW ENGINE INNODB STATUS\G在输出的LATEST DETECTED DEADLOCK部分能看到发生了什么。MySQL会自动检测死锁并回滚其中一个事务所以不用太恐慌但业务层还是要有重试机制。6.2 连接池为什么不能用一条连接打天下数据库连接是个昂贵资源每次新建连接都要经过TCP握手、身份认证、权限校验耗时几十毫秒到上百毫秒。如果每次业务请求都新建连接压力一大数据库就崩了。连接池就是预先创建一批连接放在池子里谁用谁取用完归还。Java生态里常用Druid和HikariCP核心参数不外乎initialSize初始连接数。maxActive最大活跃连接数。maxWait获取连接的最大等待时间。minIdle最小空闲连接数。配置参数不是越大越好。maxActive设成200如果数据库实际能承受的连接只有150多余的全在排队等待反而拖慢性能。连接池参数要和数据库的max_connections配合调整。另外连接池里的连接要定期保活避免数据库侧因为空闲超时把连接断了而应用层还在傻傻地用旧连接结果报错。这个也是热搜词里“mysql ssl连接错误”之类连接报错的常见来源之一多半不是SSL本身坏了是连接状态不对。6.3 存储过程的正确用法和“少用”的建议存储过程就是把一段SQL逻辑预先存在数据库里调用时传参数执行。一个带事务和异常处理的示例DELIMITER // CREATE PROCEDURE transfer( IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; UPDATE account SET balance balance - amount WHERE id from_id; UPDATE account SET balance balance amount WHERE id to_id; COMMIT; END// DELIMITER ;这段逻辑里DECLARE EXIT HANDLER FOR SQLEXCEPTION声明了一个异常处理器一旦事务中任何一步出错自动回滚并重新抛出错误。DELIMITER的作用是把语句结束符临时改成//否则MySQL会按分号直接执行存储过程体就写不完整。但我想表达一个个人观点业务上尽量少用存储过程。原因是它调试困难、版本管理麻烦、数据库迁移时会成为累赘。现在的开发模式里事务逻辑放在应用层代码里控制反而更清晰也更容易做单元测试。存储过程不是不能学面试和学习阶段写一写有助于理解事务和异常处理但生产环境的新代码能不用就不用。6.4 一条慢查询的排查链路最后分享一个完整的排查思路。假设页面接口变慢怀疑是MySQL导致的我会按这个顺序查先用SHOW PROCESSLIST看当前有哪些连接在跑什么SQL有没有长时间未提交的事务。然后打开慢查询日志抓到具体的慢SQLSET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;long_query_time 2表示超过2秒的查询记录到日志。拿到慢SQL后用EXPLAIN看执行计划检查是不是走了全表扫描、有没有用到索引、排序有没有用到临时文件。常见的问题类型无非三种缺索引、SQL写法不对导致索引失效、查询出了超过需要的数据量比如SELECT *把大字段全捞出来。按这个顺序排查90%的MySQL性能问题都能定位到。MySQL这门东西越用到后面越发现新手和老手的差距往往不在于背了多少命令而在于遇到问题时的排查思路是否清晰。这一篇把从建库到事务、再到锁和连接池的常见问题都过了一遍剩下的就是多建几张表、多写几条SQL、多看看EXPLAIN的输出。踩过几次坑之后你会慢慢形成自己的判断那时候再回头看这些基础概念感受会完全不一样。