前言

MySQL 作为当前应用最广泛的关系型数据库管理系统，是后端开发人员必须掌握的核心技能。本文不涉及空洞理论，而是系统梳理 MySQL 最基础且实用的知识点，涵盖基本概念、客户端/服务器架构、数据库与数据表操作、SQL 语句分类、存储引擎、字符集与校验规则、备份还原方法，以及主要数据类型（数值、字符串、日期时间、enum/set 等）。结合大量命令示例与原理说明，读者学完即可动手实践，快速构建完整的 MySQL 知识框架，少走弯路。

MySQL数据库初步认识

先厘清几个容易混淆的术语：mysql 是数据库客户端程序，mysqld 是数据库服务端进程（通常称为数据库服务），而“数据库”一般指按特定结构组织并存储在磁盘或内存中的数据集合。不过日常交流中提到的“mysql”或“mysql数据库”，通常泛指整个 MySQL 体系——本质上是一个基于客户端/服务器模式的网络服务。除 MySQL 外，还有 SQL Server、Oracle 等其他关系型数据库。

有人可能会问：用普通文件也能保存数据，为什么非要用数据库？因为文件系统没有提供完善的数据管理能力，而 MySQL 对 SQL 关键字不区分大小写，例如 create database t1; 和 CREATE DATABASE t1; 效果完全相同。

服务器，数据库和表的关系

创建数据库，本质就是在 Linux 系统下生成一个目录。在数据库中创建表，则是在对应目录下生成相应的二进制文件。因此，数据库的底层仍然是文件——只不过由数据库服务统一管理。安装数据库服务器，实际上是安装了数据库管理系统程序，该系统程序可以管理多个数据库实例。

MySQL体系的架构

MySQL的基本使用

连接服务器

连接命令格式为：mysql -h 主机地址 -P 端口号 -u 用户名 -p。例如 mysql -h 127.0.0.1 -P 3306 -u root -p。注意 -p 会提示输入密码，输入时不显示明文。如果省略 -h 则默认连接本机，省略 -P 则默认使用 3306 端口。因此 mysql -uroot -p 等价于 mysql -h 127.0.0.1 -P 3306 -u root -p 的简写形式。

SQL的分类

SQL 按照功能可分为三大类：DDL（数据定义语言）用于维护数据结构，例如 create；DML（数据操纵语言）用于操作数据，例如 insert；DCL（数据控制语言）负责权限管理和事务控制，例如 grant。

存储引擎

存储引擎是数据库管理系统实现数据存储、索引构建、查询更新等操作的具体方法。查看当前支持的存储引擎可使用 show engines;，加上 G 参数可使显示更整齐。常用引擎包括 MyISAM 和 InnoDB，其中 InnoDB 使用更广泛。

创建数据库

创建数据库的语法：create database [if not exists] db_name [create_specification...];。其中 create_specification 可指定字符集（character set charset_name 或 charset=charset_name）和校验规则（collate collation_name 或 collate=collation_name）。if not exists 表示仅当数据库不存在时才创建，若已存在则仅给出警告而不报错。db_name 为数据库名称。若未指定字符集和校验规则，则使用配置文件中的默认值（通常为 utf8 和 utf8_general_ci）。

例如：create database if not exists text1 charset=utf8 collate utf8_general_ci; 执行后，实质上会在 /var/lib/mysql 目录下创建一个新目录。注意不要在没有启动 MySQL 服务的情况下手动进入该目录创建——虽然技术上可行，但不推荐这样做。

字符集和校验规则

查看系统默认字符集和校验规则

查看默认字符集：show variables like 'character_set_database';；查看默认校验规则：show variables like 'collation_database';。此处 % 是 LIKE 子句中的通配符，例如 show variables like 'collation_%'; 会列出所有以 collation_ 开头的变量。

查看数据库支持的字符集和校验规则

查看支持的字符集：show charset;；查看支持的校验规则：show collation;。数据库字符集（编码集）决定了能够存储哪些类型的数据，例如 utf8 支持中文；校验规则决定了字段的比较和排序方式。数据库对数据执行任何操作时，都必须确保操作与编码一致。

校验规则对数据库的影响

一个字符集通常对应多个校验规则。例如在 utf8 的校验规则中，utf8_general_ci 不区分大小写，而 utf8_bin 区分大小写。

查看MySQL的连接情况

使用 show processlist 可以查看当前连接到本机 MySQL 的用户及其会话信息。

使用数据库

数据库名称不可重命名——也不建议随意修改，因为上层依赖该数据库的应用也需要同步调整。

查看数据库

show databases;

显示创建语句

show create database db_name; 会显示创建该数据库的完整 SQL 语句，其中 /*!40100 default.... */ 并非注释，而是表示如果当前 MySQL 版本高于 4.01，则执行该部分内容。

修改数据库

alter database db_name [alter_specification...];，其中规格参数与创建时相同。

删除数据库

drop database [if exists] db_name;，如果加上 if exists，当数据库不存在时仅给出警告而不报错。本质上会删除 /var/lib/mysql 中对应的目录。

数据库的备份

备份操作会将创建数据库、建表以及导入数据的语句全部保存到备份文件中。语法：mysqldump -P3306 -u root -p [-B] 数据库名 > 备份文件路径。加上 -B 参数会同时包括创建数据库和切换数据库的语句；不加 -B 则需要手动先创建数据库并切换。备份文件建议使用 .sql 后缀。如果只想备份指定表：mysqldump -u root -p 数据库名 表名1 表名2 ... > 文件路径。备份多个数据库：mysqldump -u root -p [-B] 库名1 库名2 ... > 文件路径。还原使用 source 备份文件完整路径;。切换数据库使用 use 数据库名;（无需先退出当前数据库）。在 MySQL 内部使用 system clear; 可以清屏。

