Mysql表数据类型详解(数值类型,日期类型,字符串类型,枚举类型,集合类型,综合练习)

引入

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

一.数值类型

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

image-20210130175607997

二.数值类型之整数类型

  • 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;  # 查看表内容

image-20210130202948683

image-20210130203234875

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

image-20210130204723636

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;  # 查看表内容

image-20210130212052506

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

?默认宽度说明
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;  # 查看表内容

image-20210130222905238

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;  # 查看表内容

image-20210130224001583

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;  # 查看表内容

image-20210130225113040

四.日期和时间类型

image-20210130225430645

  • 类型 : 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;  # 查看表内容

image-20210130230805178

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;  # 查看表内容

image-20210130231801218

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

  • 特点 : 传空的情况下自动传入当前时间, 自动更新时间
create table t07(TS timestamp);
insert into t07 values();     # 传空
insert into t07 values(null); # 传空
select * from t07;

image-20210130232919655

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;         # 查看表内容

image-20210130234909040

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;

image-20210131001028765

image-20210131001310692

image-20210131001612812

五.字符串类型

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

image-20210131083715391

注意 : 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;  # 查看表内容,还有长度

image-20210131134600631

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

image-20210131135027170

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;  # 再次查看变化

image-20210131140149490

超出规定字符数测试

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

image-20210131141256136

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;

image-20210131144007936

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

image-20210131144427023

3.char 和 varchar 对比总结

ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required
'''    '4 bytes''1 byte
'ab''ab  '4 bytes'ab'3 bytes
'abcd''abcd'4 bytes'abcd'5 bytes
'abcdefgh''abcd'4 bytes'abcd'5 bytes
  • 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;

image-20210131152242582

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

image-20210131153012628

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;

image-20210131154146647

七.综合练习

存一个员工的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());

image-20210131161231398

  • 验证 “信息最近修改时间” 这个字段自动更新时间
update info set name="派大星" where name="xing";  # 更新一个字段
select * from info;  # 查看是否变化

image-20210131161709918

版权声明：本文为songhaixing2原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。