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

引入

存储引擎决定了表的类型, 而表内存放的数据也是有不同的类型, 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个1select * from t01; # 查看表内容

2.DOUBLE (双精度浮点数)

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

create table t02(x double(255,30)); # 创建一个double类型的表, 精度取最大值insert into t02 values(1.1111111111111111111111111111111); # 插入一条数据, 小数点后31个1select * 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个1select * 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两种类型, 更多可参考/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)); # 创建表, 设置两个字段使用不同的类型规定长度为10insert 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; # 查看是否变化