07 表数据类型详解

共计 9108 个字符，预计需要花费 23 分钟才能阅读完成。

引入

存储引擎决定了表的类型, 而表内存放的数据也是有不同的类型, MySQL 支持多种类型, 大致可以分为三类 : 数值类型、日期类型、字符串类型

一. 数值类型

关键字 INT 是 INTEGER 的同义词，关键字 DEC 是 DECIMAL 的同义词

二. 数值类型之整数类型

• TINYINT : 小整数
• SAMLLINT : 短整数
• MEDIUMINT : 中整数
• INT 或 INTEGER : 整数
• BIGINT : 大整数

作用 : 存储年龄、等级、ID、各种号码等 (以下介绍几种常用整数类型)

1.TINYINT (小整数)

• 参数 : tinyint[(m)] [unsigned(无符号)] [zerofill(零填充)]
• 有符号整数范围 (-128~127), 无符号整数范围(0~255), 默认有符号

🍓默认有符号演示
create table t01(id tinyint);  # 创建一个表, id 字段类型 tinyint, 有符号
desc t01;         # 查看表结构, "describe t01"
insert table t01 values(-129),(-128),(128),(127);  # 插入四条记录
show * from t01;  # 查看表内容

🍓无符号演示
create table t02(id tinyint unsigned);        # 创建一个表, 设置无符号
insert table t01 values(-2),(0),(255),(636);  # 插入四条记录
select * from t02;  # 查看表内容

2.INT | INTEGER (整数)

• 参数 : int[(m)][unsigned(无符号)] [zerofill(零填充)]
• 有符号范围 : (-2147483648～2147483647), 无符号范围 : (0～4294967295), 默认有符号

🍓默认有符号演示
create table t03(id int) charset utf8;  # 创建一个表, 默认有符号
desc t03;  # 查看该表结构
insert into t03 values(-2147483649),(-2147483648),(2147483647),(2147483648);  # 插入记录
select id from t03;  # 查看表内容

🍓无符号演示
create table t04(id int unsigned);  # 创建一个表, 无符号
insert into t04 values(-3),(0),(4294967295),(4294967296); # 插入四条记录
select id from t04;  # 查看表内容

3.BIGINT (大整数)

• 参数 : bigint[(m)][unsigned(无符号)] [zerofill(零填充)]
• 有符号范围 : (-9223372036854775808～9223372036854775807)
• 无符号范围 : (0～18446744073709551615) 默认有符号

🍓默认有符号演示
create table t05(id bigint);  # 创建一个表, 默认有符号
desc t05;  # 查看一下表结构
insert into t05 values(-9223372036854775809),(-9223372036854775808),(9223372036854775807),(9223372036854775808);  # 插入四条记录
select id from t05;  # 查看表内容

🍓无符号演示
create table t06(id bigint unsigned);  # 创建一个表, 无符号
insert into t06 values(-1),(0),(18446744073709551615),(18446744073709551617);  # 插入四条记录
select id from t06;     # 查看表内容

4.zerofill 参数测试整数类型的显示宽度

create table t07(id int(3) zerofill);  # 创建一个表, 设置字段类型并设置显示宽度为 3, 不足以 0 填充
insert into t07 values(1),(11),(111),(1111);  # 插入四条记录
select id from t07;  # 查看表内容

由上面的测试可以发现, 为整形类型指定宽度时, 指定的仅仅是查询结果时的宽度, 与存储的范围没有关系, 存储范围如下, 其实我们完全没必要为整数类型指定显示宽度, 使用默认的就可以了

🍓默认宽度说明
int 的存储宽度是 4 个 Bytes，即 32 个 bit，即 2 **32
无符号最大值为：4294967296-1, 有符号最大值：2147483648-1
有符号和无符号的最大数字需要的显示宽度均为 10
而针对有符号的最小值则需要 11 位才能显示完全
所以 int 类型默认的显示宽度为 11

结论 : 非严格模式下

• 如果数字没有超出括号内指定的长度, 那么默认用 0 填充至括号内的长度
• 如果数字超出括号内指定的长度, 那么有几位就存几位, 但还是不能超过最大范围, 超过则显示该类型的最大值(上面已经测试过了) (如果是严格模式下, 超出了范围直接报错)

三. 数值类型之浮点型

• FLOAT : 单精度浮点数
• DOUBLE : 双精度浮点数
• DEC 或 DECIMAL : 准确小数值

作用 : 储存薪资、身高、体重、体质参数等

