1. 从零开始搭建MySQL环境安装选择的背后逻辑1.1 为什么选择MySQL作为入门数据库在正式聊增删改查之前得先把环境这关过了。你现在搜mysql安装教程、mysql下载官网、mysql安装配置教程大概率是刚接触数据库或者准备做数据相关的项目。MySQL可以说是最适合入门的关系型数据库没有之一。原因很简单它免费、跨平台、资料多随便搜个问题都能找到一堆解法而且企业里用它的比例极高。MySQL本质上是一个关系型数据库管理系统数据以表的形式组织表和表之间靠公共字段建立关联。这和Excel表格有点像但强就强在它能同时处理大量读写、保证数据一致性、支持并发访问。比如你做一个电商后台用户下单的同时可能在查询商品、更新库存这些操作如果交给Excel根本撑不住MySQL就能通过事务和锁机制保证不出乱子。我见过不少新手上来直接装最新版其实没必要。选版本这个事我的建议是如果你用的是Windows系统直接下载MySQL 8.0.x系列这是目前最主流的稳定版本如果你用的是Linux服务器5.7和8.0都有大量生产案例5.7.44是5.7系列的最终版本对老项目兼容性最好。搜索词里提到的mysql 5.7.44安装过程详细、rpm安装mysql、linux mysql 8.0.44下载说明很多人在服务器上装的时候踩过坑后面我会专门把这些坑捋一遍。1.2 Windows和Linux环境下的安装实操对比Windows环境下的安装最省心的方式是使用安装包MSI Installer。MySQL 8.0的MSI安装包会自动处理大部分配置包括服务注册、环境变量、初始密码设置对新手非常友好。装的时候有几步容易忽略一个是选安装类型时选“Server only”就行不用装那些自带工具后续用命令行或者单独装可视化工具都可以另一个是设密码时建议选“Use Strong Password Encryption”这是8.0的默认推荐方式认证插件用caching_sha2_password安全性更高。装完之后验证是否成功可以在命令行里执行mysql --version如果能正常输出版本信息说明客户端工具已经可用了。接着启动服务在Windows服务管理器里找到MySQL服务名字一般是MySQL80确认状态是“正在运行”。然后登录mysql -u root -p输入安装时设置的root密码看到mysql提示符就算成功了。Linux环境下的安装方式就多样了常见的有yum/apt在线安装、rpm包离线安装、源码编译安装。绝大多数服务器场景下用yum或rpm就够不建议源码编译除非你有特殊的性能优化需求。以CentOS为例从官方yum源安装wget https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm rpm -ivh mysql80-community-release-el7-3.noarch.rpm yum update yum install mysql-community-server注意搜索词里提到的mysql 5.7.44官方为什么之后是5.7.43呢——这个其实是因为版本命名的跳号5.7.43之后5.7.44是正常的补丁序列不存在什么奇怪的问题。如果你在rpm安装时遇到依赖错误常见的解决思路是先执行yum install -y epel-release再重试。装完Linux版的MySQL第一步是启动服务systemctl start mysqld systemctl enable mysqld这里有个很多人不知道的点MySQL 5.7及以上版本在初始化后会生成一个临时密码放在日志文件里你需要先查出来才能登录grep temporary password /var/log/mysqld.log然后登录改密码mysql -u root -p ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;1.3 环境配置的五个常见坑第一个坑是端口被占用。MySQL默认监听3306端口如果你机器上同时装了SQL Server或者别的数据库软件很可能冲突。解决方法是安装时指定别的端口或者改my.cnf/my.ini配置文件的port3307。第二个坑是防火墙没放行。Linux服务器上即使MySQL服务正常外部机器也连不上大概率是防火墙的问题firewall-cmd --zonepublic --add-port3306/tcp --permanent firewall-cmd --reload第三个坑是root用户远程登录被拒。MySQL默认root只能本地登录这是出于安全考虑。但开发环境里你可能需要远程连接这时候需要单独建一个账号CREATE USER dev% IDENTIFIED BY dev_password; GRANT ALL PRIVILEGES ON *.* TO dev%; FLUSH PRIVILEGES;把%换成具体IP可以限定访问来源。第四个坑是时区不对。连接MySQL时如果发现时间比本地时间差8小时登录后执行SET GLOBAL time_zone 8:00;或者写进配置文件里default-time-zone08:00重启服务永久生效。第五个坑是字符集。如果你插入中文数据出现乱码十有八九是字符集设置问题。MySQL 8.0默认字符集是utf8mb4但5.7及更早版本默认是latin1这和你的终端、程序编码不一致就会出现乱码。SELECT character_set_database;如果结果不是utf8mb4修改配置文件[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci改完要重启服务。这个坑几乎每个人都会遇到建议在看到“插入中文变成问号”时第一时间检查字符集。2. 增删改查的全貌理解CRUD不只是写四条SQL2.1 CRUD思维的底层逻辑增删改查这四个字翻译成专业术语就是CRUDCreate、Read、Update、Delete。这是所有数据操作的基础但很多新手只把它当成四个命令去背这是不对的。CRUD背后代表的其实是业务系统处理数据的完整生命周期数据从哪来、怎么存储、怎么被读取、怎么变更、怎么删除。你写任何业务系统从最简单的记账本到复杂的电商平台核心逻辑都逃不开这四件事。前端表单提交的数据需要“增”列表页展示的数据需要“查”编辑页修改后的内容需要“改”被删除的记录需要“删”。理解了这一点你再看增删改查就不会觉得它只是语法问题了。它决定了你的表结构怎么设计、字段怎么约束、权限怎么控制。举个例子。你设计一张用户表CREATE TABLE user_info ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, email VARCHAR(100), status TINYINT DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );这张表在建表时就内置了业务约束id主键自增保证唯一性username唯一并且非空防止重复注册status默认1表示账号可用。后面所有的增删改查都是在这个表结构约束下进行的。所以CRUD的第一步不是写INSERT而是设计表结构。2.2 增删改查对应的高频应用场景“增”最常见的是注册功能、下单功能、录入数据功能。对应SQL是INSERT核心关注点是字段完整性和唯一性冲突。“查”是最复杂的简单的是单表查询复杂的会涉及多表JOIN、聚合函数、排序分页。热门词里提到的mysql排序、mysql锁的分类其实都是在“查”或“改”的场景里出现的。“改”对应UPDATE通常配合WHERE条件使用不然你更新的就是整张表。搜索词里提到的mysql设置默认值为0和UPDATE关系不大而是CREATE TABLE/ALTER TABLE时设置默认值的问题。“删”分物理删除和逻辑删除。物理删除就是DELETE语句猛删数据逻辑删除是在表里加个is_deleted字段查数据时只查is_deleted0的记录这种方式在企业系统里更常用因为数据是企业资产不能轻易物理抹掉。2.3 设计CRUD操作时需要考虑的四个维度第一性能维度。数据量小的时候怎么写都行但数据量到了百万级每条SQL都要考虑索引、表扫描、连接顺序。增删改查不是写完就完事还得看执行计划。第二安全维度。SQL注入是最常见的攻击方式核心就是你的SQL里拼接了用户输入。正确的做法是用参数化查询/预编译语句而不是字符串拼接。第三一致性维度。一个业务操作往往涉及多条增删改查比如转账需要扣钱和加钱两步任何一步失败都要整体回滚这就要用到事务。第四可维护性维度。SQL不能散落在业务代码的各个角落建议统一封装成Dao层或者Mapper层。热门词里提到的基于mybatis-plus的通用crud服务就是这一维度的企业级实践。3. 增删改查核心语法与实操要点3.1 INSERT插入数据时最容易忽略的细节基础语法INSERT INTO user_info (username, password, email) VALUES (zhangsan, 123456, zhangsanexample.com);也可以一次插入多条INSERT INTO user_info (username, password, email) VALUES (lisi, 123456, lisiexample.com), (wangwu, 123456, wangwuexample.com), (zhaoliu, 123456, zhaoliuexample.com);一次插入多条比逐条执行性能高很多在需要初始化数据或者批量导入时一定要这么写。另一个实用语法是INSERT ... ON DUPLICATE KEY UPDATE。如果表里的username设置了唯一索引你插入的数据已存在默认会报错。加上这句话后能自动转为更新操作INSERT INTO user_info (username, password, email) VALUES (zhangsan, abc123, newmailexample.com) ON DUPLICATE KEY UPDATE password abc123, email newmailexample.com;这在同步数据场景下非常有用比如你从Excel导入数据到数据库很多数据重复了用这个语法可以一键“有则更新无则插入”省去先查询再判断的麻烦。实际插入数据时有三件需要注意的事。第一字段值要和字段类型匹配字符串字段用单引号包裹数字不用。第二如果字段有默认值插入时可以省略比如status和created_at。第三大批量插入时要把autocommit关掉插入完统一提交速度能快一个数量级。3.2 SELECT查询的进阶玩法基础语法SELECT * FROM user_info; SELECT username, email FROM user_info WHERE status 1;*一定要慎用。原因很简单第一不需要的字段也会拉出来浪费带宽和内存第二如果以后表结构变了程序拿到的字段顺序会跟着变代码可能报错。我见过有人查一张十几列的表用SELECT *结果网络IO高居不下改成只查需要的字段后性能立竿见影。WHERE条件的坑也不少。注意NULL是不能用比较的要写成IS NULL。字符串比较要小心隐性转换比如数据库字段是字符串你查的ID是数字MySQL会自动把字符串转成数字再比较索引就失效了。排序和分页是最常用的组合SELECT id, username, created_at FROM user_info WHERE status 1 ORDER BY created_at DESC LIMIT 10 OFFSET 0;ORDER BY指定排序字段DESC是倒序ASC是正序。LIMIT限制返回条数OFFSET是跳过多少条。上面这条SQL就是查第一页的10条数据。分页查询有个性能雷一定要讲LIMIT的偏移量越大查询越慢。改成下面的写法能大幅提升深分页性能SELECT id, username, created_at FROM user_info WHERE id 1000 AND status 1 ORDER BY id LIMIT 10;核心思路是先定位到上一页最后一条记录的id然后从id后面开始取不用跳过前面大量数据。聚合统计也是查询的高频场景。比如统计用户总数SELECT COUNT(*) FROM user_info; SELECT status, COUNT(*) FROM user_info GROUP BY status;想要过滤聚合之后的结果用HAVING它和WHERE的区别是WHERE在分组前过滤HAVING在分组后过滤。3.3 UPDATE更新数据前必须养成一个好习惯基础语法UPDATE user_info SET email new_emailexample.com WHERE id 1;UPDATE最大的坑是忘记加WHERE。一旦没加条件整张表的记录都会被更新。这类事故几乎每个月都能在网上看到。所以我在实际执行UPDATE前一定先把WHERE条件单独跑一遍SELECT确认影响范围SELECT id, email FROM user_info WHERE id 1;确认确实只想改这一条再执行UPDATE。UPDATE还有一个容易被忽略的细节就是SQL_SAFE_UPDATES这个模式。如果你用的是MySQL Workbench默认开启了安全更新模式直接在图形界面里执行不带WHERE的UPDATE会报错提示不允许不带条件的更新。这个机制其实是保护你的命令行模式下也可以用SET SQL_SAFE_UPDATES 1;更新之后想让updated_at字段自动刷新前提是建表时设置了ON UPDATE CURRENT_TIMESTAMP否则就要手动更新这个字段。3.4 DELETE删除数据时的手抖风险与逻辑删除方案基础语法DELETE FROM user_info WHERE id 1;DELETE不带WHERE同样是灾难级的操作整张表的数据直接清空。千万不要在生产环境里用DELETE FROM 表名这种写法除非你确定就是要清空全部数据。清空数据更推荐用TRUNCATE它会把整张表重建速度比DELETE快得多但不可按条件筛选。物理删除的另一个问题是自增ID断档。你删了id为5的记录再插入新数据新数据的id是6而不是5。如果业务上对ID连续有要求物理删除就出问题了。所以现在主流做法是逻辑删除。给表加一个字段比如is_deleted TINYINT DEFAULT 0删除操作不是真的DELETE而是UPDATE user_info SET is_deleted 1 WHERE id 1;查询时默认带上AND is_deleted 0。这样数据还在表里随时能恢复业务上也能追溯历史记录。注意逻辑删除后如果业务系统用到了唯一约束被“删除”的数据还会占据约束名额需要额外设计比如把username改成“原值_deleted_时间戳”。3.5 精通增删改查必须掌握的三个周边技能第一个是索引。增删改查的性能高地就在索引。索引相当于书的目录没有索引MySQL查找数据只能全表扫描。在经常查询的字段上加索引CREATE INDEX idx_username ON user_info(username); CREATE INDEX idx_status_created ON user_info(status, created_at);注意一个误区不是索引越多越好。索引也占磁盘空间而且每次INSERT/UPDATE/DELETE时MySQL要同步更新索引写操作会变慢。只有高频查询的字段才值得加索引。第二个是事务。事务保证一组操作要么全部成功要么全部失败。经典场景是转账START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果第二条UPDATE失败执行ROLLBACK即可回滚。MySQL默认autocommit是开着一条语句执行完自动提交。多条语句要作为一个整体提交时手动开启事务。事务的四个特性ACID就不用背了理解成“要么全做要么全不做做完不能半吊子”就行。第三个是备份恢复。你在热搜词里看到数据备份与恢复这其实是每个开发必须掌握但经常被忽略的技能。日常用的备份命令是mysqldumpmysqldump -u root -p mydb /backup/mydb.sql恢复mysql -u root -p mydb /backup/mydb.sql恢复之前要确认目标数据库存在不存在就先建库CREATE DATABASE mydb CHARACTER SET utf8mb4;热点词里提到的数据恢复场景包括误删数据、服务器故障、数据库迁移核心就是备份要定期、要异地、要演练恢复过程。备份文件打不开等于没备份。4. 事务、锁与存储过程让数据从“能用”到“靠谱”4.1 事务隔离级别的选择与踩坑实录MySQL支持四种事务隔离级别分别对应不同的一致性和并发性能隔离级别脏读不可重复读幻读适用场景READ UNCOMMITTED会出现会出现会出现极少用不推荐READ COMMITTED不会会出现会出现Oracle默认互联网常用REPEATABLE READ不会不会会出现MySQL默认SERIALIZABLE不会不会不会数据强一致性能低MySQL默认的REPEATABLE READ看起来比其他数据库默认级别高性能上也没吃太多亏靠的是MVCC多版本并发控制。具体体现在一条普通的SELECT查询不会加锁通过版本快照读到一致性数据读操作和写操作互不阻塞。这对OLTP系统很友好。实际开发中如果你做的是电商库存类系统锁的使用非常频繁。热点词里提到的mysql锁的分类常用的分类主要有这么几种按锁粒度分有表锁、行锁、页锁按操作类型分有共享锁读锁和排他锁写锁。InnoDB引擎支持行锁也是使用最多的锁机制。常见的死锁场景是这样的事务A更新了id1的记录同时想更新id2的记录事务B先更新了id2的记录再想更新id1的记录。两个事务互相等对方的锁就死锁了。MySQL会自动检测死锁并让其中一个事务回滚但你需要做的是在代码层面避免这种交叉顺序。核心原则是多个事务按固定顺序访问资源。4.2 存储过程的应用与维护成本存储过程是一组预编译的SQL语句集合可以像函数一样被调用。用存储过程的好处是什么一是减少网络传输复杂逻辑在数据库端执行不用应用服务器和数据库之间来回传数据二是统一逻辑多个应用都可以调用同一个存储过程不会出现同一逻辑不同实现的问题三是性能优势预先编译第一次执行后缓存执行计划。下面是一个带输入参数的存储过程DELIMITER $$ CREATE PROCEDURE GetUser(IN userId INT) BEGIN SELECT id, username, email FROM user_info WHERE id userId; END$$ DELIMITER ;调用CALL GetUser(1);存储过程写起来不难但运维和调试成本偏高。很多互联网公司都有一个不成文规定业务逻辑尽量放在应用层数据库只负责数据存储和高性能查询。因为存储过程一旦出现bug排查逻辑繁琐而且版本管理困难不像应用代码那样方便走Git Flow。我的建议是简单封装型存储过程可以用但超过几十行的复杂业务逻辑别写进存储过程。数据库不是业务引擎它擅长的是数据存取不是复杂流程编排。4.3 事务实际操作时的经验总结第一点事务尽量短小。一个事务里塞了太多SQL意味着占用的锁时间更长并发性能会严重下降。事务里只放必须保证一致性的操作其他的放在事务外面。第二点不要在事务中做远程调用。事务期间锁着数据行如果这时去调用外部接口、等待响应整个数据库的连接会被长时间占用非常容易把连接池打满。第三点注意事务内的数据读取。同一个事务里多次读取同一行数据在REPEATABLE READ下结果是一致的不用担心数据被别的会话改了。但如果用到联表查询事务隔离级别不同可能会出现数据不一致的体验差异这块要用场景来决定隔离级别不能盲目套默认值。5. 常见报错和排查方法增删改查之外这些问题反而最花时间5.1 连接类报错与断点排查思路登录MySQL时最常见的报错是Access denied for user rootlocalhost。原因通常是三种密码错、账号错、账号权限不足。先确认密码如果密码没问题查看用户和权限表SELECT user, host, plugin FROM mysql.user WHERE user root;如果你的root账号的plugin显示为caching_sha2_password而你是用老版客户端连接会报认证失败。解决办法是切换认证插件ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码;还有一种情况是用localhost连接时失败改用127.0.0.1反而成功这通常是MySQL的bind-address只绑定了特定地址或者MySQL对含代理/隧道场景做了限制。5.2 SQL执行报错的快查表下表汇总了我这些年遇到频率最高的SQL执行报错线下分享时也经常拿出来做排查演示。报错信息原因解决办法Unknown column xxx字段名拼错核对表结构字段名Duplicate entry 1 for key PRIMARY主键冲突改INSERT为ON DUPLICATE KEY UPDATE或更新ID策略Data too long for column xxx字段长度不足修改字段类型长度Incorrect integer value: 空字符串塞进了整型字段应用代码转成NULL或0Lock wait timeout exceeded锁等待超时查慢SQL、查事务持有锁情况Out of range value for column数值超出字段范围修改字段类型如INT改BIGINTTable xxx doesnt exist表不存在或库没选对USE先选库或表名写成库名.表名Out of range value这个报错值得多说一句很多新手在设计表时就埋了雷。比如用户积分字段用TINYINT最大值只能到127一个活动发128积分就报错了。字段类型尽量按业务增长量往上留余地状态码用TINYINT没问题但计数、金额、ID这类数据要选INT或BIGINT。5.3 锁定问题的排查案例实录线上系统突然出现大量慢SQL排查发现大量Lock wait timeout exceeded; try restarting transaction报错。当时的操作步骤是这样的先查当前哪些事务在运行SELECT * FROM information_schema.INNODB_TRX;trx_state为RUNNING且trx_started时间太早的事务基本就是锁的源头。再查锁的具体情况SELECT * FROM performance_schema.data_lock_waits;然后顺着查找到一条没有提交的UPDATE事务它锁住了目标表某几行后续大量的UPDATE都在排队等锁。归根结底是程序里那个事务执行了太久没有COMMIT。和开发确认后该事务逻辑有问题原先该短事务的地方走进了长查。解决方式是调整事务代码及时释放锁资源。事后总结排查思路很简单遇到锁等待先查INNODB_TRX看谁在持有事务再查data_lock_waits看谁在等待然后根据锁源事务的SQL去找业务代码绝大多数是事务没提交或事务内做了过重操作。正常的事务提交速度是毫秒级如果出现长时间RUNNING状态基本可以判断是代码问题。5.4 备份恢复场景中的一次实战复盘一次线上误删数据事件的处理过程可以作为备份恢复的完整参考。当时有开发在测试环境执行了不带WHERE的DELETE把一张用户行为表清空了好在生产环境有每天凌晨的全量备份。恢复流程是用mysqldump生成的备份文件直接导入到一张全新的恢复库中。确认数据完整后将恢复库中的目标表导出。再导入到生产环境。具体命令mysql -u root -p -e CREATE DATABASE recover_db CHARACTER SET utf8mb4; mysql -u root -p recover_db /backup/mydb_20240901.sql mysqldump -u root -p recover_db user_behavior /tmp/user_behavior.sql mysql -u root -p mydb /tmp/user_behavior.sql这个方案的风险点在于如果备份之后生产环境又有新的增量数据直接覆盖会丢失最近一天的数据。更稳的方案是用binlog做时间点恢复。核心原则是日常备份加上binlog归档才能做到任意时间点的数据恢复。那之后我们规定每天全备一次另外开启binlog万一需要恢复先恢复全备再应用binlog中到事故点之前的数据变更。6. 常用工具选择与进阶方向参考6.1 命令行、可视化工具与IDE插件的搭配如果你刚开始学增删改查命令行是必过的一关。用命令行能看到SQL真实执行过程、能方便调试也能在服务器上用。但日常开发中我更推荐配合可视化工具典型的有Navicat、DBeaver、DataGrip。Navicat是很多人的首选功能全面支持数据同步、结构同步、导入导出但对开发者来说收费不便宜。DBeaver是免费开源的选择社区版功能足够日常使用。DataGrip是JetBrains家的如果你经常写Java和IntelliJ IDEA无缝衔接体验顺滑。工具只是辅助核心还是要能看懂SQL。用工具建表、导数据会把效率拉满但别完全依赖。6.2 下一个值得投入的数据方向热门词里出现的基于mybatis-plus实现通用crud服务、使用flink实现mysql同步到clickhouse、python结构化数据、wpf数据绑定、数据采集卡、modbus/opc ua协议读取PLC传感器设备状态这几个方向代表了增删改查之后的不同进阶路线。如果你做业务系统开发值得好好研究ORM框架比如Java的MyBatis-Plus或Spring Data JPA把DAO层的通用CRUD封装掉专注业务逻辑。如果你做数据平台方向可以研究MySQL数据同步到ClickHouse或大数据生态的流程背后的核心还是你在这里打下的增删改查基础。如果你做物联网或工业数据方向重点在如何将PLC、传感器、数控机床等设备的数据采集后入库再把采集到的数据做增删改查与分析展示。总结下来只有一句话增删改查是起点但绝对不是终点。它覆盖了数据库最核心的读写能力而在此之上所有的数据架构、数据同步、数据治理都建立在这些基础操作之上。把所有基础语法吃透把事务、锁、索引这些高阶技能补齐再往大数据方向走会顺畅很多。6.3 我的一些心得体会做了这么多年数据相关工作回头看不光是增删改查整个数据库领域最关键的其实是细心和敬畏心。写错一条SELECT顶多报个错写错一条UPDATE或者DELETE对线上来说就是事故级别的。我的习惯是任何变更操作之前先用SELECT确认影响范围任何批量操作之后赶紧看一眼受影响行数量是否符合预期。也许你觉得这一步多余但真到了出事那天你会感谢自己多想了一步。另外一个建议是笔记本常备出错记录。每个报错把错误码、SQL、场景记下来一段时间后你会发现许多坑是重复的几百条记录里真正独特的可能就二三十条。把这些核心问题掌握住数据库增删改查就已经远远超出“熟练使用”的水平了。