数值、字符串、时间日期...MySQL数据库常见表字段数据类型
MySQL数据库中,数据类型有很多种,主要分为三大类:数值类型、字符串类型、时间日期类型。
1.表字段数据类型之数值类型
常见的数值类型的表字段有:TINYINT、SMALLINT、MEDIUMINT、INT、INTEGER、BIGINT、FLOAT、DOUBLE、DECIMAL几种。
在数值类型中存在符合范围的的概念,分别是有符号(SIGNED)范围和无符号(UNSIGNED)范围,简单理解有符号范围指的是包含负数,无符号范围指的是只有正数,不包含负数的数据类型。
1.1.常见的几种数值类型
TINYINT类型:
- TINYINT类型的含义是小整数值,数据大小为1字节。
- 有符号范围:
(-128 ~ 127)
,无符号范围:(0 ~ 255)
。
SMALLINT类型:
- SMALLINT类型的含义是大整数值,数据大小为2字节。
- 有符号范围:
(-32768 ~ 32767)
,无符号范围:(0 ~ 65535)
。
MEDIUMINT类型:
- MEDIUMINT类型的含义也是大整数值,比SMALLINT类型范围更广,数据大小为3字节。
- 有符号范围:
(-8388608 ~ 8388607)
,无符号范围:(0 ~ 16777215)
。
INT和INTEGER类型:
- INT和INTEGER类型功能一样,也都是大整数值,INT和INTEGER类型比MEDIUMINT类型的范围还要广,数据大小为4字节。
- 有符号范围:
(-2147483648 ~ 2147483647)
,无符号范围:(0 ~ 4294967295)
。
BIGINT类型:
- BINGINT类型也是大整数值,比前面提到的几种类型的范围更广,也被称为极大整数值,数据大小为8个字节。
- 有符号范围:
(-2^63 ~ 2^63-1)
,无符号范围:``(0 ~ 2^64-1)`。
FLOAT类型:
- FLOAT类型的含义是单精度浮点数值,数据大小为4字节。
- 有符号范围:
(-3.402823466 E+38 ~ 3.402823466351 E+38)
,无符号范围:0 和 (1.175494351 E-38 ~ 3.402823466 E+38)
。
DOUBLE类型:
- DOUBLE类型的含义是双精度浮点数值,数据大小为8字节。
- 有符号范围:
(-1.7976931348623157 E+308 ~ 1.7976931348623157 E+308)
,无符号范围:0 和 (2.2250738585072014 E-308 ~ 1.7976931348623157 E+308)
。
DECIMAL类型:
- DECIMAL类型是小数中最常用的数据类型,含义就是小数值,可以精确定点数。
- DECIMAL类型的有符号范围和无符号范围都依赖于M(精度)和D(标度)指定的值,所谓的精度就是小数中所有点数的总和,标度就是小数中小数点以后的数量。
- 例如34.156这个小数,小数点左边有2位,精度就是2,小数点右边有3位,标度就是3。
1.2.选择数值类型的几个要点
在所有的数值类型中,整数值不是越大越合适,要根据自己数据的存储需求,按需来选择需要设置的数据类型。
整数值的范围越大,那么占用的空间就越大,合理设置数据类型很关键。
浮点数小数一般都会用DESCIMAL数据类型。
1.3.应用数值类型的典型示例
在这么多种数值类型中,如何选择出真正适合当前应用场景的数据类型呢?在前面也说了,不是越大越好,要越合理越好,下面的例子带你理解明白。
1)人员年龄字段的数据类型选择
根据这个字段的字面意思,我们大体知道这个字段是存储人员的年龄的,那么年龄的数值范围一定不会特别大,像前面看到的几个大整数值完全可以不采用,但是很多情况下,开发只知道INT类型,INT类型的数值范围非常的广,范围大确实很好,但是对于年龄这个需求来说没必要那么大,范围大了,采用INT类型占用4个字节,占用的空间也很大。
因此要明白一个道理,合适自己的才是最好的,像第一种介绍的数值类型TINYINT,在年龄中不会存在负数,因此可以采用TINYINT类型的无符号范围,这个数据类型虽然是小整数值,但是范围却是0 ~255之间,那么足够年龄的使用了。
SQL如下所示:
sql
age TINYINT UNSIGNED
2)分数字段的数据类型选择
小数类型可以使用DOUBLE和DECIMAL两者都可,没有太大的区别,要考虑分数不会超过一千分,因此小数点中左边最大只会有3个,分数中包含小数,但是都是.5之类的,因此小数点右边只会有1个,根据分析得知,精度为4,标度为1。
sql
fenshu DOUBLE(4,1)
fenshu DECIMAL(4,1)
2.表字段数据类型之字符串类型
2.1.常见的几种字符串类型
在字符串数据类型中使用最多的就是CHAR和VARCHAR两种,TEXT和LONGTESXT也是常用的字符串数据类型。
其中带有TEXT的数据类型是针对文本的数据类型,带有BLOB的数据类型是针对二进制文本的数据类型,二进制的字符串存储很少用,会有专门的文件服务器进行存储,数据库存储二进制字符串性能不高。
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0~ 255 bytes | 定长字符串 |
VARCHAR | 0 ~ 65535 bytes | 变长字符串 |
TINYBLOB | 0 ~ 255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0 ~ 255 bytes | 短文本字符串 |
BLOB | 0 ~ 65535 bytes | 二进制形式的长文本数据 |
TEXT | 0 ~ 65535 bytes | 长文本数据 |
MEDIUMBLOB | 0 ~ 16777215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0 ~ 16777215 bytes | 中等长度文本数据 |
LONGBLOB | 0 ~ 4 294967295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0 ~ 4 294 967295 bytes | 极大文本数据 |
CHAR和VARCHAR的区别
无论使用CHAR还是VARCHAR都需要指定字符串的长度,例如
char(10) varchar(10)
。CHAR是定长字符串,当我们指定存储字符串的长度为10,如果写入的文本字符串的数量不够10个,则会以空格进行填充,性能比VARCHAR好。
VARCHAR是变长字符串,当我们指定存储字符串的长度为10,写入了几个文本字符串就算几个,不会用空格进行补充,由于需要计算写入的字符串数量与总长度进行比较,因此VARCHAR的性能略低与CHAR。
2.2.使用CHAR和VARCHAR的技巧
当明确指定该字段写入的字符串数量,并且一定会写入指定数量的字符串时,选择CHAR作为数据类型。
当不固定用户会写入多少个字符串时,但是由文字数量限制,此时就使用VARCHAR作为数据类型。
例如用户名是无法固定的,采用VARCHAR作为数据类型,而性别是固定的,只有一个字符串,则使用CHAR数据类型。
sql
name varchar(10)
xingbie char(1)
3.表字段数据类型之日期时间类型
2.1.常见的几种日期时间类型
常见的日期时间的数据类型有:DATE、TIME、YEAR、DATETIME、TIMESTAMP等,其中最为常用的是DATE、TIME和DATATIME这三种。
DATE类型
- DATE类型的大小是3字节,时间范围是1000-01-01 至 9999-12-31,足够使用。
- 在表中的显示格式为YYYY-MM-DD,相当于日期值,只显示年月日。
TIME类型
- TIME类型的大小是3字节,时间范围是-838:59:59 至 838:59:59。
- 在表中的显示格式为HH:MM:SS,相当于时间值或者持续时间,只显示时分秒。
YEAR类型
- YEAR类型的大小是1字节,时间范围是1901至2155。
- 在表中的显示格式为YYYY,相当于年份值,只显示年。
DATETIME类型
- DATETIME类型的大小是8字节,时间范围是1000-01-01 00:00:00 至 9999-12-31 23:59:59。
- 在表中的显示格式为YYYY-MM-DD HH:MM:SS,相当于混合日期和时间值,日期值和时间值的合并,既显示年月日又显示时分秒。
TIMESTAMP类型
- TIMESTAMP类型的大小是4字节,时间范围是1970-01-01 00:00:01 至 2038-01-19 03:14:07,只能使用到2038年。
- 在表中的显示格式为YYYY-MM-DD HH:MM:SS,和DATETIME类型相似,也是既显示年月日又显示时分秒,不过TIMESTAMP类型显示的是时间戳。
2.2.日期时间类型使用技巧
当字段为入职时间、毕业时间、出生时间等等,像这种只能固定到年月日的字段,使用DATE数据类型。
像创建时间、更新时间、加入时间等等可以追踪的字段,使用DATETIME类型。
来源:稀土掘金