1.FLOAT (单精度浮点数)

• 参数 : FLOAT[(m,d)] [UNSIGNED(无符号)] [ZEROFILL(0 填充)]
• 定义 : 单精度浮点数(非准确小数值), m 是数字总个数最大值为 255，d 是小数点后个数最大值为30, 整数部分225
• 有符号范围 : -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38
• 无符号范围 : 1.175494351E-38 to 3.402823466E+38
• 精准度 : 随着小数的增多, 精度变得不准确

create database test05 charset utf8;  # 创建一个新的数据库来做实验
create table t01(x,float(255,30))  # 创建一个 float 类型的表, 精度取最大范围(后面演示超出范围会怎样)
insert into t01 values(1.1111111111111111111111111111111);  # 插入一条数据, 小数点后 31 个 1
select * from t01;  # 查看表内容

2.DOUBLE (双精度浮点数)

• 参数 : DOUBLE[(m,d)] [UNSIGNED(无符号)] [ZEROFILL(0 填充)]
• 定义 : 双精度浮点数(非准确小数值), m 是数字总个数最大值为 255，d 是小数点后个数最大值为30, 整数部分225
• 有符号范围 : -1.7976931348623157E+308 to -2.2250738585072014E-308, 2.2250738585072014E-308 to 1.7976931348623157E+308
• 无符号范围 : 2.2250738585072014E-308 to 1.7976931348623157E+308
• 精准度 : 随着小数的增多, 精度比float 要高, 但也会变得不准确

create table t02(x double(255,30));  # 创建一个 double 类型的表, 精度取最大值
insert into t02 values(1.1111111111111111111111111111111);  # 插入一条数据, 小数点后 31 个 1
select * from t02;  # 查看表内容

3.DECIMAL (准确小数值)

• 参数 : DECIMAL[(m,d)] [UNSIGNED(无符号)] [ZEROFILL(0 填充)]

• 定义 : 准确的小数值, m 是数字总个数 (负号不算) 最大值为 65, d 是小数点后个数最大值为30

• 精准度 : 随着小数的增多, 精度始终准确

对于精确数值计算时需要用此类型, decaimal 能够存储精确值的原因在于其内部按照字符串存储

create table t03(x decimal(65,30));  # 创建一个 decimal 类型的表, 精度取最大值
insert into t03 values(1.1111111111111111111111111111111);  # 插入一条数据, 小数点后 31 个 1
select * from t03;  # 查看表内容

四. 日期和时间类型

• 类型 : DATE、TIME、DATETIME、TIMESTAMP、YEAR

• 作用 : 存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

• 范围限制 : 如上图, 每个类型都有范围, 超出范围的mysql 将自动设置成 "零" 值
• now() : 当前时间

1.YEAR (年)测试

• 范围 : 1901~2155

create table t05(from_year year);  # 无论 year 指定何种宽度，最后都默认是 year(4)
insert into t05 values(1111),(1901),(2155),(2222);  # 插入四条年份记录
select * from t05;  # 查看表内容

2.DATE、TIME、DATETIME(日期, 时间值, 混合日期时间)演示

create table t06(D date,T time,DT datetime);  # 创建表
desc t06;  # 查看下表结构
insert into t06 values(now(),now(),now());  # 插入一条记录
select * from t06;  # 查看表内容

3.TIMESTAMP (混合日期和时间) 演示

• 特点 : 传空的情况下自动传入当前时间, 自动更新时间

create table t07(TS timestamp);
insert into t07 values();     # 传空
insert into t07 values(null); # 传空
select * from t07;

4.datatime 与 timestamp 的区别

• datetime 的日期范围是 1001--9999, timstamp 的时间范围是 1970--2038 年

• timestamp 字段比 datetime 字段的空间利用率更高, 存取速度更快

• timestamp 字段比 datetime 字段建表的时候更方便

• datetime 的默认值为 null, timestamp 的字段默认不为空(not null), 默认值为当前时间, 如果不做特殊处理, 并且 update 语句中没有指定该列的更新值, 则默认更新为当前时间

create table t08(DT datetime not null default now());  # 指定传空时默认取当前时间
create table t09(TS timestamp);  # 不需要指定, 再传空时自动传入当前时间
insert into t08 values();  # 传空
insert into t09 values();  # 传空
select * from t08;         # 查看表内容
select * from t09;         # 查看表内容

