从零开始学MySQL大多数人第一周会做的事就是建库、建表、插数据、查数据也就是大家常说的增删改查里的CCreate和RRetrieve。这套MySQL基础操作看起来简单但恰恰是后面所有复杂查询、性能优化、数据建模的地基。我见过不少同学在建表的时候就埋下字符集混乱、字段类型乱选、主键设计不合理这些坑等数据量一上来再回头改代价非常大。这篇就围绕创建、插入与查询三个核心操作把我实际使用中的经验、踩过的坑和推荐做法一次说清楚适合刚接触MySQL的初学者也适合基础不牢想系统过一遍的开发者。1. 动手建库之前先搞懂数据库、表和字段的关系1.1 数据库到底是个什么东西很多初学者会把MySQL、数据库、表这几层概念混在一起。实际上当你登录MySQL之后会看到一层叫数据库Database的逻辑容器数据库里面装的是一张张表Table表里才是真正的数据数据按字段Column和记录Row来组织。你可以把数据库理解成一个Excel文件表就是文件里的Sheet页字段就是Sheet页的列名每一条记录就是一行数据。这个类比虽然不完全准确但对理解层级关系足够用了。MySQL本身是一个数据库管理系统它负责管理多个数据库每个数据库之间相互隔离所以你在做项目时通常会给每个独立业务建一个数据库避免表名冲突和数据混乱。实际工作中你会发现连接MySQL时都要指定一个数据库名比如mysql -u root -p登录后还要执行USE db_name;才能操作里面的表。这一步没有做MySQL就会报No database selected这也是新手最常见的报错之一。1.2 设计表之前先想清楚你要存什么我见过太多人拿到需求直接CREATE TABLE建到一半发现字段不够又ALTER TABLE加列。这不完全是坏习惯但如果建表之前花几分钟列一下业务对象后面会省很多事。比如你做一个简单的用户管理功能先问自己几个问题一个用户有哪些属性用户名、密码、邮箱、手机号、注册时间、状态。哪些属性是唯一的用户名、邮箱、手机号通常要求唯一。哪些属性是必须的用户名和密码一般不能为空。哪些属性会频繁更新最后登录时间、状态这类。哪些属性根本不需要存比如用户的年龄更好的做法是存生日年龄随时可以通过生日计算。这些问题想清楚之后建表SQL基本上就成型了。我习惯先在纸上或者文本编辑器里把字段列表写出来标记好类型、是否为空、是否唯一再落成SQL语句。这样看起来多花了几分钟实际上避免了后面反复改表结构。1.3 常用字段类型怎么选字段类型选错是新手最容易忽视的问题。MySQL的字段类型很多但实际开发中常用的就那几类。整数类型INT够用就拿它特别大的数才用BIGINT。性别、状态码这种小范围枚举用TINYINT就足够。浮点与定点金额一定要用DECIMAL不要用FLOAT或DOUBLE因为浮点数会有精度丢失问题。0.1 0.2 在浮点里能算出 0.30000000000000004 这种结果放金额上就出事了。字符串长度不确定或者超长用TEXT但TEXT不能设默认值短字符串用VARCHAR一定要指定长度比如VARCHAR(64)。CHAR是定长字符串适合长度固定的场景比如手机号虽然现在手机号也可能有变化、MD5 摘要。日期时间DATETIME存年月日时分秒DATE只存日期TIMESTAMP有时区概念。业务上大多数情况用DATETIME就够了别用字符串存日期不然排序和范围查询都会很痛苦。字段类型的选择直接决定了数据的存储空间和查询效率。我见过用VARCHAR(255)存所有字段的省事做法表面省了思考实际查询性能、索引效率、存储空间全面吃亏。入门阶段就养成选对类型的习惯后面会轻松很多。2. 创建数据库和数据表一条SQL背后的完整逻辑2.1 创建数据库指定字符集和排序规则建库的SQL看起来简单实际有个特别重要的隐藏参数字符集。很多新手直接执行CREATE DATABASE mydb;结果默认字符集是latin1或者跟服务器配置相关后面插入中文时就出现乱码或Incorrect string value报错。推荐写法CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;几个关键点IF NOT EXISTS加上之后重复执行不会报错这在写初始化脚本时非常有用。utf8mb4才是真正的完整UTF-8编码支持四字节字符包括常用的 emoji 表情。MySQL里的utf8实际是utf8mb3存不了 emoji 和很多生僻字新项目一律用utf8mb4。utf8mb4_unicode_ci是排序规则ci表示大小写不敏感这种对于大多数业务比较省心。建完库可以执行SHOW CREATE DATABASE mydb;查看实际生效的字符集配置防止建库时被服务器默认配置影响而不自知。2.2 创建数据表主键、自增、非空和注释我见过不少建表时不写注释的过两个月自己都忘了某个字段是干嘛用的。数据库是要长期维护的该有的注释哪怕一句话都能救未来的自己。下面是以用户表为例的建表语句USE mydb; CREATE TABLE IF NOT EXISTS user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, password_hash CHAR(64) NOT NULL COMMENT 密码哈希值, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, phone VARCHAR(20) DEFAULT 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这段SQL里有几个关键设计id INT UNSIGNED NOT NULL AUTO_INCREMENT无符号整数做主键自增这是最常见的单机主键方案。UNSIGNED让正数范围翻倍INT UNSIGNED最大到 42 亿多一般业务够用。PRIMARY KEY (id)定义主键索引主键必须唯一且非空InnoDB 存储引擎下数据本身就是按主键组织的。UNIQUE KEY uk_username (username)给用户名加了唯一约束防止插入重复用户名。注意加了唯一约束之后重复插入会直接报错Duplicate entry业务代码要捕获这个异常。created_at和updated_at用了默认值CURRENT_TIMESTAMP插入时不需要手动填时间更新时updated_at还会自动变成当前时间。这是MySQL 5.6.5 之后支持的功能非常省事。数据量不大的表ENGINEInnoDB是首选支持事务、行级锁、崩溃恢复MySQL 5.5 之后默认就是 InnoDB新手不需要纠结其他引擎。2.3 建表之后怎么确认表结构建完表用下面几条命令确认结构是否符合预期DESC user; -- 查看字段信息 SHOW CREATE TABLE user; -- 查看完整建表语句 SHOW INDEX FROM user; -- 查看索引信息DESC会以表格形式展示字段名、类型、是否为空、键类型、默认值、额外信息这是日常查看表结构最常用的命令。SHOW CREATE TABLE则是把完整的建表语句原样展示出来用来确认字符集、引擎、索引等配置都正确。这两条命令我都建议新手下意识多敲一敲能帮你对表结构保持敏感。3. 插入数据单条、批量与自增ID的处理3.1 插入单条数据字段列表和值的顺序必须对应建好表之后插入数据是最直观的操作。最基本的语法INSERT INTO user (username, password_hash, email, phone) VALUES (zhangsan, 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8, zhangsanexample.com, 13800138000);几个容易出错的地方字段列表可以省略但不建议省略。省略时所有字段都要按表结构顺序写值一旦表结构调整过加了一个字段原来省略字段列表的SQL就全部出错。写清楚字段列表是最稳妥的做法。自增主键id可以不用写MySQL自动生成就算写了也会被忽略或报错取决于SQL_MODE设置。有默认值的字段也可以不写比如status和created_at让默认值生效即可。值列表的顺序必须和字段列表一一对应数量也必须一致否则报Column count doesnt match value count。3.2 批量插入一次插入多条记录插入大量数据时逐条执行INSERT会非常慢因为每次插入都要经历SQL解析、权限检查、事务提交默认开启自动提交等过程。更高效的做法是一次性拼接多条值。INSERT INTO user (username, password_hash, email, phone) VALUES (lisi, hash_value_1, lisiexample.com, 13800138001), (wangwu, hash_value_2, wangwuexample.com, 13800138002), (zhaoliu, hash_value_3, zhaoliuexample.com, 13800138003);批量插入是实际开发中的常用优化手段。需要注意的是一次插入的记录数也不是越多越好一般建议几百条到几千条一批太大了会占用较多内存和锁资源。如果数据量是百万级更合理的做法是分批插入比如每批5000条分200批完成。我实际测试过同样插入1万条数据逐条插入可能需要十几秒甚至更久而批量插入通常一两秒就能完成差距非常明显。原因不只是网络交互次数的减少更关键的是每次提交事务都有刷盘成本批量提交把成本摊薄了。3.3 插入时最容易出错的三个地方第一个坑是中文乱码或报Incorrect string value。这个基本可以确定是字符集问题。检查三个地方数据库字符集、表字符集、连接字符集。即使表和库都是utf8mb4连接字符集如果不是照样乱码。可以在连接后执行SET NAMES utf8mb4;或者在连接配置里指定characterEncodingUTF-8这是JDBC的配置方式。第二个坑是插入重复数据导致报错。如果你没有处理业务层的重复判断插入时遇到了唯一约束冲突SQL会直接抛异常。很多项目会采用INSERT ... ON DUPLICATE KEY UPDATE来应对意思是插入遇到主键或唯一键冲突时改为执行更新操作INSERT INTO user (id, username, password_hash, email) VALUES (1, zhangsan, new_hash_value, new_emailexample.com) ON DUPLICATE KEY UPDATE password_hash VALUES(password_hash), email VALUES(email);注意MySQL 8.0.20 之后VALUES()函数在ON DUPLICATE KEY UPDATE里被标记为废弃推荐用别名方式AS new来引用写法会变成INSERT INTO user (id, username, password_hash, email) VALUES (1, zhangsan, new_hash_value, new_emailexample.com) AS new ON DUPLICATE KEY UPDATE password_hash new.password_hash, email new.email;第三个坑是被自增ID的值搞蒙。删除表数据后用TRUNCATE TABLE和DELETE FROM效果完全不同TRUNCATE会重置自增计数器从1重新开始DELETE不清空计数器下一次插入的ID会在被删除的最大ID基础上继续。这在测试数据时容易产生困惑知道原理就不慌了。4. 查询数据SELECT的五个基本功4.1 最基础的查询全表查询和指定列查询插入数据之后查询是验证数据正确与否的第一手段。最简单的两条SELECT * FROM user; -- 查询所有列 SELECT id, username, email FROM user; -- 只查指定列新手阶段用SELECT *没问题但进入正式项目后要养成只查所需列的习惯。原因有三个网络传输数据量大无法利用覆盖索引代码可读性差别人不知道你具体用了哪些字段。不过调试阶段偶尔用SELECT *快速看数据是完全可以的。4.2 WHERE条件过滤别一次把全表捞出来查询数据很少需要全表数据基本都会带条件。WHERE子句是查询的核心SELECT id, username, email, created_at FROM user WHERE status 1 AND created_at 2025-01-01 00:00:00;这里要强调一个新手常犯的逻辑错误在多个条件时写多个AND表示同时满足写OR表示满足其一。AND和OR混用时AND的优先级高于OR如果不确定就加括号。比如要查状态为1或者VIP等级为3的用户同时还要是2025年注册的正确写法是SELECT * FROM user WHERE (status 1 OR vip_level 3) AND created_at 2025-01-01 00:00:00;如果你把括号去掉条件就变成了状态为1并且是2025年注册或者是VIP等级3这两种语义完全不同。这种bug在真实开发里出现过无数次排查起来也不算难但小白往往第一眼看不出来。4.3 ORDER BY排序与LIMIT分页查询结果的顺序默认是不保证的除非你显式指定排序。按时间倒序是最常见的需求SELECT id, username, created_at FROM user WHERE status 1 ORDER BY created_at DESC LIMIT 20;ORDER BY后面可以跟多个字段比如先按状态排序再按时间排序ORDER BY status ASC, created_at DESCLIMIT用于限制返回的记录数两个参数时可以偏移LIMIT 20, 10表示跳过20条取10条注意第一个数是偏移量不是页码。分页查询很多人会写成LIMIT (page-1)*pageSize, pageSize原理就是这个。不过分页查询在数据量大时性能会下降因为MySQL要扫描并丢弃掉前面的所有记录才能拿到目标页数据。这是后面优化要关注的事情入门阶段先分清楚LIMIT两个参数的含义就行。4.4 模糊查询LIKE与去重DISTINCT搜索功能经常用到模糊查询SELECT id, username FROM user WHERE username LIKE 张%; -- 以张开头的用户名%是通配符代表任意长度的任意字符_下划线代表单个任意字符。LIKE 张%表示以张开头LIKE %张%表示包含张后者因为前置百分号的存在索引基本用不上在小数据表上没事数据量一大就会慢。如果你想看某个字段有哪些不重复的值用DISTINCTSELECT DISTINCT status FROM user;这条会返回 status 字段所有不重复的值。注意DISTINCT作用在后面所有列上也就是多列组合去重不是单独某一列去重。4.5 NULL值的查询陷阱写查询时最容易漏掉的是NULL值的处理。用WHERE phone NULL查不到任何数据因为NULL不能通过等号来比较。判断NULL必须用IS NULL或IS NOT NULLSELECT id, username FROM user WHERE phone IS NULL; SELECT id, username FROM user WHERE email IS NOT NULL;另外注意空字符串和NULL是两回事。空字符串是有值但内容为空NULL是从未赋值。这会影响查询条件、唯一约束和统计函数的结果入门时就要学会区分。5. 入门阶段最容易翻车的几个坑5.1 乱码问题库、表、连接三层字符集都得管乱码是MySQL新手遇到的最多的问题之一也是最让人抓狂的问题。我最近几年总结的经验是乱码永远优先怀疑三个层面。第一层库和表字符集。用SHOW CREATE DATABASE mydb;和SHOW CREATE TABLE user;确认它们是不是utf8mb4。第二层是客户端连接字符集。在命令行执行SHOW VARIABLES LIKE character_set_connection;如果不是utf8mb4执行SET NAMES utf8mb4;。第三层是应用连接串的字符集配置比如Java的JDBC要加characterEncodingutf8Python的charsetutf8mb4。这三层任何一层不对都可能出现乱码。而且注意有些字符在某一层被转换后就不可逆了所以不要等数据写进去才发现乱码再修复非常被动。建议建库时直接指定字符集应用连接串显式指定字符集从源头上堵住。5.2 不带WHERE条件的UPDATE和DELETE这是我在培训同学的时候反复强调的一条红线UPDATE和DELETE不带WHERE就是全表操作。哪怕你只漏写了WHERE id 1里的条件整个表的数据都会被更新或删除。如果没有备份这种误操作几乎是灾难性的。两条安全习惯很重要第一执行 UPDATE/DELETE 之前先写一条同条件SELECT看看会命中哪些数据。比如要删除 id5 的用户先执行SELECT * FROM user WHERE id 5;确认无误再执行DELETE FROM user WHERE id 5;。第二事务中使用BEGIN开启事务执行完先SELECT验证结果再COMMIT提交确认结果不对就ROLLBACK回滚。命令行操作普通MySQL表默认是自动提交的但你可以显式关闭自动提交来避免误操作SET autocommit 0; DELETE FROM user WHERE id 5; SELECT * FROM user WHERE id 5; -- 确认已删除但还没提交 ROLLBACK; -- 反悔就回滚5.3 自增主键用完怎么办INT UNSIGNED主键的最大值是 4294967295也就是42亿多。听起来很大但如果表里的数据是日志、流水、埋点这类高频写入加上业务运行很多年并不是没有可能触顶。主键一旦用完插入任何数据都会报主键冲突这是硬性故障。应对方案有两个一是建表时直接用BIGINT UNSIGNED范围大得离谱二是如果已经用INT事后改表结构代价较大只能通过运维手段处理。所以对于可能产生海量数据的表一开始就选BIGINT是很多老手的默认做法。低成本买平安。5.4 批量插入遇到部分失败批量插入是整体成功的要么整体插入成功要么一条都不插入这是因为InnoDB默认把一条多值的INSERT语句当作一个事务处理。如果有某一条违反了唯一约束整批数据都不会插入。这也是为什么批量插入前最好先做一次数据清洗去重。否则你可能写了一个5000条的批量插入脚本因为其中一条重复就全部失败日志也看不出来问题在哪。定位的办法是把批量拆小或者用INSERT IGNORE忽略冲突记录但要清楚它会把其他错误也吞掉不建议无脑用。6. 下一步进阶初始化脚本、索引与事务6.1 把建库建表写成可重复执行的初始化脚本学完创建、插入、查询这些基础操作后我强烈建议你把建库建表语句整理成一个init.sql脚本像下面这样组织CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE mydb; CREATE TABLE IF NOT EXISTS user ( ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; CREATE TABLE IF NOT EXISTS user_login_log ( ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户登录日志表;然后通过mysql -u root -p init.sql一键执行。这样做的好处是新环境初始化时不需要手动敲几十条SQL也不会敲错而且脚本可以纳入版本管理表结构变更历史都看得清。注意IF NOT EXISTS在写初始化脚本时特别重要它保证了脚本可以被重复执行而不报错。不过这只适用于建库建表如果表结构已经发生变化就要引入专门的迁移工具了那是后面的内容。6.2 索引查询慢的时候先想到它建表时我们只加了主键和唯一键实际业务查询经常要根据其他字段过滤比如查所有状态为1的用户或者按创建时间排序。如果没有索引MySQL就只能全表扫描数据量大了之后查询会肉眼可见地变慢。给常用查询字段加索引的语法很简单CREATE INDEX idx_status ON user (status); CREATE INDEX idx_created_at ON user (created_at);但索引不是越多越好。每个索引都会占用存储空间并且每次INSERT、UPDATE、DELETE时都要额外维护索引。这里的原则是查询频繁且数据区分度高的字段适合建索引枚举值非常少比如性别、只有几个固定值的状态的字段加索引帮助有限不要在超长字符串上直接建索引可以考虑前缀索引。6.3 事务数据一致性最后一道防线我前面提到事务这里再稍微展开。InnoDB 支持事务事务有 ACID 四个特性但如果刚开始接触你只需要记住一件事多条SQL要么全部成功要么全部回滚。典型场景是转账。A账户扣钱和B账户加钱必须是一个原子操作不能出现A扣了钱B没到账START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果中间第二条SQL失败执行ROLLBACK;就能让第一条的扣款也撤销。掌握这个基本模型之后再去研究隔离级别、锁、MVCC这些进阶内容会顺手得多。最后再说一句我经常跟初学者讲MySQL入门不是一个看完就会的过程而是敲完才懂的过程。创建、插入与查询这三板斧看起来简单但每个操作背后都有字符集、数据类型、约束、事务这些值得琢磨的点。把我上面说的这些坑都亲自踩一遍你的基础才算真正打牢了。建表的时候多想一步插入的时候多看一眼字符集查询的时候先写WHERE再写SELECT这些习惯养成了后面学索引优化、读写分离、分库分表都会轻松很多。