数值、字符串、时间日期...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类型。

作者:jiangxl
来源:稀土掘金
THE END