尧图网站建设 
1. 先把MySQL跑起来连接、建库、建表的基本功接触MySQL增删改查之前我发现很多人其实卡在最前面的几步——装好了MySQL却不知道怎么连上去也不知道建表时该设什么字段类型。这篇文章不打算讲那些高大上的架构设计就围绕我们每天都会用到的增删改查操作把里面的细节、坑和习惯掰开揉碎聊一遍。1.1 命令行连接到本地MySQL这几种方式都要会连接本地MySQL最直接的方式就是命令行。mysql -u root -p输入密码之后就能进入MySQL交互界面。如果连接远程数据库需要指定主机和端口mysql -h 192.168.1.10 -P 3306 -u root -p这里有个细节容易被忽略-P是大写的P表示端口-p是小写的p表示密码。我第一次用的时候把端口参数写成了小写p结果系统提示语法错误排查了半天。还有连接时如果不想在命令行里明文输入密码可以这样mysql -u root -p回车后手动输入密码这样历史记录里就不会留下密码信息。生产环境一定不要用-p123456这种明文密码方式。连接成功后可以先用这几个命令确认环境状态SELECT VERSION(); SHOW DATABASES; SELECT CURRENT_USER();SELECT VERSION()可以查看MySQL版本不同版本的语法和行为会有差异比如8.0版本和5.7版本在窗口函数、CTE公共表表达式等方面的支持就不同。SHOW DATABASES;查看当前实例上有哪些数据库SELECT CURRENT_USER();确认当前登录用户这在排查权限问题时很有用。1.2 建库建表时常见的三个决定字符集、排序规则、字段类型增删改查的前提是有一张表。很多人上来就CREATE TABLE对字符集、排序规则这些完全没有概念等写入中文出现乱码才开始头疼。建库时我习惯明确指定字符集CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;为什么用utf8mb4而不是utf8因为utf8在MySQL里最多存3个字节的字符像emoji表情这类4字节字符就存不进去。utf8mb4是utf8的超集能完整支持所有Unicode字符。排序规则utf8mb4_unicode_ci和utf8mb4_general_ci的区别在于精确度和排序规则前者按Unicode标准排序更准确后者排序更快但个别字符的排序结果可能不够准确。日常业务建议用utf8mb4_unicode_ci。建表时字段类型的选择也是有讲究的这里列出一些常见的对比数据类型存储大小适用场景注意事项INT4字节用户ID、数量、年龄最大值为2147483647超了要用BIGINTBIGINT8字节订单号、时间戳自增主键建议直接上BIGINTVARCHAR(n)实际长度1~2字节姓名、地址、描述n必须指定最大65535字节CHAR(n)固定n字节固定长度编码如手机号查询性能比VARCHAR略好但浪费空间DECIMAL(m,d)变长金额、单价永远不要用FLOAT存金额会有精度问题DATETIME8字节业务时间存储范围1000-01-01到9999-12-31TIMESTAMP4字节日志时间范围到2038年且受时区影响TEXT变长长文本内容无法设置默认值索引需要指定前缀长度一个典型的用户表设计可以这样CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone CHAR(11) DEFAULT NULL COMMENT 手机号, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 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), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这里有几个细节值得展开说说。BIGINT UNSIGNED意味着不允许负数能存到的最大值为18446744073709551615对绝大多数业务来说完全够用。AUTO_INCREMENT配合主键让每条数据有唯一标识。DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这两句非常实用插入数据时自动写入当前时间更新数据时自动更新时间省掉了在业务代码里手动维护时间的麻烦。还有一个容易忽视的点类型后面加了UNSIGNED之后如果UPDATE时把负数赋给该字段MySQL 8.0默认会报错而5.7可能只是警告并截断为0。这种版本差异在排查问题时需要留意。1.3 自增主键到底要不要什么场景不用自增主键是大多数表的标准配置因为它有两个天然优势插入数据时只追加不移动索引紧凑主键值连续查询范围时性能好。但有一类场景我不建议用自增主键——分布式系统。当多个实例同时写入数据时自增ID无法全局唯一这时候雪花算法、UUID才是更好的选择。MySQL 8.0的UUID可以通过UUID()函数生成也可以用UUID_SHORT()生成较短的数字型全局唯一ID。不过在单库单表环境下老老实实用BIGINT AUTO_INCREMENT就够了不用为了看起来高大上引入额外的复杂度。注意InnoDB表的主键选择直接影响插入性能。没有明确主键时InnoDB会优先选第一个非空唯一索引作为主键都没有时则会生成隐藏主键。这个隐藏主键没有业务意义还会额外占用存储空间。所以每张表都要显式定义主键。2. INSERT插入数据从单条到批量细节决定效率2.1 单条插入和批量插入的性能差距插入语句的基本格式是INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00);这种写法一次插一条语法上是没问题的。但如果要插入上万条数据逐条INSERT的性能就很差了。每条INSERT都涉及SQL解析、权限检查、事务提交等一系列操作相当于每次都要从头走一遍完整流程。把多条数据合并成一次插入性能会有数量级的提升INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00), (李四, 13900139000, 200.00), (王五, 13700137000, 300.00);我实测过插入10万条数据的场景逐条插入耗时约90秒批量插入每批1000条耗时约3秒。这个差距在生产环境中是非常明显的。批量插入时还要注意一个边界单次INSERT的数据量不要太大。每次插入的数据总大小最好控制在几条MB以内否则会占用大量内存和网络带宽。稳妥的做法是分批次比如每批500到1000条批次之间稍微加一点间隔。另外补充一个写法上的细节INSERT INTO user SET username 赵六, phone 13600136000, balance 400.00;这种列名赋值的方式在MySQL中是支持的但标准SQL中推荐使用第一种列清单方式。平时写代码时建议保持一致统一用列清单方式后续维护也方便。2.2 特殊字符、NULL与默认值插入时最容易搞错的三件事插入操作看似简单但在处理数据边界情况时容易出错。第一件容易搞错的事是特殊字符的转义。如果用户名里有单引号直接拼接SQL就会报语法错误-- 这样会报错 INSERT INTO user (username) VALUES (OReilly); -- 正确写法 INSERT INTO user (username) VALUES (OReilly);在SQL中单引号通过两个单引号转义。而在实际开发中更推荐使用参数化查询比如在Java的JDBC中使用PreparedStatement在Python中使用pymysql的execute方法传参。参数化查询不仅能自动处理转义问题还能防止SQL注入。自己拼SQL时用字符串替换函数去转义永远不如参数化来得稳。第二件容易搞错的事是NULL和默认值的处理。举一个实际业务中常见的场景有一个字段不允许为NULL且设置了默认值0INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, NULL);如果balance字段没有指定默认值且定义时加了NOT NULL这条SQL会直接报错。但如果有默认值插入NULL时可能报错也可能静默替换为默认值具体取决于MySQL的SQL模式。在严格模式下sql_mode包含STRICT_TRANS_TABLES插入NULL到NOT NULL字段会直接报错在非严格模式下可能只是警告然后插入默认值。建议开启严格模式避免数据意外被静默更正。第三件容易踩坑的事是显式插入自增ID。比如下面的语句INSERT INTO user (id, username, phone) VALUES (1001, 张三, 13800138000);某些场景下这样做是合理的比如数据迁移时为了保持原ID不变。但如果业务代码里混用显式指定ID和不指定ID两种方式可能导致自增计数错乱后续插入数据时出现Duplicate entry错误。因此非必要不显式插入自增ID。2.3 主键冲突使用INSERT时最常见的业务场景插入数据时主键或唯一键冲突非常常见。典型场景是用户点击同步按钮把远程API的数据同步到本地表。第一次同步时数据不存在直接插入第二次同步时数据已经存在需要更新。如果先查后插就存在竞态条件——两个请求同时查出不存在然后同时插入导致一个失败。更好的方案是使用MySQL的ON DUPLICATE KEY UPDATE语法INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00) ON DUPLICATE KEY UPDATE phone VALUES(phone), balance VALUES(balance);这条SQL的含义是如果插入时没有触发唯一键冲突就正常插入如果触发了就执行后面的UPDATE操作。上面的例子中phone和balance会被更新为当前要插入的新值。注意MySQL 8.0.20之后推荐使用AS NEW的别名写法不再推荐VALUES()函数INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00) AS new ON DUPLICATE KEY UPDATE phone new.phone, balance new.balance;这个写法的小改动是为了将来移除VALUES()函数做准备。如果用的是MySQL 8.0以上版本建议直接使用新写法。还有一类情况主键冲突时希望保持不变。比如统计表里只有在数据变化时更新INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00) ON DUPLICATE KEY UPDATE balance balance;看起来有点奇怪但核心思想是冲突时不更新。除此之外还可以用INSERT IGNOREINSERT IGNORE INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00);INSERT IGNORE遇到重复键直接跳过不报错也不更新。如果业务逻辑是已存在的就忽略不存在的插入这个语法比ON DUPLICATE KEY UPDATE更合适。补充一个与热搜词相关的点热搜词里出现了mysql中int5其实这是UPDATE场景中给数值加固定值的写法后面讲UPDATE时会详细展开。3. SELECT查询条件、排序、分页和聚合3.1 WHERE条件的基本规则为什么它决定了查询性能查询是增删改查里最常用的操作。基础语法这里就不再赘述了说几个高频问题和容易被忽视的规则。WHERE条件中的运算符执行优先级是固定的但书写时逻辑关系可能会让人困惑。比如SELECT * FROM user WHERE status 1 OR status 0 AND balance 100;AND的优先级高于OR所以这条SQL实际执行的是WHERE status 1 OR (status 0 AND balance 100)这和很多人直觉上的(status 1 ORstatus 0) ANDbalance 100完全不同。遇到复杂的组合条件一定不要吝啬括号。括号不仅让逻辑清晰也能防止后续维护的人误读。WHERE条件最影响查询性能的是索引的使用。一个常见误区是在索引列上做函数运算会让索引失效。例如-- 索引失效 SELECT * FROM user WHERE DATE(created_at) 2024-01-01; -- 可以走索引 SELECT * FROM user WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;第一条会对每一行的created_at做DATE函数计算MySQL无法直接利用索引定位第二种写法是范围查询索引可以正常使用。类似的在索引列上做运算、隐式类型转换都会破坏索引。比如phone列是VARCHAR类型却用数字去比较-- 隐式类型转换索引失效 SELECT * FROM user WHERE phone 13800138000;MySQL会尝试把字符串转换为数字来比较导致无法走索引。正确做法是带上引号SELECT * FROM user WHERE phone 13800138000;3.2 ORDER BY和LIMIT分页查询的实战坑排序和分页是查询中如影随形的两个操作。SELECT * FROM user ORDER BY created_at DESC LIMIT 10;这段SQL本身没有语法问题但分页越到后面越慢SELECT * FROM user ORDER BY created_at DESC LIMIT 100000, 10;MySQL执行这条SQL时需要先扫描出前100010条数据然后把前100000条丢弃只返回最后10条。扫描的数据量非常大性能自然就差。改善方法之一是使用「延迟关联」或「基于游标的分页」。基于游标的分页思路是记住上一页最后一条记录的ID或时间下一页只取比它更小的记录SELECT * FROM user WHERE created_at 2024-01-15 10:00:00 ORDER BY created_at DESC LIMIT 10;这样每次查询都只需要走索引数据量再大也能保持稳定性能。缺点是翻页时无法跳到任意页码只能一页一页往后翻。但对于大多数加载更多的应用场景来说这种分页方式完全够用。关于排序还有一个注意点ORDER BY与LIMIT一起用时如果没有ORDER BYLIMIT的结果顺序是不确定的。千万不要依赖无ORDER BY的LIMIT来取前几条。数据删除、插入都会影响物理存储顺序查出来的顺序随时可能变化。3.3 GROUP BY与聚合热搜词里ONLY_FULL_GROUP_BY的由来聚合查询涉及GROUP BY、COUNT、SUM、AVG、MAX、MIN等函数。最常见的报错是MySQL 5.7及以上版本开启了ONLY_FULL_GROUP_BY模式SELECT username, COUNT(*) FROM user GROUP BY status;这条SQL报错信息会提示username没有出现在GROUP BY子句中。SQL标准的语义是GROUP BY之后SELECT列表中的第1列必须要么是聚合函数要么是GROUP BY子句中的列。上面这条SQL中username既不是聚合函数也不在GROUP BY里因此报错。很多人碰到这个报错第一反应是关掉ONLY_FULL_GROUP_BY我不建议这么干。关掉之后虽然不报错但返回的username是组内任意一条记录的结果不确定容易产生线上数据不一致。正确的做法是改SQL把非聚合列去掉或者用聚合函数包一下。如果确实需要查出每组中的某一个具体值MySQL 8.0推荐用窗口函数。比如要查每个状态的用户中余额最高的那个SELECT username, status, balance FROM ( SELECT username, status, balance, ROW_NUMBER() OVER (PARTITION BY status ORDER BY balance DESC) AS rn FROM user ) t WHERE t.rn 1;这段SQL的执行逻辑是先按status分组在每个分组内按balance降序编号然后只取每个分组内编号为1的记录也就是每个状态下余额最高的用户。窗口函数是MySQL 8.0才引入的遇到相关需求时用它能写出既清晰又高效的SQL。3.4 JOIN热搜词里mysql数据库join含义的通俗解释JOIN的本质是把两张表的数据通过关联条件组合起来。通常见过的有INNER JOIN内连接、LEFT JOIN左连接、RIGHT JOIN右连接和CROSS JOIN交叉连接。用生活例子来类比一张user表存用户信息一张order表存用户下单记录。要查每个用户名下的订单就需要JOINSELECT u.username, o.order_no, o.amount FROM user u INNER JOIN order o ON u.id o.user_id;INNER JOIN只返回两边都匹配上的记录也就是有订单的用户及其订单。LEFT JOIN则以左表user为准返回左表所有记录右表没有匹配上的字段置NULLSELECT u.username, o.order_no, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id;这条SQL能查出包括没有下过单的用户在内的所有用户数据订单字段为NULL。写JOIN时常见的坑是关联条件不完整。如果一张表的主键是联合主键比如order_id加order_lineJOIN时只写了其中一个条件会查到大量重复数据结果行数远超预期。另外JOIN的字段类型要保持一致否则会导致索引失效。VARCHAR类型的字段和INT类型的字段直接关联时MySQL需要做类型转换转换后索引就失效了。关于JOIN的优化建议控制连接的数据量。如果user表有100万条记录order表有500万条记录直接JOIN再过滤查询会执行很久。更好的做法是先缩小每张表的范围再连接SELECT u.username, o.order_no FROM ( SELECT id, username FROM user WHERE status 1 ) u INNER JOIN ( SELECT user_id, order_no FROM order WHERE created_at 2024-01-01 ) o ON u.id o.user_id;4. UPDATE更新数据那些年全表更新的事故4.1 忘记WHERE条件的教训和防范措施UPDATE user SET balance 0;这条SQL会更新表中所有记录的balance为0。如果业务上本来就打算全表重置那没问题。但如果在生产环境中手动执行后果不可想象。一个实用的习惯是执行UPDATE之前先用同条件的SELECT确认影响范围-- 先确认 SELECT COUNT(*) FROM user WHERE status 0; -- 再更新 UPDATE user SET balance 0 WHERE status 0;MySQL客户端默认在非交互模式下执行包含WHERE的更新不需要显式确认safe-update模式是个例外。连接时加--safe-updates选项能开启保护模式强制要求UPDATE/DELETE语句带WHERE或LIMIT条件否则报错。日常开发环境建议开启mysql --safe-updates -u root -p4.2 热搜词mysql中int5与mysql中更新子查询的坑热搜词里mysql中int5看起来是类似于java那套5的值加5写法但其实SQL中就是普通的算术表达式。常见的用法UPDATE user SET balance balance 5 WHERE id 123;这行语句在并发场景下是线程安全的。即使在多个事务同时执行balance balance 5时MySQL的行锁会保证每次更新都是基于最新值。相比之下如果用SELECT先查出来再加回去可能导致丢失更新。这一点后面讲事务时会再结合具体例子展开。mysql中更新子查询是另一个典型的坑。例如想把这张表里每个用户的余额更新为订单总额最高的那个用户的订单金额-- 这种写法在MySQL中会报错You cant specify target table user for update in FROM clause UPDATE user SET balance (SELECT MAX(amount) FROM order WHERE order.user_id user.id);MySQL不允许在同一语句中对目标表做UPDATE的同时又从同一张表中SELECT。解决方案是包一层派生表UPDATE user SET balance ( SELECT max_amount FROM ( SELECT user_id, MAX(amount) AS max_amount FROM order GROUP BY user_id ) t WHERE t.user_id user.id );这个报错信息在很多低版本MySQL中都会出现新人很容易被折磨半天。4.3 UPDATE的锁与事务为什么更新要放在事务里MySQL默认每个单条SQL是自动提交的UPDATE执行完立即生效。但有时候业务需求是要么全部成功要么全部回滚——比如转账操作从A账户扣钱给B账户加钱。这两步必须作为一个整体任何一步失败都要把另一部分撤销。这时候就要用事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE account_id A; UPDATE account SET balance balance 100 WHERE account_id B; COMMIT;如果在第二条UPDATE执行时发现B账户不存在需要回滚就执行ROLLBACK;第一条对A账户的扣款也会被撤销。这里深挖一下锁的问题。上面的第一步UPDATE执行后MySQL会锁定A账户所在的行直到提交或回滚才释放。如果别的事务在这期间也要更新A账户就会被阻塞等待。这是InnoDB的默认行为也是避免并发更新的关键机制。但是在开发中常见的死锁场景是两个事务以不同顺序更新相同的记录。比如事务1先更新A再更新B事务2先更新B再更新A两个事务可能互相等对方持有的锁造成死锁。MySQL检测到死锁后会自动回滚其中一个事务。防范的关键是让多个事务以相同顺序更新记录。事务隔离级别也会影响UPDATE的可见性。默认隔离级别是REPEATABLE READ可重复读在这个级别下事务内多次SELECT同一条件结果都是一致的。如果需要验证可以用SELECT transaction_isolation;查看当前隔离级别。5. DELETE删除数据安全删除、逻辑删除和TRUNCATE5.1 删除前先确认这是保命的第一条纪律DELETE操作的语法本身不复杂DELETE FROM user WHERE id 123;但数据一旦删除就没了这个事实让DELETE成为生产环境中最危险的操作之一。删数据前应该像执行UPDATE一样先用SELECT确认-- 先确认影响行数 SELECT * FROM user WHERE status 0 AND created_at 2023-01-01; -- 再删除 DELETE FROM user WHERE status 0 AND created_at 2023-01-01;如果删错了数据MySQL没有像文件系统那样的回收站功能常规操作几乎是找不回来的。即便有备份恢复也需要时间。更稳妥的办法是在低峰期操作而且DDL/DML语句执行后及时进行逻辑备份。5.2 逻辑删除为什么推荐用deleted字段代替物理删除生产环境里用户表、订单表这些核心业务表通常不建议做物理删除。因为数据之间有关联删掉一张表的记录可能导致另一张表出现孤儿数据另一方面业务上需要保留历史数据用于分析和审计。常见的做法是加一个is_deleted字段ALTER TABLE user ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0未删除1已删除; -- 逻辑删除 UPDATE user SET is_deleted 1 WHERE id 123; -- 查询时过滤掉已删除数据 SELECT * FROM user WHERE is_deleted 0;这样数据还在但业务查询时统一过滤。代价是每条查询都要加上is_deleted 0条件。有些团队会在查询接口的公共层做统一处理避免每个SQL都漏掉这个条件。5.3 TRUNCATE和DELETE的区别不止是速度不同TRUNCATE是另一种删除操作TRUNCATE TABLE user;它和DELETE有本质区别对比项DELETETRUNCATE条件过滤支持WHERE不支持全表清空事务回滚可以回滚隐式提交无法回滚自增ID不清零重置为初始值性能逐行删除慢直接释放数据页快触发器会触发不会触发返回结果返回删除的行数不返回具体行数平时业务代码里几乎用不到TRUNCATE它更像DBA层面的操作。但有些测试环境清空数据时会用到了解它和DELETE的差异能避免误操作。6. 新手最常踩的五个报错现象、原因、解决方案6.1 ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock这个报错的热度一直很高。现象是连接本地MySQL时提示无法通过socket文件连接。原因通常是MySQL服务未启动或者socket文件路径不一致。排查思路如下# 1. 检查MySQL服务状态 systemctl status mysqld # 或者不同系统命令不同 /etc/init.d/mysql status # 2. 如果没启动启动服务 systemctl start mysqld # 3. 确认socket文件存在 ls -l /tmp/mysql.sock如果服务已启动但socket路径不对需要查看MySQL的配置文件找到socket参数。可以让客户端通过TCP方式连接mysql -h 127.0.0.1 -P 3306 -u root -p跳过socket直接使用TCP连接。这个报错在通过本地命令行连接时最常见80%的情况都是服务根本没启动或启动失败。看到这个报错先别慌第一步永远是检查服务状态。6.2 root的初始密码到底是什么安装MySQL后连不上的经典问题很多人在Linux下装完MySQL用mysql -u root -p登录输入安装时设定的密码却提示Access denied。原因可能有两个一是MySQL 5.7及以上版本在安装时会自动生成一个临时密码存放在/var/log/mysqld.log中grep temporary password /var/log/mysqld.log用临时密码登录后必须马上修改密码ALTER USER rootlocalhost IDENTIFIED BY 新的密码;这个新密码还要满足MySQL的密码复杂度要求比如长度至少8位且包含大小写字母、数字、特殊字符。如果不想这么严格可以调整validate_password的相关参数但不建议在本地学习环境之外关闭密码强度校验。二是账户允许登录的主机范围受限。默认情况下root账号的host为localhost也就是说只能从本机连接。如果要用Navicat等工具从另一台机器连这台服务器的MySQL需要创建一个host允许远程访问的账号CREATE USER app% IDENTIFIED BY 密码; GRANT ALL PRIVILEGES ON demo.* TO app%; FLUSH PRIVILEGES;%表示允许从任意IP连接。但生产环境用%要谨慎更安全的方式是指定具体IP或网段比如app192.168.1.%只允许内网某网段连接。6.3 数据库连接池报错与版本不兼容热搜词里有这么一条报错django.db.utils.NotSupportedError: MySQL 8.4 or later is required (found 8.0。这看起来是Django版本与MySQL版本不匹配导致的。新版Django要求更高版本的MySQL但系统中装的是MySQL 8.0。遇到这种问题有几个思路检查Django版本如果是新版本可以降级到兼容MySQL 8.0的Django版本。检查Python的MySQL连接驱动比如mysqlclient或pymysql的版本升级驱动也可能解决问题。确认连接字符串中是否指定了正确的数据库版本属性。部分ORM可以通过配置让驱动忽略版本检查。遇到兼容性报错最忌讳的就是盲目升级或者盲目降级。应当先查官方版本的对应关系再决定调整哪一端。6.4 MySQL 8.0的SSL连接错误与JDBC驱动的useSSL参数热搜词里有mysql ssl连接错误和mysql jdbc usessl 与 sslmode 使用两条。这类问题多出现在Java应用通过JDBC连接MySQL时。MySQL 8.0默认开启了SSL相关的支持如果JDBC连接串没有配置SSL参数可能报类似SSL connection error或Public Key Retrieval is not allowed的错误。常见的处理方式是修改连接串参数jdbc:mysql://localhost:3306/demo?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai其中useSSLfalse表示不启用SSL连接allowPublicKeyRetrievaltrue允许客户端自动获取服务器公钥这两个参数是解决此类报错最常见的方式。但要注意生产环境如果数据链路涉及公网传输不建议直接关闭SSL。正确的做法是配置SSL证书并启用加密连接而非为了省事关闭加密。内网环境安全性可控的前提下关闭SSL提升性能可以理解但要经过风险评估。6.5 ONLY_FULL_GROUP_BY等SQL模式问题这个在3.3节已详细展开过这里只需要记住排查思路执行SELECT sql_mode;查看当前模式如果包含ONLY_FULL_GROUP_BY且SQL中存在未聚合的非分组列就会报错。解决方案不是移除这个模式而是修正业务SQL。同理STRICT_TRANS_TABLES是另一个值得保留的严格模式它保证写入不符合定义的数据时直接报错而不是静默截断。所谓严格能让问题在第一时间暴露减少线上数据异常。7. 增删改查之外的三个好习惯7.1 索引不是越多越好但核心查询必须有增删改查里SELECT查询性能最依赖索引。但索引不是越多越好因为每次INSERT、UPDATE、DELETE都需要同步维护索引索引越多写入越慢。索引占存储空间。优化器选择索引也需要成本。实际项目中我遵循的原则是主键索引必须要有。高频查询的WHERE条件列、ORDER BY列、JOIN关联列尽量加索引。区分度低的列如status只有0和1不适合建索引。联合索引要遵循最左前缀原则查询条件从最左列开始才能命中索引。冗余索引要及时清理比如已经有索引(a, b)就不要再建单列索引a。以一个订单表为例常见的查询是按用户查订单、按时间范围查订单、按订单号查订单ALTER TABLE order ADD KEY idx_user_created (user_id, created_at); ALTER TABLE order ADD UNIQUE KEY uk_order_no (order_no);idx_user_created是一个联合索引同时覆盖了user_id和created_at。查询某个用户最近订单时能直接走这个索引。7.2 执行计划EXPLAIN用数据说话而不是靠猜想确认一条SQL有没有走索引最直接的方式是看执行计划EXPLAIN SELECT * FROM user WHERE username 张三;执行结果里的关键列列名作用重点关注type访问类型从好到差const eq_ref ref range index ALLkey实际使用的索引NULL表示没走索引rows预估扫描行数越小越好Extra额外信息Using filesort、Using temporary 都是性能警示type为ALL意味着全表扫描数据量大时就应该想办法优化。rows只是预估行数但能反映MySQL的成本估算。排查慢SQL时EXPLAIN是第一步先看有没有走索引再看走了哪个索引最后看有没有额外的排序、临时表操作。这个习惯能省下很多性能调优的时间。7.3 备份与恢复不是DBA也要会的基本操作哪怕是本地开发环境也应该养成定期备份的习惯。MySQL的备份方式有很多最基础的是mysqldumpmysqldump -u root -p demo demo_backup.sql恢复时执行mysql -u root -p demo demo_backup.sqlmysqldump默认导出的是SQL语句文件结构清晰适合中小规模数据库。数据量大时可以考虑使用mydumper或MySQL企业版备份工具也可以做物理备份直接复制数据文件但物理备份需要停机或使用xtrabackup等方式复杂度高一些。我个人的习惯是重要表的增删改操作前后都先做一次逻辑备份。写完这篇博客我把自己常用的几条命令整理成脚本需要时一键执行心里踏实。8. 最后再分享两个小技巧写SQL这件事门槛不高但把SQL写好、写稳、写高效是个需要长时间积累的过程。最后分享两个我实际工作中一直在用的小技巧。第一个技巧是给每条核心SQL写注释。这个注释不是把SQL本身抄一遍而是记录这条SQL的业务含义、涉及表的关系、调用它的业务场景。半年后再回来看能快速理解当年为什么这样写。第二个技巧是善于利用MySQL自带的元数据。比如查看某张表的表结构、索引信息SHOW CREATE TABLE user; DESC user; SHOW INDEX FROM user;这三个命令能快速了解一张表的设计排查问题时非常有用。特别是SHOW CREATE TABLE它会显示出表完整的DDL语句包括字符集、索引、约束等是了解表结构最快的途径。增删改查这四个操作看似基础实际藏着大量细节。字符集的选择、索引的设计、事务的使用、报错的排查每一项都能单独写一篇很长的文章。希望这篇内容能帮你在使用MySQL的路上少踩一些坑至少踩坑的时候知道怎么排查。 MySQL增删改查实战：从建库建表到SQL优化与常见报错排查
1. 先把MySQL跑起来连接、建库、建表的基本功接触MySQL增删改查之前我发现很多人其实卡在最前面的几步——装好了MySQL却不知道怎么连上去也不知道建表时该设什么字段类型。这篇文章不打算讲那些高大上的架构设计就围绕我们每天都会用到的增删改查操作把里面的细节、坑和习惯掰开揉碎聊一遍。1.1 命令行连接到本地MySQL这几种方式都要会连接本地MySQL最直接的方式就是命令行。mysql -u root -p输入密码之后就能进入MySQL交互界面。如果连接远程数据库需要指定主机和端口mysql -h 192.168.1.10 -P 3306 -u root -p这里有个细节容易被忽略-P是大写的P表示端口-p是小写的p表示密码。我第一次用的时候把端口参数写成了小写p结果系统提示语法错误排查了半天。还有连接时如果不想在命令行里明文输入密码可以这样mysql -u root -p回车后手动输入密码这样历史记录里就不会留下密码信息。生产环境一定不要用-p123456这种明文密码方式。连接成功后可以先用这几个命令确认环境状态SELECT VERSION(); SHOW DATABASES; SELECT CURRENT_USER();SELECT VERSION()可以查看MySQL版本不同版本的语法和行为会有差异比如8.0版本和5.7版本在窗口函数、CTE公共表表达式等方面的支持就不同。SHOW DATABASES;查看当前实例上有哪些数据库SELECT CURRENT_USER();确认当前登录用户这在排查权限问题时很有用。1.2 建库建表时常见的三个决定字符集、排序规则、字段类型增删改查的前提是有一张表。很多人上来就CREATE TABLE对字符集、排序规则这些完全没有概念等写入中文出现乱码才开始头疼。建库时我习惯明确指定字符集CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;为什么用utf8mb4而不是utf8因为utf8在MySQL里最多存3个字节的字符像emoji表情这类4字节字符就存不进去。utf8mb4是utf8的超集能完整支持所有Unicode字符。排序规则utf8mb4_unicode_ci和utf8mb4_general_ci的区别在于精确度和排序规则前者按Unicode标准排序更准确后者排序更快但个别字符的排序结果可能不够准确。日常业务建议用utf8mb4_unicode_ci。建表时字段类型的选择也是有讲究的这里列出一些常见的对比数据类型存储大小适用场景注意事项INT4字节用户ID、数量、年龄最大值为2147483647超了要用BIGINTBIGINT8字节订单号、时间戳自增主键建议直接上BIGINTVARCHAR(n)实际长度1~2字节姓名、地址、描述n必须指定最大65535字节CHAR(n)固定n字节固定长度编码如手机号查询性能比VARCHAR略好但浪费空间DECIMAL(m,d)变长金额、单价永远不要用FLOAT存金额会有精度问题DATETIME8字节业务时间存储范围1000-01-01到9999-12-31TIMESTAMP4字节日志时间范围到2038年且受时区影响TEXT变长长文本内容无法设置默认值索引需要指定前缀长度一个典型的用户表设计可以这样CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone CHAR(11) DEFAULT NULL COMMENT 手机号, balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 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), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这里有几个细节值得展开说说。BIGINT UNSIGNED意味着不允许负数能存到的最大值为18446744073709551615对绝大多数业务来说完全够用。AUTO_INCREMENT配合主键让每条数据有唯一标识。DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这两句非常实用插入数据时自动写入当前时间更新数据时自动更新时间省掉了在业务代码里手动维护时间的麻烦。还有一个容易忽视的点类型后面加了UNSIGNED之后如果UPDATE时把负数赋给该字段MySQL 8.0默认会报错而5.7可能只是警告并截断为0。这种版本差异在排查问题时需要留意。1.3 自增主键到底要不要什么场景不用自增主键是大多数表的标准配置因为它有两个天然优势插入数据时只追加不移动索引紧凑主键值连续查询范围时性能好。但有一类场景我不建议用自增主键——分布式系统。当多个实例同时写入数据时自增ID无法全局唯一这时候雪花算法、UUID才是更好的选择。MySQL 8.0的UUID可以通过UUID()函数生成也可以用UUID_SHORT()生成较短的数字型全局唯一ID。不过在单库单表环境下老老实实用BIGINT AUTO_INCREMENT就够了不用为了看起来高大上引入额外的复杂度。注意InnoDB表的主键选择直接影响插入性能。没有明确主键时InnoDB会优先选第一个非空唯一索引作为主键都没有时则会生成隐藏主键。这个隐藏主键没有业务意义还会额外占用存储空间。所以每张表都要显式定义主键。2. INSERT插入数据从单条到批量细节决定效率2.1 单条插入和批量插入的性能差距插入语句的基本格式是INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00);这种写法一次插一条语法上是没问题的。但如果要插入上万条数据逐条INSERT的性能就很差了。每条INSERT都涉及SQL解析、权限检查、事务提交等一系列操作相当于每次都要从头走一遍完整流程。把多条数据合并成一次插入性能会有数量级的提升INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00), (李四, 13900139000, 200.00), (王五, 13700137000, 300.00);我实测过插入10万条数据的场景逐条插入耗时约90秒批量插入每批1000条耗时约3秒。这个差距在生产环境中是非常明显的。批量插入时还要注意一个边界单次INSERT的数据量不要太大。每次插入的数据总大小最好控制在几条MB以内否则会占用大量内存和网络带宽。稳妥的做法是分批次比如每批500到1000条批次之间稍微加一点间隔。另外补充一个写法上的细节INSERT INTO user SET username 赵六, phone 13600136000, balance 400.00;这种列名赋值的方式在MySQL中是支持的但标准SQL中推荐使用第一种列清单方式。平时写代码时建议保持一致统一用列清单方式后续维护也方便。2.2 特殊字符、NULL与默认值插入时最容易搞错的三件事插入操作看似简单但在处理数据边界情况时容易出错。第一件容易搞错的事是特殊字符的转义。如果用户名里有单引号直接拼接SQL就会报语法错误-- 这样会报错 INSERT INTO user (username) VALUES (OReilly); -- 正确写法 INSERT INTO user (username) VALUES (OReilly);在SQL中单引号通过两个单引号转义。而在实际开发中更推荐使用参数化查询比如在Java的JDBC中使用PreparedStatement在Python中使用pymysql的execute方法传参。参数化查询不仅能自动处理转义问题还能防止SQL注入。自己拼SQL时用字符串替换函数去转义永远不如参数化来得稳。第二件容易搞错的事是NULL和默认值的处理。举一个实际业务中常见的场景有一个字段不允许为NULL且设置了默认值0INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, NULL);如果balance字段没有指定默认值且定义时加了NOT NULL这条SQL会直接报错。但如果有默认值插入NULL时可能报错也可能静默替换为默认值具体取决于MySQL的SQL模式。在严格模式下sql_mode包含STRICT_TRANS_TABLES插入NULL到NOT NULL字段会直接报错在非严格模式下可能只是警告然后插入默认值。建议开启严格模式避免数据意外被静默更正。第三件容易踩坑的事是显式插入自增ID。比如下面的语句INSERT INTO user (id, username, phone) VALUES (1001, 张三, 13800138000);某些场景下这样做是合理的比如数据迁移时为了保持原ID不变。但如果业务代码里混用显式指定ID和不指定ID两种方式可能导致自增计数错乱后续插入数据时出现Duplicate entry错误。因此非必要不显式插入自增ID。2.3 主键冲突使用INSERT时最常见的业务场景插入数据时主键或唯一键冲突非常常见。典型场景是用户点击同步按钮把远程API的数据同步到本地表。第一次同步时数据不存在直接插入第二次同步时数据已经存在需要更新。如果先查后插就存在竞态条件——两个请求同时查出不存在然后同时插入导致一个失败。更好的方案是使用MySQL的ON DUPLICATE KEY UPDATE语法INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00) ON DUPLICATE KEY UPDATE phone VALUES(phone), balance VALUES(balance);这条SQL的含义是如果插入时没有触发唯一键冲突就正常插入如果触发了就执行后面的UPDATE操作。上面的例子中phone和balance会被更新为当前要插入的新值。注意MySQL 8.0.20之后推荐使用AS NEW的别名写法不再推荐VALUES()函数INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00) AS new ON DUPLICATE KEY UPDATE phone new.phone, balance new.balance;这个写法的小改动是为了将来移除VALUES()函数做准备。如果用的是MySQL 8.0以上版本建议直接使用新写法。还有一类情况主键冲突时希望保持不变。比如统计表里只有在数据变化时更新INSERT INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00) ON DUPLICATE KEY UPDATE balance balance;看起来有点奇怪但核心思想是冲突时不更新。除此之外还可以用INSERT IGNOREINSERT IGNORE INTO user (username, phone, balance) VALUES (张三, 13800138000, 100.00);INSERT IGNORE遇到重复键直接跳过不报错也不更新。如果业务逻辑是已存在的就忽略不存在的插入这个语法比ON DUPLICATE KEY UPDATE更合适。补充一个与热搜词相关的点热搜词里出现了mysql中int5其实这是UPDATE场景中给数值加固定值的写法后面讲UPDATE时会详细展开。3. SELECT查询条件、排序、分页和聚合3.1 WHERE条件的基本规则为什么它决定了查询性能查询是增删改查里最常用的操作。基础语法这里就不再赘述了说几个高频问题和容易被忽视的规则。WHERE条件中的运算符执行优先级是固定的但书写时逻辑关系可能会让人困惑。比如SELECT * FROM user WHERE status 1 OR status 0 AND balance 100;AND的优先级高于OR所以这条SQL实际执行的是WHERE status 1 OR (status 0 AND balance 100)这和很多人直觉上的(status 1 ORstatus 0) ANDbalance 100完全不同。遇到复杂的组合条件一定不要吝啬括号。括号不仅让逻辑清晰也能防止后续维护的人误读。WHERE条件最影响查询性能的是索引的使用。一个常见误区是在索引列上做函数运算会让索引失效。例如-- 索引失效 SELECT * FROM user WHERE DATE(created_at) 2024-01-01; -- 可以走索引 SELECT * FROM user WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;第一条会对每一行的created_at做DATE函数计算MySQL无法直接利用索引定位第二种写法是范围查询索引可以正常使用。类似的在索引列上做运算、隐式类型转换都会破坏索引。比如phone列是VARCHAR类型却用数字去比较-- 隐式类型转换索引失效 SELECT * FROM user WHERE phone 13800138000;MySQL会尝试把字符串转换为数字来比较导致无法走索引。正确做法是带上引号SELECT * FROM user WHERE phone 13800138000;3.2 ORDER BY和LIMIT分页查询的实战坑排序和分页是查询中如影随形的两个操作。SELECT * FROM user ORDER BY created_at DESC LIMIT 10;这段SQL本身没有语法问题但分页越到后面越慢SELECT * FROM user ORDER BY created_at DESC LIMIT 100000, 10;MySQL执行这条SQL时需要先扫描出前100010条数据然后把前100000条丢弃只返回最后10条。扫描的数据量非常大性能自然就差。改善方法之一是使用「延迟关联」或「基于游标的分页」。基于游标的分页思路是记住上一页最后一条记录的ID或时间下一页只取比它更小的记录SELECT * FROM user WHERE created_at 2024-01-15 10:00:00 ORDER BY created_at DESC LIMIT 10;这样每次查询都只需要走索引数据量再大也能保持稳定性能。缺点是翻页时无法跳到任意页码只能一页一页往后翻。但对于大多数加载更多的应用场景来说这种分页方式完全够用。关于排序还有一个注意点ORDER BY与LIMIT一起用时如果没有ORDER BYLIMIT的结果顺序是不确定的。千万不要依赖无ORDER BY的LIMIT来取前几条。数据删除、插入都会影响物理存储顺序查出来的顺序随时可能变化。3.3 GROUP BY与聚合热搜词里ONLY_FULL_GROUP_BY的由来聚合查询涉及GROUP BY、COUNT、SUM、AVG、MAX、MIN等函数。最常见的报错是MySQL 5.7及以上版本开启了ONLY_FULL_GROUP_BY模式SELECT username, COUNT(*) FROM user GROUP BY status;这条SQL报错信息会提示username没有出现在GROUP BY子句中。SQL标准的语义是GROUP BY之后SELECT列表中的第1列必须要么是聚合函数要么是GROUP BY子句中的列。上面这条SQL中username既不是聚合函数也不在GROUP BY里因此报错。很多人碰到这个报错第一反应是关掉ONLY_FULL_GROUP_BY我不建议这么干。关掉之后虽然不报错但返回的username是组内任意一条记录的结果不确定容易产生线上数据不一致。正确的做法是改SQL把非聚合列去掉或者用聚合函数包一下。如果确实需要查出每组中的某一个具体值MySQL 8.0推荐用窗口函数。比如要查每个状态的用户中余额最高的那个SELECT username, status, balance FROM ( SELECT username, status, balance, ROW_NUMBER() OVER (PARTITION BY status ORDER BY balance DESC) AS rn FROM user ) t WHERE t.rn 1;这段SQL的执行逻辑是先按status分组在每个分组内按balance降序编号然后只取每个分组内编号为1的记录也就是每个状态下余额最高的用户。窗口函数是MySQL 8.0才引入的遇到相关需求时用它能写出既清晰又高效的SQL。3.4 JOIN热搜词里mysql数据库join含义的通俗解释JOIN的本质是把两张表的数据通过关联条件组合起来。通常见过的有INNER JOIN内连接、LEFT JOIN左连接、RIGHT JOIN右连接和CROSS JOIN交叉连接。用生活例子来类比一张user表存用户信息一张order表存用户下单记录。要查每个用户名下的订单就需要JOINSELECT u.username, o.order_no, o.amount FROM user u INNER JOIN order o ON u.id o.user_id;INNER JOIN只返回两边都匹配上的记录也就是有订单的用户及其订单。LEFT JOIN则以左表user为准返回左表所有记录右表没有匹配上的字段置NULLSELECT u.username, o.order_no, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id;这条SQL能查出包括没有下过单的用户在内的所有用户数据订单字段为NULL。写JOIN时常见的坑是关联条件不完整。如果一张表的主键是联合主键比如order_id加order_lineJOIN时只写了其中一个条件会查到大量重复数据结果行数远超预期。另外JOIN的字段类型要保持一致否则会导致索引失效。VARCHAR类型的字段和INT类型的字段直接关联时MySQL需要做类型转换转换后索引就失效了。关于JOIN的优化建议控制连接的数据量。如果user表有100万条记录order表有500万条记录直接JOIN再过滤查询会执行很久。更好的做法是先缩小每张表的范围再连接SELECT u.username, o.order_no FROM ( SELECT id, username FROM user WHERE status 1 ) u INNER JOIN ( SELECT user_id, order_no FROM order WHERE created_at 2024-01-01 ) o ON u.id o.user_id;4. UPDATE更新数据那些年全表更新的事故4.1 忘记WHERE条件的教训和防范措施UPDATE user SET balance 0;这条SQL会更新表中所有记录的balance为0。如果业务上本来就打算全表重置那没问题。但如果在生产环境中手动执行后果不可想象。一个实用的习惯是执行UPDATE之前先用同条件的SELECT确认影响范围-- 先确认 SELECT COUNT(*) FROM user WHERE status 0; -- 再更新 UPDATE user SET balance 0 WHERE status 0;MySQL客户端默认在非交互模式下执行包含WHERE的更新不需要显式确认safe-update模式是个例外。连接时加--safe-updates选项能开启保护模式强制要求UPDATE/DELETE语句带WHERE或LIMIT条件否则报错。日常开发环境建议开启mysql --safe-updates -u root -p4.2 热搜词mysql中int5与mysql中更新子查询的坑热搜词里mysql中int5看起来是类似于java那套5的值加5写法但其实SQL中就是普通的算术表达式。常见的用法UPDATE user SET balance balance 5 WHERE id 123;这行语句在并发场景下是线程安全的。即使在多个事务同时执行balance balance 5时MySQL的行锁会保证每次更新都是基于最新值。相比之下如果用SELECT先查出来再加回去可能导致丢失更新。这一点后面讲事务时会再结合具体例子展开。mysql中更新子查询是另一个典型的坑。例如想把这张表里每个用户的余额更新为订单总额最高的那个用户的订单金额-- 这种写法在MySQL中会报错You cant specify target table user for update in FROM clause UPDATE user SET balance (SELECT MAX(amount) FROM order WHERE order.user_id user.id);MySQL不允许在同一语句中对目标表做UPDATE的同时又从同一张表中SELECT。解决方案是包一层派生表UPDATE user SET balance ( SELECT max_amount FROM ( SELECT user_id, MAX(amount) AS max_amount FROM order GROUP BY user_id ) t WHERE t.user_id user.id );这个报错信息在很多低版本MySQL中都会出现新人很容易被折磨半天。4.3 UPDATE的锁与事务为什么更新要放在事务里MySQL默认每个单条SQL是自动提交的UPDATE执行完立即生效。但有时候业务需求是要么全部成功要么全部回滚——比如转账操作从A账户扣钱给B账户加钱。这两步必须作为一个整体任何一步失败都要把另一部分撤销。这时候就要用事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE account_id A; UPDATE account SET balance balance 100 WHERE account_id B; COMMIT;如果在第二条UPDATE执行时发现B账户不存在需要回滚就执行ROLLBACK;第一条对A账户的扣款也会被撤销。这里深挖一下锁的问题。上面的第一步UPDATE执行后MySQL会锁定A账户所在的行直到提交或回滚才释放。如果别的事务在这期间也要更新A账户就会被阻塞等待。这是InnoDB的默认行为也是避免并发更新的关键机制。但是在开发中常见的死锁场景是两个事务以不同顺序更新相同的记录。比如事务1先更新A再更新B事务2先更新B再更新A两个事务可能互相等对方持有的锁造成死锁。MySQL检测到死锁后会自动回滚其中一个事务。防范的关键是让多个事务以相同顺序更新记录。事务隔离级别也会影响UPDATE的可见性。默认隔离级别是REPEATABLE READ可重复读在这个级别下事务内多次SELECT同一条件结果都是一致的。如果需要验证可以用SELECT transaction_isolation;查看当前隔离级别。5. DELETE删除数据安全删除、逻辑删除和TRUNCATE5.1 删除前先确认这是保命的第一条纪律DELETE操作的语法本身不复杂DELETE FROM user WHERE id 123;但数据一旦删除就没了这个事实让DELETE成为生产环境中最危险的操作之一。删数据前应该像执行UPDATE一样先用SELECT确认-- 先确认影响行数 SELECT * FROM user WHERE status 0 AND created_at 2023-01-01; -- 再删除 DELETE FROM user WHERE status 0 AND created_at 2023-01-01;如果删错了数据MySQL没有像文件系统那样的回收站功能常规操作几乎是找不回来的。即便有备份恢复也需要时间。更稳妥的办法是在低峰期操作而且DDL/DML语句执行后及时进行逻辑备份。5.2 逻辑删除为什么推荐用deleted字段代替物理删除生产环境里用户表、订单表这些核心业务表通常不建议做物理删除。因为数据之间有关联删掉一张表的记录可能导致另一张表出现孤儿数据另一方面业务上需要保留历史数据用于分析和审计。常见的做法是加一个is_deleted字段ALTER TABLE user ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0未删除1已删除; -- 逻辑删除 UPDATE user SET is_deleted 1 WHERE id 123; -- 查询时过滤掉已删除数据 SELECT * FROM user WHERE is_deleted 0;这样数据还在但业务查询时统一过滤。代价是每条查询都要加上is_deleted 0条件。有些团队会在查询接口的公共层做统一处理避免每个SQL都漏掉这个条件。5.3 TRUNCATE和DELETE的区别不止是速度不同TRUNCATE是另一种删除操作TRUNCATE TABLE user;它和DELETE有本质区别对比项DELETETRUNCATE条件过滤支持WHERE不支持全表清空事务回滚可以回滚隐式提交无法回滚自增ID不清零重置为初始值性能逐行删除慢直接释放数据页快触发器会触发不会触发返回结果返回删除的行数不返回具体行数平时业务代码里几乎用不到TRUNCATE它更像DBA层面的操作。但有些测试环境清空数据时会用到了解它和DELETE的差异能避免误操作。6. 新手最常踩的五个报错现象、原因、解决方案6.1 ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock这个报错的热度一直很高。现象是连接本地MySQL时提示无法通过socket文件连接。原因通常是MySQL服务未启动或者socket文件路径不一致。排查思路如下# 1. 检查MySQL服务状态 systemctl status mysqld # 或者不同系统命令不同 /etc/init.d/mysql status # 2. 如果没启动启动服务 systemctl start mysqld # 3. 确认socket文件存在 ls -l /tmp/mysql.sock如果服务已启动但socket路径不对需要查看MySQL的配置文件找到socket参数。可以让客户端通过TCP方式连接mysql -h 127.0.0.1 -P 3306 -u root -p跳过socket直接使用TCP连接。这个报错在通过本地命令行连接时最常见80%的情况都是服务根本没启动或启动失败。看到这个报错先别慌第一步永远是检查服务状态。6.2 root的初始密码到底是什么安装MySQL后连不上的经典问题很多人在Linux下装完MySQL用mysql -u root -p登录输入安装时设定的密码却提示Access denied。原因可能有两个一是MySQL 5.7及以上版本在安装时会自动生成一个临时密码存放在/var/log/mysqld.log中grep temporary password /var/log/mysqld.log用临时密码登录后必须马上修改密码ALTER USER rootlocalhost IDENTIFIED BY 新的密码;这个新密码还要满足MySQL的密码复杂度要求比如长度至少8位且包含大小写字母、数字、特殊字符。如果不想这么严格可以调整validate_password的相关参数但不建议在本地学习环境之外关闭密码强度校验。二是账户允许登录的主机范围受限。默认情况下root账号的host为localhost也就是说只能从本机连接。如果要用Navicat等工具从另一台机器连这台服务器的MySQL需要创建一个host允许远程访问的账号CREATE USER app% IDENTIFIED BY 密码; GRANT ALL PRIVILEGES ON demo.* TO app%; FLUSH PRIVILEGES;%表示允许从任意IP连接。但生产环境用%要谨慎更安全的方式是指定具体IP或网段比如app192.168.1.%只允许内网某网段连接。6.3 数据库连接池报错与版本不兼容热搜词里有这么一条报错django.db.utils.NotSupportedError: MySQL 8.4 or later is required (found 8.0。这看起来是Django版本与MySQL版本不匹配导致的。新版Django要求更高版本的MySQL但系统中装的是MySQL 8.0。遇到这种问题有几个思路检查Django版本如果是新版本可以降级到兼容MySQL 8.0的Django版本。检查Python的MySQL连接驱动比如mysqlclient或pymysql的版本升级驱动也可能解决问题。确认连接字符串中是否指定了正确的数据库版本属性。部分ORM可以通过配置让驱动忽略版本检查。遇到兼容性报错最忌讳的就是盲目升级或者盲目降级。应当先查官方版本的对应关系再决定调整哪一端。6.4 MySQL 8.0的SSL连接错误与JDBC驱动的useSSL参数热搜词里有mysql ssl连接错误和mysql jdbc usessl 与 sslmode 使用两条。这类问题多出现在Java应用通过JDBC连接MySQL时。MySQL 8.0默认开启了SSL相关的支持如果JDBC连接串没有配置SSL参数可能报类似SSL connection error或Public Key Retrieval is not allowed的错误。常见的处理方式是修改连接串参数jdbc:mysql://localhost:3306/demo?useSSLfalseallowPublicKeyRetrievaltrueserverTimezoneAsia/Shanghai其中useSSLfalse表示不启用SSL连接allowPublicKeyRetrievaltrue允许客户端自动获取服务器公钥这两个参数是解决此类报错最常见的方式。但要注意生产环境如果数据链路涉及公网传输不建议直接关闭SSL。正确的做法是配置SSL证书并启用加密连接而非为了省事关闭加密。内网环境安全性可控的前提下关闭SSL提升性能可以理解但要经过风险评估。6.5 ONLY_FULL_GROUP_BY等SQL模式问题这个在3.3节已详细展开过这里只需要记住排查思路执行SELECT sql_mode;查看当前模式如果包含ONLY_FULL_GROUP_BY且SQL中存在未聚合的非分组列就会报错。解决方案不是移除这个模式而是修正业务SQL。同理STRICT_TRANS_TABLES是另一个值得保留的严格模式它保证写入不符合定义的数据时直接报错而不是静默截断。所谓严格能让问题在第一时间暴露减少线上数据异常。7. 增删改查之外的三个好习惯7.1 索引不是越多越好但核心查询必须有增删改查里SELECT查询性能最依赖索引。但索引不是越多越好因为每次INSERT、UPDATE、DELETE都需要同步维护索引索引越多写入越慢。索引占存储空间。优化器选择索引也需要成本。实际项目中我遵循的原则是主键索引必须要有。高频查询的WHERE条件列、ORDER BY列、JOIN关联列尽量加索引。区分度低的列如status只有0和1不适合建索引。联合索引要遵循最左前缀原则查询条件从最左列开始才能命中索引。冗余索引要及时清理比如已经有索引(a, b)就不要再建单列索引a。以一个订单表为例常见的查询是按用户查订单、按时间范围查订单、按订单号查订单ALTER TABLE order ADD KEY idx_user_created (user_id, created_at); ALTER TABLE order ADD UNIQUE KEY uk_order_no (order_no);idx_user_created是一个联合索引同时覆盖了user_id和created_at。查询某个用户最近订单时能直接走这个索引。7.2 执行计划EXPLAIN用数据说话而不是靠猜想确认一条SQL有没有走索引最直接的方式是看执行计划EXPLAIN SELECT * FROM user WHERE username 张三;执行结果里的关键列列名作用重点关注type访问类型从好到差const eq_ref ref range index ALLkey实际使用的索引NULL表示没走索引rows预估扫描行数越小越好Extra额外信息Using filesort、Using temporary 都是性能警示type为ALL意味着全表扫描数据量大时就应该想办法优化。rows只是预估行数但能反映MySQL的成本估算。排查慢SQL时EXPLAIN是第一步先看有没有走索引再看走了哪个索引最后看有没有额外的排序、临时表操作。这个习惯能省下很多性能调优的时间。7.3 备份与恢复不是DBA也要会的基本操作哪怕是本地开发环境也应该养成定期备份的习惯。MySQL的备份方式有很多最基础的是mysqldumpmysqldump -u root -p demo demo_backup.sql恢复时执行mysql -u root -p demo demo_backup.sqlmysqldump默认导出的是SQL语句文件结构清晰适合中小规模数据库。数据量大时可以考虑使用mydumper或MySQL企业版备份工具也可以做物理备份直接复制数据文件但物理备份需要停机或使用xtrabackup等方式复杂度高一些。我个人的习惯是重要表的增删改操作前后都先做一次逻辑备份。写完这篇博客我把自己常用的几条命令整理成脚本需要时一键执行心里踏实。8. 最后再分享两个小技巧写SQL这件事门槛不高但把SQL写好、写稳、写高效是个需要长时间积累的过程。最后分享两个我实际工作中一直在用的小技巧。第一个技巧是给每条核心SQL写注释。这个注释不是把SQL本身抄一遍而是记录这条SQL的业务含义、涉及表的关系、调用它的业务场景。半年后再回来看能快速理解当年为什么这样写。第二个技巧是善于利用MySQL自带的元数据。比如查看某张表的表结构、索引信息SHOW CREATE TABLE user; DESC user; SHOW INDEX FROM user;这三个命令能快速了解一张表的设计排查问题时非常有用。特别是SHOW CREATE TABLE它会显示出表完整的DDL语句包括字符集、索引、约束等是了解表结构最快的途径。增删改查这四个操作看似基础实际藏着大量细节。字符集的选择、索引的设计、事务的使用、报错的排查每一项都能单独写一篇很长的文章。希望这篇内容能帮你在使用MySQL的路上少踩一些坑至少踩坑的时候知道怎么排查。