datetime 自动填充时间需要指定 not null default now(), 一般用于注册等定格的时间, 如果想用作更新时间就得指定on update now(), 而timestamp 自带此功能

• on update now() 功能演示

create table t10(name varchar(5),T datetime not null default now());  # 创建两个字段的表
create table t11(name varchar(5),T datetime not null default now() on update now());  # 添加了时间更新功能
create table t12(name varchar(5),T timestamp);  # 自带更新时间功能
desc t10;  # 分别查看一下三个表结构
desc t11;
desc t12;
insert into t10(name) values("shawn");  # 插入 name 字段的记录, 让 T 字段自动填充
insert into t11(name) values("shawn");
insert into t12(name) values("shawn");
select * from t10;  # 分别查看一下三表的内容, 注意一下三表的时间
select * from t11;
select * from t12;
update t10 set name="song" where name="shawn";  # 对三表的 name 字段记录进行修改
update t11 set name="song" where name="shawn";
update t12 set name="song" where name="shawn";
select * from t10;  # 再次查看三表内容, 注意查看三表的时间变化
select * from t11;
select * from t12;

五. 字符串类型

字符串类型有很多, 这里只介绍 CHAR、VARCHAR两种类型, 更多可参考https://www.runoob.com/mysql/mysql-data-types.html

注意 : char 和 varchar 括号内的参数值的都是 字符 的长度, 不是字节长度

ps : 对于 MySQL 4.1 之前的版本, 如 MySQL 3.23 和 MySQL 4.0, CHAR(N)和 VARCHAR (N)中的 N 代表字节长度

1.CHAR 类型

• 特点 : 定长, 存储简单, 耗费空间, 但存取速度快
• 字符长度范围 : CHAR(N) 用来保存固定长度的字符串, N 的范围 为 (0 ~ 255)
• 存储特点 : 存储char 类型的值时, 会往右填充空格来满足你指定的字符长度, 例如指定长度为 5, 存 大于 5 字符报错, 小于 5 就以空格填充到 5 个字符存储
• 查询 : 在检索(查询) 时, 查出的结果会自动删除尾部的空格, (设置 SQL 模式可以让其不删除空格显示:SET sql_mode = 'pad_char_to_full_length';)

下边将使用到的函数 :

• hex() : 将传入的字符串货数字转成十六进制格式的字符串
• length() : 返回传入内容的 字节 长度, utf8 编码英文 1 字节, 中文 3 字节, gbk英文都 1 字节, 中文 2 字节
• char_length() : 返回传入内容的字符长度

UTF8编码测试

create database test01 charset utf8;  # 创建一个新库做测试
use test01;  # 进入库
create table t01(n char(10));  # 创建一个表,n 字段 char 类型规定 10 字符长度
insert into t01 values("abc 你好 ");  # 插入 "abc 你好 "
select n,hex(n),length(n),char_length(n) from t01;  # 查看表内容, 还有长度

• 设置 SQL 模式, 填充空格 : SET sql_mode = 'pad_char_to_full_length';

GBK编码测试

create database test02 charset gbk;  # 创建一个新库,gbk 编码
use test02;  # 进入库
create table t01(n char(10));        #创建表, n 字段 char 类型规定 10 长度
insert into t01 values("abc 你好 ");                # 插入 "abc 你好 "
select n,hex(n),length(n),char_length(n) from t01;  # 查看表内容和内容长度
set sql_mode='pad_char_to_full_length';             # 设置 SQL 模式, 空格填充
select n,hex(n),length(n),char_length(n) from t01;  # 再次查看变化

超出 规定字符数测试

insert into t01 values("abc 你好 aaaaaaaaaaaaaaaaaaaa");
select n,hex(n),length(n),char_length(n) from t01;

2.varchar 类型

• 特点 : 变长, 精准, 节省空间, 但存取速度慢
• 字符长度范围 : 0-65535（如果大于 21845 会提示用其他类型。mysql 行最大限制为 65535 字节，字符编码为 utf-8
• 存储特点 :
• varchar 类型存储数据的真实内容，不会用空格填充，如果 'ab', 尾部的空格也会被存起来
• varchar 类型会在真实数据前加 1 -2Bytes 的前缀，该前缀用来表示真实数据的 bytes 字节数（1-2Bytes 最大表示 65535 个数字，正好符合 mysql 对 row 的最大字节限制，即已经足够使用）
• 如果真实的数据 <255bytes 则需要 1Bytes 的前缀（1Bytes=8bit 2** 8 最大表示的数字为 255）
• 如果真实的数据 >255bytes 则需要 2Bytes 的前缀（2Bytes=16bit 2**16 最大表示的数字为 65535）
• 查询 : 尾部有空格会保存下来，在查询(检索) 时，也会正常显示包含空格在内的内容

