专栏首页小手冰凉数据库之数据类型详解

数据库之数据类型详解

一、数据类型简介

  • 数据表由多列字段构成,每一个字段指定了不同的数据类型,指定了数据类型之后,也就决定了向字段插入的数据内容;
  • 不同的数据类型也决定了 MySQL 在存储它们的时候使用的方式,以及在使用它们的时候选择什么运算符号进行运算;
  • 数值数据类型:TINYINT 、SMALINT 、MEDIUMINT 、INT 、BIGINT 、FLOAT 、DOUBLE 、DECIMAL;
  • 日期/时间类型:YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP;
  • 字符串类型:CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM 、SET。

二、数值类型简介

数值类型主要用来存储数字,不同的数值类型提供不同的取值范围,可以存储的值范围越大,所需要的存储空间也越大; 数值类型分为:①整数类型 ②浮点数类型 ③定点数类型。

1、整数类型如下:

示例:

mysql> create table t1(
    -> m tinyint,
    -> n smallint,
    -> x mediumint,
    -> y int,
    -> z bigint unsigned       # 默认是有符号的列,unsigned表示无符号列
    -> );

查看表的详细信息如下(在创建表的时候没有指定其长度,但是每一列都有自己默认的长度):

2、浮点数类型和定点数类型

  • MySQL 中使用浮点数和定点数来表示小数,浮点数有两种类型:单精度浮点数(FLOAT)和双精度浮点数(DOUBLE),定点数只有 DECIMAL;
  • 浮点数和定点数都可以用 (M,N) 来表示,其中 M 是精度,表示总共的位数,N 是标度,表示小数的位数,如:3.145,用M/N来表示就是4,3;
  • DECIMAL 实际是以字符串形式存放的,在对精度要求比较高的时候(如货币、科学数据等)使用 DECIMAL 类型会比较好;
  • 浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围,它的缺点是会引起精度问题。

优化建议:

  • 建议使用 TINYINT 代替 ENUM、BITENUM、SET;
  • 避免使用整数的显示宽度,也就是说,不要用INT(10)类似的方法指定字段显示宽度,直接用INT;
  • DECIMAL最适合保存准确度要求高,而且用于计算的数据,比如价格。但是在使用DECIMAL类型的时候,注意长度设置;
  • 建议使用整型来运算和存储实数,方法是,实数乘以相应的倍数后再操作;
  • 整数通常是最佳的数据类型,因为它速度快,并且能使用AUTO_INCREMENT。

示例1:

#新建一个表,值的长度都为5,小数点后都是两位
mysql> create table tab2(
    -> x float(5,2),
    -> y double(5,2),
    -> z decimal(5,2)
    -> );
#插入一些正常符合要求的数据,并无报错
mysql> insert into tab2 values(123.45,123.45,123.45);
#插入一些不符合规定的数据,会返回1个warning信息
mysql> insert into tab2 values(123.456,123.456,123.456);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;            # 查看warning信息,提示z列有截断的数据
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

上述示例插入的数据,实际显示如下(这里会有两个123.46,是我不小心多插入了一边数据,所以可忽略多出的一行):

通过实际插入的数据不难发现,如果插入不符合列规定的数据,那么最终会以四舍五入的方法处理。

需要注意的是,在上面的数值类型中,它只允许在小数点后面多一位,而不允许在小数点之前多一位,如插入1234.5或1234.35就会报错。

示例2:

mysql> create table tab3(                     # 创建多个列,长度都为10,小数点后面有两位
    -> x float(10,2),
    -> y double(10,2),
    -> z decimal(10,2)
    -> );

mysql> insert into tab3 values(12345678.123,12345678.123,12345678.123);
Query OK, 1 row affected, 1 warning (0.01 sec)
#同样会返回warning信息,提示z列有截断数据

最终插入到表中的数据如下:

在上面的表中,x列为float数值类型,其他两列的数值还是基于四舍五入的方法进行插入的,但是float数值类型的x列,插入的数据和实际输入的数据就有些出入了,并且会随着小数点位数的增加,这个浮动范围会更大。 3、日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

优化建议:

  • MySQL能存储的最小时间粒度为秒。
  • 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd;
  • 用MySQL的内建类型DATE、TIME、DATETIME来存储时间,而不是使用字符串;
  • 当数据格式为TIMESTAMP和DATETIME时,可以用CURRENT_TIMESTAMP作为默认(MySQL5.6以后), MySQL会自动返回记录插入的确切时间;
  • TIMESTAMP是UTC时间戳,与时区相关;
  • DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数,与时区无关;
  • 除非有特殊需求,否则建议使用TIMESTAMP,它比DATETIME更节约空间。

YEAR

