MySQL入门实操从建库到增删查改一篇吃透核心操作干了这么多年开发几乎每套系统都离不开数据库而MySQL又是国内用得最广的那一个。很多新手朋友一开始接触MySQL被各种概念绕得头晕什么存储引擎、索引、事务、隔离级别一堆名词。但你真去写业务代码天天打交道最多的其实就是那几个最基础的动作插入数据、查询数据、修改数据、删除数据也就是俗称的增删查改CRUD。今天我不讲那些花里胡哨的高深理论就从一个实际项目的角度把这四个核心操作掰开揉碎了讲清楚连带着建库建表、连接数据库这些前置步骤也一并梳理一遍。你把这篇文章吃透日常开发里百分之八十的数据库操作基本就够用了不管是自己写小项目还是接手别人的代码心里都不会再发怵。这篇文章适合谁看刚学MySQL没多久、对命令行操作还不太熟的新手用了Navicat这类图形工具但从来没手写过SQL的同学以及准备面试、想快速把基础操作过一遍的求职者。我会尽量把每一步操作背后的原因和注意事项都交代清楚不光是告诉你“怎么做”更告诉你“为什么要这么做”这样你踩过的坑才会真正变成自己的经验。1. 准备环境装好MySQL连上数据库1.1 下载安装与连接方式要练增删查改首先得有一个能跑起来的MySQL环境。我建议直接去MySQL官网下载社区版Community Server8.0以上版本都行最新的8.x系列在性能和功能上都很成熟网上教程也最多。安装的时候有几个地方要留个心眼一是字符集记得选utf8mb4不然以后存emoji或者中文生僻字容易乱码二是端口默认3306一般不用改改了反而增加记忆负担三是root密码要记牢这玩意儿丢了找回挺麻烦。装好之后连接MySQL有两种主流方式。第一种是命令行打开终端Windows下是cmd或PowerShellMac/Linux下直接开终端输入mysql -u root -p回车后会提示你输入密码输完就进入MySQL的命令行交互界面了能看到mysql这样的提示符。第二种是图形化工具比如Navicat、MySQL Workbench、DBeaver推荐新手用DBeaver开源免费界面清爽。连接时填主机localhost、端口3306、用户名root、密码测试连接成功就进去了。我个人的建议是图形工具用来查看数据、调试SQL很方便但命令行一定要会因为你以后上了服务器绝大多数情况是没有图形界面可用的只能靠命令行。而且很多线上环境排查问题命令行的响应速度比图形工具快得多。1.2 基本数据类型的选择建表之前得先搞明白字段用什么类型存。这个选择看似不起眼实际上对后续的查询性能和数据准确性影响很大。MySQL常用的数据类型大致分三类数值型、字符串型、日期时间型。数值型里面整数用INT范围约21亿如果只需要存0到255之间的小数字可以用TINYINT长整数用BIGINT带小数点的用DECIMAL钱相关的金额字段强烈推荐DECIMAL别用FLOAT或DOUBLE浮点型会有精度丢失的问题特别是做金额计算时0.1加0.2可能给你算成0.30000000000000004。字符串型里最常用的是VARCHAR它存的是可变长字符串比如用户名、邮箱、手机号后面要跟上长度像VARCHAR(50)表示最多存50个字符。如果文本内容特别长比如文章正文用TEXT类型。这里有个默认值的小坑MySQL 8.0里VARCHAR的默认长度是255超过的话会报错所以创建表时最好显式指定长度。日期时间型用DATETIME和TIMESTAMPDATETIME范围更广从1000年到9999年TIMESTAMP从1970年到2038年日常业务基本够用。TIMESTAMP有个特性是会自动更新适合记录数据最后修改时间。日期建议用DATE类型只存年月日。2. 建库建表增删查改之前必须先有个“家”2.1 创建数据库数据库相当于一个文件夹表就是里面的文件。在动手增删查改之前先创建一个数据库。命令行下执行CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里有几个细节要理解清楚。IF NOT EXISTS的意思是如果库已经存在就不重复创建避免报错这是个好习惯线上脚本反复执行的时候不会因为已存在而中断。DEFAULT CHARACTER SET utf8mb4指定了库默认字符集COLLATE utf8mb4_general_ci指定了排序规则general_ci表示大小写不敏感的通用排序对英文和数字的排序匹配表现稳定中文场景下也没问题。创建完用SHOW DATABASES;查看所有库然后USE shop;切换到当前库后续的操作都是在这个库的范围内进行的。2.2 设计表结构有了库接下来建表。举个电商系统的例子建一张用户表包含用户ID、用户名、手机号、邮箱、注册时间这几个字段CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID自增主键, username VARCHAR(50) NOT NULL COMMENT 用户名, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这套建表语句里每一行都值得细看。id INT UNSIGNED NOT NULL AUTO_INCREMENT表示无符号整数只能存正数范围翻倍、非空必填、自增每次插入自动加1这三点组合起来就是最标准的主键设计保证每条记录都有一条独一无二的ID。DEFAULT CURRENT_TIMESTAMP表示如果插入数据时没填这个字段数据库自动填当前时间省得应用层手动拼时间戳。ENGINEInnoDB指定存储引擎InnoDB支持事务、行级锁和崩溃恢复是MySQL默认引擎也是最稳妥的选择除非有特殊需求一般不用换。COMMENT给表和字段加说明方便后期维护你自己写的表过两个月再看没有注释基本看不懂当初想干什么。建好之后用DESC user;查看表结构用SHOW CREATE TABLE user\G查看建表语句这两个命令在排查问题的时候特别有用。2.3 修改表结构业务是演进的表结构不可能一成不变。常见的ALTER操作包括-- 新增字段 ALTER TABLE user ADD COLUMN age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄 AFTER phone; -- 修改字段类型 ALTER TABLE user MODIFY COLUMN email VARCHAR(150) COMMENT 邮箱加长; -- 修改字段名 ALTER TABLE user CHANGE COLUMN age user_age TINYINT UNSIGNED DEFAULT NULL COMMENT 用户年龄; -- 删除字段 ALTER TABLE user DROP COLUMN user_age; -- 给字段添加索引 ALTER TABLE user ADD INDEX idx_phone (phone);这里最想强调的是线上环境修改表结构一定要谨慎。在大数据量表上执行ALTER会锁表导致业务短暂不可用。一般建议在低峰期操作或者使用gh-ost、pt-online-schema-change这类在线变更工具。但这是后话了小项目直接ALTER问题不大。3. INSERT插入数据把数据存进去3.1 基础插入语法插入数据是最基础的操作语法格式为INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);比如往user表插入一条数据INSERT INTO user (username, phone, email) VALUES (小明, 13812345678, xiaomingexample.com);注意几个细节id字段是自增的不用写数据库会自动分配created_at字段没有指定值但表结构里定义了默认值CURRENT_TIMESTAMP所以数据库会填入当前时间如果字段有DEFAULT NULL默认值插入时也可以省略不写。很多人会好奇VALUES后面到底能不能写关键字比如往password字段里存abc123这种字符串完全没问题只要被单引号包裹就是纯字符串。但如果存的是保留字比如username字段想存字符串也直接加引号就行。数字不需要引号比如age INT字段存18直接写18。3.2 批量插入一次插多条插入单条记录效率太低批量插入性能高得多INSERT INTO user (username, phone, email) VALUES (小红, 13912345678, xiaohongexample.com), (小刚, 13712345678, xiaogangexample.com), (小丽, 13612345678, xiaoliexample.com);多条记录用逗号分隔一次执行完。批量插入的好处是减少网络往返SQL解析和执行开销也小很多。我实测过一次插入100条和插100次单条性能差距可能在十倍以上。日常开发和脚本写入能批量插入就批量插入。3.3 插入时常见的坑第一插入的数据要符合字段类型和约束。往INT字段插字符串abcMySQL会报警告并插入0而不是报错这很容易造成数据不准。用严格模式可以避免这个隐患在MySQL 8.0里默认开启了严格模式所以报错的情况会直接提示Data too long或者Incorrect integer value之类的原因。第二如果表有UNIQUE约束唯一索引插入重复数据会报Duplicate entry错误。有些场景我们希望“没有就插入有了就更新”MySQL提供了ON DUPLICATE KEY UPDATE语法INSERT INTO user (id, username, phone) VALUES (1, 小明, 13800000000) ON DUPLICATE KEY UPDATE username小明, phone13800000000;这个语法适合做数据同步、幂等写入线上脚本里非常实用。第三插入大量数据时事务要分批提交。如果使用InnoDB引擎默认是自动提交模式autocommit1即每插入一条就提交一次。大批量插入时可以手动开启事务插入完再一起提交速度更快而且中途出错可以整体回滚不会留下半截数据。示例START TRANSACTION; INSERT INTO user (username) VALUES (a), (b), (c); -- ... 更多插入 COMMIT;4. SELECT查询数据把数据捞出来4.1 基本查询与条件过滤查询是增删查改里用得最多、也最考验功力的操作。最简单的查询SELECT * FROM user;*代表所有字段开发环境随便用但线上代码不建议用*因为如果表字段很多*会多查出很多用不到的字段白白增加网络传输和内存压力。更规范的做法是把需要的字段列出来SELECT id, username, phone FROM user;条件过滤用WHERESELECT id, username, phone FROM user WHERE id 1;WHERE后面可以跟各种条件等于、不等于!或、大于、小于、大于等于、小于等于、模糊匹配LIKE、范围BETWEEN AND、枚举IN等。举几个实际场景-- 查成年用户 SELECT * FROM user WHERE age 18; -- 查手机号以138开头的用户 SELECT * FROM user WHERE phone LIKE 138%; -- 查ID在1到5之间的用户 SELECT * FROM user WHERE id BETWEEN 1 AND 5; -- 查用户名是小明或小红的用户 SELECT * FROM user WHERE username IN (小明, 小红);LIKE使用%表示任意多个字符_表示单个任意字符。比如LIKE 张_匹配的是姓张且名字只有一个字的人LIKE 张%匹配所有姓张的人。注意LIKE查询如果用法是%xxx%这种前后都带百分号的走不了索引大数据量下查询会特别慢要谨慎使用。4.2 排序与分页查询结果默认是无序的按插入顺序返回但不保证所以需要显式排序SELECT id, username, phone FROM user ORDER BY id DESC;ORDER BY后面跟排序字段ASC表示升序默认DESC表示降序。多字段排序用逗号分隔比如先按age降序、再按id升序SELECT * FROM user ORDER BY age DESC, id ASC;分页查询用LIMIT和OFFSET-- 查前十条 SELECT * FROM user LIMIT 10; -- 跳过前20条查10条即第21到30条 SELECT * FROM user LIMIT 10 OFFSET 20;或者用简写的LIMIT 20, 10前面是偏移量后面是条数。分页在列表页里用得特别多。不过偏移量特别大的时候比如翻到第100万页OFFSET 1000000会扫描前面所有行性能很差。优化的思路是用主键定位比如WHERE id 1000000 LIMIT 10这种基于游标的分页方式。4.3 聚合函数与分组统计数量、求和、平均值这类需求用聚合函数-- 统计用户总数 SELECT COUNT(*) FROM user; -- 统计年龄总和 SELECT SUM(age) FROM user; -- 统计平均年龄 SELECT AVG(age) FROM user; -- 查最大最小年龄 SELECT MAX(age), MIN(age) FROM user;分组统计用GROUP BY比如按年龄分组统计人数SELECT age, COUNT(*) AS cnt FROM user GROUP BY age;GROUP BY后面还可以加HAVING做分组后的过滤注意WHERE是分组前过滤HAVING是分组后过滤。比如SELECT age, COUNT(*) AS cnt FROM user GROUP BY age HAVING cnt 5;这个查询的含义是按年龄分组只保留人数大于5的年龄段。理解WHERE和HAVING的区别是面试常考的点WHERE是在原始数据上过滤HAVING是分组后再过滤性能上前者通常更好。4.4 JOIN关联查询实际项目里数据经常分散在多张表里比如订单表只存用户ID用户的具体信息在user表里。这时候需要JOIN两张表SELECT o.id, o.amount, u.username FROM orders o INNER JOIN user u ON o.user_id u.id;JOIN分几种INNER JOIN只返回两边都匹配的记录LEFT JOIN返回左表所有记录右表没有匹配的字段用NULL填充RIGHT JOIN相反。实际开发中INNER JOIN和LEFT JOIN用得最多。JOIN查询是MySQL的进阶重点涉及索引、驱动表选择等一系列性能问题这里先不展开但你只要记住JOIN时关联字段一定要有索引不然数据量一大查询就直接卡死。4.5 子查询子查询就是把一个SELECT语句嵌套在另一个SELECT语句里比如查订单金额大于平均值的用户SELECT * FROM user WHERE id IN ( SELECT user_id FROM orders WHERE amount (SELECT AVG(amount) FROM orders) );子查询写起来直观但性能往往不如JOIN改写。MySQL优化器对子查询的支持已经进步很多但遇到性能问题时还是优先考虑改写为JOIN。5. UPDATE更新数据改数据要小心5.1 基础更新语法更新数据用UPDATE语法格式UPDATE 表名 SET 字段1 值1, 字段2 值2 WHERE 条件;比如把ID为1的用户的手机号改了UPDATE user SET phone 15900000000 WHERE id 1;可以一次性更新多个字段UPDATE user SET phone 15900000000, email newexample.com WHERE id 1;也可以基于字段本身的值做更新-- 所有人年龄加1岁 UPDATE user SET age age 1;5.2 WHERE是保命符这条必须单独拿出来强调UPDATE语句如果忘了WHERE会把整张表的记录全部更新。我见过不止一个同事在测试环境执行了UPDATE user SET age 100;这种操作结果全表年龄都变成了100。测试环境还好线上环境这就是重大事故了。所以在执行UPDATE之前强烈建议你先用SELECT验证一下WHERE条件选中的数据SELECT * FROM user WHERE id 1; -- 先看看这次要改哪些数据确认无误后再执行UPDATE。这是个保命的习惯。5.3 UPDATE与事务如果一次要更新多条记录而且这些更新是有关联的最好放在事务里START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;转账这个经典场景就是两条UPDATE必须保证同时成功或者同时失败。如果执行第一条成功、第二条失败钱不知道去哪了数据就不一致了。事务的ACID特性里原子性保证的就是这种“全做或全不做”。InnoDB引擎才支持事务MyISAM引擎是不支持的这也是为什么我一直说选InnoDB。5.4 大批量更新的性能问题如果要更新几万条记录逐条UPDATE每条都走一次事务提交速度极慢。优化的思路有几种。一是用批量更新UPDATE user SET age CASE id WHEN 1 THEN 20 WHEN 2 THEN 21 WHEN 3 THEN 22 END WHERE id IN (1, 2, 3);另一种是先查出要更新的数据的主键列表一条UPDATE配合IN条件一次性提交。MySQL 8.0还支持VALUES ROW语法不过日常开发用得少知道有这两种思路就行。6. DELETE删除数据删库跑路前先想清楚6.1 基础删除语法删除数据的语法相对简单DELETE FROM 表名 WHERE 条件;比如删除ID为1的用户DELETE FROM user WHERE id 1;和UPDATE一样DELETE没写WHERE就是删全表。删除前也务必备份或确认特别是生产环境。6.2 DELETE和TRUNCATE的区别清空全表数据还有另一个命令TRUNCATE TABLE user;TRUNCATE和DELETE FROM有本质区别面试爱考。DELETE是逐行删除走事务删错了还能ROLLBACK回滚而且不会重置自增IDTRUNCATE是直接丢弃表再重建速度极快但不能回滚自增ID也重置回1。另外TRUNCATE属于DDL数据定义语言DELETE属于DML数据操作语言两者在事务处理、权限控制、触发器等维度都有差异。日常开发中清空表数据一般会用DELETE因为可以回滚但如果确认不要了用TRUNCATE更快。这里有一个很实用的技巧在测试环境或数据可以重建的环境里用TRUNCATE清表在可能有审计需求的环境里永远不要物理删除数据。6.3 软删除与硬删除实际业务开发中做删除操作要好好想想直接DELETE是“硬删除”数据彻底没了后面想查历史记录就查不到了。大厂的规范做法一般是“软删除”也就是给表加一个is_deleted字段0表示正常1表示已删除删除操作就是一次UPDATEALTER TABLE user ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0未删除 1已删除; -- 软删除 UPDATE user SET is_deleted 1 WHERE id 1; -- 查询时过滤掉已删除的 SELECT * FROM user WHERE is_deleted 0;软删除的优点是数据不会丢方便追溯和恢复但缺点也明显每次查询都要带is_deleted 0条件忘带了就查出脏数据表内无效数据越积越多需要定期清理。要不要软删除取决于业务对数据保留的要求。6.4 删除大表数据如果一次要删除的数据量很大比如几百万条一条DELETE FROM log WHERE create_time 2023-01-01可能会锁很多行导致业务卡顿。优化思路一是分批删除每次删除一小部分控制事务大小DELETE FROM log WHERE create_time 2023-01-01 LIMIT 1000;反复执行直到影响行数为0。二是用pt-archiver这类工具它内置了分批删除和限速逻辑对线上影响小。小项目的话分批删除就够用了。7. 常见问题与排查技巧实录7.1 连不上数据库ERROR 2002热词里出现过error 2002 (hy000): cant connect to local mysql server through socket这个报错。最常见的原因是MySQL服务没有启动。Linux下用systemctl status mysqld或者service mysql status查看服务状态Windows下到服务管理器里看MySQL服务的状态。其次可能是Socket文件路径不对本地连接走的是Socket协议Unix/Linux默认如果socket文件被修改过连接时就找不到。用mysql -h 127.0.0.1 -P 3306 -u root -p通过TCP方式连接可以绕开socket问题。7.2 忘记MySQL root密码怎么办这个情况很多人遇到过。思路是跳过权限验证启动MySQL然后重置密码。步骤大致是先停掉MySQL服务以mysqld --skip-grant-tables方式启动跳过权限表然后用mysql -u root免密登录执行ALTER USER rootlocalhost IDENTIFIED BY 新密码;最后正常重启服务。注意8.0版本的密码字段改成了authentication_string用ALTER USER语法是标准做法。7.3 中文乱码怎么办中文乱码的根因几乎都是字符集不统一。排查方法是依次查看客户端、连接、数据库、表的字符集SHOW VARIABLES LIKE character_set%; SHOW CREATE DATABASE shop; SHOW CREATE TABLE user;除了建库建表时指定utf8mb4客户端连接时也要指定mysql -u root -p --default-character-setutf8mb4或者进入mysql后执行SET NAMES utf8mb4;。在JDBC连接串里加characterEncodingutf8mb4这样一套下来基本能保证中文不乱码。7.4 MySQL 8.4版本与兼容性问题热词里有个django.db.utils.NotSupportedError: MySQL 8.4 or later is required (found 8.0)。这是Django框架的版本兼容性问题某些新版本Django开始要求MySQL 8.4但你本地装的还是8.0。解决思路是选择匹配的Django版本或者升级MySQL。碰到版本兼容报错先看清报错原文搜索时带上框架名和MySQL版本号通常能很快找到解决方案。这类问题不是你不会写SQL而是开源生态的版本匹配问题放宽心态就好。7.5 常用运维命令速查命令行下有几个命令使用频率极高单独列出来-- 查看当前连接的数据库 SELECT DATABASE(); -- 查看所有表 SHOW TABLES; -- 查看表结构 DESC user; -- 查看表所有数据按主键降序排 SELECT * FROM user ORDER BY id DESC LIMIT 100; -- 查看当前时间 SELECT NOW();搞开发的时候随时用SHOW PROCESSLIST;查看当前有哪些SQL在跑排查慢查询和死锁时是第一步操作。生产环境出现连接数飙高的时候这个命令能快速定位到是哪个查询卡住了。8. 写在实操之外几个让效率翻倍的好习惯最后分享几个我在实际项目中一直坚持的习惯谈不上高深但确实能帮你少走弯路。第一SQL关键字统一大写表名字段名统一小写。比如SELECT id, username FROM user WHERE id 1视觉上层次清楚关键结构一眼就能看出来。团队协作时风格统一代码评审也省力。第二写任何UPDATE、DELETE语句前先把WHERE条件复制到一个SELECT语句里跑一遍看它返回的数据对不对。这套流程多花十秒钟但能避免的灾难可能是一整天的数据恢复。第三在本地开发时如果可以尽量把MySQL跑在Docker容器里。好处是环境隔离一台机器上可以同时跑多个版本的MySQL互不干扰踩坏了随时删掉重建。热词里也有docker安装mysql说明这条路已经被很多人在用了docker run -d --name mysql8 -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ -e MYSQL_DATABASEshop \ -v /my/own/datadir:/var/lib/mysql \ mysql:8.0第四建表时宁可多写注释也不要少写。表注释和字段注释都加上半年后你回来看表结构会发现当时写注释的自己有多贴心。这就像写代码时的变量命名当时觉得多此一举后面维护才知道都是救命稻草。增删查改是MySQL最基础却最重要的能力几乎所有的业务逻辑最终都会落到这四类操作上把这些基础打得扎实后面再去深入索引优化、读写分离、分库分表才有底气。别急着追求那些炫酷的高阶功能先把今天这几条SQL写到滚瓜烂熟遇到任何一张表都能快速做出增删查改你就已经超过不少人可以开始打开那张真正的进阶之门了。 MySQL入门实操：从建库到增删查改，一篇吃透核心操作
MySQL入门实操从建库到增删查改一篇吃透核心操作干了这么多年开发几乎每套系统都离不开数据库而MySQL又是国内用得最广的那一个。很多新手朋友一开始接触MySQL被各种概念绕得头晕什么存储引擎、索引、事务、隔离级别一堆名词。但你真去写业务代码天天打交道最多的其实就是那几个最基础的动作插入数据、查询数据、修改数据、删除数据也就是俗称的增删查改CRUD。今天我不讲那些花里胡哨的高深理论就从一个实际项目的角度把这四个核心操作掰开揉碎了讲清楚连带着建库建表、连接数据库这些前置步骤也一并梳理一遍。你把这篇文章吃透日常开发里百分之八十的数据库操作基本就够用了不管是自己写小项目还是接手别人的代码心里都不会再发怵。这篇文章适合谁看刚学MySQL没多久、对命令行操作还不太熟的新手用了Navicat这类图形工具但从来没手写过SQL的同学以及准备面试、想快速把基础操作过一遍的求职者。我会尽量把每一步操作背后的原因和注意事项都交代清楚不光是告诉你“怎么做”更告诉你“为什么要这么做”这样你踩过的坑才会真正变成自己的经验。1. 准备环境装好MySQL连上数据库1.1 下载安装与连接方式要练增删查改首先得有一个能跑起来的MySQL环境。我建议直接去MySQL官网下载社区版Community Server8.0以上版本都行最新的8.x系列在性能和功能上都很成熟网上教程也最多。安装的时候有几个地方要留个心眼一是字符集记得选utf8mb4不然以后存emoji或者中文生僻字容易乱码二是端口默认3306一般不用改改了反而增加记忆负担三是root密码要记牢这玩意儿丢了找回挺麻烦。装好之后连接MySQL有两种主流方式。第一种是命令行打开终端Windows下是cmd或PowerShellMac/Linux下直接开终端输入mysql -u root -p回车后会提示你输入密码输完就进入MySQL的命令行交互界面了能看到mysql这样的提示符。第二种是图形化工具比如Navicat、MySQL Workbench、DBeaver推荐新手用DBeaver开源免费界面清爽。连接时填主机localhost、端口3306、用户名root、密码测试连接成功就进去了。我个人的建议是图形工具用来查看数据、调试SQL很方便但命令行一定要会因为你以后上了服务器绝大多数情况是没有图形界面可用的只能靠命令行。而且很多线上环境排查问题命令行的响应速度比图形工具快得多。1.2 基本数据类型的选择建表之前得先搞明白字段用什么类型存。这个选择看似不起眼实际上对后续的查询性能和数据准确性影响很大。MySQL常用的数据类型大致分三类数值型、字符串型、日期时间型。数值型里面整数用INT范围约21亿如果只需要存0到255之间的小数字可以用TINYINT长整数用BIGINT带小数点的用DECIMAL钱相关的金额字段强烈推荐DECIMAL别用FLOAT或DOUBLE浮点型会有精度丢失的问题特别是做金额计算时0.1加0.2可能给你算成0.30000000000000004。字符串型里最常用的是VARCHAR它存的是可变长字符串比如用户名、邮箱、手机号后面要跟上长度像VARCHAR(50)表示最多存50个字符。如果文本内容特别长比如文章正文用TEXT类型。这里有个默认值的小坑MySQL 8.0里VARCHAR的默认长度是255超过的话会报错所以创建表时最好显式指定长度。日期时间型用DATETIME和TIMESTAMPDATETIME范围更广从1000年到9999年TIMESTAMP从1970年到2038年日常业务基本够用。TIMESTAMP有个特性是会自动更新适合记录数据最后修改时间。日期建议用DATE类型只存年月日。2. 建库建表增删查改之前必须先有个“家”2.1 创建数据库数据库相当于一个文件夹表就是里面的文件。在动手增删查改之前先创建一个数据库。命令行下执行CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里有几个细节要理解清楚。IF NOT EXISTS的意思是如果库已经存在就不重复创建避免报错这是个好习惯线上脚本反复执行的时候不会因为已存在而中断。DEFAULT CHARACTER SET utf8mb4指定了库默认字符集COLLATE utf8mb4_general_ci指定了排序规则general_ci表示大小写不敏感的通用排序对英文和数字的排序匹配表现稳定中文场景下也没问题。创建完用SHOW DATABASES;查看所有库然后USE shop;切换到当前库后续的操作都是在这个库的范围内进行的。2.2 设计表结构有了库接下来建表。举个电商系统的例子建一张用户表包含用户ID、用户名、手机号、邮箱、注册时间这几个字段CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID自增主键, username VARCHAR(50) NOT NULL COMMENT 用户名, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这套建表语句里每一行都值得细看。id INT UNSIGNED NOT NULL AUTO_INCREMENT表示无符号整数只能存正数范围翻倍、非空必填、自增每次插入自动加1这三点组合起来就是最标准的主键设计保证每条记录都有一条独一无二的ID。DEFAULT CURRENT_TIMESTAMP表示如果插入数据时没填这个字段数据库自动填当前时间省得应用层手动拼时间戳。ENGINEInnoDB指定存储引擎InnoDB支持事务、行级锁和崩溃恢复是MySQL默认引擎也是最稳妥的选择除非有特殊需求一般不用换。COMMENT给表和字段加说明方便后期维护你自己写的表过两个月再看没有注释基本看不懂当初想干什么。建好之后用DESC user;查看表结构用SHOW CREATE TABLE user\G查看建表语句这两个命令在排查问题的时候特别有用。2.3 修改表结构业务是演进的表结构不可能一成不变。常见的ALTER操作包括-- 新增字段 ALTER TABLE user ADD COLUMN age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄 AFTER phone; -- 修改字段类型 ALTER TABLE user MODIFY COLUMN email VARCHAR(150) COMMENT 邮箱加长; -- 修改字段名 ALTER TABLE user CHANGE COLUMN age user_age TINYINT UNSIGNED DEFAULT NULL COMMENT 用户年龄; -- 删除字段 ALTER TABLE user DROP COLUMN user_age; -- 给字段添加索引 ALTER TABLE user ADD INDEX idx_phone (phone);这里最想强调的是线上环境修改表结构一定要谨慎。在大数据量表上执行ALTER会锁表导致业务短暂不可用。一般建议在低峰期操作或者使用gh-ost、pt-online-schema-change这类在线变更工具。但这是后话了小项目直接ALTER问题不大。3. INSERT插入数据把数据存进去3.1 基础插入语法插入数据是最基础的操作语法格式为INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);比如往user表插入一条数据INSERT INTO user (username, phone, email) VALUES (小明, 13812345678, xiaomingexample.com);注意几个细节id字段是自增的不用写数据库会自动分配created_at字段没有指定值但表结构里定义了默认值CURRENT_TIMESTAMP所以数据库会填入当前时间如果字段有DEFAULT NULL默认值插入时也可以省略不写。很多人会好奇VALUES后面到底能不能写关键字比如往password字段里存abc123这种字符串完全没问题只要被单引号包裹就是纯字符串。但如果存的是保留字比如username字段想存字符串也直接加引号就行。数字不需要引号比如age INT字段存18直接写18。3.2 批量插入一次插多条插入单条记录效率太低批量插入性能高得多INSERT INTO user (username, phone, email) VALUES (小红, 13912345678, xiaohongexample.com), (小刚, 13712345678, xiaogangexample.com), (小丽, 13612345678, xiaoliexample.com);多条记录用逗号分隔一次执行完。批量插入的好处是减少网络往返SQL解析和执行开销也小很多。我实测过一次插入100条和插100次单条性能差距可能在十倍以上。日常开发和脚本写入能批量插入就批量插入。3.3 插入时常见的坑第一插入的数据要符合字段类型和约束。往INT字段插字符串abcMySQL会报警告并插入0而不是报错这很容易造成数据不准。用严格模式可以避免这个隐患在MySQL 8.0里默认开启了严格模式所以报错的情况会直接提示Data too long或者Incorrect integer value之类的原因。第二如果表有UNIQUE约束唯一索引插入重复数据会报Duplicate entry错误。有些场景我们希望“没有就插入有了就更新”MySQL提供了ON DUPLICATE KEY UPDATE语法INSERT INTO user (id, username, phone) VALUES (1, 小明, 13800000000) ON DUPLICATE KEY UPDATE username小明, phone13800000000;这个语法适合做数据同步、幂等写入线上脚本里非常实用。第三插入大量数据时事务要分批提交。如果使用InnoDB引擎默认是自动提交模式autocommit1即每插入一条就提交一次。大批量插入时可以手动开启事务插入完再一起提交速度更快而且中途出错可以整体回滚不会留下半截数据。示例START TRANSACTION; INSERT INTO user (username) VALUES (a), (b), (c); -- ... 更多插入 COMMIT;4. SELECT查询数据把数据捞出来4.1 基本查询与条件过滤查询是增删查改里用得最多、也最考验功力的操作。最简单的查询SELECT * FROM user;*代表所有字段开发环境随便用但线上代码不建议用*因为如果表字段很多*会多查出很多用不到的字段白白增加网络传输和内存压力。更规范的做法是把需要的字段列出来SELECT id, username, phone FROM user;条件过滤用WHERESELECT id, username, phone FROM user WHERE id 1;WHERE后面可以跟各种条件等于、不等于!或、大于、小于、大于等于、小于等于、模糊匹配LIKE、范围BETWEEN AND、枚举IN等。举几个实际场景-- 查成年用户 SELECT * FROM user WHERE age 18; -- 查手机号以138开头的用户 SELECT * FROM user WHERE phone LIKE 138%; -- 查ID在1到5之间的用户 SELECT * FROM user WHERE id BETWEEN 1 AND 5; -- 查用户名是小明或小红的用户 SELECT * FROM user WHERE username IN (小明, 小红);LIKE使用%表示任意多个字符_表示单个任意字符。比如LIKE 张_匹配的是姓张且名字只有一个字的人LIKE 张%匹配所有姓张的人。注意LIKE查询如果用法是%xxx%这种前后都带百分号的走不了索引大数据量下查询会特别慢要谨慎使用。4.2 排序与分页查询结果默认是无序的按插入顺序返回但不保证所以需要显式排序SELECT id, username, phone FROM user ORDER BY id DESC;ORDER BY后面跟排序字段ASC表示升序默认DESC表示降序。多字段排序用逗号分隔比如先按age降序、再按id升序SELECT * FROM user ORDER BY age DESC, id ASC;分页查询用LIMIT和OFFSET-- 查前十条 SELECT * FROM user LIMIT 10; -- 跳过前20条查10条即第21到30条 SELECT * FROM user LIMIT 10 OFFSET 20;或者用简写的LIMIT 20, 10前面是偏移量后面是条数。分页在列表页里用得特别多。不过偏移量特别大的时候比如翻到第100万页OFFSET 1000000会扫描前面所有行性能很差。优化的思路是用主键定位比如WHERE id 1000000 LIMIT 10这种基于游标的分页方式。4.3 聚合函数与分组统计数量、求和、平均值这类需求用聚合函数-- 统计用户总数 SELECT COUNT(*) FROM user; -- 统计年龄总和 SELECT SUM(age) FROM user; -- 统计平均年龄 SELECT AVG(age) FROM user; -- 查最大最小年龄 SELECT MAX(age), MIN(age) FROM user;分组统计用GROUP BY比如按年龄分组统计人数SELECT age, COUNT(*) AS cnt FROM user GROUP BY age;GROUP BY后面还可以加HAVING做分组后的过滤注意WHERE是分组前过滤HAVING是分组后过滤。比如SELECT age, COUNT(*) AS cnt FROM user GROUP BY age HAVING cnt 5;这个查询的含义是按年龄分组只保留人数大于5的年龄段。理解WHERE和HAVING的区别是面试常考的点WHERE是在原始数据上过滤HAVING是分组后再过滤性能上前者通常更好。4.4 JOIN关联查询实际项目里数据经常分散在多张表里比如订单表只存用户ID用户的具体信息在user表里。这时候需要JOIN两张表SELECT o.id, o.amount, u.username FROM orders o INNER JOIN user u ON o.user_id u.id;JOIN分几种INNER JOIN只返回两边都匹配的记录LEFT JOIN返回左表所有记录右表没有匹配的字段用NULL填充RIGHT JOIN相反。实际开发中INNER JOIN和LEFT JOIN用得最多。JOIN查询是MySQL的进阶重点涉及索引、驱动表选择等一系列性能问题这里先不展开但你只要记住JOIN时关联字段一定要有索引不然数据量一大查询就直接卡死。4.5 子查询子查询就是把一个SELECT语句嵌套在另一个SELECT语句里比如查订单金额大于平均值的用户SELECT * FROM user WHERE id IN ( SELECT user_id FROM orders WHERE amount (SELECT AVG(amount) FROM orders) );子查询写起来直观但性能往往不如JOIN改写。MySQL优化器对子查询的支持已经进步很多但遇到性能问题时还是优先考虑改写为JOIN。5. UPDATE更新数据改数据要小心5.1 基础更新语法更新数据用UPDATE语法格式UPDATE 表名 SET 字段1 值1, 字段2 值2 WHERE 条件;比如把ID为1的用户的手机号改了UPDATE user SET phone 15900000000 WHERE id 1;可以一次性更新多个字段UPDATE user SET phone 15900000000, email newexample.com WHERE id 1;也可以基于字段本身的值做更新-- 所有人年龄加1岁 UPDATE user SET age age 1;5.2 WHERE是保命符这条必须单独拿出来强调UPDATE语句如果忘了WHERE会把整张表的记录全部更新。我见过不止一个同事在测试环境执行了UPDATE user SET age 100;这种操作结果全表年龄都变成了100。测试环境还好线上环境这就是重大事故了。所以在执行UPDATE之前强烈建议你先用SELECT验证一下WHERE条件选中的数据SELECT * FROM user WHERE id 1; -- 先看看这次要改哪些数据确认无误后再执行UPDATE。这是个保命的习惯。5.3 UPDATE与事务如果一次要更新多条记录而且这些更新是有关联的最好放在事务里START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;转账这个经典场景就是两条UPDATE必须保证同时成功或者同时失败。如果执行第一条成功、第二条失败钱不知道去哪了数据就不一致了。事务的ACID特性里原子性保证的就是这种“全做或全不做”。InnoDB引擎才支持事务MyISAM引擎是不支持的这也是为什么我一直说选InnoDB。5.4 大批量更新的性能问题如果要更新几万条记录逐条UPDATE每条都走一次事务提交速度极慢。优化的思路有几种。一是用批量更新UPDATE user SET age CASE id WHEN 1 THEN 20 WHEN 2 THEN 21 WHEN 3 THEN 22 END WHERE id IN (1, 2, 3);另一种是先查出要更新的数据的主键列表一条UPDATE配合IN条件一次性提交。MySQL 8.0还支持VALUES ROW语法不过日常开发用得少知道有这两种思路就行。6. DELETE删除数据删库跑路前先想清楚6.1 基础删除语法删除数据的语法相对简单DELETE FROM 表名 WHERE 条件;比如删除ID为1的用户DELETE FROM user WHERE id 1;和UPDATE一样DELETE没写WHERE就是删全表。删除前也务必备份或确认特别是生产环境。6.2 DELETE和TRUNCATE的区别清空全表数据还有另一个命令TRUNCATE TABLE user;TRUNCATE和DELETE FROM有本质区别面试爱考。DELETE是逐行删除走事务删错了还能ROLLBACK回滚而且不会重置自增IDTRUNCATE是直接丢弃表再重建速度极快但不能回滚自增ID也重置回1。另外TRUNCATE属于DDL数据定义语言DELETE属于DML数据操作语言两者在事务处理、权限控制、触发器等维度都有差异。日常开发中清空表数据一般会用DELETE因为可以回滚但如果确认不要了用TRUNCATE更快。这里有一个很实用的技巧在测试环境或数据可以重建的环境里用TRUNCATE清表在可能有审计需求的环境里永远不要物理删除数据。6.3 软删除与硬删除实际业务开发中做删除操作要好好想想直接DELETE是“硬删除”数据彻底没了后面想查历史记录就查不到了。大厂的规范做法一般是“软删除”也就是给表加一个is_deleted字段0表示正常1表示已删除删除操作就是一次UPDATEALTER TABLE user ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0未删除 1已删除; -- 软删除 UPDATE user SET is_deleted 1 WHERE id 1; -- 查询时过滤掉已删除的 SELECT * FROM user WHERE is_deleted 0;软删除的优点是数据不会丢方便追溯和恢复但缺点也明显每次查询都要带is_deleted 0条件忘带了就查出脏数据表内无效数据越积越多需要定期清理。要不要软删除取决于业务对数据保留的要求。6.4 删除大表数据如果一次要删除的数据量很大比如几百万条一条DELETE FROM log WHERE create_time 2023-01-01可能会锁很多行导致业务卡顿。优化思路一是分批删除每次删除一小部分控制事务大小DELETE FROM log WHERE create_time 2023-01-01 LIMIT 1000;反复执行直到影响行数为0。二是用pt-archiver这类工具它内置了分批删除和限速逻辑对线上影响小。小项目的话分批删除就够用了。7. 常见问题与排查技巧实录7.1 连不上数据库ERROR 2002热词里出现过error 2002 (hy000): cant connect to local mysql server through socket这个报错。最常见的原因是MySQL服务没有启动。Linux下用systemctl status mysqld或者service mysql status查看服务状态Windows下到服务管理器里看MySQL服务的状态。其次可能是Socket文件路径不对本地连接走的是Socket协议Unix/Linux默认如果socket文件被修改过连接时就找不到。用mysql -h 127.0.0.1 -P 3306 -u root -p通过TCP方式连接可以绕开socket问题。7.2 忘记MySQL root密码怎么办这个情况很多人遇到过。思路是跳过权限验证启动MySQL然后重置密码。步骤大致是先停掉MySQL服务以mysqld --skip-grant-tables方式启动跳过权限表然后用mysql -u root免密登录执行ALTER USER rootlocalhost IDENTIFIED BY 新密码;最后正常重启服务。注意8.0版本的密码字段改成了authentication_string用ALTER USER语法是标准做法。7.3 中文乱码怎么办中文乱码的根因几乎都是字符集不统一。排查方法是依次查看客户端、连接、数据库、表的字符集SHOW VARIABLES LIKE character_set%; SHOW CREATE DATABASE shop; SHOW CREATE TABLE user;除了建库建表时指定utf8mb4客户端连接时也要指定mysql -u root -p --default-character-setutf8mb4或者进入mysql后执行SET NAMES utf8mb4;。在JDBC连接串里加characterEncodingutf8mb4这样一套下来基本能保证中文不乱码。7.4 MySQL 8.4版本与兼容性问题热词里有个django.db.utils.NotSupportedError: MySQL 8.4 or later is required (found 8.0)。这是Django框架的版本兼容性问题某些新版本Django开始要求MySQL 8.4但你本地装的还是8.0。解决思路是选择匹配的Django版本或者升级MySQL。碰到版本兼容报错先看清报错原文搜索时带上框架名和MySQL版本号通常能很快找到解决方案。这类问题不是你不会写SQL而是开源生态的版本匹配问题放宽心态就好。7.5 常用运维命令速查命令行下有几个命令使用频率极高单独列出来-- 查看当前连接的数据库 SELECT DATABASE(); -- 查看所有表 SHOW TABLES; -- 查看表结构 DESC user; -- 查看表所有数据按主键降序排 SELECT * FROM user ORDER BY id DESC LIMIT 100; -- 查看当前时间 SELECT NOW();搞开发的时候随时用SHOW PROCESSLIST;查看当前有哪些SQL在跑排查慢查询和死锁时是第一步操作。生产环境出现连接数飙高的时候这个命令能快速定位到是哪个查询卡住了。8. 写在实操之外几个让效率翻倍的好习惯最后分享几个我在实际项目中一直坚持的习惯谈不上高深但确实能帮你少走弯路。第一SQL关键字统一大写表名字段名统一小写。比如SELECT id, username FROM user WHERE id 1视觉上层次清楚关键结构一眼就能看出来。团队协作时风格统一代码评审也省力。第二写任何UPDATE、DELETE语句前先把WHERE条件复制到一个SELECT语句里跑一遍看它返回的数据对不对。这套流程多花十秒钟但能避免的灾难可能是一整天的数据恢复。第三在本地开发时如果可以尽量把MySQL跑在Docker容器里。好处是环境隔离一台机器上可以同时跑多个版本的MySQL互不干扰踩坏了随时删掉重建。热词里也有docker安装mysql说明这条路已经被很多人在用了docker run -d --name mysql8 -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD123456 \ -e MYSQL_DATABASEshop \ -v /my/own/datadir:/var/lib/mysql \ mysql:8.0第四建表时宁可多写注释也不要少写。表注释和字段注释都加上半年后你回来看表结构会发现当时写注释的自己有多贴心。这就像写代码时的变量命名当时觉得多此一举后面维护才知道都是救命稻草。增删查改是MySQL最基础却最重要的能力几乎所有的业务逻辑最终都会落到这四类操作上把这些基础打得扎实后面再去深入索引优化、读写分离、分库分表才有底气。别急着追求那些炫酷的高阶功能先把今天这几条SQL写到滚瓜烂熟遇到任何一张表都能快速做出增删查改你就已经超过不少人可以开始打开那张真正的进阶之门了。