MySQL表的操作

创建表

创建表的语法格式：

create table [if not exists] table_name (
    field1 datatype [其他属性如 comment '注释'],
    field2 datatype [其他属性],
    ...
) [character set 字符集] [collate 校验规则] [engine 存储引擎];

最后一个字段后不加逗号。其他属性没有严格的书写顺序。最后一行若未指定则采用配置文件默认值（存储引擎默认通常为 InnoDB）。comment 用于添加字段注释。注意不同存储引擎生成的文件不同：MyISAM 会生成 .frm（表结构）、.MYD、.MYI（数据与索引分开存储）；InnoDB 则只有 .frm 和 .ibd（数据与索引合并存储）。

示例：create table if not exists users(id int, name varchar(20) comment '用户名');

查看表

desc 表名; 查看表结构，返回字段名（Field）、类型（Type）、是否允许空（NULL）、索引类型（Key）、默认值（Default）、扩充信息（Extra）。若要查看创建时的详细定义，使用 show create table 表名; 或加上 G 使显示更整齐。

修改表

修改表结构的常用命令：

• alter table table_name add (column datatype [其他属性] [,...]); 默认追加到末尾，若要插入到指定列后加 after column1，插入到开头加 first。
• alter table table_name modify (column datatype [其他属性]); 修改字段属性。
• alter table table_name rename [to] now_table_name; 修改表名，有无 to 均可。
• rename table table_name to now_table_name, ...; 可一次修改多个表名，此处的 to 不可省略。
• alter table table_name change 原列名 新列名 新列名的datatype [其他属性]; 修改列名。
• alter table table_name drop (column1 [,...]); 删除字段。

注意：表名和列名不要随意更改，因为上层代码需要同步调整。如果只对单个字段操作，可以省略括号。

向表中插入数据：insert [into] 表名 values(字段值[,...]); 或 insert [into] 表名 (列名1[,...]) values(字段值[,...]);。

查询表中数据

select 要查的列名1[,...] from table_name; 查询所有列使用 select * from table_name;。注意这是查看表内的具体数据，而 desc 是查看表结构。

删除表

drop [temporary] table [if exists] table_name1, table_name2 ...;，temporary 仅用于删除临时表。例如 drop table if exists user1, user2;。也可以删除表中的单个属性：alter table 表名 drop primary key;。

向 MySQL 插入不合法的数据会被直接拦截——这样能保证数据的合法性。

MySQL数据类型的分类

数值类型

tinyint

tinyint [unsigned] 占用 1 字节，有符号范围 -128~127，无符号范围 0~255。通常建议尽量不使用 unsigned，如果数值范围不够，直接升级到更大的数据类型即可，但年龄字段使用 tinyint unsigned 是很合适的选择。

bit

bit(M) 位字段类型，M 表示位数，默认值为 1。例如 bit(3) 取值 0~7，插入时应使用数字而非二进制字符串。

小数类型

float

float[(m, d)] [unsigned]，m 指定显示总长度，d 指定小数位数，占用 4 字节。例如 float(4,2) 范围 -99.99~99.99，加上 unsigned 则范围 0.00~99.99。精度不足时会自动补零，精度过高会四舍五入，但如果四舍五入后超出 m 则报错。不指定 m、d 时，可以在 float 允许的范围内任意长度并保留原始小数位数。

decimal

decimal(m, d) [unsigned]，m 默认 10，d 默认 0。与 float 的主要区别在于精度更高——MySQL 5.0 之后 float 大约在小数点后第 7 位开始不精确，而 decimal 能保持更高的精度。当然，float 固定占用 4 字节，而 decimal 占用的字节数与位数相关。

字符串类型

定长与变长的选择：定长会浪费空间但效率更高，变长则按实际需求动态分配。定长的意义在于预先分配好固定空间，变长则在不超过自定义范围时按需分配。

char

char(L) 固定长度字符串，L 表示字符数（而非字节数），最大值为 255。注意单位是字符——中文、逗号等均算作一个字符。

varchar

varchar(L) 可变长度字符串，L 也是字符数，最大为 65535 字节。实际占用空间根据插入的字符串长度决定。L 的最大值受编码集以及待分配空间影响，而且 65535 需要先减去 1~3 字节用于记录数据大小。例如在 utf8 编码中，一个字符占 3 字节。

日期和时间的类型

常见三种日期时间类型：

• date：格式 'yyyy-mm-dd'，占用 3 字节。
• datetime：格式 'yyyy-mm-dd HH:ii:ss'，范围 1000~9999，占用 8 字节。
• timestamp：格式也为 'yyyy-mm-dd HH:ii:ss'，插入时若不指定值会自动填入当前时间。

enum和set

enum 用于多选一，set 用于多选多或多选一。例如 enum('男','女')，插入时填入 '男' 或 1。例如 set('手机','电脑','平板')，插入时填入 '手机,电脑' 或 11（二进制表示）。

查询时严格匹配 set 中某个选项：select * from 表名 where 列名='选项';。查询包含某个选项（不要求唯一匹配）：select * from 表名 where find_in_set('选项', 列名);。若要匹配多个选项，使用 and 分别写条件。