格式1:以4位字符串格式表示的 YEAR ,范围为 '1901' ~ '2155'; 格式2:以4位数字格式表示的 YEAR ,范围为 1901 ~ 2155; 格式3:以2位字符串格式表示的 YEAR ,范围为 '00' ~ '99' ,其中,'00' ~ '69' 被转换为 2000 ~ 2069 ,'70' ~ '99' 被转换为 1970 ~ 1999; 格式4:以2位数字格式表示的 YEAR ,范围为 1 ~ 99 ,其中,1 ~ 69 被转换为 2001 ~ 2069 ,70 ~ 99 被转换为 1970 ~ 1999。

例:

mysql> create table tab4(x year);              # 新建一个表,只有一列,数值类型为year

mysql> insert into tab4 values('2000'),(2000),('96'),(96);             # 依次插入四位的字符、数值;两位的字符、数值

插入的数据如下:

mysql> delete from tab4;           # 删除原有数据

mysql> insert into tab4 values('0'),(0),('00'),(00);              # 插入一些不符合列规定的数值

当插入的年份不合法时,会用0000表示。 当插入的年份不合法时,会用0000表示。 当插入的年份不合法时,会用0000表示。

2)TIME

  • TIME 类型的格式为 HH:MM:SS ,HH 表示小时,MM 表示分钟,SS 表示秒
  • 格式1:以 'HHMMSS' 格式表示的 TIME ,例如 '101112' 被理解为 10:11:12 ,但如果插入不合法的时间,如 '109712' ,则被存储为 00:00:00
  • 格式2:以 'D HH:MM:SS' 字符串格式表示的 TIME ,其中 D 表示日,可以取 0 ~ 34 之间的值,在插入数据库的时候 D 会被转换成小时,如 '2 10:10' 在数据库中表示为 58:10:00 ,即 2x24+10 = 58

例:

mysql> create table tab5(date time);

mysql> insert into tab5 values('12:12:12'),(121212),(3),('3 10:2'),(14),('08:08');

上述插入的数据,基本可以对应SQL语句来看出来其规律,唯一需要解释的,应该就是“82:02:00”,对应的插入值是“3 10:2”,最中写入表中的时间是3天(3 X 24)+10小时,零2分钟,也就是82个小时零两分钟。 3)DATE

  • DATE 类型的格式为 YYYY-MM-DD ,其中,YYYY 表示年,MM 表示月,DD 表示日;
  • 格式1:'YYYY-MM-DD' 或 'YYYYMMDD' ,取值范围为 '1000-01-01' ~ '9999-12-3';
  • 格式2:'YY-MM-DD' 或 'YYMMDD' ,这里 YY 表示两位的年值,范围为 '00' ~ '99' ,其中,'00' ~ '69' 被转换为 2000 ~ 2069 ,'70' ~ '99' 被转换为 1970 ~ 1999;
  • 格式3:YY-MM-DD 或 YYMMDD ,数字格式表示的日期,其中 YY 范围为 00 ~ 99 ,其中,00 ~ 69 被转换为 2000 ~ 2069 ,70 ~ 99 被转换为 1970 ~ 1999。

例:

mysql> create table tab6(t date);             # 创建一个表,列的数据类型为date

mysql> insert into tab6 values('1999-09-09'),(990909),(19990909);                          # 插入不同格式的日期

插入的结果如下:

其实,对于date这一种数值类型,对于其格式并没有严格的要求,如2019-12-12这样的数值可以插入成功,2000!10:10这样的数值同样可以插入成功,如下:

4)DATETIME

  • DATETIME 类型的格式为 YYYY-MM-DD HH:MM:SS ,其中,YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒;
  • 格式1:'YYYY-MM-DD HH:MM:SS' 或 'YYYYMMDDHHMMSS' ,字符串格式,取值范围为 '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59';
  • 格式2:'YY-MM-DD HH:MM:SS' 或 'YYMMDDHHMMSS' ,字符串格式,其中 YY 范围为 '00' ~ '99' ,其中,'00' ~ '69' 被转换为 2000 ~ 2069 ,'70' ~ '99' 被转换为 1970 ~ 1999;
  • 格式3:YYYYMMDDHHMMSS 或 YYMMDDHHMMSS ,数字格式,取值范围同上。

例:

mysql> create table tab7(dt datetime);

mysql> insert into tab7 values('1996-09-19 12:24:56');

5)TIMESTAMP

  • TIMESTAMP 类型的格式为 YYYY-MM-DD HH:MM:SS,显示宽度固定在19个字符;
  • TIMESTAMP 与 DATETIME 的区别在于,TIMESTAMP 的取值范围小于 DATETIME 的取值范围;
  • TIMESTAMP 的取值范围为 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC ,其中 UTC 是世界标准时间,存储时会对当前时区进行转换,检索时再转换回当前时区。

三、字符串数据类型

