跳到主要内容
MySQL 数据库表操作:数据类型与约束详解 | 极客日志
SQL
MySQL 数据库表操作:数据类型与约束详解 详细讲解了 MySQL 数据库表的核心操作,涵盖表的创建、查看、修改与删除流程。重点解析了数值、字符串、日期及枚举集合等数据类型的使用规范与精度差异,特别是 float 与 decimal 的区别。此外,文章深入剖析了各类表约束机制,包括非空、默认值、主键、自增、唯一键及外键的定义与应用场景,旨在帮助开发者构建数据完整且高效的数据库结构。
不知所云 发布于 2026/3/16 更新于 2026/6/16 24 浏览
在深入 MySQL 表操作之前,我们需要先选定具体的数据库。如果不确定当前选中的是哪个库,可以直接查询。
mysql> use learn1;
Database changed
mysql> select database();
一、表的操作
1. 创建表
创建表是定义数据结构的基础。语法结构如下:
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) CHARACTER SET 字符集 COLLATE 校验规则 ENGINE 存储引擎;
其中 field 表示列名,datatype 表示列类型。若未指定字符集或校验规则,则默认继承所在数据库的设置。
2. 查看表
查看表结构通常使用 DESC 命令,它能快速展示字段信息。
desc 表名;
3. 修改表
实际开发中,经常需要调整表结构,比如修改字段名、类型、大小,或者增减字段。这里不存在'查找'操作,只有增删改。
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][, column datatype]...);
ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][, column datatype]...);
ALTER TABLE tablename DROP (column );
4. 删除表
DROP TABLE t1;
说明:
像 CREATE, DROP, SHOW TABLES 这类操作属于 DDL(数据定义语言),它们操作的是表的结构。而 SELECT 操作的是表的内容,属于 DML。目前我们学到的库和表的基本操作主要归类为 DDL。
5. 案例实战
CREATE TABLE IF NOT EXISTS user1 (
id INT ,
name VARCHAR (20 ) COMMENT '用户名' ,
password VARCHAR (20 ) COMMENT '用户密码' ,
birthday DATE COMMENT '用户生日'
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE MyISAM;
CREATE TABLE IF NOT EXISTS user2 (
id INT ,
name VARCHAR (20 ) COMMENT '用户名' ,
password VARCHAR (20 ) COMMENT '用户密码' ,
birthday DATE COMMENT '用户生日'
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE= InnoDB;
注意 IF NOT EXISTS 表示表不存在时才创建。两种存储引擎的文件结构不同:
MyISAM :生成三个文件,users.frm(表结构)、users.MYD(表数据)、users.MYI(表索引)。
InnoDB :生成两个文件,Users.frm(表结构)、Users.ibd(表数据 & 表索引)。
执行相应的 INSERT, DELETE, SELECT 语句即可管理数据。
MySQL 会记录用户的操作痕迹,包括建表、删表等行为。查看创建表语句时,有时会发现设置编码的字段不见了,这是因为服务器内部进行了语法优化。
mysql> SHOW TABLES;
+
| Tables_in_learn1 |
+
| u1 |
| user1 |
+
mysql> ALTER TABLE user1 RENAME TO user ;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+
| Tables_in_learn1 |
+
| u1 |
| user |
+
ALTER TABLE tablename ADD column_name datatype AFTER existing_column;
AFTER 表示你想指定新增到那一列的后面。插入新字段后,对原来表中的数据没有影响,之前的数据仍然保留。
ALTER TABLE tablename MODIFY column_name new_datatype;
我们发现 name 字段大小确实已经改变,且不影响原有数据。但需要注意的是,如果使用 MODIFY 修改字段属性,它会覆盖该字段原有的所有属性(如 COMMENT)。如果未来想改某一列,建议把这一列曾经的所有属性全部复制下来,再重新提交。
ALTER TABLE user DROP password;
ALTER TABLE user CHANGE name xingming VARCHAR (60 ) DEFAULT NULL ;
CHANGE 后面跟着旧列名,再跟新列名和属性。列名称要改不仅仅需要提供新列名称,这个列的相关属性也需要完整定义,因为修改是覆盖实现的。
二、数据类型
1. 数据类型分类 MySQL 中每种数据类型都有其特定用途,类似于 C/C++ 等语言中的类型系统。常见的类型包括数值、字符串、日期时间等。
2. 数值类型 数值类型分为位类型、布尔类型、整数类型、浮点数类型等。以下主要以整型为例。
整数类型包括 TINYINT, SMALLINT 等,它们根据名称不同,所占字节数也不同。默认情况下是有符号类型的,取值范围与 C/C++ 对应整数范围相同。如果加上 UNSIGNED,则表示无符号类型。
2.1 TINYINT 类型 以 TINYINT 为例,其他整数类型使用方法类似。
CREATE TABLE IF NOT EXISTS t1(num TINYINT);
查看表结构时,TINYINT 后面有一个数字 4,这代表显示宽度。在 MySQL Server 8.0.17 之后,在不使用 ZEROFILL 修饰符时已弃用显示宽度,所以 TINYINT 后没有 4 也是正常的。
插入数据时,TINYINT 默认有符号,取值范围 -128~127。超出范围的数据无法插入。
可以通过 UNSIGNED 指定为无符号类型,范围变为 0~255。
mysql> INSERT INTO t1 VALUES (-129 );
ERROR 1264 (22003 ): Out of range value for column 'num' at row 1
mysql> INSERT INTO t1 VALUES (-128 );
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t2 VALUES (128 );
Query OK, 1 row affected (0.00 sec)
虽然无符号类型范围扩大,但建议在设计时将 INT 提升为 BIGINT 而不是依赖 UNSIGNED,以防数据增长导致溢出。
约束机制:
如果我们向 MySQL 特定类型中插入不合法数据,MySQL 通常会直接拦截,保证数据的完整性。这意味着一旦成功插入,数据一定是合法的,不会发生截断或隐式类型转化。
2.2 BIT 类型 语法: BIT [(M)],M 表示比特位数,范围 1 到 64。默认为 1。
CREATE TABLE t3(id INT , online BIT(1 ));
使用一个比特位表示用户是否在线,只能插入 0 或 1。查询时通常按 ASCII 码值显示,不可见内容可用 HEX 函数以十六进制形式查看。
2.3 浮点数类型
2.3.1 FLOAT 语法: FLOAT [(M, D)] [UNSIGNED],M 指定显示长度,D 指定小数位数,占用 4 个字节。
CREATE TABLE t4(id INT , salary FLOAT (4 ,2 ));
FLOAT(4,2) 范围约为 -99.99 ~ 99.99。插入数据时精度不够补 0,超过则四舍五入。浮点数存储可能存在精度损失。
2.3.2 DECIMAL 语法: DECIMAL(M, D) [UNSIGNED],定点数,M 指定总长度,D 指定小数位数。
DECIMAL 能有效避免精度损失。FLOAT 默认精度约 7 位,而 DECIMAL 可精确到 65 位整数和 30 位小数。对于高精度场景(如金额),应优先使用 DECIMAL。
CREATE TABLE t6 (id INT , salary FLOAT (10 ,8 ), salary2 DECIMAL (10 ,8 ));
INSERT INTO t6 VALUES (100 , 23.12345612 , 23.12345612 );
SELECT * FROM t6;
可以看到 FLOAT 存储的值发生了微小变化,而 DECIMAL 保持了原样。DECIMAL 能完全按照怎么存就怎么取的方式工作。
3. 字符串类型
3.1 CHAR 语法: CHAR(L),固定长度字符串,L 最大 255。
CREATE TABLE IF NOT EXISTS t7(id INT , name CHAR (2 ));
插入数据时,如果超过定义长度不允许插入。MySQL 中的字符概念不同于 C/C++,一个汉字就是一个字符。CHAR(2) 表示存放两个字符,可以是字母或汉字。
总结:
CHAR 括号内填的是固定长度上限,一旦定义好空间就申请好了。如果用多少是你的事,给多少由 L 决定。如果插入字符超过 L 就不让你插。
3.2 VARCHAR 语法: VARCHAR(L),可变长度字符串,L 最大 65535 字节。
与 CHAR 的区别在于:VARCHAR 是变长的,实际使用空间根据字符串长度动态分配,而 CHAR 是固定长度。关于 VARCHAR(len),len 的值与表的编码密切相关。
MySQL 的 utf8 不是真正的 UTF-8 编码。在 utf8 编码中,单个字符占 3 个字节,因此 varchar(n) 参数 n 最大值为 21844(65535 / 3 ≈ 21845,减去长度记录字节)。
真正的 UTF-8 支持 4 个字节(如 emoji、繁体字),MySQL 称之为 utf8mb4。MySQL 官网在 2010 年发布了 utf8mb4 来支持真正的 UTF-8。现在 MySQL 8.0 之后,默认字符集已变为 utf8mb4。
建议: 所有使用 utf8 的用户都应该改用 utf8mb4,永远不要再使用旧的 utf8。
总结:
VARCHAR 有自己长度上限,范围内用多少给多少。最大字节数为 65535,需预留 1-4 个字节记录实际字符长度。在 UTF8 下最大字符个数是 16383,如果有其他字段,这个值会变小。
3.3 CHAR VS VARCHAR
共同点 :都能保存字符串,都有上限。
区别 :CHAR 定长,一次分配固定空间;VARCHAR 变长,根据实际使用分配。
实际存储 char(4) varchar(4) char 占用字节 varchar 占用字节 abcd abcd abcd 4*3=12 4*3+1=13 A A A 4*3=12 1*3+1=4 Abcde × × 数据超过长度 数据超过长度
数据确定长度一样(如身份证、手机号、MD5),使用定长 CHAR。
数据长度有变化(如名字、地址),使用变长 VARCHAR,但要保证最长能存进去。
定长磁盘空间浪费但效率高。
变长磁盘空间节省但效率低。
4. 日期和时间类型
DATE :日期 'yyyy-mm-dd',占 3 字节。
DATETIME :日期时间 'yyyy-mm-dd HH:ii:ss',范围 1000 到 9999,占 8 字节,手动设置。
TIMESTAMP :时间戳,从 1970 年开始,占 4 字节,自动更新。
CREATE TABLE t10(t1 DATE , t2 DATETIME, t3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
INSERT INTO t10(t1, t2) VALUES ('1997-7-1' , '2008-8-8 12:1:1' );
SELECT * FROM t10;
注意: TIMESTAMP 会自动更新,适用于记录最后修改时间。DATETIME 适合存储固定时间,如入职时间。
5. ENUM 和 SET
ENUM :枚举,'单选'类型。实际存储的是数字编号(1, 2, 3...),最多 65535 个选项。
SET :集合,'多选'类型。实际存储的是位图(1, 2, 4, 8...),最多 64 个选项。
不建议添加枚举值或集合值时采用数字方式,不利于阅读。
CREATE TABLE votes(
username VARCHAR (30 ),
hobby SET ('clime' ,'swim' ,'draw' ),
gender ENUM('男' ,'女' )
);
ENUM 提供约束,插入时只能插入枚举限定的常量或对应下标(从 1 开始)。SET 插入数字代表位图,例如插入 3 表示前两个爱好都被选中。
对于集合类型字段,可以使用 FIND_IN_SET 进行查询。此函数用于确定子串是否存在于逗号分隔的字符串列表中。
SELECT FIND_IN_SET('b' , 'a,b,c' );
SELECT FIND_IN_SET('ab' , 'a,b,c' );
返回值解释:存在返回下标(从 1 开始),不存在返回 0。非 0 即真,0 即假。
应用实例:查找用户爱好包含 'client' 的记录。
SELECT * FROM votes WHERE FIND_IN_SET('clime' , hobby);
三、表的约束 真正约束字段的是数据类型,但为了更好保证数据合法性,还需要额外约束。约束的最终目标是保证数据的完整性和可预期性。
3.1 空属性(NULL/NOT NULL) NULL 表示列可以为空,NOT NULL 表示列不能为空。
通过 NOT NULL,可以设置某列数据在插入时必须填入具体值,否则会报错。例如注册账号时的必填信息。
CREATE TABLE myclass(
class_name VARCHAR (20 ) NOT NULL ,
other VARCHAR (20 )
);
在此示例中,class_name 设置了 NOT NULL,而 other 默认允许为空。如果插入时未指定 class_name 且无默认值,会报错。
3.2 默认值(DEFAULT) DEFAULT:当插入数据时,如果未指定该列的值,将使用默认值。
CREATE TABLE IF NOT EXISTS student(
name VARCHAR (20 ) NOT NULL ,
age TINYINT UNSIGNED DEFAULT 18 ,
gender VARCHAR (10 ) DEFAULT '男'
);
DEFAULT 和 NOT NULL 并不冲突,而是互相补充的。当用户指明这一列要插的时候,受 NULL 和 NOT NULL 约束;当用户忽略这一列的时候,如果设置了默认值使用默认值,如果没有则可能报错。
COMMENT:用于给列添加注释说明,便于程序员和数据库管理员理解字段用途。该属性不会对数据插入产生约束效果,相当于代码里的注释。
CREATE TABLE IF NOT EXISTS t5(
name VARCHAR (20 ) NOT NULL COMMENT '用户的用户名' ,
age TINYINT UNSIGNED DEFAULT 18 COMMENT '用户的年龄'
);
3.4 ZEROFILL ZEROFILL:在数字前补零,使显示字符长度符合指定的位数。数据库存储的数值不变,仅用于展示效果。
CREATE TABLE IF NOT EXISTS t6(a INT UNSIGNED, b INT UNSIGNED ZEROFILL);
INSERT INTO t6 VALUES (111 , 222 );
SELECT * FROM t6;
我们会发现,ZEROFILL 会让 222 显示为 000000222。验证一下:值不会改变,只是一种格式化输出。
3.5 主键(PRIMARY KEY) PRIMARY KEY:用于标识表中的唯一记录,不允许重复或为空。表中最多只能有一个主键列,但可以通过复合主键的方式使用多列联合唯一标识。
创建表的时候就把主键设置好。
表建好之后但没有主键,可以追加主键。
CREATE TABLE t7(id INT PRIMARY KEY , name VARCHAR (20 ));
INSERT INTO t7 VALUES (1 , 'amy' );
INSERT INTO t7 VALUES (1 , 'anna' );
主键约束倒逼程序员插入时不要出现冲突,同时也保证了根据主键绝对能取出确定的一条记录。
ALTER TABLE t7 DROP PRIMARY KEY ;
ALTER TABLE t7 ADD PRIMARY KEY (id);
一张表只能有一个主键,但这个主键可以由多列组成。例如让两列合起来充当一个主键:
CREATE TABLE t8(id INT , name VARCHAR (20 ), PRIMARY KEY (id, name));
这里 id 和 name 都是主键,但它们合起来才是一个主键。只有多个同时和历史数据一样才会出现主键冲突。
3.6 自增长(AUTO_INCREMENT) AUTO_INCREMENT:字段自动增长,从当前最大值加 1,通常配合主键使用,确保值唯一。
任何字段要做自增长,前提本身是一个索引。
自增长字段必须是整数。
一张表最多只能有一个自增长。
CREATE TABLE t10(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20 ));
INSERT INTO t10(name) VALUES ('a' );
INSERT INTO t10(name) VALUES ('b' );
SELECT * FROM t10;
插入时可以不指定 id,系统自动赋值并增长。如果指定 id 插入,也能进行,但再插入相同值时会触发主键冲突。
默认行为:若未设置起始值,默认从 1 开始。
手动设置起始值:如果手动插入一个新的起始值且大于历史值,自增主键将从新的起始值开始。
设置 AUTO_INCREMENT 的原理:
除了表内设置,还可以在表外设置 AUTO_INCREMENT 的值,代表下一次插入的起始值。
CREATE TABLE t11(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20 )) AUTO_INCREMENT= 100 ;
获取上次插入的 AUTO_INCREMENT 值可使用 LAST_INSERT_ID() 函数。单条插入返回最后一次插入的值,批量插入返回第一个值。
3.7 唯一键(UNIQUE KEY) 定义: 一张表中往往有很多字段需要唯一性,数据不能重复,但一张表中只能有一个主键,此时唯一键就可以解决表中有多个字段需要唯一性约束的问题。
主键 :标识唯一性,主要用于唯一标识记录,不允许为空。
唯一键 :更多用于业务逻辑上的唯一性约束,允许字段为空,且多个空值不会影响唯一性。
示例场景: 员工管理系统中,身份证号作为主键,工号设置为唯一键。
CREATE TABLE student(
id CHAR (10 ) UNIQUE COMMENT '学号,不能重复,但可以为空' ,
name VARCHAR (10 )
);
INSERT INTO student(id, name) VALUES ('01' , 'aaa' );
INSERT INTO student(id, name) VALUES ('01' , 'bbb' );
INSERT INTO student(id, name) VALUES (NULL , 'bbb' );
唯一键和主键不冲突,可以理解为对主键的补充设置。一般而言,建议将主键设计成和当前业务无关的字段,这样业务调整时不会做过大的改动。
3.8 外键(FOREIGN KEY) 外键: 从表和主表的关联关系,产生外键约束。为什么需要外键约束?为了确保表间数据的一致性,例如防止插入一个不存在班级的学生或删除一个还有学生的班级。
定义: 外键约束用于建立主表和从表之间的关联关系,主要定义在从表上,主表必须包含主键或唯一键。当定义外键后,要求外键列数据必须在主表的主键列存在或为 NULL。
FOREIGN KEY (字段名) REFERENCES 主表 (列)
CREATE TABLE stu(
id INT PRIMARY KEY ,
name VARCHAR (30 ) NOT NULL COMMENT '学生名' ,
class_id INT ,
FOREIGN KEY (class_id) REFERENCES class(id)
);
INSERT INTO class VALUES (10 , '1班' ), (20 , '2班' );
INSERT INTO stu VALUES (1 , '1 班' , 10 ), (2 , '2 班' , 20 );
INSERT INTO stu VALUES (30 , '1 班' , 3 );
如果两张表在业务上有相关性但没有建立约束关系,就可能出现问题。建立外键的本质就是把相关性交给 MySQL 去审核,提前告诉 MySQL 表之间的约束关系。当用户插入不符合业务逻辑的数据时,MySQL 不允许你插入。
相关免费在线工具 SQL 美化和格式化 在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
SQL转CSV/JSON/XML 解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 工具包 CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
Base64 字符串编码/解码 将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
Base64 文件转换器 将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
Markdown转HTML 将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online