目录一、回顾 MYSQL 对象层次结构二、创建数据库语法与案例三、字符集与校验规则1. 什么是字符集2. 查看系统字符集3. 校验规则4. 校验规则对数据库的影响四、数据库操作1. 查看数据库2. 使用数据库3. 查看当前数据库4. 显示数据库创建语句5. 修改数据库6. 删除数据库五、数据库备份与恢复1. 数据库备份2. 数据库恢复3. 数据库还原原理六、查看连接情况1. 查看当前连接情况2. processlist 字段解析七、创建数据表1. 创建表语法2. 创建表案例八、查看表结构1. 查看当前数据库中的表2. 查看表结构3. 查看建表语句九、修改/删除表1. 添加字段2. 删除字段3. 修改字段4. 修改字段名称5. 修改表名6. 删除表7. 删除表注意事项总结一、回顾 MYSQL 对象层次结构在设计数据库之前我们需要在脑海中清晰构建MySQL严谨的对象层次结构。这套体系能帮助我们定位数据、规划业务架构MySQL 内部的数据组织呈现四层结构第一层数据库服务器实例物理本质在后台长驻运行的 mysqld 进程。它独占计算机的内存与磁盘存储空间是整个数据库系统的顶层物理边界第二层逻辑数据库物理本质在 mysqld 数据目录下创建的物理文件夹。它是各个独立业务如电商、OA的独立命名空间各个库之间在逻辑上互不越界第三层数据表物理本质在对应数据库文件夹内部创建的物理数据文件例如 InnoDB 引擎下的 .ibd 文件。表是关系的物理载体严格定义了业务实体的 Schema 骨架列数、数据类型、约束第四层行记录与字段物理本质存储在16KB物理数据页中的紧密排列二进制数据片段。列定义了数据结构而行则记录了实际业务实体的具体信息理解了从实例→文件夹→物理文件→页内行数据这一层级结构后当我们进行建库操作时就不再是单纯面对文本指令而是能联想到操作系统在磁盘底层创建文件夹的实际过程二、创建数据库在 MySQL 的实际应用中创建逻辑数据库远非简单的执行命令。特别是在生产环境中必须审慎考虑命名规范、字符集兼容以及排序规则语法与案例在 MySQL 中创建数据库的标准语法结构如下CREATE DATABASE [IF NOT EXISTS] db_name [CHARSETcharset_name] [COLLATEcollation_name];语法拆解方括号 [] 内的内容代表可选参数。IF NOT EXISTS 是防御性子句。如果线上自动化脚本在执行建库时该库已经存在不加此子句会导致 SQL 报错中断而加上之后MySQL 只会弹出一个 Warning 并继续向下执行确保了部署脚本的健壮性基础建库案例在终端中执行以下最简建库命令CREATE DATABASE IF NOT EXISTS test_db;这条指令一旦执行成功mysqld 就会在磁盘上创建一个名为 test_db 的真实文件夹。因为我们没有显式指定字符集和校验规则它会默认继承 MySQL 服务器全局配置的默认值指定字符集与校验规则创建数据库在开发支持多语言或 Emoji 表情的现代 Web 系统如电商、社交平台时无脑使用系统默认配置极易埋下乱码的隐患。标准的工业级建库手法必须显式指定字符集与校验规则CREATE DATABASE IF NOT EXISTS shop_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;CHARACTER SET: 指定数据库采用的字符集可以缩写为 CHARSETCOLLATE: 指定数据库字符集的校验规则三、字符集与校验规则在真实的生产环境中这两大设定直接决定了数据的物理存储格式与逻辑比对行为是保障数据库字符安全的根本准则1. 什么是字符集定义字符集是一套文字符号的物理编码规则。它定义了计算机底层如何将人类可读的字符汉字、英文、Emoji等转换成机器识别的二进制字节以及如何将这些字节反向解码为什么必须是 utf8mb4在早期MySQL 默认的 utf8 字符集其实是一个 阉割版最大只支持 3 字节。当用户在起网名或发评论时带入了一个 Emoji 表情通常占 4 字节旧版的 utf8 就会因为字节溢出导致后端抛出异常甚至引发系统崩溃。utf8mb4是 UTF-8 的完全实现版本全面兼容所有 Unicode 字符可完整支持 Emoji 表情和各类生僻汉字。作为当前互联网行业的标准编码方案它已成为业界广泛采用的强制规范2. 查看系统字符集我们可以通过以下两条核心查询指令去查看服务器当前的字符集配置① 查看系统当前的默认字符集SHOW VARIABLES LIKE character_set_%;执行后服务器会输出一张变量矩阵character_set_server 代表 MySQL 服务器全局的默认字符集character_set_database 代表当前切入的数据库所继承的字符集② 查看当前引擎支持的字符集SHOW CHARACTER SET;3. 校验规则字符集解决的是数据 如何安全存储的问题而校验规则解决 如何进行比对与排序的问题定义校验规则是一套用于比较字符大小、决定排序先后以及判断字符是否相等的算法规则校验规则依赖于字符集。每一种字符集都拥有一套或多套属于它的校验规则。比如针对 utf8mb4 字符集MySQL 就衍生出了 utf8mb4_general_ci、utf8mb4_bin 等多种规则查看支持的校验规则① 查看当前支持的所有校验规则SHOW COLLATION;以 utf8mb4_ 开头的说明它们是专为 utf8mb4 字符集服务的以后缀 _ciCase Insensitive结尾的代表大小写不敏感以后缀 _binBinary结尾的代表基于二进制编码直接比对② 查看正在生效的默认校验规则SHOW VARIABLES LIKE collation_%;4. 校验规则对数据库的影响校验规则的算法不同会对上层的业务查询产生颠覆性的影响。假设我们在一个校验规则为 _ci大小写不敏感的数据库里执行SELECT * FROM users WHERE username abc;此时mysqld 在底层比对数据页时会启动字母淡化算法认为 abc、ABC、Abc 完全是同一个东西从而把它们全部检索出来而一旦我们将规则切换为 _bin二进制比对由于大写字母 AASCII 码 65与小写字母 aASCII 码 97的二进制机器码截然不同引擎会执行绝对精确的硬匹配此时查询 abc 就绝对不可能把 ABC 检索出来四、数据库操作在日常开发和线上运维中我们需要频繁地对逻辑数据库进行查看、切换、修改和清理。以下是工业界最常用的控制命令1. 查看数据库当你想知道当前的 mysqld 实例中究竟托管了哪些项目时可以使用以下指令SHOW DATABASES;information_schemaMySQL 自身的元数据仓库里面存放着整个服务器所有的表名、列名、访问权限等字典信息mysql核心配置库存放着用户账号、核心权限、时区等机密数据performance_schema专门用于收集数据库运行期性能指标sys基于前面几个系统库封装的视图方便运维人员快速排查死锁、慢查询等故障2. 使用数据库如前文所述在对具体的表执行增删改查之前必须先切入某一个命名空间USE test_db;3. 查看当前数据库如果由于高频切换而忘记了当前在哪个库中可以调用内置的 DATABASE() 函数进行查看SELECT DATABASE();4. 显示数据库创建语句在团队协作中如果你想知道前人创建某个旧数据库时究竟用了什么字符集和校验规则可以使用以下命令打印SHOW CREATE DATABASE test_db;回显中出现的 /*!40100 ... */ 并不是普通的注释它被称为 条件注释。它的意思是如果当前 MySQL 的版本号大于或等于 4.01.00就无条件执行注释内部的语句。这保证了 SQL 脚本在不同历史版本之间的平稳兼容性5. 修改数据库如果项目在线上运行了一段时间发现旧库的配置无法满足新业务可以使用 ALTER DATABASE 语句进行在线重构-- 将 test_db 的校验规则修改为 utf8mb4_bin ALTER DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;修改数据库的字符集和校验规则只会对该命令执行之后新创建的表生效。对于已经在库中存在的旧表它们依然会保留旧的规则。如果想让旧表全量生效必须针对每张表单独执行ALTER TABLE 重构6. 删除数据库当项目下线或者测试环境需要清空时可以使用 DROP 命令进行销毁DROP DATABASE [IF EXISTS] test_db;加上 IF EXISTS 防御性子句可以确保在数据库不存在时脚本不报错、中断在终端执行 DROP 命令时MySQL 守护进程会执行以下操作立刻发起系统调用将物理磁盘上名为 test_db 的真实文件夹删除文件夹内部存放的所有表的物理数据文件.ibd 文件会被操作系统抹去数据瞬间消失。因为这个动作不经过任何操作系统垃圾箱且在 MySQL 中无法通过 ROLLBACK 撤销。这就是为什么普通研发人员的账号通常没有 DROP 权限的原因五、数据库备份与恢复在真实的互联网生产环境中硬件故障、黑客攻击以及开发人员疲劳操作导致的误删库风险时刻威胁着企业的数据安全。因此实现自动化备份与平滑恢复能力成为必须坚守的数据生命线1. 数据库备份既然我们强调 数据库本质就是操作系统下的物理文件夹那当我们需要备份时能不能直接用操作系统的 cp -r 命令或者拖拽压缩包的方式把数据库文件夹拷贝一份出来呢答案是绝对不行线上数据库随时随地都有海量的网络线程在并发读写。当你正在复制物理文件时MySQL 的存储引擎层可能正在内存里修改着某些 16KB 的数据页且尚未刷写到磁盘。此时直接复制出来的文件内部的二进制编码、索引结构往往是对不齐的。这种文件在未来恢复时会直接触发系统的校验错误导致数据库因底层物理损坏而拒绝启动因此工业界必须使用 MySQL 官方提供的专用备份工具——mysqldumpmysqldump工具mysqldump 是一个常驻在 MySQL 安装目录下的可执行命令行工具注意它是一个独立的程序需要在系统终端中运行而不是在 mysql 提示符内运行mysqldump -u用户名 -p密码 [可选参数] 数据库名 /绝对路径/备份文件名.sql实战案例假设我们要将测试 test_db 数据库全量备份到 Linux 系统的 /var/mysql_backup/ 目录下并命名为 test_db_backup.sql# 在 Linux 操作系统的 Shell 终端中敲下此命令 mysqldump -uroot -pxxx test_db /var/mysql_backup/test_db_backup.sql如果想一次性把服务器上的多个库打包备份可以加上 --databases 参数mysqldump -uroot -pxxx --databases shop_db finance_db multi_db.sql2. 数据库恢复假设此时线上不幸发生人为灾难test_db 被人恶意用 DROP 命令删除。我们需要利用刚刚生成的 脚本在最短时间内将数据恢复恢复数据主要有两种标准方法在操作系统终端执行重定向最常用在恢复前因为原库已经被彻底 DROP 物理销毁了我们必须先登录 mysql 命令行重新手动拉起一个同名的空壳数据库-- 先在 mysql 客户端内创建一个干净的空库 CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;接着退出 mysql 客户端回到操作系统的 Shell 终端利用输入重定向将备份的 SQL 文本倒灌进去# 在操作系统的终端中运行将备份文件灌入刚刚建好的空库中 mysql -uroot -pxxx test_db /var/mysql_backup/test_db_backup.sql在 MySQL 客户端内部恢复如果你已经登录了 MySQL 终端不想频繁切出到操作系统也可以采用以下方式-- 1. 先确保空壳数据库已建好并切入 USE test_db; -- 2. 直接在 mysql 提示符内利用 source 命令加载外部 SQL 脚本 source /var/mysql_backup/test_db_backup.sql;3. 数据库还原原理source 为什么 mysqldump 生成的 .sql 文本文件能将已经删除的数据库完美复活我们可以用文本编辑器强行打开 test_db_backup.sql 备份文件一探究竟。可以看到一系列清晰可读的纯文本SQL语句核心底层逻辑mysqldump 的本质并不是去拷贝磁盘上的二进制 .ibd 数据页它做的事情叫 逻辑备份备份期逆向解构它通过扫描数据库的元数据把当前库里的表结构、真实的行记录全部逆向翻译、重组成一条条标准的 CREATE TABLE 和 INSERT INTO 的 DDL 与 DML 纯文本命令恢复期正向重放当我们试图恢复数据时其实就是命令 mysqld 的 SQL层重新把曾经的建表、插入数据的命令按照历史顺序原封不动地重新在内存和磁盘里 重放 了一遍这种还原机制确保了通过 mysqldump 导出的备份文件具有出色的跨平台兼容性在 Windows 系统下导出的数据可以无缝还原到运行 Linux 的远程 MySQL 服务器中六、查看连接情况当线上生产环境出现突发状况如大面积请求超时、接口响应延迟或 CPU 使用率瞬时飙升至 100% 时切忌慌乱无措地盲目猜测问题原因此时最常用的指令是SHOW PROCESSLIST。它能实时显示当前所有连接到服务器的客户端及其正在执行的SQL语句1. 查看当前连接情况SHOW PROCESSLIST;如果当前并发较高为了防止某些超长的 SQL 文本被强制截断建议使用其完全体版本SHOW FULL PROCESSLIST;服务器执行后将输出一张详尽的实时活动表2. processlist 字段解析① Id连接/线程唯一标识MySQL 为每一个连入的客户端指派的唯一会话 ID当发现10号线程正在执行一条危险 SQL 语句并已卡死秒时可以通过终端直接执行 KILL 10 命令来紧急处理。MySQL 会立即强制终止该线程的连接并自动释放该线程持有的所有锁资源从而实现快速恢复线上服务② User 与 HostUser 代表当前登录的数据库账号Host 展示客户端的IP 地址与端口号③ db当前数据库展示了该连接目前切换到了哪一个命名空间下。如果显示为 NULL说明该客户端刚刚通过网络认证登录进来还没来得及指定任何目标库④ Command线程当前的运行期动作指令这是排查性能瓶颈的核心指标。最常见的几种工业状态包括Sleep代表该连接当前处于空闲休眠状态。也就是说客户端虽然跟 MySQL 维持着 TCP 长连接但此时此刻并没有任何 SQL 语句交过来Query代表该线程活跃目前正在执行某条 SQL 语句⑤ Time状态持续秒数代表当前这个线程处于当前 Command 状态下已经持续消耗了多少秒。如果一个线程的 Command 是 Query而 Time 已经高达上百秒这就说明该线程遇到了严重的 死锁阻塞必须立刻介入⑥ State微观执行状态展示了 SQL 语句在服务层或存储引擎层流转时的微观卡点。例如Sending data说明优化器已经定下了执行计划执行器和引擎正在磁盘与内存页之间搜寻、搬运、组装数据并开始通过网络套接字向客户端回传⑦ Info真实的 SQL 文本此处会直接显示当前连接正在执行的 SQL 语句。使用 SHOW FULL PROCESSLIST 命令时它会完整展示所有 SQL 语句七、创建数据表在 MySQL 的世界里表是二维关系模型的物理载体。创建一张数据表不仅需要规定它有多少列更需要规划每个字段的存储容器与业务约束1. 创建表语法在 MySQL 中在当前切入的逻辑数据库下创建一张新表的标准语法如下CREATE TABLE [IF NOT EXISTS] table_name ( field1 datatype [constraints] [COMMENT 列注释], field2 datatype [constraints] [COMMENT 列注释], ... fieldN datatype [constraints] [COMMENT 列注释] ) [ENGINEengine_name] [DEFAULT CHARSETcharset_name] [COLLATEcollation_name] [COMMENT 表注释];语法要素field字段名称datatype数据类型如整数 INT、定长字符串 CHAR、变长字符串 VARCHAR 等。决定该列占多少个字节的物理空间constraints列约束可选例如规定该列是否允许为空NOT NULL、是否有默认值DEFAULTENGINE / CHARSET / COLLATE表的物理级参数。MySQL 的存储引擎和字符集是基于表级别可插拔配置的。如果不写这张表将自动继承它所属数据库的字符集与校验规则并使用服务器默认的 InnoDB 引擎2. 创建表案例以一个高频电商或教务系统常用的 用户主体表 为例演示一套建表流程USE test_db; CREATE TABLE IF NOT EXISTS users_tbl ( id INT COMMENT 用户全局唯一编号, username VARCHAR(50) COMMENT 用户登录账号, age INT COMMENT 用户实体年龄 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin COMMENT用户核心主体信息表;为了防止表名与 MySQL 内部的系统保留关键字如 users发生冲突通常会为表名加上 _tbltable或 _bbase等特定业务后缀八、查看表结构MySQL 提供了三种标准指令分别用于查看表的逻辑清单、字段微观定义以及物理建表语句1. 查看当前数据库中的表在明确切入某一个逻辑数据库之后若要盘点当前命名空间下的数据表可执行以下命令SHOW TABLES;2. 查看表结构若需要进一步下沉到表内部查看其定义的字段名称、数据类型以及列级约束可以使用 DESCRIBE 指令该指令可简写为 DESCDESC users_tbl;字段属性解析Field字段名称即数据表定义的列名Type数据类型明确限制了该列在底层内所占据的物理存储空间与编码格式Null是否允许为空。如果为 YES代表当插入数据未指定该列时系统会用 NULL 填充如果为 NO则该列属于强约束必填项Key索引标记。由于未引入主键及非主键索引此列目前显示为空Default默认值。在未显式插入数据时该字段自发填充的预设值Extra附加扩展信息例如自增属性 auto_increment 等3. 查看建表语句DESCRIBE 指令虽然能够展示清晰的结构但它抹去了我们在建表时写入的列注释、表注释以及特定的物理存储参数。若需要还原该表在创建时的完整 SQL 骨架应使用以下指令SHOW CREATE TABLE users_tbl;通过返回的结果我们可以获取以下信息显式补全属性MySQL 在物理存储层面会自动补全 DEFAULT NULL 约束即使建表语句中未显式声明这一信息也会完整记录在表定义中物理参数审查能够明确审查该表当前的存储引擎ENGINEInnoDB、字符集CHARSETutf8mb4以及校验规则COLLATEutf8mb4_bin这是线上故障排查与数据迁移时的重要依据九、修改/删除表随着业务需求的不断演进最初设计的表结构往往无法完全满足后续的功能迭代。此时我们需要使用 ALTER TABLE 系列语句对已经存在的数据表进行重构需要注意的是在生产环境中对已拥有大量数据的表执行 ALTER 操作属于高风险行为必须严格遵循标准的 DDL 语法。以下是五种最常用的表结构变更操作1. 添加字段当需要为已有表引入新的业务维度时可以使用 ADD 子句在表的末尾或指定位置追加新的列ALTER TABLE table_name ADD field_name datatype [constraints] [AFTER existing_field / FIRST];案例为 users_tbl 表追加一个用于记录用户电子邮箱的字段 email要求其类型为变长字符串且紧跟在 username 字段的后面ALTER TABLE users_tbl ADD email VARCHAR(32) COMMENT 用户电子邮箱 AFTER username;可以发现 email 字段已被插入到指定位置2. 删除字段若某个字段因业务线调整不再使用为节省物理存储空间并提升 I/O 效率可将其从表结构中将其抹除ALTER TABLE table_name DROP field_name;案例将 users_tbl 表中的年龄字段 age 执行物理删除ALTER TABLE users_tbl DROP age;DROP 列操作属于不可逆的物理变更。该列对应的所有历史行记录数据会在磁盘文件中同步被抹去且无法通过回滚进行恢复。在线上环境操作前必须确保该字段在应用层已无任何代码依赖并做好逻辑备份3. 修改字段当字段无法承载现有的业务数据例如原本定义的 VARCHAR(50) 长度超限需要扩容到 VARCHAR(150)或者需要调整其约束与注释时可以使用 MODIFY 子句ALTER TABLE table_name MODIFY field_name new_datatype [new_constraints];案例将 users_tbl 表中的 username 字段的长度由 50 扩容至 150ALTER TABLE users_tbl MODIFY username VARCHAR(150) COLLATE utf8mb4_bin COMMENT 用户登录账号扩容版;使用 MODIFY 修改字段属性时必须在语句中完整地重新声明该字段所需的全部属性如字符集、校验规则、是否为空等。如果未显式声明原有的部分默认属性可能会被新定义的规则直接覆盖。此外物理类型缩容如 INT 变 TINYINT若遇到已有数据溢出会导致变更失败4. 修改字段名称若需要在一个动作中同时修改字段的字面名称以及底层的物理属性MODIFY 将无法支持此时必须使用功能更全面的 CHANGE 子句ALTER TABLE table_name CHANGE old_field_name new_field_name new_datatype [new_constraints];案例将 users_tbl 表中的 id 字段更名为 user_id并将其数据类型由原本的 INT 变更为更适合分布式全局唯一的 BIGINTALTER TABLE users_tbl CHANGE id user_id BIGINT COMMENT 用户全局唯一大整数编号;5. 修改表名在业务重构或数据库版本迁移中如果需要变更整张二维表名称可使用 RENAME TO 子句ALTER TABLE old_table_name RENAME TO new_table_name;案例将当前的 users_tbl 表名修改为更符合系统规范的 account_tblALTER TABLE users_tbl RENAME TO account_tbl;6. 删除表在当前数据库上下文环境下擦除一张数据表及其内部所有行记录的标准语法如下DROP TABLE [IF EXISTS] table_name;案例若要将我们之前重命名后的 account_tbl 表从系统中彻底销毁可执行以下 DDL 指令DROP TABLE IF EXISTS account_tbl;终端回显 Query OK, 0 rows affected代表该表在逻辑层与物理层均已被成功抹除7. 删除表注意事项当 DROP TABLE 被执行时存储引擎层会直接触发系统调用在操作系统的文件系统中将对应的 物理数据文件直接删除这意味着该表在磁盘上所有数据页以及构建索引都会在一瞬间被标记为废弃并清空。由于 DDL 操作不经过任何垃圾箱且无法在事务中被回滚这在线上生产环境中极易导致灾难性后果。为此工业界推行了以下三条铁律① 严禁在线上直接执行未经评审的 DROP在互联网企业中任何对 DROP 特权关键字的使用必须通过 DBA数据库管理员及核心架构师的评审并只能在低流量的凌晨运维窗口期执行。在应用层的数据库连接账号中普通微服务所持有的权限应当被死死锁在 DML增删改与 DQL查询之内绝对不允许在线上赋予应用账号 DROP 权限② 理解 DROP、TRUNCATE 与 DELETE 的本质许多初学者容易混淆这三个同样带有“删除”含义的关键字。在工业治理中它们有着明确的性能和安全边界DROP TABLE属于最高危的DDL。它既销毁数据也拆毁表的物理骨架同时在磁盘上释放 .ibd 文件的空间TRUNCATE TABLE属于高效的DDL。它保留表结构但清空所有行记录并重置自增计数器。它的原理是直接释放数据页的数据区其效率远超按行删除但同样无法回滚DELETE FROM属于标准的DML。它用于按条件逻辑删除或按行清除内容但表的结构、索引树完全不动。因为它是逐行给记录打上删除标记并且会高频写入日志所以可以被回滚安全性最高但面对海量数据时 I/O 吞吐极慢③ 先下线、后隔离、再销毁在真实的工业重构中要销毁一张重要的数据表绝对不允许一上马就敲下 DROP。标准的操作如下第一步代码下线通过全局搜索将微服务集群中所有对该表的读写代码全部下线或解耦观察监控至少 1 到 2 周确保没有任何流量触达此表第二步逻辑隔离不删除表而是通过 ALTER TABLE ... RENAME TO ... 指令为表名加上诸如 _bak_20260609 这样的备份后缀将其在逻辑上隔离保护起来第三步物理落地在隔离期内如果系统未发生任何异常报警说明该表确实已成死表。此时在确保已有冷备份的前提下方可最终执行 DROP TABLE 释放物理磁盘空间总结综上所述我们已经掌握了 MySQL 中数据库与数据表的基本管理方法包括数据库的创建、修改、删除、备份与恢复以及数据表的创建、查看、修改和删除等常见操作在此过程中我们还进一步认识了字符集与校验规则的作用并通过实验理解了不同校验规则对于字符串比较和查询结果的影响。与此同时通过 show processlist 等命令我们也从客户端与服务端模型的角度进一步理解了 MySQL 的连接管理机制至此我们已经能够完成数据库对象的管理工作但新的问题也随之出现表已经创建好了那么表中的每个字段应该使用什么数据类型这些问题都属于数据库表设计中最基础、也是最重要的内容因此在下一篇中我们将正式学习 MySQL 的数据类型体系深入理解数值类型、字符串类型以及日期时间类型的特点与适用场景为后续学习约束、索引以及数据库设计打下基础