字符串类型用来存储字符串数据,还可以存储比如图片和声音的二进制数据; MySQL 支持两种字符串类型:文本字符串和二进制字符串。

优化建议:

  • 字符串的长度相差较大用VARCHAR;
  • 字符串短,且所有值都接近一个长度用CHAR;
  • BINARY和VARBINARY存储的是二进制字符串,与字符集无关;
  • BLOB系列存储二进制字符串,与字符集无关;
  • TEXT是一个更大的VARCHAR;
  • BLOB和TEXT都不能有默认值。

1、char和varchar

  • CHAR(M) 为固定长度的字符串,在定义时指定字符串列长,当保存时在右侧填充空格以达到指定的长度,M 表示列长度,取值范围是 0~255 个字符,例如,CHAR(4) 定义了一个固定长度的字符串列,其包含的字符个数最大为 4,当检索到 CHAR 值时,尾部的空格将被删掉;
  • VARCHAR(M) 为可变长度的字符串,M 表示最大列长度,取值范围是 0~65535 ,VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度加一(一个字符串结束符);

例:

#新建一个表,字符类型分别为char和varchar
mysql> create table tab8(c char(4),vc varchar(4));
#插入数据,每条数据都是两个字母加两个空格
mysql> insert into tab8 values('ab  ','ab  ');

调用length函数,查看插入的数据长度:

调用紧凑函数,查看其实际数据:

可以验证了,如果是char类型的列,尾部的空格会被删除掉,如果是varchar类型的列,空格不会被删除掉,而是一个空格占一个位。

需要注意的是,如果是char类型的列,假如定义数值的长度为4,那么就算插入的数值长度只有2,它还是会占4个长度的空间,而varchar则不会,因为前者属于不可变长度的数值类型,而后者是可变的。 2、TEXT

TINYTEXT 最大长度为 255 个字符; TEXT 最大长度为 65536 个字符; MEDIUMTEXT 最大长度为 16777215 个字符; LONGTEXT 最大长度为 4294967295 个字符。

3、ENUM 在基本的数据类型中,无外乎就是些数字和字符,但是某些事物是较难用数字和字符来准确地表示的。比如一周有七天,分别是Sunday、Monday、Tuesday、Wednesday、Thursday、Friday 和 Saturday。如果我们用整数 0、1、2、3、4、5、6 来表示这七天,那么多下来的那些整数该怎么办?而且这样的设置很容易让数据出错,即取值超出范围。我们能否自创一个数据类型,而数据的取值范围就是这七天呢?因此有了 ENUM 类型(Enumeration,枚举),它允许用户自己来定义一种数据类型,并且列出该数据类型的取值范围。ENUM 是一个字符串对象,其值为表创建时在列规定中枚举(即列举)的一列值,语法格式为:字段名 ENUM ('值1', '值2', ..... '值n') 字段名指将要定义的字段,值 n 指枚举列表中的第 n 个值,ENUM类型的字段在取值时,只能在指定的枚举列表中取,而且一次只能取一个。如果创建的成员中有空格时,其尾部的空格将自动删除。ENUM 值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从 1 开始编号,MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。 例1:

#创建一个表,类型为enum,默认值依次为first、second、third
mysql> create table tab9(
    -> enm enum('first','second','third')
    -> );
#插入数据
mysql> insert into tab9 values('first'),('third'),('second');

正常查看插入的数据如下:

通过下面的方法,查看出每个值所对应的枚举的值,如下:

例2:

#创建两个列的表,第二列为enum类型
mysql> create table tab10(
    -> soc int,
    -> level enum('excellent','good','bad')
    -> );

#插入数据测试,第二列的值,可以直接写枚举中包含的值,也可以通过所在位数来调用
mysql> insert into tab10 values(70,'good'),(90,1),(75,2),(50,3);
#如果插入一个没有定义过的枚举值则会报错,如下:
mysql> insert into tab10 values(70,'best'),(90,1),(75,2),(50,4);
ERROR 1265 (01000): Data truncated for column 'level' at row 1

查看最终表中的值(只有第一个语句插入的值):

4、SET

  • SET 是一个字符串对象,可以有零个或多个值,SET 列最多可以有 64 个成员,其值为表创建时规定的一列值,语法:SET('值1','值2',...... '值n');
  • 与 ENUM 类型相同,SET 值在内部用整数表示,列表中每一个值都有一个索引编号;
  • 与 ENUM 类型不同的是,ENUM 类型的字段只能从定义的列值中选择一个值插入,而 SET 类型的列可从定义的列值中选择多个字符的联合;
  • 如果插入 SET 字段中列值有重复,则 MySQL 自动删除重复的值,插入 SET 字段的值的顺序并不重要,MySQL 会在存入数据库时,按照定义的顺序显示。

例:

#创建一个表,数据类型为set,并且自定义set字段的值
mysql> create table tab11(s set('a','b','c','d'));
#只能插入自定义的set字段的值
mysql> insert into tab11 values('a'),('b,c,a'),('a,b,a');
#如果插入没有定义的值,则会报错
mysql> insert into tab11 values('d,g,s');
ERROR 1265 (01000): Data truncated for column 's' at row 1

查看表中的顺序,发现已经把值去重并且将顺序排列好了,如下:

5、BIT

  • BIT 数据类型用来保存位字段值,即以二进制的形式来保存数据,如保存数据 13,则实际保存的是 13 的二进制值,即 1101;
  • BIT 是位字段类型,BIT(M) 中的 M 表示每个值的位数,范围为 1~64 ,如果 M 被省略,则默认为 1 ,如果为 BIT(M) 列分配的值的长度小于 M 位,则在值得左边用 0 填充;
  • 如果需要位数至少为 4 位的 BIT 类型,即可定义为 BIT(4) ,则大于 1111 的数据是不能被插入的。

例:

#创建一个表,数据类型为bit,宽度为4,也就是说,最多只能插入16以下的数据
mysql> create table tab12(b bit(4));
#插入正常的数据
mysql> insert into tab12 values(2),(9),(15);
#插入大于15以上的数据就会报错
mysql> insert into tab12 values(2),(9),(18);
ERROR 1406 (22001): Data too long for column 'b' at row 3

查看表中最终插入的数据(二进制类型的值,需要用以下语句查看,可以看到,只有第一条sql语句成功插入了):

6、BINARY 和 VARBINARY

  • BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字节字符串;
  • BINARY 类型的长度是固定的,指定长度之后,不足最大长度的,将在它们右边填充 '\0' 以补齐指定长度;
  • VARBINARY 类型的长度是可变的,指定长度之后,其长度可以在 0 到最大值之间。

例:

#创建一个表,数据类型分别为binary和varbinary
mysql> create table tab13(
    -> b binary(3),
    -> vb varbinary(30)
    -> );

#插入数据测试
mysql> insert into tab13 values(5,5);

也可以通过以下语句进行查看对比:

mysql> select  b,vb='0\0\5',b='5\0\0',vb='5',vb='5\0\0' from tab13;

7、BLOB

  • BLOB 用来存储可变数量的二进制字符串,分为 TINYBLOB 、BLOB 、MEDIUMBLOB 、LONGBLOB 四种类型;
  • BLOB 存储的是二进制字符串,TEXT 存储的是文本字符串;
  • BLOB 没有字符集,并且排序和比较基于列值字节的数值;TEXT 有一个字符集,并且根据字符集对值进行排序和比较。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 高可用之MMM

    一、MMM简介 MMM即Multi-Master Replication Manager for MySQL:mysql多主复制管理器,基于perl实现,关于m...

    小手冰凉
  • Cisco 三层交换详解

    当公司网络规模较小、划分的VLAN比较少时,可能单臂路由就可以满足各VLAN间的通信,但是当VLAN较多、网络规模比较大时。那么使用单臂路由技术就显得有点力不从...

    小手冰凉
  • 安装部署ESXi

    目前虚拟化技术已经日渐成熟,很多企业已经开始采用并实施这种技术,其中VMware的服务器虚拟化方案更是占据了绝大多数的服务虚拟化市场,这里主要介绍 VMware...

    小手冰凉
  • Springcloud +Gitlab+ Rancher2.0+Kubernetes微服务持续集成

    用户4044670
  • less特性小结

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.n...

    j_bleach
  • 如何删除无效的<none>Docker镜像?

    为什么会有 <none> 这样命名的镜像?这些镜像 docker 称为 虚悬镜像,当镜像被新的镜像覆盖时候,老版本镜像名称会变成 <none> 。

    用户1560186
  • 如何使用mysql数据库【Golang 入门系列十】

    之前,已经讲过一些Golang的基础的东西,感兴趣的可以看看以前的文章,https://www.cnblogs.com/zhangweizhong/catego...

    架构师精进
  • Gitlab CI 搭建持续集成环境实现Docker自动化部署使用Harbor镜像仓库

    本文简单介绍了持续集成的概念并着重介绍了如何基于 Gitlab CI 快速构建持续集成环境以及使用Docker实现自动化部署,主要介绍了 Gitlab CI 的...

    小东啊
  • 初学Docker容器网络不得不看的学习笔记

    Docker 是一个开源的应用容器引擎,基于 Go 语言 并遵从Apache2.0协议开源。

    宜信技术学院
  • 【腾讯云的1001种玩法】几种在腾讯云建立WordPress的方法(Linux)(二)

    续前文《几种在腾讯云建立WordPress的方法(Linux)(一)》。

    ZNing

扫码关注云+社区

领取腾讯云代金券