MySQL这门课我接触了十年最早也是从“数据库和表的基本操作”入门的。说实话刚开始觉得无非就是CREATE、DROP、ALTER那几条命令后来在真实项目里踩了无数坑才明白基础操作里藏着大量细节比如字符集选错导致乱码、字段类型不合适导致慢查询、误删表后怎么救。这期内容我打算把MySQL里数据库和表的基本操作完整拆一遍不只讲语法还会把每个操作背后的原理、适用场景、常见误区讲清楚适合刚学SQL的同学打基础也适合写过一段时间但体系不牢的开发者查漏补缺。这里要说明一点我对MySQL的版本约定是5.7如果你用的是8.0及以上个别语法和默认行为有差异我会在文中标注出来。下面直接进入正题。1. 动手之前的准备工作客户端选型与基础认知1.1 图形化工具和命令行到底选哪个很多新手一上来就问用Navicat还是用MySQL Workbench我的建议是图形工具可以用但命令行必须会。原因很简单图形工具点按钮生成的SQL你看不到背后发生了什么一旦遇到生产环境没有图形界面、只能SSH连接的情况你就懵了。还有一点命令行能让你对SQL语句本身更敏感比如多写一个分号、漏了一个逗号都能立刻感受到语法的严谨性。我个人的习惯是本地开发用图形工具做快速数据浏览但所有涉及表结构的变更都先在命令行里跑一遍确认无误后再执行。这样既高效又稳妥。常用的命令行连接方式如下mysql -u root -p如果指定主机和端口mysql -h 127.0.0.1 -P 3306 -u root -p提示-h 默认是localhost-P 默认是3306如果都用默认值直接mysql -u root -p就行大写P是端口小写p是密码别搞混了。1.2 连接MySQL时的几个关键参数连接MySQL不只是输入用户名密码那么简单有几个参数直接影响后续操作。第一个是字符集参数。连接时如果不指定字符集即使数据库建表时用了utf8mb4客户端传输时也可能因为连接字符集不对导致乱码。所以在命令行里建议连接后先执行SET NAMES utf8mb4;或者在启动连接时指定mysql --default-character-setutf8mb4 -u root -p第二个是默认数据库参数。如果不想每次写SQL都带库名可以用-D指定默认数据库mysql -u root -p -D test_db连接后当前库就是test_db直接写SELECT * FROM users就能查到test_db.users里的数据。第三个是超时参数。命令行连接一般不会超时但如果你写脚本连MySQL要关注wait_timeout和interactive_timeout这两个值默认是8小时看起来很长但如果是短连接频繁建立反而容易把连接数打满。生产环境里我更倾向于用连接池这个问题后面有机会再说。1.3 库和表的逻辑关系用生活例子讲透数据库和表的关系我习惯用“仓库”和“货架”来理解。一个MySQL实例就像一个大型仓库仓库里可以划分出很多独立区域每个区域就是“数据库”。MySQL里的数据库其实是一个命名空间它把表、视图、存储过程、函数、触发器等对象归拢在一起方便管理和权限控制。不同数据库之间的表是隔离的但可以在同一个实例里互相访问前提是用户有权限。每个数据库里可以有好多张“货架”也就是表。表本身是二维结构由行和列组成。列定义了数据的类型和名称行则是具体的一条条记录。比如一张users表列可能是id、name、email每一行就是一个真实用户的数据。理解了这个关系你就知道“切换到某个数据库”只是在告诉MySQL你接下来想操作哪个区域里的表。执行USE db_name就是切换仓库区域不切的话就得用db_name.table_name这种完整写法来引用表。2. 数据库的基本操作从创建到删除的完整闭环2.1 创建数据库的语法和字符集选择创建数据库是一个看似简单、实则暗藏玄机的操作。最简单的写法CREATE DATABASE school;但这样创建出来的库会使用MySQL服务端的默认字符集和排序规则。如果你所在环境的默认字符集是latin1那后续存中文就会出现乱码。所以我强烈建议任何时候建库都显式指定字符集CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;这里有两个关键参数CHARACTER SET是字符集utf8mb4是UTF-8的完整实现支持4字节的emoji和生僻字。老项目里常见的utf8其实是utf8mb3最多3字节根本存不了emoji。从MySQL 8.0开始默认字符集已经是utf8mb4了但5.7及更早版本不是所以建库时显式指定最保险。COLLATE是排序规则utf8mb4_unicode_ci表示基于Unicode的、大小写不敏感的排序。如果你更在意中文排序准确性可以用utf8mb4_zh_0900_as_cs8.0支持但一般业务场景用utf8mb4_unicode_ci就够了。注意排序规则里的_ci表示 case insensitive即大小写不敏感。如果需要区分大小写用_cs或_bin。utf8mb4_bin是二进制排序比较时严格区分大小写适合存储区分大小写的编码场景。创建数据库时还可以判断是否存在CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;加上IF NOT EXISTS后如果库已存在MySQL不会报错只是给一个warning。这个写法适合在初始化脚本里使用避免反复执行报错。2.2 查看和切换数据库创建完数据库先看看当前实例里有哪些库SHOW DATABASES;这个命令会列出当前用户有权限看到的所有数据库。如果你想看某个库的创建语句包括字符集定义SHOW CREATE DATABASE school;切换到某个库USE school;切换后当前会话的“默认库”就变成了school以后写表名不需要再带库名前缀。还有一个命令我经常用就是查看当前所在的库SELECT DATABASE();如果在没切库的状态下它会返回NULL提醒你还没有指定默认库。2.3 修改数据库参数与删除数据库数据库创建之后有些参数是可以改的比如默认字符集和排序规则ALTER DATABASE school DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;这个操作只影响默认值已经存在的表不会自动改只有新建的表才会继承新的默认值。所以如果是线上库需要改字符集千万别只执行这一句后续还要对已有表逐一修改。删除数据库DROP DATABASE school;加上判断DROP DATABASE IF EXISTS school;这个操作有多危险一个数据库里可能有几十张表DROP DATABASE会连同表、视图、存储过程、函数、触发器、事件全部删除而且不走回收站。所以我在生产环境从来不敢直接执行它非删不可时也会先mysqldump备份再找非业务低峰期执行且必须有二次确认。2.4 实操心得字符集与排序规则别等出问题再改我在某项目上吃过一次亏数据库建的时候没指定字符集默认是latin1软件上线后中文写入全部变成“???”只能把库里的表全部改一遍备份恢复折腾了整晚。所以现在的习惯是新项目初始化时我会写一个标准建库脚本CREATE DATABASE IF NOT EXISTS project_core DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;同时把所有建表语句里的字段类型也统一用utf8mb4。在MySQL 8.0里默认字符集已经是utf8mb4但我依然会显式声明原因很简单团队里如果某个人本地或测试环境的MySQL版本是5.7默认字符集还是latin1不显式声明就会触发迁移陷阱。答应我建库时多写几个字符别偷这个懒。3. 数据表的核心操作创建、查看、修改、删除3.1 建表前先梳理字段类型和约束建表之前最忌讳的是脑子一热直接开写。我一般会先拿出一张纸列出业务需要哪些字段、每个字段的类型是什么、要不要允许NULL、有没有默认值、主键选什么、哪些字段需要唯一约束。比如要建一张学生表可能包含学号、姓名、性别、年龄、入学时间、邮箱、手机号。建表前先思考学号字符串类型定长且唯一适合做主键。姓名字符串可变长度用VARCHAR(50)足够。性别可以用CHAR(1)存男/女或M/F也可以用ENUM。年龄要不要存如果只是展示当前年龄可以不存用出生日期计算如果要存用TINYINT UNSIGNED即可不需要INT。入学时间日期类型用DATE如果还要精确到时分秒用DATETIME。邮箱VARCHAR(100)或者VARCHAR(255)加唯一约束。手机号VARCHAR(20)或CHAR(11)因为手机号前可能有86或分机号VARCHAR更灵活。这个梳理过程能帮你避免两种错误一是把全部字段都定义成VARCHAR(255)导致索引长度受限、存储膨胀二是对不需要NULL约束的字段放任NULL导致查询时出现IS NULL和某个具体值的比较结果不一致的问题。3.2 常用字段类型选择实战字段类型选得好表和索引效率高一截。这里整理几个高频类型直接对照选择数据类型存储容量适用场景注意事项TINYINT1字节状态值0/1年龄有符号范围-128~127INT4字节一般整数主键范围约±21亿BIGINT8字节雪花ID、自增主键如果预期数据量过亿用它DECIMAL(M,D)变长金额、价格绝不用FLOAT存钱DATE3字节生日、日期格式YYYY-MM-DDDATETIME8字节创建时间带时区无法存储暂不用TIMESTAMP4字节自动记录时间受2038年问题影响CHAR(N)定长身份证号、手机号、MD5长度固定时用VARCHAR(N)变长名称、邮箱、地址N代表字符数非字节数TEXT变长长文本无法全文索引建议抽取一个特别容易踩的坑是用FLOAT存金额。浮点类型在计算机里是近似存储计算累计金额时会出现0.10.20.30000000000000004的尴尬结果。金额必须用DECIMAL比如DECIMAL(10,2)表示最多8位整数2位小数足够覆盖绝大多数业务。3.3 创建表的完整SQL示例回到刚才的学生表一个完整的建表SQL长这样CREATE TABLE student ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT 自增主键, student_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender ENUM(M,F) NOT NULL DEFAULT M COMMENT 性别, birth_date DATE DEFAULT NULL COMMENT 出生日期, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, phone VARCHAR(20) DEFAULT NULL COMMENT 联系方式, 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_student_no (student_no), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT学生表;这里有几个细节想重点说AUTO_INCREMENT自增列必须建立索引通常就是主键。如果表里用BIGINT那主键就用BIGINT UNSIGNED避免自增到负数。DEFAULT CURRENT_TIMESTAMP可以在插入时不传创建时间MySQL自动填充当前时间。ON UPDATE CURRENT_TIMESTAMP会在行更新时自动刷新updated_at这个组合在业务表里很实用。UNIQUE KEY加在学号和邮箱上防止重复数据。存储引擎明确写成InnoDB不要用MyISAM。原因很简单InnoDB支持事务、行级锁、外键和崩溃恢复MyISAM除了某些全表扫描场景几乎没优势。建表后可以用下面的命令确认表结构DESC student; SHOW CREATE TABLE student;DESC能看到列名、类型、是否为空、默认值、是否为主键等信息。SHOW CREATE TABLE能看清完整的建表语句包括索引名、字符集、存储引擎迁移数据库时这个输出就是标准参考。3.4 修改表结构ALTER TABLE的细节项目上线后改表结构太常见了比如加列、删列、改列类型、加索引。几个高频操作添加列ALTER TABLE student ADD COLUMN class_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 班级ID AFTER name;AFTER name表示把新列加在name后面。如果不想指定位置可以不加AFTER新列默认加在最后。修改列类型ALTER TABLE student MODIFY COLUMN name VARCHAR(100) NOT NULL COMMENT 姓名;MODIFY COLUMN会修改列的完整定义包括类型、约束、注释相当于把原来那一列重新定义一遍。如果只想改类型其他属性也要完整写出来否则会被重置。修改列名ALTER TABLE student CHANGE COLUMN gender sex ENUM(M,F) NOT NULL DEFAULT M COMMENT 性别;CHANGE COLUMN比MODIFY COLUMN多了一个新列名参数因为它既改名字又改定义。需要注意CHANGE必须写出完整的列定义否则默认值和注释会丢。删除列ALTER TABLE student DROP COLUMN class_id;添加索引ALTER TABLE student ADD INDEX idx_name (name);删除索引ALTER TABLE student DROP INDEX idx_name;修改表结构时InnoDB对很多操作会重建表比如修改列类型、添加索引。所以大表上执行ALTER一定要看准时机。几条经验业务低峰期执行大表重建可能锁表或复制延迟很久。先确认磁盘空间足够因为操作会产生临时表文件。MySQL 5.7的在线DDL能力有限8.0优化了很多但依然不建议上班时间在几千万行的表上直接改结构。3.5 删除表与清空表TRUNCATE和DELETE的区别删除表DROP TABLE student;这个操作会连表结构和数据一起删掉表里如果有外键引用可能报错。我一般建议如果没有100%确认表废弃先RENAME TABLE student TO student_bak_20250101保留一段时间确认无影响再删。清空表数据但不删表结构有两个选择TRUNCATE TABLE student; DELETE FROM student;两者区别非常大TRUNCATE是DDL操作直接释放整个表的存储空间速度极快执行后自增ID会从1重新开始而且无法通过事务回滚。DELETE是DML操作逐行删除支持WHERE条件速度慢但可以回滚自增ID不会重置。如果只是测试数据想快速清空并重置自增列用TRUNCATE。如果只删部分数据或需要事务保护用DELETE。4. 常见问题与排查技巧实录4.1 连接不上数据库怎么办连接MySQL失败是新手遇到最多的坑报错基本分几类。第一类ERROR 2003 (HY000): Cant connect to MySQL server on localhost。这个说明MySQL服务没启动或者端口不对。先检查服务是否在运行比如在Linux上执行systemctl status mysqldWindows上检查服务管理器弹窗里MySQL对应的服务是否已启动。第二类ERROR 1045 (28000): Access denied for user rootlocalhost。这表示用户名或密码错误也可能是该用户不允许从当前主机登录。先核对密码如果密码忘了需要跳过授权表重置这个操作比较险具体不展开生产环境请找有经验的人操作。第三类ERROR 1130 (HY000): Host xxx is not allowed to connect to this MySQL server。这是用户权限问题说明你的账号只允许在某个主机登录。解决办法是授权用户允许从对应IP访问标准的授权方式是CREATE USER app10.0.0.10 IDENTIFIED BY password; GRANT ALL PRIVILEGED ON app_db.* TO app10.0.0.10; FLUSH PRIVILEGES;不要偷懒用app%直接开放所有主机权限收得越紧越好。4.2 中文乱码的根源和解决方案中文乱码的根源只有一个数据某几个环节的字符集不一致。一个完整的链路包括客户端字符集、连接字符集、服务器字符集、数据库字符集、表字符集、字段字符集。其中任何一环不一致写入和读取就可能出问题。排查乱码先看当前会话的字符集SHOW VARIABLES LIKE character_set%;重点看character_set_client、character_set_connection、character_set_results这三个变量应该都是utf8mb4。如果不是执行SET NAMES utf8mb4;。再看数据库、表、字段的字符集SHOW CREATE DATABASE school; SHOW CREATE TABLE student;如果发现表或字段是latin1或utf8需要修改。修改表默认字符集ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CONVERT TO会同时转换表字符集和所有字符串字段的字符集但要注意如果表里有LONGTEXT或巨大长度字段这个操作锁表时间会很长同样需要低峰期执行。4.3 字段类型选错导致的数据异常类型选错的表现是慢、错、丢。慢字符串类型做主键。如果用VARCHAR存手机号当主键InnoDB的聚簇索引会非常臃肿每次插入数据还可能要页分裂性能远不如BIGINT自增主键。所以除非有强业务场景否则一律用无意义的自增ID做主键唯一业务编号用唯一索引约束。错日期存成字符串。很多人喜欢用VARCHAR存日期觉得“格式自己控制比较灵活”。可一旦要按日期范围查询字符串比较的结果和真实日期顺序可能不一致比如2025-1-1按字符串排序会比2024-12-31靠前导致范围查询出错。日期就用DATE或DATETIME。丢ID用INT但数据量增长超过21亿。MySQL INT最大2147483647很多业务上线三五年就达到千万级看起来离21亿很远但如果你把订单ID直接暴露在URL里被恶意刷或者好几个来源同时写入自增ID耗尽的速度比想象快。预留余量用BIGINT是成本最低的保险。4.4 误删数据的应急处理思路误删数据手别抖先做这几件事第一立刻停止写入把相关表或库设为只读或者直接把应用停掉防止新数据覆盖旧数据的存储空间。第二尽快用备份恢复。如果你有mysqldump的定时备份把备份恢复到临时实例然后导出需要的数据再导回生产。日常备份命令参考mysqldump -u root -p school school_20250101.sql恢复也简单mysql -u root -p school school_20250101.sql第三如果你开了binlog并且确认binlog覆盖了误删之前的时间点可以考虑用mysqlbinlog解析binlog反向生成恢复语句。这个过程很复杂通常需要专业人士现场操作。我的建议是与其事后恢复不如平时就养成好习惯每次执行DROP或TRUNCATE前先备份一遍目标库再动手。最后说一个关于表结构变更的土办法改表前先用SHOW CREATE TABLE把建表语句存到本地文件命名成student_table_bak_20250101.sql。万一改坏了可以直接重建表再导入备份数据。这个习惯救过我很多次推荐你也用起来。说实话数据库和表的基本操作听起来基础但越基础的东西越容易被忽视。我见过不少写了几年SQL的开发者遇到“数据库和表的基本操作”这部分内容时不屑一顾直到线上出现问题才回头补课。我个人从这些坑里走过后最大的体会是基础操作不是背语法而是要理解MySQL在背后做了什么字符集为什么不能乱选表结构为什么不能上来就写删除为什么不能无脑执行。这些认知一旦建立起来后面学复杂查询、事务、索引调优都会顺很多。如果你刚接触MySQL建议准备一个干净的测试环境把本文的命令挨个敲一遍建库、建表、改表、删表每个动作执行后都用SHOW CREATE TABLE或DESC看一遍结构变化。只有亲手感受过才能把这些细节变成肌肉记忆。下一篇我会继续聊“MySQL数据库之二”——插入、更新、删除数据的高级技巧包括批量插入性能对比、UPDATE误操作防护、大事务拆分思路到时候见。
