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

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

admin • 2021-03-30 11:05:23 • MySQL • 阅读803
[导读]：一、数据类型简介 数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段插入的数据内容; 不同的数据类型也决定了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://javajz.cn

数据库,数据类型,详解,史上最全,mysql
打赏 微信扫一扫微信扫一扫
admin
JavaDataBaseConnectivity Java数据库连接.
« 上一篇
批处理备份mysql数据库脚本[直接用]
下一篇 »

相关推荐

留言区

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

历史留言

鲁ICP备20021488号
欢迎加Easy的QQ