数据库之数据类型详解 史上最全-MySQL-E先生的博客
Java
MySQL
大数据
Python
前端
黑科技
大语言模型
    首页 >> 互联网 >> MySQL

数据库之数据类型详解 史上最全

[导读]:一、数据类型简介 数据表由多列字段构成,每一个字段指定了不同的数据类型,指定了数据类型之后,也就决定了向字段插入的数据内容; 不同的数据类型也决定了MySQL在存储它们的时候使用的...
  一、数据类型简介
  数据表由多列字段构成,每一个字段指定了不同的数据类型,指定了数据类型之后,也就决定了向字段插入的数据内容;
  不同的数据类型也决定了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类型的长度是固定的,指定长度之后,不足最大长度的,将在它们右边填充''以补齐指定长度;
  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='05',b='5',vb='5',vb='5'from tab13;
  
 
  7、BLOB
  BLOB用来存储可变数量的二进制字符串,分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB四种类型;
  BLOB存储的是二进制字符串,TEXT存储的是文本字符串;
  BLOB没有字符集,并且排序和比较基于列值字节的数值;TEXT有一个字符集,并且根据字符集对值进行排序和比较。

本文来自E先生的博客,如若转载,请注明出处:https://www.javajz.cn

留言区

联系人:
手   机:
内   容:
验证码:

历史留言

欢迎加Easy的QQ