MySQL数据类型

1、字符集

创建数据库时，可以指定字符集。

创建表、创建字段也可以。

未指明字符集时，向上选择字符集。（字段默认使用表的字符集、表默认使用数据库的字符集）

CREATE DATABASE 数据库名 CHARACTER SET '字符集';

CREATE TABLE 表名 (数据类型 数据长度) CHARACTER SET '字符集';

2、整型

 

整数类型             字节           有符号数取值范围                    无符号数取值范围

TINYINT                   1              -128~127                                                0~255

SMALLINT               2              -32768~32767                                       0~65535

MEDIUMINT           3              -8388608~8388607                              0~16777215

INT、INTEGER      4           -2147483648~2147483647             0~4294967295

BIGINT                      8             -9223372036854775808~9223372036854775807            0~18446744073709551615

整型的三个可选属性：

（1）M

表示宽度，取值范围是（0，255）。该功能需要配合ZEROFILL使用，表示用‘0’填满宽度，否则指定宽度无效。

结果展示：

（2）UNSIGNED

所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。

（3）ZEROFILL : 以0填充,。

如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL同时数据表示不够M位时，用0在左边填充。如果超过M位，只要不超过数据存储范围即可。在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。也就是说，int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义。如果整数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充。

适用场景

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

3、浮点数

（1）概述

MySQL允许使用非标准语法：

FLOAT(M,D)  或者 DOUBLE(M,D) 

M为精度，D为标度

M<=255   0<=D<=30

（2）特性

浮点类型允许加unsigned，但是不会改变数据范围

浮点型数据，如果整数部分超出M的精度限制，则报错。

如果小数部分标度大于D，则根据四舍五入后的结果进行区分。

如果四舍五入后，通过进位导致整数部分超出M的精度限制，则报错。

否则，只进行四舍五入，损失部分精度，可以正常操作。

注意：因为浮点数是不准确的，所以要避免使用“=”进行是否相等的判断。

4、定点数 DECIMAL(M,D)

（1）定点数的概述

①DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。 DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。

②定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。

③当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

DECIMAL(M,D)

0<=M<=65 ， 0<=D<=30

占用存储空间为M+2  

定点数可以满足精度要求高的场景。

举例：

5、位类型：BIT

BIT类型中存储的是二进制值。

二进制字符串类型                       长度                          长度范围                                   占用空间

BIT(M)                                               M                             1 <= M <= 64                           约为(M + 7)/8个字节

BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值，也就是说最大值为二进制的1，换算为十进制就是1。

(M)是表示二进制的位数，位数最小值为1，最大值为64。

注意：向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内

举例：

关于查询结果的表现形式：如果想将查询结果以二进制的形式显示，使用BIN()

如果想查看查询结果的十六进制形式，使用HEX()，但一般不使用，因为结果本就是以十六进制的形式呈现

举例：

结果：

6、日期与时间

类型             名称         字节             日期格式              最小值             最大值

YEAR                 年             1                 YYYY或YY               1901               2155

TIME               时间           3                 HH:MM:SS          -838:59:59          838:59:59

DATE               日期           3               YYYY-MM-DD        1000-01-01        9999-12-03

DATETIME       日期时间    8              YYYY-MM-DD HH:MM:SS        1000-01-01 00:00:00            9999-12-31 23:59:59

TIMESTAMP      日期时间    4             YYYY-MM-DD HH:MM:SS             1970-01-01 00:00:00 UTC    2038-01-19 03:14:07UTC

（1）YEAR

以2位字符串格式表示YEAR类型，最小值为00，最大值为99。

                   ①当取值为01到69时，表示2001到2069；

                   ②当取值为70到99时，表示1970到1999；

                   ③当取值整数的0或00添加的话，那么是0000年；

                   ④当取值是日期/字符串的'0'添加的话，是2000年。

建议使用XXXX的YEAR形式，没有歧义，可读性更好、

结果展示：

（2）DATE

只包含日期不包含时间。

结果展示：

（3）TIME

不包含日期只表时间。

