五、数据类型5.1 数值类型MySQL的数据类型主要分为数值类型、文本和二进制类型、时间日期类型以及String类型四大类。整数类型按存储空间从小到大依次为TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT各类型的字节数和取值范围如下整型默认是有符号的可通过UNSIGNED关键字指定为无符号。以TINYINT为例有符号范围为-128至127无符号范围为0至255。插入超出范围的值时MySQL会报错create table tt1(num tinyint); insert into tt1 values(1); -- 成功 insert into tt1 values(128); -- 越界报错实际开发中建议尽量不使用UNSIGNED因为对于int类型可能存不下的数据int unsigned同样可能存不下不如直接将类型提升为bigint。1bit类型bit[(M)]为位字段类型M表示每个值的位数范围1至64默认为1。bit字段在显示时按照ASCII码对应值显示例如插入65会显示为字符A。create table tt5(gender bit(1)); insert into tt5 values(0); insert into tt5 values(1); insert into tt5 values(2); -- 越界报错2小数类型float[(M,D)]中M指定显示长度D指定小数位数占用4字节。例如float(4,2)表示范围为-99.99至99.99保存值时会进行四舍五入。create table tt6(id int, salary float(4,2)); insert into tt6 values(100, -99.99); insert into tt6 values(101, -99.991); -- 四舍五入后为-99.99decimal(M,D)为定点数精度高于float。float的精度大约为7位有效数字而decimal整数最大位数为65小数最大位数为30。create table tt8 (id int, salary float(10,8), salary2 decimal(10,8)); insert into tt8 values(100, 23.12345612, 23.12345612); -- 查询结果salary为23.12345695salary2为23.123456125.2 字符串类型1char类型char(L)为固定长度字符串L表示可存储的字符数最大为255。char(2)可以存放两个字母或两个汉字。create table tt9(id int, name char(2)); insert into tt9 values(100, ab); insert into tt9 values(101, 中国);2varchar类型varchar(L)为可变长度字符串L表示字符长度最大为65535个字节。其中有1至3个字节用于记录数据长度因此有效字节数约为65532。在utf8编码下一个字符占用3字节varchar(n)的n最大值约为21844在gbk编码下一个字符占用2字节n最大值约为32766。3char与varchar的比较char为定长存储无论实际数据长度多少都占用固定空间磁盘空间浪费较多但读写效率较高varchar为变长存储按实际数据长度分配空间节省磁盘但效率相对较低。5.3 日期时间类型常用的日期时间类型有三种类型占用字节格式说明date3yyyy-mm-dd仅存储日期datetime8yyyy-mm-dd HH:ii:ss日期时间范围1000至9999年timestamp4yyyy-mm-dd HH:ii:ss时间戳从1970年开始插入数据时可自动填充当前时间使用示例create table birthday (t1 date, t2 datetime, t3 timestamp); insert into birthday(t1, t2) values(1997-7-1, 2008-8-8 12:1:1); -- t3字段会自动填充为当前时间5.4 枚举与集合类型1枚举类型ENUMENUM是一个字符串对象其值从建表时指定的枚举列表中选取一次只能选一个值。常用于性别、状态等单选场景。2集合类型SETSET也是字符串对象可以有零个或多个值各值之间用逗号分隔。常用于多选场景如兴趣爱好。以下示例创建一个调查表hobby字段为集合类型gender字段为枚举类型create table votes( username varchar(30), hobby set(登山,游泳,篮球,武术), gender enum(男,女) ); insert into votes values(雷锋, 登山,武术, 男); insert into votes values(Juse, 登山,武术, 2); -- 2对应枚举中的女查询集合中包含某个值的记录时不能直接用等号比较等号要求完全匹配。应使用find_in_set函数select * from votes where find_in_set(登山, hobby);find_in_set(sub, str_list)的作用是如果sub在以逗号分隔的str_list中则返回对应的下标位置如果不在则返回0。六、表的约束数据类型本身对字段有一定约束但这种约束较为单一。从业务逻辑角度确保数据正确性还需要使用表的约束。常用约束包括空属性、默认值、列描述、zerofill、主键、自增长、唯一键和外键。6.1 空属性与默认值1空属性空属性有两个取值null和not null。数据库字段默认为空因为null值无法参与运算1null的结果仍为null。以班级表为例班级名和教室名都不应为空否则无法确定班级信息create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 插入时未给class_room赋值会报错 insert into myclass(class_name) values(class1);2默认值当某个字段的数据经常出现某个固定值时可以在定义时指定默认值。插入数据时不给该字段赋值就会使用默认值。create table tt10 ( name varchar(20) not null, age tinyint unsigned default 0, sex char(2) default 男 ); insert into tt10(name) values(zhangsan); -- 查询结果age为0sex为男需要注意的是只有设置了default的列插入时才可以省略该列。6.2 列描述与zerofill1列描述commentcomment用于描述字段含义会随建表语句保存方便程序员或DBA了解字段用途。desc命令无法查看注释需通过show create table查看create table tt12 ( name varchar(20) not null comment 姓名, age tinyint unsigned default 0 comment 年龄, sex char(2) default 男 comment 性别 ); show create table tt12\G2zerofill整型字段后括号中的数字在没有zerofill属性时没有实际意义。添加zerofill后如果数值宽度小于设定宽度会自动在左侧填充0。alter table tt3 change a a int(5) unsigned zerofill; -- 查询时a的值1会显示为00001 -- 使用hex(a)可验证内部存储仍为16.3 主键与自增长1主键primary key主键用于唯一约束字段中的数据不能重复且不能为空。一张表中最多只能有一个主键主键所在的列通常是整数类型。create table tt13 ( id int unsigned primary key comment 学号不能为空, name varchar(20) not null ); insert into tt13 values(1, aaa); -- 成功 insert into tt13 values(1, aaa); -- 重复报错表创建后若未指定主键可追加主键alter table 表名 add primary key(字段列表);删除主键alter table 表名 drop primary key;2自增长auto_increment自增长字段在不给值时系统会自动从当前最大值加1生成新值。自增长的特点字段本身必须是一个索引字段必须是整数类型一张表最多只能有一个自增长字段create table tt21( id int unsigned primary key auto_increment, name varchar(10) not null default ); insert into tt21(name) values(a); -- id自动为1 insert into tt21(name) values(b); -- id自动为2 select last_insert_id(); -- 获取上次插入的自增长值6.4 唯一键一张表中往往有多个字段需要保证唯一性但一张表只能有一个主键。主键更多用于标识记录的唯一性唯一键更多用于保证业务字段不与其他信息重复。一般建议将主键设计为与当前业务无关的字段这样业务调整时不会对主键造成过大影响。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); -- 可以为空成功6.5 外键外键用于定义主表和从表之间的关联关系。外键约束定义在从表上主表的对应列必须具有主键约束或unique约束。以学生表和班级表为例如果将班级信息冗余在每个学生记录中会造成数据冗余。创建外键时需先创建主表再创建从表-- 先创建主表班级表 create table myclass ( id int primary key, name varchar(30) not null comment 班级名 ); -- 再创建从表学生表 create table stu ( id int primary key, name varchar(30) not null comment 学生名, class_id int, foreign key (class_id) references myclass(id) );正常插入数据insert into myclass values(10, C大牛班), (20, java大神班); insert into stu values(100, 张三, 10), (101, 李四, 20);插入一个不存在的班级号时外键约束会阻止插入insert into stu values(102, wangwu, 30); -- 报错外键约束失败若学生暂时未分配班级class_id可以插入null值。6.6 综合案例以下是一个商店数据库的综合设计案例包含商品表、客户表和购买表三张表综合运用了主键、自增长、非空、唯一键、枚举和外键等约束。-- 创建数据库 create database if not exists bit32mall default character set utf8; use bit32mall; -- 商品表 create table if not exists goods ( goods_id int primary key auto_increment comment 商品编号, goods_name varchar(32) not null comment 商品名称, unitprice int not null default 0 comment 单价单位分, category varchar(12) comment 商品分类, provider varchar(64) not null comment 供应商名称 ); -- 客户表 create table if not exists customer ( customer_id int primary key auto_increment comment 客户编号, name varchar(32) not null comment 客户姓名, address varchar(256) comment 客户地址, email varchar(64) unique key comment 电子邮箱, sex enum(男,女) not null comment 性别, card_id char(18) unique key comment 身份证 ); -- 购买表 create table if not exists purchase ( order_id int primary key auto_increment comment 订单号, customer_id int comment 客户编号, goods_id int comment 商品编号, nums int default 0 comment 购买数量, foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );该设计中商品表和客户表以自增长主键作为唯一标识客户表的邮箱和身份证通过唯一键保证不重复购买表通过两个外键分别关联客户表和商品表确保订单数据的引用完整性。以上内容覆盖了MySQL从环境搭建到表结构设计的基础知识。掌握这些内容后可以进一步学习数据的增删改查、索引优化和事务管理等进阶主题。 
长河君 · 2026/10/15 12:40:13 · 阅读 1.2k MySQL数据库基础使用（2）—数据类型与表的约束
五、数据类型5.1 数值类型MySQL的数据类型主要分为数值类型、文本和二进制类型、时间日期类型以及String类型四大类。整数类型按存储空间从小到大依次为TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT各类型的字节数和取值范围如下整型默认是有符号的可通过UNSIGNED关键字指定为无符号。以TINYINT为例有符号范围为-128至127无符号范围为0至255。插入超出范围的值时MySQL会报错create table tt1(num tinyint); insert into tt1 values(1); -- 成功 insert into tt1 values(128); -- 越界报错实际开发中建议尽量不使用UNSIGNED因为对于int类型可能存不下的数据int unsigned同样可能存不下不如直接将类型提升为bigint。1bit类型bit[(M)]为位字段类型M表示每个值的位数范围1至64默认为1。bit字段在显示时按照ASCII码对应值显示例如插入65会显示为字符A。create table tt5(gender bit(1)); insert into tt5 values(0); insert into tt5 values(1); insert into tt5 values(2); -- 越界报错2小数类型float[(M,D)]中M指定显示长度D指定小数位数占用4字节。例如float(4,2)表示范围为-99.99至99.99保存值时会进行四舍五入。create table tt6(id int, salary float(4,2)); insert into tt6 values(100, -99.99); insert into tt6 values(101, -99.991); -- 四舍五入后为-99.99decimal(M,D)为定点数精度高于float。float的精度大约为7位有效数字而decimal整数最大位数为65小数最大位数为30。create table tt8 (id int, salary float(10,8), salary2 decimal(10,8)); insert into tt8 values(100, 23.12345612, 23.12345612); -- 查询结果salary为23.12345695salary2为23.123456125.2 字符串类型1char类型char(L)为固定长度字符串L表示可存储的字符数最大为255。char(2)可以存放两个字母或两个汉字。create table tt9(id int, name char(2)); insert into tt9 values(100, ab); insert into tt9 values(101, 中国);2varchar类型varchar(L)为可变长度字符串L表示字符长度最大为65535个字节。其中有1至3个字节用于记录数据长度因此有效字节数约为65532。在utf8编码下一个字符占用3字节varchar(n)的n最大值约为21844在gbk编码下一个字符占用2字节n最大值约为32766。3char与varchar的比较char为定长存储无论实际数据长度多少都占用固定空间磁盘空间浪费较多但读写效率较高varchar为变长存储按实际数据长度分配空间节省磁盘但效率相对较低。5.3 日期时间类型常用的日期时间类型有三种类型占用字节格式说明date3yyyy-mm-dd仅存储日期datetime8yyyy-mm-dd HH:ii:ss日期时间范围1000至9999年timestamp4yyyy-mm-dd HH:ii:ss时间戳从1970年开始插入数据时可自动填充当前时间使用示例create table birthday (t1 date, t2 datetime, t3 timestamp); insert into birthday(t1, t2) values(1997-7-1, 2008-8-8 12:1:1); -- t3字段会自动填充为当前时间5.4 枚举与集合类型1枚举类型ENUMENUM是一个字符串对象其值从建表时指定的枚举列表中选取一次只能选一个值。常用于性别、状态等单选场景。2集合类型SETSET也是字符串对象可以有零个或多个值各值之间用逗号分隔。常用于多选场景如兴趣爱好。以下示例创建一个调查表hobby字段为集合类型gender字段为枚举类型create table votes( username varchar(30), hobby set(登山,游泳,篮球,武术), gender enum(男,女) ); insert into votes values(雷锋, 登山,武术, 男); insert into votes values(Juse, 登山,武术, 2); -- 2对应枚举中的女查询集合中包含某个值的记录时不能直接用等号比较等号要求完全匹配。应使用find_in_set函数select * from votes where find_in_set(登山, hobby);find_in_set(sub, str_list)的作用是如果sub在以逗号分隔的str_list中则返回对应的下标位置如果不在则返回0。六、表的约束数据类型本身对字段有一定约束但这种约束较为单一。从业务逻辑角度确保数据正确性还需要使用表的约束。常用约束包括空属性、默认值、列描述、zerofill、主键、自增长、唯一键和外键。6.1 空属性与默认值1空属性空属性有两个取值null和not null。数据库字段默认为空因为null值无法参与运算1null的结果仍为null。以班级表为例班级名和教室名都不应为空否则无法确定班级信息create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 插入时未给class_room赋值会报错 insert into myclass(class_name) values(class1);2默认值当某个字段的数据经常出现某个固定值时可以在定义时指定默认值。插入数据时不给该字段赋值就会使用默认值。create table tt10 ( name varchar(20) not null, age tinyint unsigned default 0, sex char(2) default 男 ); insert into tt10(name) values(zhangsan); -- 查询结果age为0sex为男需要注意的是只有设置了default的列插入时才可以省略该列。6.2 列描述与zerofill1列描述commentcomment用于描述字段含义会随建表语句保存方便程序员或DBA了解字段用途。desc命令无法查看注释需通过show create table查看create table tt12 ( name varchar(20) not null comment 姓名, age tinyint unsigned default 0 comment 年龄, sex char(2) default 男 comment 性别 ); show create table tt12\G2zerofill整型字段后括号中的数字在没有zerofill属性时没有实际意义。添加zerofill后如果数值宽度小于设定宽度会自动在左侧填充0。alter table tt3 change a a int(5) unsigned zerofill; -- 查询时a的值1会显示为00001 -- 使用hex(a)可验证内部存储仍为16.3 主键与自增长1主键primary key主键用于唯一约束字段中的数据不能重复且不能为空。一张表中最多只能有一个主键主键所在的列通常是整数类型。create table tt13 ( id int unsigned primary key comment 学号不能为空, name varchar(20) not null ); insert into tt13 values(1, aaa); -- 成功 insert into tt13 values(1, aaa); -- 重复报错表创建后若未指定主键可追加主键alter table 表名 add primary key(字段列表);删除主键alter table 表名 drop primary key;2自增长auto_increment自增长字段在不给值时系统会自动从当前最大值加1生成新值。自增长的特点字段本身必须是一个索引字段必须是整数类型一张表最多只能有一个自增长字段create table tt21( id int unsigned primary key auto_increment, name varchar(10) not null default ); insert into tt21(name) values(a); -- id自动为1 insert into tt21(name) values(b); -- id自动为2 select last_insert_id(); -- 获取上次插入的自增长值6.4 唯一键一张表中往往有多个字段需要保证唯一性但一张表只能有一个主键。主键更多用于标识记录的唯一性唯一键更多用于保证业务字段不与其他信息重复。一般建议将主键设计为与当前业务无关的字段这样业务调整时不会对主键造成过大影响。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); -- 可以为空成功6.5 外键外键用于定义主表和从表之间的关联关系。外键约束定义在从表上主表的对应列必须具有主键约束或unique约束。以学生表和班级表为例如果将班级信息冗余在每个学生记录中会造成数据冗余。创建外键时需先创建主表再创建从表-- 先创建主表班级表 create table myclass ( id int primary key, name varchar(30) not null comment 班级名 ); -- 再创建从表学生表 create table stu ( id int primary key, name varchar(30) not null comment 学生名, class_id int, foreign key (class_id) references myclass(id) );正常插入数据insert into myclass values(10, C大牛班), (20, java大神班); insert into stu values(100, 张三, 10), (101, 李四, 20);插入一个不存在的班级号时外键约束会阻止插入insert into stu values(102, wangwu, 30); -- 报错外键约束失败若学生暂时未分配班级class_id可以插入null值。6.6 综合案例以下是一个商店数据库的综合设计案例包含商品表、客户表和购买表三张表综合运用了主键、自增长、非空、唯一键、枚举和外键等约束。-- 创建数据库 create database if not exists bit32mall default character set utf8; use bit32mall; -- 商品表 create table if not exists goods ( goods_id int primary key auto_increment comment 商品编号, goods_name varchar(32) not null comment 商品名称, unitprice int not null default 0 comment 单价单位分, category varchar(12) comment 商品分类, provider varchar(64) not null comment 供应商名称 ); -- 客户表 create table if not exists customer ( customer_id int primary key auto_increment comment 客户编号, name varchar(32) not null comment 客户姓名, address varchar(256) comment 客户地址, email varchar(64) unique key comment 电子邮箱, sex enum(男,女) not null comment 性别, card_id char(18) unique key comment 身份证 ); -- 购买表 create table if not exists purchase ( order_id int primary key auto_increment comment 订单号, customer_id int comment 客户编号, goods_id int comment 商品编号, nums int default 0 comment 购买数量, foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );该设计中商品表和客户表以自增长主键作为唯一标识客户表的邮箱和身份证通过唯一键保证不重复购买表通过两个外键分别关联客户表和商品表确保订单数据的引用完整性。以上内容覆盖了MySQL从环境搭建到表结构设计的基础知识。掌握这些内容后可以进一步学习数据的增删改查、索引优化和事务管理等进阶主题。