拓冰网站定制 
1. 表操作到底是学什么以及为什么它是MySQL的第一道门槛很多刚接触MySQL的朋友一开始都会有这种感觉数据库的安装配置折腾完了CREATE DATABASE也敲出来了结果一到真正开始建表、往表里塞数据、把数据查出来用就有点不知道从哪儿下手了。其实这很正常——数据库本身只是一个仓库骨架真正让业务跑起来、让程序有数据可用的就是表。而表的增删查改也就是常说的CRUDCreate、Read、Update、Delete是所有SQL操作里出现频率最高、也必须彻底吃透的一组能力。标题里说的增删查改落到MySQL的语法上就是这么四类增INSERT往表里写入一行或多行数据删DELETE从表里移除满足条件的数据查SELECT把数据从表里取出来按各种条件过滤、排序、聚合改UPDATE修改表里已有数据的字段值但实际做项目时你会发现表的操作不只这四类语句。你要先建表设计字段类型和约束用到一半还可能要改表结构加字段、加索引、改字段长度。所以这篇博文我会把这整条链路都梳理清楚包括建表时的设计思路、增删查改的完整用法、开发中容易踩的坑以及一些性能相关的习惯。适合刚学完MySQL安装配置、准备系统过一遍表操作的初学者也适合工作了一两年但对某些细节还比较模糊的同学用来查漏补缺。我一直认为SQL这种东西不需要背语法但一定要理解每条语句背后的执行逻辑。比如为什么更新数据前要先查一遍为什么删除大量数据时TRUNCATE比DELETE快那么多这些为什么搞明白了你写出来的SQL就会自然带上一层专业感而不是停留在能跑就行的层面。2. 建表是增删查改的地基字段类型、约束、字符集怎么选很多人一上来就急着写INSERT和SELECT结果表结构设计得一塌糊涂后面查询慢、数据乱、字段不够用各种问题接踵而至。实际上改和查的体验很大程度上在建的那一步就已经决定了。2.1 字段类型选不对后面全是泪MySQL里最常用的字段类型集中在以下几类我把它们的适用场景和注意事项整理一下整数类型TINYINT1字节、SMALLINT2字节、MEDIUMINT3字节、INT4字节、BIGINT8字节。按存储范围选比如状态值用TINYINT就够主键或订单号用BIGINT更稳妥。不要滥用BIGINT每条记录多占4字节千万级数据量差距就很明显了。小数类型DECIMAL(M,D)用于精确计算比如金额、单价、汇率一定要用DECIMAL而不是FLOAT或DOUBLE。FLOAT和DOUBLE是浮点存储存在精度丢失问题做金额计算时会出现0.10.2不等于0.3这种尴尬。字符串类型CHAR定长、VARCHAR变长。VARCHAR需要指定长度长度不是越大越好——VARCHAR(255)和VARCHAR(5000)在内存排序时消耗完全不同。超长文本用TEXT但注意TEXT类型不能有默认值而且索引上有更多限制。时间类型DATE年月日、TIME时分秒、DATETIME年月日时分秒、TIMESTAMP时间戳。存业务时间用DATETIME存自动记录创建/更新时间可以配合DEFAULT CURRENT_TIMESTAMP使用。TIMESTAMP有2038年问题而且受时区影响容易踩坑。布尔类型MySQL没有真正的BOOLEAN用TINYINT(1)代替0表示假1表示真。2.2 约束不只是限制更是在帮数据兜底建表时最常见的约束有以下几类主键约束PRIMARY KEY每张表都建议有主键用来唯一标识一行。主键天然是唯一索引而且InnoDB存储引擎的聚簇索引就是基于主键构建的。没有主键的表InnoDB会偷偷选一个唯一索引或生成隐藏主键这会产生不必要的开销。推荐用自增INT主键或BIGINT主键业务唯一标识如身份证号另建唯一索引。非空约束NOT NULL字段值不允许为空。设计时想清楚哪些字段业务上必须有值比如用户表的用户名、订单表的金额。NULL在查询时非常麻烦WHERE name NULL是查不到数据的必须写IS NULL。唯一约束UNIQUE KEY保证字段或字段组合的值不重复比如用户名、手机号。把唯一约束建好比在代码里先查再插要可靠得多。默认值约束DEFAULT不给值时自动填充比如状态字段默认0创建时间默认CURRENT_TIMESTAMP。外键约束FOREIGN KEY保证关联数据的完整性但实际开发中很多团队刻意不用外键而是在应用层维护一致性。原因是外键在插入、删除时会触发额外的检查影响性能而且大表之间用外键后续分库分表时会很痛苦。如果你是初学者学习阶段可以建外键帮助理解关系正式项目中可以先不加但要在设计文档里明确关联关系。2.3 字符集和排序规则别等乱码了才想起来建表时最好显式指定字符集不要依赖MySQL默认值。当前的主流选择是CREATE TABLE user ( ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;utf8mb4是真正的四字节UTF-8能存储emoji和生僻字而老旧的utf8在MySQL里最多三字节存个emoji就直接报错。排序规则utf8mb4_unicode_ci在几乎所有场景下都够用它的特点是大小写不敏感如果业务上有大小写敏感需求可以用utf8mb4_bin或utf8mb4_general_ci按需选择。这里有个很容易忽略的点如果数据库、表、字段三层的字符集不一致查询时可能触发隐式转换明明字段上有索引却走不了。所以建库的时候就统一用utf8mb4后面能省掉大量编码噩梦。2.4 一个完整的建表示例下面用一个用户表和订单表来演示你可以直接复制到本地MySQL跑一遍CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; USE shop; CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone CHAR(11) NOT NULL COMMENT 手机号, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, 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_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id INT UNSIGNED NOT NULL COMMENT 用户ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;这个例子里有几个细节值得注意自增主键配UNSIGNED正数范围直接翻倍用户名和手机号都建了唯一索引从数据库层面杜绝重复注册updated_at用ON UPDATE CURRENT_TIMESTAMP自动更新省去应用层每次手动维护订单表的amount用DECIMAL(10,2)而不是FLOAT金额计算不会出现精度问题。3. 插入数据不只是INSERT INTO那么简单建好表之后第一件事就是往里塞数据。INSERT看起来简单但实际使用中也分很多场景每一种的写法和注意事项都不一样。3.1 最基础的单行插入INSERT INTO user (username, phone, status) VALUES (张三, 13800138000, 1);这里我显式列出了字段名这是个好习惯。如果省略字段名直接写VALUES就必须和表结构完全一致哪天表结构加了个字段这条SQL就废了。显式列字段名后没有列出的字段会自动用默认值比如created_at会自动填当前时间id会自动自增。3.2 批量插入提升效率需要插入多条数据时不要一条一条执行而是把多组VALUES拼在一条SQL里INSERT INTO user (username, phone, status) VALUES (李四, 13800138001, 1), (王五, 13800138002, 1), (赵六, 13800138003, 0);一次插入几十几百行比循环单条插入快一个数量级。原因是减少了客户端和服务器之间的网络往返次数。实测过在本地插入1万条数据批量插入可能1秒以内完成单条循环能到几十秒。应用层用JDBC的话还可以配合rewriteBatchedStatementstrue参数来开启真正的批量执行。3.3 从查询结果直接插入这是复制数据最常见的场景比如要把用户表里的部分数据归档到历史表或者把一张临时表的数据合并到正式表INSERT INTO orders_archive (id, user_id, amount, status, created_at) SELECT id, user_id, amount, status, created_at FROM orders WHERE created_at 2024-01-01;这种写法的核心价值是避免先把数据查出来再逐条插入整个过程在数据库内部完成效率非常高。唯一要注意的是INSERT和SELECT的字段顺序要严格对应哪怕字段名不同也没关系位置对上就行。3.4 插入时的冲突处理实际开发中很常见的场景批量插入时某几条数据已经存在你不想让整条SQL报错而是希望存在就跳过或存在就更新。MySQL提供了两种方案-- 存在唯一键冲突时忽略新数据 INSERT IGNORE INTO user (username, phone, status) VALUES (张三, 13800138000, 1); -- 存在唯一键冲突时更新指定字段 INSERT INTO user (username, phone, status) VALUES (张三, 13800138000, 1) ON DUPLICATE KEY UPDATE phone VALUES(phone), status VALUES(status);INSERT IGNORE适合有就跳过的幂等场景ON DUPLICATE KEY UPDATE适合数据同步场景比如从外部系统拉取数据存在就更新关键字段。这里要注意触发条件是基于主键或唯一索引的冲突不是所有重复都能触发。另外VALUES()函数在MySQL 8.0.20之后被标记为废弃建议用别名写法但绝大多数老代码里仍然是VALUES()短期内仍然兼容。3.5 插入操作的两个隐形坑第一个坑是自增ID跳号。你可能遇到过这种情况插入失败了几条后面的ID直接跳了几十甚至几百。这通常是事务回滚导致的——自增ID一旦分配就不会回收哪怕事务后来回滚了。这在业务上一般无所谓但如果你对ID连续有强迫症得提前做好心理建设。第二个坑是字段长度超出报错。比如phone CHAR(11)插入一个13位的手机号会直接报Data too long for column应用层最好提前做长度校验而不是把错误留给数据库。4. 查询数据SELECT是增删查改里最值得花时间的部分如果说整个CRUD里哪个操作最值得深入学习那一定是SELECT。因为增、删、改都有明确的条件而查询要面对海量数据、复杂条件、多表关联、统计聚合写得好不好直接影响系统性能和用户体验。4.1 从最简单的SELECT说起SELECT * FROM user;这条SQL会把user表里所有字段、所有行都取出来。功能上没错但实际开发中尽量不要直接用SELECT *写业务代码。原因有几点如果表有几十个字段而业务只需要其中3个多查出来的字段白白消耗网络流量和内存如果表结构加字段SELECT *的结果集结构会跟着变应用层的映射代码可能直接报错。更推荐的是只查需要的字段比如SELECT id, username, phone FROM user;4.2 WHERE过滤查询的核心逻辑WHERE子句决定哪些行会被返回。常用操作符可以分成几类等值/比较、!、、、、范围BETWEEN ... AND ...集合IN (...)也可以加NOT模糊匹配LIKE%表示任意多个字符_表示单个字符空值判断IS NULL、IS NOT NULL组合AND、OR、NOT举个实际例子SELECT id, username, phone, created_at FROM user WHERE status 1 AND created_at 2024-06-01 00:00:00 AND created_at 2024-07-01 00:00:00 ORDER BY created_at DESC;这段查询在找六月份注册且状态正常的用户按注册时间倒序排列。这里有个细节时间范围判断用和的组合而不是BETWEEN加上最后一秒。因为BETWEEN 2024-06-01 AND 2024-06-30 23:59:59虽然看着直观但如果有记录的时间精确到微秒就可能漏掉最后一秒内的数据。用左闭右开区间最稳妥。关于LIKE要记住一个性能常识LIKE %关键字%这种写法因为%在开头MySQL无法使用普通索引优化只能全表扫描。如果业务确实需要模糊搜索要么只查前缀匹配的LIKE 关键字%要么引入全文索引、ES等方案。当然数据量小时无所谓但量大了这就是性能瓶颈。4.3 ORDER BY与LIMIT排序和分页排序的关键是理解排序字段和索引的关系。比如SELECT id, username FROM user ORDER BY created_at DESC LIMIT 10;如果created_at上有索引并且查询没有其他条件干扰MySQL可能直接按索引顺序扫描避免文件排序filesort。如果ORDER BY和WHERE里用了不同的字段排序就很可能走filesort数据量大时查询会明显变慢。分页是另一门学问。最常见的写法是SELECT * FROM orders ORDER BY id LIMIT 0, 20; -- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20, 20; -- 第二页这在小数据量上没问题但翻到很深的页数时就出问题了。比如LIMIT 1000000, 20MySQL并不是直接跳过100万行而是先取100万零20行再丢掉前100万行。这种深分页慢的根本原因在于扫描了大量无用数据。优化方案包括用WHERE id 上次最大id ORDER BY id LIMIT 20这种基于游标的方式或者先子查询取主键再回表SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20;4.4 聚合查询与GROUP BY统计场景是SQL的强项常用的聚合函数有COUNT()、SUM()、AVG()、MAX()、MIN()。配合GROUP BY可以对分组后的数据进行各自统计SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE status 1 GROUP BY user_id HAVING order_count 3 ORDER BY total_amount DESC;这个查询统计了每个已支付用户的订单数和总金额然后筛出订单数不低于3的用户。这里有个语法细节WHERE是先过滤每一行原始记录再分组HAVING是分组之后再过滤聚合结果顺序不能搞错。想按聚合结果过滤只能用HAVING且HAVING中引用的别名在MySQL里是允许的别的数据库不一定支持。COUNT(*)和COUNT(字段)的区别也经常被问到COUNT(*)统计行数会包含字段值为NULL的行COUNT(字段)统计该字段非NULL的行数。大多数场景用COUNT(*)就对了MySQL对COUNT(*)有专门的优化。4.5 多表关联JOIN的使用逻辑实际业务中数据很少只存在一张表里。比如订单表里只有user_id要显示下单用户的名字就得关联user表SELECT o.id, u.username, o.amount, o.created_at FROM orders o INNER JOIN user u ON o.user_id u.id WHERE o.status 1 ORDER BY o.created_at DESC;INNER JOIN只返回两边都匹配上的行。LEFT JOIN则会保留左表所有行右表没匹配到就用NULL填充这个在查有哪些用户没下过单时特别好用SELECT u.id, u.username FROM user u LEFT JOIN orders o ON u.id o.user_id WHERE o.id IS NULL;JOIN的性能注意事项小表驱动大表MySQL优化器会自己判断但你在写的时候尽量用「过滤范围更小」的表作为驱动表连接字段尽量有索引多表JOIN超过三张时要警惕数据量级的笛卡尔积放大效应。4.6 子查询的坑与替代方案子查询写法直观但早期MySQL版本对子查询的优化能力比较弱有些包含子查询的SQL会被优化成低效的执行计划。一个经典场景-- 查出所有订单金额大于平均金额的订单 SELECT id, user_id, amount FROM orders WHERE amount (SELECT AVG(amount) FROM orders);标量子查询问题不大但IN子查询在某些版本上存在优化瓶颈-- 查出有订单的用户老写法 SELECT * FROM user WHERE id IN (SELECT user_id FROM orders); -- 更稳妥的写法 SELECT DISTINCT u.* FROM user u INNER JOIN orders o ON u.id o.user_id;关联改写后执行计划往往更可控。MySQL 5.7以后子查询优化提升明显但为了线上稳定遇到复杂子查询我还是习惯先看EXPLAIN再决定要不要改写。5. 更新数据UPDATE的安全意识比语法更重要UPDATE的语法非常简洁核心风险不在语法而在你有没有想清楚影响哪些行。5.1 基础UPDATE的正确姿势UPDATE user SET status 0 WHERE username 张三;通常的做法是先写WHERE条件确认范围再写SET。更严谨的操作顺序是先在应用层SELECT一下确认满足条件的记录确实是你想改的那批再执行更新。5.2 忘记WHERE条件的翻车现场做开发和运维最怕的就是执行了UPDATE user SET status 0;没有WHERE限制会更新全表所有行而且不会有任何确认弹窗。如果你还开启了自动提交MySQL直接物理落盘连反悔的余地都没有。虽然可以通过binlog回滚但过程极其痛苦。一个特别实用的防线是MySQL的安全更新模式safe update modeSET SQL_SAFE_UPDATES 1;开启后UPDATE和DELETE如果没有带WHERE条件或者WHERE条件里没有使用索引字段MySQL会直接报错拒绝执行。强烈建议开发环境的会话里都加上这一句能帮你拦住一堆低级失误。注意它是会话级别的新连接需要重新设置。5.3 批量更新与JOIN更新需要根据另一张表的数据来更新当前表时可以这样UPDATE orders o INNER JOIN user u ON o.user_id u.id SET o.user_name u.username WHERE u.status 1;这种写法把查出来再循环更新的工作在数据库里一次完成性能好很多。但要注意JOIN UPDATE会同时锁定两张表中涉及的行如果表很大或并发操作很多锁范围扩大会拖垮其他事务。5.4 更新操作务必配合事务如果一次更新涉及多行数据、多张表或者更新失败后需要恢复现场一定要用事务包起来START TRANSACTION; UPDATE orders SET status 1 WHERE id 1001; UPDATE user SET total_spent total_spent 99.00 WHERE id 10; COMMIT;事务的意义在于要么全部成功要么全部回滚。上面的场景是修改订单状态和累计用户消费金额如果第一句执行成功、第二句出差错没有事务的话数据就处于不一致状态。加了事务后任一步出错都可以ROLLBACK回到起点。实际开发中建议所有写操作都在服务层开启事务并设置合理的隔离级别默认REPEATABLE READ一般够用。5.5 UPDATE语句的常见坑更新后自增ID不变UPDATE不会修改自增主键只有删除后重新插入才会占用新ID。SET顺序影响旧值引用SET col col 1这种是安全的因为MySQL先读旧值再写新值。但如果SET a b, b a这种交叉赋值最终值和你想的可能不一样因为MySQL不是同时赋值。实测中老版本和新版本行为可能不同最好不要写出依赖赋值顺序的SQL。大量更新导致锁等待一次更新几百万行时InnoDB会加很多行锁并占用undo日志空间很容易导致其他事务阻塞。建议分批更新比如每次WHERE id BETWEEN ... LIMIT 5000多跑几轮对生产环境影响小得多。6. 删除数据DELETE与TRUNCATE的选择以及误删的挽回方案删除是CRUD里最危险的操作没有之一。你永远不希望在生产环境执行一条不带条件的DELETE。6.1 DELETE的基本用法DELETE FROM user WHERE id 1001;只删除满足条件的行。DELETE操作在InnoDB里是逐行删除并记录binlog的所以如果你删了大量数据注意看下binlog文件大小和磁盘空间。删除操作产生的undo日志如果事务没提交其他事务还能看到旧数据如果大量数据在一个事务里删掉又长时间不提交会拖慢整个实例。6.2 DELETE与TRUNCATE的核心区别对比项DELETETRUNCATE条件过滤支持WHERE不支持直接清空全表逐行触发会逐行删除可事务回滚一次性释放表空间不可回滚自增IDID继续接着涨ID重置从1开始速度慢数据量大时尤其明显快秒级完成表结构保留保留如果你确定要把整张表的数据清空、但保留表结构用TRUNCATE TABLE比DELETE FROM快得多。但注意TRUNCATE不能和事务一起回滚虽然它隐式提交但执行了就是执行了没有后悔药。另外有外键约束的表不能TRUNCATE会报错需要先删外键或改用DELETE。6.3 误删数据后的急救思路真误删了也别完全慌。以下几种恢复思路按优先级排列没提交还能回滚如果删除在一个事务里还没COMMIT直接ROLLBACK。物理备份恢复有全量备份binlog可以恢复到误删时间点附近这是最稳妥的生产恢复方案。binlog反向解析通过binlog2sql这类工具解析binlog找到误删前镜像反向生成INSERT语句。要求开启binlog_formatROW最好是在误删后立刻停止写入防止binlog被刷掉。延迟从库有些团队在主库误删后立刻从从库把数据捞回来——如果从库的复制还没来得及执行那段DELETE。作为个人学习阶段最实用的建议是删除前先SELECT确定范围删除时用事务包好删除后立刻检查影响行数。6.4 删除操作和索引的关系你可能注意到大量删除之后表的查询速度会下降。原因是InnoDB删除大量行后索引和表空间会出现页分裂和碎片。解决方式有两种对于全表清空直接用TRUNCATE对于按条件删除大量数据后想整理碎片可以用ALTER TABLE ... ENGINEInnoDB来重建表ALTER TABLE orders ENGINEInnoDB;这条SQL会重建表并整理索引释放碎片空间。生产环境执行时要评估表大小和影响MySQL 5.6以后普通表的这个操作是INPLACE在线执行的但大表还是建议维护窗口期操作。7. 修改表结构ALTER TABLE的常用场景与生产环境注意事项前面说了表的操作不只是数据层面还包含表结构的修改。这个内容教科书经常一笔带过但实际开发中经常遇到需求变更要加字段、字段长度不够要改、查询慢了要加索引。我单独用一节来讲因为这里面的坑也挺多。7.1 增加、修改、删除字段-- 增加字段 ALTER TABLE user ADD COLUMN nickname VARCHAR(50) NULL COMMENT 昵称; -- 修改字段类型/默认值 ALTER TABLE user MODIFY COLUMN phone CHAR(11) NOT NULL DEFAULT COMMENT 手机号; -- 修改字段名和类型 ALTER TABLE user CHANGE COLUMN nickname nick_name VARCHAR(80) NULL COMMENT 昵称; -- 删除字段 ALTER TABLE user DROP COLUMN nick_name;MODIFY和CHANGE的区别MODIFY只改字段定义不改字段名CHANGE可以同时改字段名和定义而且需要写两遍字段名旧名和新名。删除字段前务必确认业务代码里没有引用不然应用启动直接报字段不存在。7.2 添加和删除索引-- 添加普通索引 ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- 添加唯一索引 ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no); -- 添加联合索引 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); -- 删除索引 ALTER TABLE orders DROP INDEX idx_user_id;联合索引的设计是门学问核心是最左前缀原则。比如(user_id, status)联合索引可以支持WHERE user_id 1的查询也可以支持WHERE user_id 1 AND status 1的查询但单独的WHERE status 1用不上这个索引。设计时要把最常作为过滤条件的字段放前面。7.3 修改表名ALTER TABLE user RENAME TO member;改表名后所有相关的存储过程、视图、外键、业务代码里的表名都要同步更新。批量改表名在生产环境比较常见于分表场景比如把orders表改名为orders_2024_06然后新建orders。7.4 大表ALTER的在线DDL问题这是生产环境比较容易翻车的地方。MySQL 5.6之前ALTER TABLE几乎都是COPY算法先把数据复制到临时表再切换回来期间原表加锁禁止读写。5.6之后引入了在线DDL部分操作可以INPLACE执行并允许并发DML。所以执行ALTER前先确认MySQL版本然后用ALGORITHM参数显式指定ALTER TABLE orders ADD INDEX idx_amount (amount), ALGORITHMINPLACE;如果MySQL判断无法INPLACE会直接报错而不是默默回退到COPY模式这能帮你提前发现问题。另外即便是INPLACE操作大表上的DDL也可能消耗大量CPU和磁盘IO建议业务低峰期执行。8. 表操作中的高频死坑与性能习惯这些坑是我在实际开发和帮别人排查问题时反复遇到的单独列出来每一个都对应真实的教训。8.1 字符集混乱N个表N种编码如果建库时统一用的是utf8mb4基本不会遇到这个问题。但很多老项目是历史原因各表字符集不统一联合查询对比字符串时就会报Illegal mix of collations。解决办法是查询时显式指定排序规则SELECT * FROM orders o INNER JOIN member_archive m ON o.user_id m.user_id WHERE o.note m.note COLLATE utf8mb4_unicode_ci;但根治还是靠统一表结构、迁移存量数据。8.2 NULL值带来的查询陷阱NULL参与计算时结果也是NULL所以WHERE amount NULL永远查不到数据必须写IS NULL字符串和NULL拼接结果还是NULL聚合函数SUM会自动忽略NULL行但COUNT(字段)不忽略NULL。设计表时能用NOT NULL DEFAULT兜底的字段就不要让它空着。8.3 索引失效的几大原因查询慢时第一反应是看索引但很多时候你发现明明建了索引却不生效常见的索引失效场景包括对索引字段使用函数WHERE DATE(created_at) 2024-06-01改成created_at 2024-06-01 AND created_at 2024-06-02就能走索引。隐式类型转换字段是VARCHAR你传数字进去MySQL会做转换导致索引失效。比如phone 13800138000应该写成phone 13800138000。模糊匹配%在开头前面说过的LIKE %xx。OR连接非索引字段WHERE id 1 OR username 张三如果username没索引整个查询可能放弃索引。每一条SQL执行前用EXPLAIN看一眼执行计划type是ALL或ref都会告诉你当前查询的性能状况。养成这个习惯能少踩90%的SQL性能坑。8.4 LIMIT深分页和COUNT大表的性能问题前面讲了深分页的优化方案。再补充一个经典的场景SELECT COUNT(*) FROM orders统计总行数在千万级InnoDB表上会非常慢因为InnoDB不像MyISAM那样直接存储行数需要逐行统计。如果业务非常频繁地请求总行数要么加缓存要么接受延迟统计不要每次都打全表。8.5 事务里别混着大量SELECT项目里有人图省事在一个事务里执行大量查询再做少量更新还长时间不提交。这会导致两个问题一是事务太长undo日志膨胀二是持锁时间过长其他业务被阻塞。写操作事务保持短小精悍查询不放事务里是基本素养。9. 一个综合练习从建表到业务统计的完整流程把上面说到的知识串起来我用一个模拟的电商用户订单统计例子演示从建表到完成统计的完整流程。你可以直接在本地MySQL环境跑一遍这块内容会帮你把零散的知识点连成线。-- 1. 建库建表 CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; USE demo; CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, phone CHAR(11) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 2. 插入测试数据 INSERT INTO user (username, phone) VALUES (user1, 13800000001), (user2, 13800000002), (user3, 13800000003); INSERT INTO order (user_id, amount, status) VALUES (1, 100.00, 1), (1, 200.00, 1), (2, 50.00, 1), (3, 300.00, 0), (1, 80.00, 1); -- 3. 查询所有已支付订单以及用户名字 SELECT u.username, o.amount, o.created_at FROM order o INNER JOIN user u ON o.user_id u.id WHERE o.status 1 ORDER BY o.created_at DESC; -- 4. 统计每个用户已支付订单的总金额和订单数只显示有订单的用户 SELECT u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent FROM user u LEFT JOIN order o ON u.id o.user_id AND o.status 1 GROUP BY u.id, u.username HAVING order_count 0 ORDER BY total_spent DESC;第4条SQL里有个细节JOIN条件里带上o.status 1比在WHERE里过滤更合理因为它保证LEFT JOIN时未支付的订单以NULL形式保留不影响用户分组统计同时又不会把没有订单的用户过滤掉。如果你把过滤条件放到WHERE里LEFT JOIN就退化成了INNER JOIN。建议你自己动手把这条SQL跑一遍再对比用WHERE o.status 1的结果差异对理解JOIN的执行逻辑会特别有帮助。10. 把表操作练扎实后下一步该学什么表操作的CRUD练熟之后你已经具备了操作MySQL的核心能力。但要真正在生产环境里游刃有余后面还有几条线值得接着啃。第一是索引原理。为什么BTree能支撑千万级数据查询为什么联合索引要遵循最左前缀为什么覆盖索引能消除回表这些问题的答案直接决定了你写出的SQL是走索引还是全表扫描。第二是事务与锁机制。ACID怎么实现的MVCC是什么行锁、间隙锁在什么场景下会触发并发更新同一行时如何避免死锁这不仅是面试高频题也是排查线上数据问题的基础能力。第三是性能优化。从慢查询日志分析、EXPLAIN解读、到分页优化、批量写入优化、甚至主从复制和读写分离这是一条非常长但非常值得的路。最后再结合我自己的体会说几句表的增删查改看起来简单但恰恰是这些基础操作构成了你在数据库领域的天花板。很多人工作两三年写SQL还是靠拼凑遇到问题就百度核心原因就是CRUD的基本功没有真正形成体系。建议不要只满足于能跑出结果多用EXPLAIN观察执行计划多想想每条SQL在InnoDB内部是怎么执行的。等这几件事成了习惯你会发现自己排查问题、写优化方案的能力会有一个明显的跳跃。先把表操作这关彻底过去后面的路自然就顺了。 MySQL表操作完全指南：从建表到增删改查的性能优化
1. 表操作到底是学什么以及为什么它是MySQL的第一道门槛很多刚接触MySQL的朋友一开始都会有这种感觉数据库的安装配置折腾完了CREATE DATABASE也敲出来了结果一到真正开始建表、往表里塞数据、把数据查出来用就有点不知道从哪儿下手了。其实这很正常——数据库本身只是一个仓库骨架真正让业务跑起来、让程序有数据可用的就是表。而表的增删查改也就是常说的CRUDCreate、Read、Update、Delete是所有SQL操作里出现频率最高、也必须彻底吃透的一组能力。标题里说的增删查改落到MySQL的语法上就是这么四类增INSERT往表里写入一行或多行数据删DELETE从表里移除满足条件的数据查SELECT把数据从表里取出来按各种条件过滤、排序、聚合改UPDATE修改表里已有数据的字段值但实际做项目时你会发现表的操作不只这四类语句。你要先建表设计字段类型和约束用到一半还可能要改表结构加字段、加索引、改字段长度。所以这篇博文我会把这整条链路都梳理清楚包括建表时的设计思路、增删查改的完整用法、开发中容易踩的坑以及一些性能相关的习惯。适合刚学完MySQL安装配置、准备系统过一遍表操作的初学者也适合工作了一两年但对某些细节还比较模糊的同学用来查漏补缺。我一直认为SQL这种东西不需要背语法但一定要理解每条语句背后的执行逻辑。比如为什么更新数据前要先查一遍为什么删除大量数据时TRUNCATE比DELETE快那么多这些为什么搞明白了你写出来的SQL就会自然带上一层专业感而不是停留在能跑就行的层面。2. 建表是增删查改的地基字段类型、约束、字符集怎么选很多人一上来就急着写INSERT和SELECT结果表结构设计得一塌糊涂后面查询慢、数据乱、字段不够用各种问题接踵而至。实际上改和查的体验很大程度上在建的那一步就已经决定了。2.1 字段类型选不对后面全是泪MySQL里最常用的字段类型集中在以下几类我把它们的适用场景和注意事项整理一下整数类型TINYINT1字节、SMALLINT2字节、MEDIUMINT3字节、INT4字节、BIGINT8字节。按存储范围选比如状态值用TINYINT就够主键或订单号用BIGINT更稳妥。不要滥用BIGINT每条记录多占4字节千万级数据量差距就很明显了。小数类型DECIMAL(M,D)用于精确计算比如金额、单价、汇率一定要用DECIMAL而不是FLOAT或DOUBLE。FLOAT和DOUBLE是浮点存储存在精度丢失问题做金额计算时会出现0.10.2不等于0.3这种尴尬。字符串类型CHAR定长、VARCHAR变长。VARCHAR需要指定长度长度不是越大越好——VARCHAR(255)和VARCHAR(5000)在内存排序时消耗完全不同。超长文本用TEXT但注意TEXT类型不能有默认值而且索引上有更多限制。时间类型DATE年月日、TIME时分秒、DATETIME年月日时分秒、TIMESTAMP时间戳。存业务时间用DATETIME存自动记录创建/更新时间可以配合DEFAULT CURRENT_TIMESTAMP使用。TIMESTAMP有2038年问题而且受时区影响容易踩坑。布尔类型MySQL没有真正的BOOLEAN用TINYINT(1)代替0表示假1表示真。2.2 约束不只是限制更是在帮数据兜底建表时最常见的约束有以下几类主键约束PRIMARY KEY每张表都建议有主键用来唯一标识一行。主键天然是唯一索引而且InnoDB存储引擎的聚簇索引就是基于主键构建的。没有主键的表InnoDB会偷偷选一个唯一索引或生成隐藏主键这会产生不必要的开销。推荐用自增INT主键或BIGINT主键业务唯一标识如身份证号另建唯一索引。非空约束NOT NULL字段值不允许为空。设计时想清楚哪些字段业务上必须有值比如用户表的用户名、订单表的金额。NULL在查询时非常麻烦WHERE name NULL是查不到数据的必须写IS NULL。唯一约束UNIQUE KEY保证字段或字段组合的值不重复比如用户名、手机号。把唯一约束建好比在代码里先查再插要可靠得多。默认值约束DEFAULT不给值时自动填充比如状态字段默认0创建时间默认CURRENT_TIMESTAMP。外键约束FOREIGN KEY保证关联数据的完整性但实际开发中很多团队刻意不用外键而是在应用层维护一致性。原因是外键在插入、删除时会触发额外的检查影响性能而且大表之间用外键后续分库分表时会很痛苦。如果你是初学者学习阶段可以建外键帮助理解关系正式项目中可以先不加但要在设计文档里明确关联关系。2.3 字符集和排序规则别等乱码了才想起来建表时最好显式指定字符集不要依赖MySQL默认值。当前的主流选择是CREATE TABLE user ( ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;utf8mb4是真正的四字节UTF-8能存储emoji和生僻字而老旧的utf8在MySQL里最多三字节存个emoji就直接报错。排序规则utf8mb4_unicode_ci在几乎所有场景下都够用它的特点是大小写不敏感如果业务上有大小写敏感需求可以用utf8mb4_bin或utf8mb4_general_ci按需选择。这里有个很容易忽略的点如果数据库、表、字段三层的字符集不一致查询时可能触发隐式转换明明字段上有索引却走不了。所以建库的时候就统一用utf8mb4后面能省掉大量编码噩梦。2.4 一个完整的建表示例下面用一个用户表和订单表来演示你可以直接复制到本地MySQL跑一遍CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; USE shop; CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone CHAR(11) NOT NULL COMMENT 手机号, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, 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_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id INT UNSIGNED NOT NULL COMMENT 用户ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;这个例子里有几个细节值得注意自增主键配UNSIGNED正数范围直接翻倍用户名和手机号都建了唯一索引从数据库层面杜绝重复注册updated_at用ON UPDATE CURRENT_TIMESTAMP自动更新省去应用层每次手动维护订单表的amount用DECIMAL(10,2)而不是FLOAT金额计算不会出现精度问题。3. 插入数据不只是INSERT INTO那么简单建好表之后第一件事就是往里塞数据。INSERT看起来简单但实际使用中也分很多场景每一种的写法和注意事项都不一样。3.1 最基础的单行插入INSERT INTO user (username, phone, status) VALUES (张三, 13800138000, 1);这里我显式列出了字段名这是个好习惯。如果省略字段名直接写VALUES就必须和表结构完全一致哪天表结构加了个字段这条SQL就废了。显式列字段名后没有列出的字段会自动用默认值比如created_at会自动填当前时间id会自动自增。3.2 批量插入提升效率需要插入多条数据时不要一条一条执行而是把多组VALUES拼在一条SQL里INSERT INTO user (username, phone, status) VALUES (李四, 13800138001, 1), (王五, 13800138002, 1), (赵六, 13800138003, 0);一次插入几十几百行比循环单条插入快一个数量级。原因是减少了客户端和服务器之间的网络往返次数。实测过在本地插入1万条数据批量插入可能1秒以内完成单条循环能到几十秒。应用层用JDBC的话还可以配合rewriteBatchedStatementstrue参数来开启真正的批量执行。3.3 从查询结果直接插入这是复制数据最常见的场景比如要把用户表里的部分数据归档到历史表或者把一张临时表的数据合并到正式表INSERT INTO orders_archive (id, user_id, amount, status, created_at) SELECT id, user_id, amount, status, created_at FROM orders WHERE created_at 2024-01-01;这种写法的核心价值是避免先把数据查出来再逐条插入整个过程在数据库内部完成效率非常高。唯一要注意的是INSERT和SELECT的字段顺序要严格对应哪怕字段名不同也没关系位置对上就行。3.4 插入时的冲突处理实际开发中很常见的场景批量插入时某几条数据已经存在你不想让整条SQL报错而是希望存在就跳过或存在就更新。MySQL提供了两种方案-- 存在唯一键冲突时忽略新数据 INSERT IGNORE INTO user (username, phone, status) VALUES (张三, 13800138000, 1); -- 存在唯一键冲突时更新指定字段 INSERT INTO user (username, phone, status) VALUES (张三, 13800138000, 1) ON DUPLICATE KEY UPDATE phone VALUES(phone), status VALUES(status);INSERT IGNORE适合有就跳过的幂等场景ON DUPLICATE KEY UPDATE适合数据同步场景比如从外部系统拉取数据存在就更新关键字段。这里要注意触发条件是基于主键或唯一索引的冲突不是所有重复都能触发。另外VALUES()函数在MySQL 8.0.20之后被标记为废弃建议用别名写法但绝大多数老代码里仍然是VALUES()短期内仍然兼容。3.5 插入操作的两个隐形坑第一个坑是自增ID跳号。你可能遇到过这种情况插入失败了几条后面的ID直接跳了几十甚至几百。这通常是事务回滚导致的——自增ID一旦分配就不会回收哪怕事务后来回滚了。这在业务上一般无所谓但如果你对ID连续有强迫症得提前做好心理建设。第二个坑是字段长度超出报错。比如phone CHAR(11)插入一个13位的手机号会直接报Data too long for column应用层最好提前做长度校验而不是把错误留给数据库。4. 查询数据SELECT是增删查改里最值得花时间的部分如果说整个CRUD里哪个操作最值得深入学习那一定是SELECT。因为增、删、改都有明确的条件而查询要面对海量数据、复杂条件、多表关联、统计聚合写得好不好直接影响系统性能和用户体验。4.1 从最简单的SELECT说起SELECT * FROM user;这条SQL会把user表里所有字段、所有行都取出来。功能上没错但实际开发中尽量不要直接用SELECT *写业务代码。原因有几点如果表有几十个字段而业务只需要其中3个多查出来的字段白白消耗网络流量和内存如果表结构加字段SELECT *的结果集结构会跟着变应用层的映射代码可能直接报错。更推荐的是只查需要的字段比如SELECT id, username, phone FROM user;4.2 WHERE过滤查询的核心逻辑WHERE子句决定哪些行会被返回。常用操作符可以分成几类等值/比较、!、、、、范围BETWEEN ... AND ...集合IN (...)也可以加NOT模糊匹配LIKE%表示任意多个字符_表示单个字符空值判断IS NULL、IS NOT NULL组合AND、OR、NOT举个实际例子SELECT id, username, phone, created_at FROM user WHERE status 1 AND created_at 2024-06-01 00:00:00 AND created_at 2024-07-01 00:00:00 ORDER BY created_at DESC;这段查询在找六月份注册且状态正常的用户按注册时间倒序排列。这里有个细节时间范围判断用和的组合而不是BETWEEN加上最后一秒。因为BETWEEN 2024-06-01 AND 2024-06-30 23:59:59虽然看着直观但如果有记录的时间精确到微秒就可能漏掉最后一秒内的数据。用左闭右开区间最稳妥。关于LIKE要记住一个性能常识LIKE %关键字%这种写法因为%在开头MySQL无法使用普通索引优化只能全表扫描。如果业务确实需要模糊搜索要么只查前缀匹配的LIKE 关键字%要么引入全文索引、ES等方案。当然数据量小时无所谓但量大了这就是性能瓶颈。4.3 ORDER BY与LIMIT排序和分页排序的关键是理解排序字段和索引的关系。比如SELECT id, username FROM user ORDER BY created_at DESC LIMIT 10;如果created_at上有索引并且查询没有其他条件干扰MySQL可能直接按索引顺序扫描避免文件排序filesort。如果ORDER BY和WHERE里用了不同的字段排序就很可能走filesort数据量大时查询会明显变慢。分页是另一门学问。最常见的写法是SELECT * FROM orders ORDER BY id LIMIT 0, 20; -- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20, 20; -- 第二页这在小数据量上没问题但翻到很深的页数时就出问题了。比如LIMIT 1000000, 20MySQL并不是直接跳过100万行而是先取100万零20行再丢掉前100万行。这种深分页慢的根本原因在于扫描了大量无用数据。优化方案包括用WHERE id 上次最大id ORDER BY id LIMIT 20这种基于游标的方式或者先子查询取主键再回表SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20;4.4 聚合查询与GROUP BY统计场景是SQL的强项常用的聚合函数有COUNT()、SUM()、AVG()、MAX()、MIN()。配合GROUP BY可以对分组后的数据进行各自统计SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE status 1 GROUP BY user_id HAVING order_count 3 ORDER BY total_amount DESC;这个查询统计了每个已支付用户的订单数和总金额然后筛出订单数不低于3的用户。这里有个语法细节WHERE是先过滤每一行原始记录再分组HAVING是分组之后再过滤聚合结果顺序不能搞错。想按聚合结果过滤只能用HAVING且HAVING中引用的别名在MySQL里是允许的别的数据库不一定支持。COUNT(*)和COUNT(字段)的区别也经常被问到COUNT(*)统计行数会包含字段值为NULL的行COUNT(字段)统计该字段非NULL的行数。大多数场景用COUNT(*)就对了MySQL对COUNT(*)有专门的优化。4.5 多表关联JOIN的使用逻辑实际业务中数据很少只存在一张表里。比如订单表里只有user_id要显示下单用户的名字就得关联user表SELECT o.id, u.username, o.amount, o.created_at FROM orders o INNER JOIN user u ON o.user_id u.id WHERE o.status 1 ORDER BY o.created_at DESC;INNER JOIN只返回两边都匹配上的行。LEFT JOIN则会保留左表所有行右表没匹配到就用NULL填充这个在查有哪些用户没下过单时特别好用SELECT u.id, u.username FROM user u LEFT JOIN orders o ON u.id o.user_id WHERE o.id IS NULL;JOIN的性能注意事项小表驱动大表MySQL优化器会自己判断但你在写的时候尽量用「过滤范围更小」的表作为驱动表连接字段尽量有索引多表JOIN超过三张时要警惕数据量级的笛卡尔积放大效应。4.6 子查询的坑与替代方案子查询写法直观但早期MySQL版本对子查询的优化能力比较弱有些包含子查询的SQL会被优化成低效的执行计划。一个经典场景-- 查出所有订单金额大于平均金额的订单 SELECT id, user_id, amount FROM orders WHERE amount (SELECT AVG(amount) FROM orders);标量子查询问题不大但IN子查询在某些版本上存在优化瓶颈-- 查出有订单的用户老写法 SELECT * FROM user WHERE id IN (SELECT user_id FROM orders); -- 更稳妥的写法 SELECT DISTINCT u.* FROM user u INNER JOIN orders o ON u.id o.user_id;关联改写后执行计划往往更可控。MySQL 5.7以后子查询优化提升明显但为了线上稳定遇到复杂子查询我还是习惯先看EXPLAIN再决定要不要改写。5. 更新数据UPDATE的安全意识比语法更重要UPDATE的语法非常简洁核心风险不在语法而在你有没有想清楚影响哪些行。5.1 基础UPDATE的正确姿势UPDATE user SET status 0 WHERE username 张三;通常的做法是先写WHERE条件确认范围再写SET。更严谨的操作顺序是先在应用层SELECT一下确认满足条件的记录确实是你想改的那批再执行更新。5.2 忘记WHERE条件的翻车现场做开发和运维最怕的就是执行了UPDATE user SET status 0;没有WHERE限制会更新全表所有行而且不会有任何确认弹窗。如果你还开启了自动提交MySQL直接物理落盘连反悔的余地都没有。虽然可以通过binlog回滚但过程极其痛苦。一个特别实用的防线是MySQL的安全更新模式safe update modeSET SQL_SAFE_UPDATES 1;开启后UPDATE和DELETE如果没有带WHERE条件或者WHERE条件里没有使用索引字段MySQL会直接报错拒绝执行。强烈建议开发环境的会话里都加上这一句能帮你拦住一堆低级失误。注意它是会话级别的新连接需要重新设置。5.3 批量更新与JOIN更新需要根据另一张表的数据来更新当前表时可以这样UPDATE orders o INNER JOIN user u ON o.user_id u.id SET o.user_name u.username WHERE u.status 1;这种写法把查出来再循环更新的工作在数据库里一次完成性能好很多。但要注意JOIN UPDATE会同时锁定两张表中涉及的行如果表很大或并发操作很多锁范围扩大会拖垮其他事务。5.4 更新操作务必配合事务如果一次更新涉及多行数据、多张表或者更新失败后需要恢复现场一定要用事务包起来START TRANSACTION; UPDATE orders SET status 1 WHERE id 1001; UPDATE user SET total_spent total_spent 99.00 WHERE id 10; COMMIT;事务的意义在于要么全部成功要么全部回滚。上面的场景是修改订单状态和累计用户消费金额如果第一句执行成功、第二句出差错没有事务的话数据就处于不一致状态。加了事务后任一步出错都可以ROLLBACK回到起点。实际开发中建议所有写操作都在服务层开启事务并设置合理的隔离级别默认REPEATABLE READ一般够用。5.5 UPDATE语句的常见坑更新后自增ID不变UPDATE不会修改自增主键只有删除后重新插入才会占用新ID。SET顺序影响旧值引用SET col col 1这种是安全的因为MySQL先读旧值再写新值。但如果SET a b, b a这种交叉赋值最终值和你想的可能不一样因为MySQL不是同时赋值。实测中老版本和新版本行为可能不同最好不要写出依赖赋值顺序的SQL。大量更新导致锁等待一次更新几百万行时InnoDB会加很多行锁并占用undo日志空间很容易导致其他事务阻塞。建议分批更新比如每次WHERE id BETWEEN ... LIMIT 5000多跑几轮对生产环境影响小得多。6. 删除数据DELETE与TRUNCATE的选择以及误删的挽回方案删除是CRUD里最危险的操作没有之一。你永远不希望在生产环境执行一条不带条件的DELETE。6.1 DELETE的基本用法DELETE FROM user WHERE id 1001;只删除满足条件的行。DELETE操作在InnoDB里是逐行删除并记录binlog的所以如果你删了大量数据注意看下binlog文件大小和磁盘空间。删除操作产生的undo日志如果事务没提交其他事务还能看到旧数据如果大量数据在一个事务里删掉又长时间不提交会拖慢整个实例。6.2 DELETE与TRUNCATE的核心区别对比项DELETETRUNCATE条件过滤支持WHERE不支持直接清空全表逐行触发会逐行删除可事务回滚一次性释放表空间不可回滚自增IDID继续接着涨ID重置从1开始速度慢数据量大时尤其明显快秒级完成表结构保留保留如果你确定要把整张表的数据清空、但保留表结构用TRUNCATE TABLE比DELETE FROM快得多。但注意TRUNCATE不能和事务一起回滚虽然它隐式提交但执行了就是执行了没有后悔药。另外有外键约束的表不能TRUNCATE会报错需要先删外键或改用DELETE。6.3 误删数据后的急救思路真误删了也别完全慌。以下几种恢复思路按优先级排列没提交还能回滚如果删除在一个事务里还没COMMIT直接ROLLBACK。物理备份恢复有全量备份binlog可以恢复到误删时间点附近这是最稳妥的生产恢复方案。binlog反向解析通过binlog2sql这类工具解析binlog找到误删前镜像反向生成INSERT语句。要求开启binlog_formatROW最好是在误删后立刻停止写入防止binlog被刷掉。延迟从库有些团队在主库误删后立刻从从库把数据捞回来——如果从库的复制还没来得及执行那段DELETE。作为个人学习阶段最实用的建议是删除前先SELECT确定范围删除时用事务包好删除后立刻检查影响行数。6.4 删除操作和索引的关系你可能注意到大量删除之后表的查询速度会下降。原因是InnoDB删除大量行后索引和表空间会出现页分裂和碎片。解决方式有两种对于全表清空直接用TRUNCATE对于按条件删除大量数据后想整理碎片可以用ALTER TABLE ... ENGINEInnoDB来重建表ALTER TABLE orders ENGINEInnoDB;这条SQL会重建表并整理索引释放碎片空间。生产环境执行时要评估表大小和影响MySQL 5.6以后普通表的这个操作是INPLACE在线执行的但大表还是建议维护窗口期操作。7. 修改表结构ALTER TABLE的常用场景与生产环境注意事项前面说了表的操作不只是数据层面还包含表结构的修改。这个内容教科书经常一笔带过但实际开发中经常遇到需求变更要加字段、字段长度不够要改、查询慢了要加索引。我单独用一节来讲因为这里面的坑也挺多。7.1 增加、修改、删除字段-- 增加字段 ALTER TABLE user ADD COLUMN nickname VARCHAR(50) NULL COMMENT 昵称; -- 修改字段类型/默认值 ALTER TABLE user MODIFY COLUMN phone CHAR(11) NOT NULL DEFAULT COMMENT 手机号; -- 修改字段名和类型 ALTER TABLE user CHANGE COLUMN nickname nick_name VARCHAR(80) NULL COMMENT 昵称; -- 删除字段 ALTER TABLE user DROP COLUMN nick_name;MODIFY和CHANGE的区别MODIFY只改字段定义不改字段名CHANGE可以同时改字段名和定义而且需要写两遍字段名旧名和新名。删除字段前务必确认业务代码里没有引用不然应用启动直接报字段不存在。7.2 添加和删除索引-- 添加普通索引 ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- 添加唯一索引 ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no); -- 添加联合索引 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); -- 删除索引 ALTER TABLE orders DROP INDEX idx_user_id;联合索引的设计是门学问核心是最左前缀原则。比如(user_id, status)联合索引可以支持WHERE user_id 1的查询也可以支持WHERE user_id 1 AND status 1的查询但单独的WHERE status 1用不上这个索引。设计时要把最常作为过滤条件的字段放前面。7.3 修改表名ALTER TABLE user RENAME TO member;改表名后所有相关的存储过程、视图、外键、业务代码里的表名都要同步更新。批量改表名在生产环境比较常见于分表场景比如把orders表改名为orders_2024_06然后新建orders。7.4 大表ALTER的在线DDL问题这是生产环境比较容易翻车的地方。MySQL 5.6之前ALTER TABLE几乎都是COPY算法先把数据复制到临时表再切换回来期间原表加锁禁止读写。5.6之后引入了在线DDL部分操作可以INPLACE执行并允许并发DML。所以执行ALTER前先确认MySQL版本然后用ALGORITHM参数显式指定ALTER TABLE orders ADD INDEX idx_amount (amount), ALGORITHMINPLACE;如果MySQL判断无法INPLACE会直接报错而不是默默回退到COPY模式这能帮你提前发现问题。另外即便是INPLACE操作大表上的DDL也可能消耗大量CPU和磁盘IO建议业务低峰期执行。8. 表操作中的高频死坑与性能习惯这些坑是我在实际开发和帮别人排查问题时反复遇到的单独列出来每一个都对应真实的教训。8.1 字符集混乱N个表N种编码如果建库时统一用的是utf8mb4基本不会遇到这个问题。但很多老项目是历史原因各表字符集不统一联合查询对比字符串时就会报Illegal mix of collations。解决办法是查询时显式指定排序规则SELECT * FROM orders o INNER JOIN member_archive m ON o.user_id m.user_id WHERE o.note m.note COLLATE utf8mb4_unicode_ci;但根治还是靠统一表结构、迁移存量数据。8.2 NULL值带来的查询陷阱NULL参与计算时结果也是NULL所以WHERE amount NULL永远查不到数据必须写IS NULL字符串和NULL拼接结果还是NULL聚合函数SUM会自动忽略NULL行但COUNT(字段)不忽略NULL。设计表时能用NOT NULL DEFAULT兜底的字段就不要让它空着。8.3 索引失效的几大原因查询慢时第一反应是看索引但很多时候你发现明明建了索引却不生效常见的索引失效场景包括对索引字段使用函数WHERE DATE(created_at) 2024-06-01改成created_at 2024-06-01 AND created_at 2024-06-02就能走索引。隐式类型转换字段是VARCHAR你传数字进去MySQL会做转换导致索引失效。比如phone 13800138000应该写成phone 13800138000。模糊匹配%在开头前面说过的LIKE %xx。OR连接非索引字段WHERE id 1 OR username 张三如果username没索引整个查询可能放弃索引。每一条SQL执行前用EXPLAIN看一眼执行计划type是ALL或ref都会告诉你当前查询的性能状况。养成这个习惯能少踩90%的SQL性能坑。8.4 LIMIT深分页和COUNT大表的性能问题前面讲了深分页的优化方案。再补充一个经典的场景SELECT COUNT(*) FROM orders统计总行数在千万级InnoDB表上会非常慢因为InnoDB不像MyISAM那样直接存储行数需要逐行统计。如果业务非常频繁地请求总行数要么加缓存要么接受延迟统计不要每次都打全表。8.5 事务里别混着大量SELECT项目里有人图省事在一个事务里执行大量查询再做少量更新还长时间不提交。这会导致两个问题一是事务太长undo日志膨胀二是持锁时间过长其他业务被阻塞。写操作事务保持短小精悍查询不放事务里是基本素养。9. 一个综合练习从建表到业务统计的完整流程把上面说到的知识串起来我用一个模拟的电商用户订单统计例子演示从建表到完成统计的完整流程。你可以直接在本地MySQL环境跑一遍这块内容会帮你把零散的知识点连成线。-- 1. 建库建表 CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; USE demo; CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, phone CHAR(11) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 2. 插入测试数据 INSERT INTO user (username, phone) VALUES (user1, 13800000001), (user2, 13800000002), (user3, 13800000003); INSERT INTO order (user_id, amount, status) VALUES (1, 100.00, 1), (1, 200.00, 1), (2, 50.00, 1), (3, 300.00, 0), (1, 80.00, 1); -- 3. 查询所有已支付订单以及用户名字 SELECT u.username, o.amount, o.created_at FROM order o INNER JOIN user u ON o.user_id u.id WHERE o.status 1 ORDER BY o.created_at DESC; -- 4. 统计每个用户已支付订单的总金额和订单数只显示有订单的用户 SELECT u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent FROM user u LEFT JOIN order o ON u.id o.user_id AND o.status 1 GROUP BY u.id, u.username HAVING order_count 0 ORDER BY total_spent DESC;第4条SQL里有个细节JOIN条件里带上o.status 1比在WHERE里过滤更合理因为它保证LEFT JOIN时未支付的订单以NULL形式保留不影响用户分组统计同时又不会把没有订单的用户过滤掉。如果你把过滤条件放到WHERE里LEFT JOIN就退化成了INNER JOIN。建议你自己动手把这条SQL跑一遍再对比用WHERE o.status 1的结果差异对理解JOIN的执行逻辑会特别有帮助。10. 把表操作练扎实后下一步该学什么表操作的CRUD练熟之后你已经具备了操作MySQL的核心能力。但要真正在生产环境里游刃有余后面还有几条线值得接着啃。第一是索引原理。为什么BTree能支撑千万级数据查询为什么联合索引要遵循最左前缀为什么覆盖索引能消除回表这些问题的答案直接决定了你写出的SQL是走索引还是全表扫描。第二是事务与锁机制。ACID怎么实现的MVCC是什么行锁、间隙锁在什么场景下会触发并发更新同一行时如何避免死锁这不仅是面试高频题也是排查线上数据问题的基础能力。第三是性能优化。从慢查询日志分析、EXPLAIN解读、到分页优化、批量写入优化、甚至主从复制和读写分离这是一条非常长但非常值得的路。最后再结合我自己的体会说几句表的增删查改看起来简单但恰恰是这些基础操作构成了你在数据库领域的天花板。很多人工作两三年写SQL还是靠拼凑遇到问题就百度核心原因就是CRUD的基本功没有真正形成体系。建议不要只满足于能跑出结果多用EXPLAIN观察执行计划多想想每条SQL在InnoDB内部是怎么执行的。等这几件事成了习惯你会发现自己排查问题、写优化方案的能力会有一个明显的跳跃。先把表操作这关彻底过去后面的路自然就顺了。