时间前独立的数字 n 表示天数，结果展示的时候，自动转换为 n*24 小时

结果展示：

（4）DATETIME

同时展示日期与时间。

结果展示：

（5）TIMESATAMP

存储的时间范围比DATETIME小很多，只能从1970-01-01 00:00:01到2038-01-19 03:14:07 UTC之间的时间。

底层会把时间转换为毫秒数，与时区有关。因为该底层原理，涉及到计算的内容，建议使用时间戳。

小结：最常使用DATETIME，包括完整的日期与时间，取值范围也最大，使用更加方便。

而储存注册时间、商品发布时间的时候，一般使用时间戳，因为TIMESTAMP便于计算。

7、文本字符串类型

（1）CHAR 与 VARCHAR

二者都可以存放较短的字符串。

CHAR是固定长度

VARCHAR是可变长度。

CHAR(M)一般需要指明M长度，否则默认长度为1。因为CHAR（）类型是固定长度，字符串长度没有达到M长度的时候，会自动在字符串右侧填充空格以达到指定长度。但检索时，对空格不进行检索。

对CHAR()的举例：

MySQL5.0之后，VARCAHR(M)中存储的从M个字节（如果使用utf8字符集则为6个汉字，应该汉字占3字节）变为M个字符（一个汉字占用一个字符）。

对于CHAR和VARCHAR的选择，当信息很短、长度固定、频繁改变的column的情况下，选择CHAR。

在存储引擎中，MyISAM（MySQL5.5之前）：使用CHAR，数据检索更快。

MEMORY：因为无论输入CHAR还是VARCHAR都会转化为CHAR使用，所以建议使用CHAR

InnoDB（MySQL5.5之后）：主要影响性能的因素是数据行使用的存储总量，建议使用VARCHAR

（2）TEXT

实际长度不确定，因此，MySQL不允许TEXT类型的字段做主键。

检索时可以检索空格。

存储文本比较大时，建议使用TEXT类型。

（3）ENUM枚举类型

字段名 ENUM(‘枚举数据’，‘枚举数据’，…)

①当ENUM类型包含1～255个成员时，需要1个字节的存储空间；

②当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。

③ENUM类型的成员个数的上限为65535个。

一次性只能添加一个枚举中存在的数据。

可以通过复写（忽略大小写）或者索引检索的方式添加数据。

（4）SET

可以一次性写入多个SET中的数据。

如果重复写入，会自动去重。

不能输入SET之外的数据。

8、二进制字符串类型

（1）BINARY 与 VARBINARY

BINARY(M) 可存储M个字节

VARBINARY（M）可变长度的二进制字符串，必须指明长度

一般不使用，与CHAR、VARCHAR的辨析基本相同。

（2）BLOB

二进制的大对象，可以容纳可变数量的数据。

可以存储二进制的大对象，比如图片、音频，但一般不这样操作。

TEXT和BLOB的删除会引起“空洞”的问题，需要定期使用OPTIMIZE TABLE功能进行碎片整理。

如果需要对大文本字段进行模糊查询，MySQL提供了前缀索引

把BLOB和TEXT列分离到单独的表中，可以以此减少主表中的碎片。

9、JSON类型

JSON是一种轻量级的数据交换格式。

JSON可以将JavaScript对象中表示的一组数据转换为字符串。然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候，将其还原为个编程语言所支持的数据格式。

 

10、小结与选择建议：

整数INT

小数DECIMAL(M,D)

日期与时间 DATETIME

关于字符串的选择：

（1）任何字段如果是非负数，必须是UNSIGNED

（2）小数类型为DECIMAL。

存储时，DOUBLE和FLOAT都存在精度损失问题，可能得到不正确的比较结果。如果存储的数据范围超过DECIMAL的范围，建议将数据拆成整数和小数分开存储

（3）如果存储字符串长度几乎相等，使用CHAR定长字符串类型

（4）VARCHAR是可变长字符串，不会预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为TEXT，独立出来一张表，用主键对应，避免影响其他字段索引效率。