MySQL数据类型

数值类型

整数类型

进制

以十进制数字109为例,它表示有1个10²,加0个10¹,加9个10⁰,用数学符号表示就是:

109 = 1 × 10² + 0 × 10¹ + 9 × 10

这个数字也可以这么表示:

109 = 1 × 8² + 5 × 8¹ + 5 × 8

也就是从八进制的逢8进一的角度上考虑,这个数可以被表示为155(八进制)。当然这个数也可以这么写:

109 = 1 × 2⁶ + 1 × 2⁵ + 0 × 2⁴ + 1 × 2³ + 1 × 2² + 0 × 2¹ + 1 × 2

也就是从二进制的逢2进一的角度上考虑,这个数也可以被表示为:1101101(二进制)。又因为计算机中8个比特位代表一个字节,平时都是用若干个字节来表示一个整数,假如用1个字节表示109的话,那效果就是这样:01101101(二进制),假如用两个字节表示十进制数109的话,那效果就是这样:0000000001101101(二进制)。

MySQL的整数类型

MySQL把整数划分成如下所示的类型:

类型 占用的存储空间(单位:字节) 无符号数取值范围 有符号数取值范围 含义
TINYINT 1 0 ~ 2⁸-1 -2⁷ ~ 2⁷-1 非常小的整数
SMALLINT 2 0 ~ 2¹⁶-1 -2¹⁵ ~ 2¹⁵-1 小的整数
MEDIUMINT 3 0 ~ 2²⁴-1 -2²³ ~ 2²³-1 中等大小的整数
INT(别名:INTEGER 4 0 ~ 2³²-1 -2³¹ ~ 2³¹-1 标准的整数
BIGINT 8 0 ~ 2⁶⁴-1 -2⁶³ ~ 2⁶³-1 大整数

浮点数类型

用二进制表示十进制小数

9.875,这个小数可以被表示成这样:

9.875 = 8 + 1 + 0.5 + 0.25 + 0.125 = 1 × 2³ + 1 × 2⁰ + 1 × 2⁻¹ + 1 × 2⁻² + 1 × 2⁻³

如果十进制小数9.875转换成二进制小数的话就是:1001.111。为了在计算机里存储这种二进制小数,我们统一把它们表示成a × 2ⁿ的科学计数法的形式,其中1≤|a|<2,比如1001.111可以被表示成1.001111 × 2³,我们把小数点之后的001111称为尾数,把中的3称为指数,然后只需要在计算机中的比特位中表示出尾数指数就行了。另外,小数也有正负之分,我们还需要单独的部分来表示小数的正负号。综上所述,表示一个浮点数需要下边几个部分:

  • 符号部分,占用1个比特位即可。
  • 指数部分,视具体浮点数格式而定。
  • 尾数部分,视具体浮点数格式而定。

MySQL的浮点数类型

设计MySQL的大叔根据表示一个小数需要的不同字节数定义了如下的两种浮点数类型:

类型 占用的存储空间(单位:字节) 绝对值最小非0值 绝对值最大非0值 含义
FLOAT 4 ±1.175494351E-38 ±3.402823466E+38 单精度浮点数
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308 双精度浮点数

以单精度浮点数类型FLOAT类型为例,它占用的4个字节的各个组成部分如下图所示:

单精度浮点数格式

设置最大位数和小数位数

在定义浮点数类型时,还可以在FLOAT或者DOUBLE后边跟上两个参数,就像这样:

FLOAT(M, D)
DOUBLE(M, D)

如果我们事先知道表中的某个列要存储的小数在一定范围内,我们可以使用FLOAT(M, D)或者DOUBLE(M, D)来限制可以存储到本列中的小数范围。其中:

  • M表示该小数最多需要的十进制有效数字个数。

    注意是有效数字个数,比方说对于小数-2.3来说有效数字个数就是2,对于小数0.9来说有效数字个数就是1

  • D表示该小数的小数点后的十进制数字个数。

设置了MD的单精度浮点数的取值范围的变化:

类型 取值范围
FLOAT(4, 1) -999.9~999.9
FLOAT(5, 1) -9999.9~9999.9
FLOAT(6, 1) -99999.9~99999.9
FLOAT(4, 0) -9999~9999
FLOAT(4, 1) -999.9~999.9
FLOAT(4, 2) -99.99~99.99

M的取值范围是1~255D的取值范围是0~30,而且D的值必须不大于MMD都是可选的,如果我们省略了它们,那它们的值按照机器支持的最大值来存储。

定点数类型

正因为用浮点数表示小数可能会有不精确的情况,在一些情况下我们必须保证小数是精确的,所以设计MySQL的大叔们提出一种称之为定点数的数据类型,它也是存储小数的一种方式:

类型 占用的存储空间(单位:字节) 取值范围
DECIMAL(M, D) 取决于M和D 取决于M和D

对于给定MD值的DECIMAL(M, D)类型,比如DEMCIMAL(16, 4)来说:

  • 首先确定小数点左边的整数最多需要存储的十进制位数是12位,小数点右边的整数需要存储的十进制位数是4位,如图所示:

    定点数类型

  • 从小数点位置出发,每个整数每隔9个十进制位划分为1组,效果就是这样:
    定点数类型

  • 针对每个组中的十进制数字,将其转换为二进制数字进行存储,根据组中包含的十进制数字位数不同,所需的存储空间大小也不同,具体见下表:

    组中包含的十进制位数 占用存储空间大小(单位:字节)
    1或2 1
    3或4 2
    5或6 3
    7或8或9 4
  • 将转换完成的比特位序列的最高位设置为1。

举个例子就都清楚了。比方说我们使用定点数类型DECIMAL(16, 4)来存储十进制小数1234567890.1234,这个小数会被划分成3个部分:

1 234567890 1234

也就是:

  • 第1组中包含整数1
  • 第2组中包含整数234567890
  • 第3组中包含整数1234

然后将每一组中的十进制数字转换成对应的二进制数字:

  • 第1组占用2个字节,整数1对应的二进制数就是:

    00000000 00000001

    二进制看起来太难受,我们还是转换成对应的十六进制看一下:

    0x0001
  • 第2组占用4个字节,整数234567890对应的十六进制数就是:

    0x0DFB38D2
  • 第3组占用2个字节,整数1234对应的十六进制数就是:

    0x04D2

所以将这些十六进制数字连起来之后就是:

0x00010DFB38D204D2

最后还要将这个结果的最高位设置为1,所以最终十进制小数1234567890.1234使用定点数类型DECIMAL(16, 4)存储时共占用8个字节,具体内容为:

0x80010DFB38D204D2

如果我们想使用定点数类型DECIMAL(16, 4)存储一个负数怎么办,比方说-1234567890.1234,这时只需要将0x80010DFB38D204D2中的每一个比特位都执行一个取反操作就好,也就是得到下边这个结果:

0x7FFEF204C72DFB2D

对于定点数类型DECIMAL(M, D)来说,MD都是可选的,默认的M的值是10,默认的D的值是0,也就是说下列等式是成立的:

DECIMAL = DECIMAL(10) = DECIMAL(10, 0)
DECIMAL(n) = DECIMAL(n, 0)

另外M的范围是1~65D的范围是0~30,且D的值不能超过M

无符号数值类型的表示

对于数值类型,包括整数、浮点数和定点数,有些情况下我们只需要用到无符号数(就是非负数)。MySQL给我们提供了一个表示无符号数值类型的方式,就是在原数值类型后加一个单词UNSIGNED

数值类型 UNSIGNED

比如INT UNSIGNED就表示无符号整数,FLOAT UNSIGNED表示无符号浮点数,DECIMAL UNSIGNED表示无符号定点数。

日期和时间类型

各种类型能表示的范围如下:

类型 存储空间要求 取值范围 含义
YEAR 1字节 1901~2155 年份值
DATE 3字节 ‘1000-01-01’ ~ ‘9999-12-31’ 日期值
TIME 3字节 ‘-838:59:59’ ~ ‘838:59:59’ 时间值
DATETIME 8字节 ‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’ 日期加时间值
TIMESTAMP 4字节 ‘1970-01-01 00:00:01’ ~ ‘2038-01-19 03:14:07’ 时间戳

如果我们想让TIMEDATETIMETIMESTAMP这几种类型支持小数秒,可以这样写:

类型(小数秒位数)

其中的小数秒位数可以在0123456中选择

比如DATETIME(0)表示精确到秒,DATETIME(3)表示精确到毫秒,DATETIME(5)表示精确到10微秒。如果你在选择TIMEDATETIMETIMESTAMP这几种类型的时候添加了对小数秒的支持,那么所需的存储空间需要相应的扩大,保留不同的小数秒位数,那么增加的存储空间大小也不同,如下表:

保留的小数秒位数 额外需要的存储空间要
0 0字节
1或2 1字节
3或4 2字节
5或6 3字节

也就是说如果你选择使用DATETIME(1),那么需要的存储空间就是在DATETIME的空间上再加上小数秒需要的空间,就是8 + 1 = 9个字节,类似的,DATETIME(3)就需要8 + 2 = 10个字节。所以,MySQL5.6.4这个版本之后的各个类型需要的存储空间和取值范围就如下:

类型 存储空间要求 取值范围 含义
YEAR 1字节 1901~2155 年份值
DATE 3字节 ‘1000-01-01’ ~ ‘9999-12-31’ 日期值
TIME 3字节+小数秒的存储空间 ‘-838:59:59[.000000]’ ~ ‘838:59:59[.000000]’ 时间值
DATETIME 5字节+小数秒的存储空间 ‘1000-01-01 00:00:00[.000000]’ ~ ‘9999-12-31 23:59:59’[.999999] 日期加时间值
TIMESTAMP 4字节+小数秒的存储空间 ‘1970-01-01 00:00:01[.000000]’ ~ ‘2038-01-19 03:14:07’[.999999] 时间戳

YEAR

YEAR类型也可以写成YEAR(4),它单纯表示一个年份值,取值范围为1901 ~ 2155,仅仅占用1个字节大小而已。因为可以存储的年份值有限,如果我们想存储更大范围的年份值,可以不使用MySQL自带的YEAR类型,换成SMALLINT(2字节)或者字符串类型啥的都可以。

DATE、TIME和DATETIME

顾名思义,DATE表示日期,格式是YYYY-MM-DDTIME表示时间,格式是hh:mm:ss[.uuuuuu]或者hhh:mm:ss[.uuuuuu](有时候要存储的小时值是三位数),DATETIME表示日期+时间,格式是YYYY-MM-DD hh:mm:ss[.uuuuuu]。其中的YYYYMMDDhhmmssuuuuuu分别表示年、月、日、时、分、秒、小数秒。

需要注意的是,DATETIME中的时间部分表示的是一天内的时间(00:00:00 ~ 23:59:59),而 TIME表示的是一段时间,而且可以表示负值。

TIMESTAMP

1970-01-01 00:00:00注定是一个特殊的时刻,我们把某个时刻距离1970-01-01 00:00:00的秒数称为时间戳。比方说当前时间是2018-01-24 11:39:21,距离1970-01-01 00:00:00的秒数为1516765161,那么2018-01-24 11:39:21这个时刻的时间戳就是1516765161

用时间戳存储时间的好处就是,它展示的值可以随着时区的变化而变化。比方说我们把2018-01-24 11:39:21这个时刻存储到一个TIMESTAMP的列中,那么在中国你看到的时间就是2018-01-24 11:39:21,如果你去了日本,他们哪里的使用的是东京时间,比北京时间早一个小时,所以他们那显示的就是2018-01-24 12:39:21。而如果你用DATETIME存储2018-01-24 11:39:21的话,那不同时区看到的时间值都是一样的。

字符串类型

字符和字符串

字符可以大致分为两种,一种叫可见字符,一种叫不可见字符。顾名思义,可见字符就是打印出来后能看见的字符。比如'a''b''我''。'不可见字符就是打印机或者在黑框框里打印字符的时候有时候需要换行,打个制表符啥的。字符串就是把字符连起来的样子,比如'abc',就是由'a''b''c'三个字符连起来的一个字符串,下边列举了4个字符串的例子:

'我喜欢你'
'me, too'
'give me a hug'
'么么哒'

本博客所有文章除特别声明外,均采用 CC BY-SA 3.0协议 。转载请注明出处!