折腾MySQL也快十年了今天要聊的这套基础增删查改其实是每个后端同学都绕不过去的看家本事。不管你是刚接触数据库的小白还是写了几年业务代码但没系统梳理过SQL细节的老手把增删查改这四类语句吃透都能让后面写报表、调接口、修数据时少踩很多坑。我会把安装环境、库表设计、CRUD实操、排序分组、事务锁、索引优化和常见报错全部串起来给你一套可以直接照着敲的完整路线。1. 环境准备装一个能用的MySQL比想象中麻烦1.1 安装方式怎么选很多人卡在第一步不是SQL不会写而是MySQL装不上。网上搜“mysql安装教程”能出来一堆版本Windows有exe安装包和解压版Linux有yum、apt和rpm还有用Docker的。我自己的建议是个人学习首选Windows解压版或Docker生产环境用Linux包管理器或官方rpm。Windows下最省心的是zip解压版。去官网下载mysql-8.0.x-winx64.zip解压后新建一个my.ini配置文件核心配置就三块[mysqld] basedirD:/mysql-8.0.36-winx64 datadirD:/mysql-8.0.36-winx64/data port3306 character-set-serverutf8mb4然后用管理员身份打开cmd进入bin目录执行mysqld --initialize-insecure mysqld --install net start mysql初始化参数--initialize-insecure的意思是生成一个无密码的root账户方便首次登录后再修改。这里有个容易踩的坑如果不加参数直接启动mysqlddata目录是空的服务根本起不来日志里会提示找不到系统表。用Docker的话更简洁直接拉官方镜像docker run -d --name mysql8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD123456 mysql:8.0但网上经常有“docker安装mysql失败”的帖子大多数情况是端口被占、宿主机3306已被其他MySQL实例占用或者容器内数据卷权限不对。遇到这种问题第一步先用docker ps -a看容器状态再docker logs mysql8看报错不要盲目删镜像重来。Linux生产环境我一般用CentOS的rpm安装MySQL 5.7或8.0好处是systemd会帮你管理服务。装完后第一件事是查看初始密码grep temporary password /var/log/mysqld.log这句话基本是MySQL运维入门必须记住的官方安装方式生成的临时密码就在日志里。1.2 登录验证和基础命令装好之后用root登录:mysql -uroot -p登录成功后先跑几条命令确认环境SELECT VERSION(); SHOW DATABASES;如果安装的是5.7版本默认字符集是latin1强烈建议改成utf8mb4。修改方式可以直接在my.ini或my.cnf里加character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci改完重启服务不然中文存储后查询会出现乱码。2. 库表设计增删查改开始前的关键一步2.1 建库建表的基本思路很多人上来就写INSERT结果发现字段类型不对、字符集不对、主键没建后面越改越乱。我自己在带项目时要求新人必须先想清楚三件事字段用什么类型、要不要主键、哪些列需要索引。建库用一条语句CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4; USE school;建议显式指定DEFAULT CHARACTER SET utf8mb4避免继承服务器默认值。这里也呼应了一个热搜词“mysql设置默认值为0”建表时就可以用DEFAULT 0来定义数字字段默认值比如一个积分字段score INT NOT NULL DEFAULT 0建表的基本原则是能用INT就不要用VARCHAR存数字能用DATE就不要用VARCHAR存日期定长字符串用CHAR不定长用VARCHAR。主键通常用自增ID格式写成id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY2.2 一套简单但够用的示例表为了演示增删查改我设计一个“学生课程成绩”的小库正好也对应了热搜词里的“学生课程成绩信息实体表设计mysql”。三张表就够了CREATE TABLE student ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT NOT NULL DEFAULT 1 COMMENT 1男 2女, birthday DATE, class_name VARCHAR(100), create_time DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, course_no VARCHAR(20) NOT NULL UNIQUE, course_name VARCHAR(100) NOT NULL, credit DECIMAL(4,1) DEFAULT 0.0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE score ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NOT NULL, course_id INT UNSIGNED NOT NULL, score DECIMAL(5,2) DEFAULT NULL, exam_time DATETIME, KEY idx_student_course (student_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有个细节score表的关联字段必须和student.id、course.id类型一致否则关联查询时索引失效。有些老库会把外键约束写进去我个人的经验是业务系统尽量不建物理外键用应用层保证完整性方便后续分库分表。2.3 从SQL脚本执行开始网上常有人问“mysql执行sql脚本怎么写”其实就是一句mysql -uroot -p school school.sql或者进入mysql命令行后执行SOURCE /path/to/school.sql;执行之前先确认脚本里有USE school;否则表会建到默认库里。3. 增删查改四板斧核心语法与实战细节3.1 INSERT插入数据单条插入是最基础的INSERT INTO student (stu_no, name, gender, birthday, class_name) VALUES (2024001, 张三, 1, 2005-03-12, 高一(3)班);这里推荐始终把字段列表写出来不要省略直接写VALUES。好处是表结构一旦调整比如中间新增了字段省略写法直接报错或错位而显式字段写法还能保证可读性。批量插入可以这样写性能比逐条INSERT好很多尤其是初始化几万条测试数据时INSERT INTO student (stu_no, name, gender, birthday, class_name) VALUES (2024002, 李四, 1, 2005-07-21, 高一(3)班), (2024003, 王五, 2, 2005-01-15, 高一(4)班), (2024004, 赵六, 2, 2004-12-30, 高二(2)班);如果要插入的数据来自另一张表用INSERT INTO ... SELECTINSERT INTO temp_student (stu_no, name) SELECT stu_no, name FROM student WHERE class_name 高一(3)班;这条语句在数据迁移、初始化临时表时非常实用。这里还有个容易出现的坑自增主键插入失败再重试ID并不会回退。比如插入失败一次下次成功插入的ID可能从2开始这是InnoDB的正常机制不要大惊小怪。3.2 SELECT查询与条件过滤查询永远是CRUD里用得最多的。最基础的是全表查询SELECT * FROM student;但生产环境尽量不要用SELECT *。你无法预判表字段以后会加多少大字段如果表里有个TEXT或BLOB字段全表查询会把大量无用数据加载到内存白白浪费IO。写业务时按需选取字段才是正经做法。条件过滤的几个关键写法SELECT stu_no, name, class_name FROM student WHERE class_name 高一(3)班 AND gender 1;模糊查询用LIKE注意百分号的位置SELECT * FROM student WHERE name LIKE 张%; SELECT * FROM student WHERE name LIKE %张%;第一个查姓“张”的人第二个查名字里包含“张”的人。%在前面会导致索引失效数据量小无所谓几百万行时就是灾难。去重用DISTINCTSELECT DISTINCT class_name FROM student;经常有人问“mysql的or能去重吗”其实OR和去重没关系但OR会破坏索引优化建议拆成UNIONSELECT * FROM student WHERE class_name 高一(3)班 UNION SELECT * FROM student WHERE class_name 高二(2)班;UNION自带去重效果如果要保留全部重复行用UNION ALL。3.3 UPDATE更新数据UPDATE语句长这样UPDATE student SET class_name 高一(2)班 WHERE stu_no 2024001;关键点在于必须有WHERE。我见过不止一次执行UPDATE student SET class_name高一(2)班把整张表的班级都改了。这种事故在面试里被翻来覆去地考实际上就是没注意WHERE。解决方法是先写SELECT确认影响范围再改成UPDATE或者用事务包起来START TRANSACTION; UPDATE student SET class_name 高一(2)班 WHERE stu_no 2024001; SELECT * FROM student WHERE stu_no 2024001; COMMIT;如果发现update数据不对在事务中直接ROLLBACK;就能还原。热搜词里有个“mysql update 还原”其实指的就是这种事务回滚方式以及利用备份恢复。另一个细节是UPDATE时如果想在原有字段基础上累加直接写UPDATE student SET score score 10 WHERE id 1;这条看起来简单但在并发场景下比“先SELECT再计算再UPDATE”安全得多。3.4 DELETE删除数据删除同样必须带WHEREDELETE FROM score WHERE student_id 1;DELETE不加WHERE就是清空全表。清空全表还有另一条语法TRUNCATE TABLE temp_student;区别是DELETE支持回滚TRUNCATE相当于直接删除表再重建速度极快但不可回滚。如果只是删除部分数据必须用DELETE。当然更稳妥的做法是逻辑删除给表加一个is_deleted字段默认0UPDATE student SET is_deleted 1 WHERE id 1;查询时统一过滤SELECT * FROM student WHERE is_deleted 0;逻辑删除的好处是保留历史轨迹后续做数据分析和误删恢复都有余地。4. 从基础走向实用排序、分组与多表关联4.1 排序与分页热搜词里有“mysql排序”这个太常用了。按分数从高到低排列SELECT student_id, course_id, score FROM score ORDER BY score DESC;DESC是降序ASC是升序默认升序。多个字段排序时注意顺序SELECT * FROM score ORDER BY course_id ASC, score DESC;这条先按course_id升序相同course_id内再按score降序。把有索引字段放在前面通常更高效。排序配合分页才是查询的王牌组合SELECT * FROM score ORDER BY score DESC LIMIT 10 OFFSET 20;意思是从第21行开始取10行。MySQL 8.0还支持更简洁的写法LIMIT 20, 10两条等价。分页查询在数据量大的时候OFFSET越翻越大效率越低。一个常见优化办法是记录上一页最后一条IDSELECT * FROM score WHERE score 95 ORDER BY score DESC LIMIT 10;用这种“键集分页”方式即使翻到第100页也不会越来越慢。4.2 分组与聚合GROUP BY通常和聚合函数一起用。比如统计每个班的人数SELECT class_name, COUNT(*) AS cnt FROM student GROUP BY class_name;统计每门课的平均分、最高分、最低分SELECT course_id, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM score GROUP BY course_id;GROUP BY有两个坑。第一个坑是SQL模式里ONLY_FULL_GROUP_BY开启后SELECT后面的非聚合字段必须出现在GROUP BY中否则直接报错。比如上面这条如果把student_id也放SELECT里就会报错因为一个课程有多个学生搞不清楚要哪个。第二个坑是分组后再过滤要用HAVING不能用WHERESELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id HAVING avg_score 90;WHERE在分组前过滤HAVING在分组后过滤这个顺序记死就能避开一半的SQL报错。4.3 多表关联查询真正上业务之后单表查询绝对不够用。学生成绩表只有student_id和course_id要显示姓名和课程名必须关联。经典的内连接SELECT s.name, c.course_name, sc.score FROM score sc JOIN student s ON sc.student_id s.id JOIN course c ON sc.course_id c.id;三个表JOIN一次搞定。LEFT JOIN是左外连接左表数据全部保留右表没有匹配则以NULL显示。比如要查所有学生及他们的成绩即使某些学生没有成绩也要出现SELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.id sc.student_id;这里我建议写关联条件时一律用表名.字段的方式别图省事写字段名否则三个表JOIN后字段名冲突你都不知道错在哪。关联查询还有一个细节关联字段必须有索引。如果score.student_id没索引JOIN会把student表驱动score表进行全表扫描数据量大时直接卡死。5. 让CRUD更稳事务、锁与隔离级别5.1 事务ACID为什么重要CRUD里写单条SQL很简单但真实业务往往是多条SQL的组合。比如学生选课既要往course表插入记录又要往score表插入成绩还得更新学生选课数量。三步操作只要一步失败数据就乱了。事务就是来兜底的START TRANSACTION; UPDATE student SET course_count course_count 1 WHERE id 1; INSERT INTO score (student_id, course_id, score) VALUES (1, 102, 88); COMMIT;如果INSERT报错直接ROLLBACKUPDATE的修改也会被撤回。事务的ACID特性里一致性最容易理解要么都成功要么都失败。这也是面试常问的“mysql事务处理”背后真正的逻辑。5.2 锁的分类与锁表场景热搜词里“mysql锁的分类”和“mysql锁表”都是高频搜索。锁分成两大类按粒度分有表锁和行锁按锁定模式分有共享锁和排他锁。InnoDB默认支持行锁但行锁只有在查询和更新能用到索引时才会生效。上面提到的UPDATE student SET ... WHERE id 1由于id是主键锁住的是这一行。如果WHERE条件不带索引InnoDB会退化成锁全表。不少线上事故就是这么来的比如UPDATE student SET score score 10 WHERE name 张三;name字段如果没有索引更新时会锁住student表的所有行。平时排查锁表问题最常用的命令是SHOW ENGINE INNODB STATUS;看里面的LATEST DETECTED DEADLOCK和TRANSACTIONS部分。还有一个查询当前运行事务与锁等待的命令SELECT * FROM performance_schema.data_locks;遇到过锁表导致后续SQL全部卡住的情况多半是有事务一直没提交。解决办法先找到阻塞源SELECT * FROM sys.innodb_lock_waits;按结果定位到阻塞的事务ID和SQL语句果断KILL掉对应线程KILL 12345;5.3 隔离级别怎么选事务并发会产生脏读、不可重复读、幻读MySQL通过隔离级别来平衡。查看当前隔离级别SELECT transaction_isolation;MySQL默认是REPEATABLE READ可重复读这也是InnoDB在事务隔离上的默认策略。设置成READ COMMITTED能减少锁范围很多互联网公司会切成这个级别。设置方法SET GLOBAL transaction_isolation READ-COMMITTED;注意设置了GLOBAL之后已存在的连接不会即时生效需要重新连接。5.4 存储过程与函数搜“mysql存储过程”的也很多基础CRUD掌握后存储过程可以帮你把多条SQL封装起来。比如批量给某个班学生赋分DELIMITER $$ CREATE PROCEDURE add_score_for_class(IN p_class VARCHAR(100), IN p_course INT, IN p_score DECIMAL(5,2)) BEGIN INSERT INTO score (student_id, course_id, score) SELECT id, p_course, p_score FROM student WHERE class_name p_class; END$$ DELIMITER ;调用CALL add_score_for_class(高一(3)班, 101, 90);存储过程适合固定逻辑的批量操作但现在微服务架构下并不建议把业务逻辑写进数据库因为很难做版本管理和测试。我倾向于只在定时任务、报表汇总这类场景使用。6. 索引与性能调优6.1 索引类型与创建方式热搜词里“mysql创建索引”、“mysql性能调优”都是大热点。索引的作用可以类比成书的目录没有索引就得一页页翻。常见的索引类型有普通索引加速查询唯一索引加速查询且字段值不能重复联合索引多个字段组合成一个索引全文索引用于全文检索中文分词场景不太方便一般用ES创建普通索引CREATE INDEX idx_name ON student(name);创建唯一索引CREATE UNIQUE INDEX idx_stu_no ON student(stu_no);联合索引最容易踩坑核心原则是“最左前缀”。比如建了(class_name, gender)联合索引查询条件里必须出现class_name才能用到只有gender的话索引失效。CREATE INDEX idx_class_gender ON student(class_name, gender);可以命中索引的写法SELECT * FROM student WHERE class_name 高一(3)班 AND gender 1;无法命中索引的写法SELECT * FROM student WHERE gender 1;6.2 用EXPLAIN分析慢SQL写完SQL先别急着跑用EXPLAIN看一下执行计划是职业习惯EXPLAIN SELECT s.name, sc.score FROM score sc JOIN student s ON sc.student_id s.id WHERE sc.course_id 101;重点关注type字段从好到差大致是system const eq_ref ref range index ALL。看到ALL代表全表扫描这个SQL基本要优化了。看到key是NULL说明没走索引排查是不是WHERE列没索引或者有函数运算。一个很常见的索引失效场景SELECT * FROM student WHERE YEAR(birthday) 2005;这种对字段使用函数的方式即使birthday有索引也白搭正确写法是改成范围查询SELECT * FROM student WHERE birthday 2005-01-01 AND birthday 2006-01-01;6.3 常用命令速查整理一下日常维护经常用的命令方便照着敲-- 查看所有库 SHOW DATABASES; -- 查看当前库所有表 USE school; SHOW TABLES; -- 查看表结构 DESC student; -- 查看建表语句 SHOW CREATE TABLE student; -- 查看索引 SHOW INDEX FROM student; -- 查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log;慢查询日志在排查性能问题时非常好用可以在配置文件中打开slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1超过1秒的SQL都会记录到slow.log然后就能用EXPLAIN逐一分析。7. 常见问题与排查技巧实录7.1 SQL语法与配置报错我把这些年遇到的高频问题整理成了一张速查表报错现象常见原因解决思路ERROR 1064 (42000)SQL语法错误检查引号、逗号、关键字拼写ERROR 1142 (42000)权限不足GRANT授权或用root登录ERROR 1045 (28000)密码错误或账号不存在检查用户名密码必要时跳过授权重置ERROR 1055 (42000)违反ONLY_FULL_GROUP_BYSELECT字段全部加入GROUP BY或用聚合函数ERROR 1172 (42000)查询返回多行却被当单行处理检查子查询是否意外返回多行ERROR 1366 (HY000)字符集不匹配导致中文乱码表和连接统一使用utf8mb4ERROR 1410 (42000)权限相关操作语法错误检查GRANT语句格式ERROR 1451 (23000)外键约束阻止删除先删除子表记录或禁用外键检查其中ERROR 1045遇到最多忘记root密码也很常见。重置MySQL 8.0 root密码的常规操作是# 停止服务 net stop mysql # 安全模式启动 mysqld --skip-grant-tables --console # 新开一个终端 mysql -uroot FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY NewPass123; EXIT;注意你是在跳过授权表模式下执行ALTER USER必须先FLUSH PRIVILEGES否则密码不生效。7.2 锁表、连接数满等运行问题除了语法问题运行期最多的是连接数被占满。登录数据库后查看SHOW VARIABLES LIKE max_connections; SHOW STATUS LIKE Threads_connected;Threads_connected接近max_connections说明有连接泄漏。常见原因是应用层没有用连接池每条SQL都新建连接没释放。临时提高连接数SET GLOBAL max_connections 500;但重启后就没了要永久生效得改配置文件写max_connections500。锁表问题在5.2已经讲过这里再补充一个参考命令。查看正在执行的慢SQLSELECT * FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC;能看到卡了很久的事务和对应的SQL再配合KILL命令处理。这条命令在线上排查时价值极高。7.3 备份恢复其实和CRUD有关当一个基础增删查改玩熟了下一步就是备份恢复。备份本质上就是把数据导出来再导进去。最快最简单的备份是mysqldump -uroot -p school school_backup.sql恢复mysql -uroot -p school school_backup.sql如果只需要导出某张表mysqldump -uroot -p school student student_backup.sql还有更细的只要数据不要建表语句mysqldump -uroot -p --no-create-info school score score_data.sql恢复之前先确认目标库存在否则MySQL会报“Unknown database”。另外mysqldump备份出的文件默认不包含事件和存储过程需要加参数mysqldump -uroot -p --routines --events school full_backup.sql这个细节很多人忘记备份完才发现存储过程全丢了。7.4 我的一些后端编程经验最后再分享一个配合编程语言执行CRUD的小技巧。不管是用Java的MyBatis还是Python的PyMySQL都应该使用预编译参数占位而不是手动拼接SQL字符串。比如Python里cursor.execute( SELECT * FROM student WHERE stu_no %s, (stu_no,) )千万不要写成cursor.execute(fSELECT * FROM student WHERE stu_no {stu_no})后者一旦stu_no里带了单引号轻则SQL报错重则被注入攻击。基础增删查改看着简单但这层安全意识必须刻在骨子里。我在实际排查中遇到过不少“昨天还能查今天就报错”的情况九成是配置文件被动过、连接数满了或者磁盘满了。建议每个后端同学都把information_schema.processlist和SHOW ENGINE INNODB STATUS这两条命令练成肌肉记忆CRUD之外的稳定运行才是一个新手向老手转变的分水岭。