create database test04 charset utf8; 
use test04; 
create table t01(x char(10),y varchar(10));  # 创建表, 设置两个字段使用不同的类型规定长度为 10
insert t01 values("1111","1111");
select * from t01;
set sql_mode='pad_char_to_full_length';      # 设置 SQL 模式, 空格填充
select * from t01;

select x,y,length(x),length(y) from t01;  # 查看字符长度

3.char 和 varchar 对比总结

• char填充空格来满足固定长度，但是在查询显示时却会删除尾部的空格 (装作自己好像没有浪费过空间一样), 可以修改sql_mode 让其现出原形

• varchar存的是真实长度 +1~2Bytes 说明信息(类似于头)

• 存数据时, 在非严格模式下, 字符数超过了或者刚刚等于指定得宽度 , char 类型更加节省空间

• 字符数小于指定得宽度 varchar 类型, 更加节省空间, 基于网络 IO 得情况, varchar 更好,

char、varchar 如何选择 :

• 对于 MyISAM 表, 尽量使用 Char: 对于那些经常需要修改而容易形成碎片的 myisam 和 isam 数据表就更是如此，它的缺点就是占用磁盘空间
• 对于 InnoDB 表, 尽量使用 varVhar: 因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分(所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针)，所以使用 char 类型不见得会比使用 varchar 类型好。事实上, 因为 char 类型通常要比 varchar 类型占用更多的空间，所以从减少空间占用量和减少磁盘 i / o 的角度，使用 varchar 类型反而更有利
• 存储很短的信息，尽量使用 Char: 比如门牌号码 101, 20.... 这样很短的信息应该用 char, 因为 varchar 还要 占个 byte 用于存储信息长度，本来打算节约存储的现在得不偿失
• 固定长度的数据, 尽量使用 Char: 比如使用 uuid 作为主键, 那用 char 应该更合适。因为他固定长度，varchar 动态根据长度的特性就消失了，而且还要占个长度信息
• 十分频繁改变的字段列, 尽量使用 varchar: 因为 varchar 每次存储都要 有额外的计算, 得到长度等工作, 如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于 char 来说是不需要的。

六. 枚举类型与集合类型

作用 : 让字段的值只能在给定的范围内进行选择 : 多选, 单选

1. 枚举类型 (enum)

• 单选, 只能在给定范围内选择一个值, 比如性别, 只能从男、女、太监中选择一个

create table t03(name varchar(10),sex enum("male","female","eunuch"));
insert t03(name,sex) values("shawn","male");  # 选择一个性别
select sex from t03;

• 选择不存在的性别测试

insert t03 values("song","wooo");  # 插入一条记录, sex 字段选择一个不存在的选项
select * from t03;  

2. 集合类型 (set)

• 多选, 在给定范围内可以选择一个或多个值, 比如爱好可以有多种(read,run,eat,sleep,play...)

create table t04(name varchar(10),hobby set("read","run","eat","sleep","play"));
insert t04 values("shawn","read,run"),("song","run,sleep,play");
select * from t04;

七. 综合练习

存一个员工的 id、姓名、年龄、身高、性别、爱好(这里提供爱好选项)、家庭住址、入职时间、每月收入、信息最近修改时间, 选择合适的类型

create table info(
id int,
name char(16),
age tinyint,
height float,
sex enum("male","female"),
hobby set("read","run","eat","sleep","play"),
home_addr varchar(16),
entry_time datetime not null default now(),
salary double(255,3),
info_change_time timestamp
);
insert info values(01,"shawn",22,1.72,"male","read,sleep"," 上海市青浦区 ",now(),15000.53,now());
insert info values(02,"pai",23,1.82,"female","read,play"," 上海市黄浦区 ",now(),1000.342,now());
insert info values(03,"da",44,1.62,"female","run,eat,sleep"," 北京天安门前 ",now(),9000,now());
insert info values(04,"xing",25,1.52,"male","play,sleep"," 美国巴黎 ",now(),35000.53,now());

• 验证 "信息最近修改时间" 这个字段自动更新时间

update info set name=" 派大星 " where name="xing";  # 更新一个字段
select * from info;  # 查看是否变化