MySQL(4)：数据类型简介

​ MySQL 的数据类型有大概可以分为 4 种，分别是数值类型、日期和时间类型、字符串类型、二进制类型等。

一、数值类型

1、整数类型

类型	说明	存储空间(byte)	取值范围
TINYINT	很小的整数	1	-128~127
SMALLINT	小的整数	2	-32768〜32767
MEDIUMINT	中等大小的整数	3	-8388608〜8388607
INT (INTEGHR)	普通大小的整数	4	-2147483648〜2147483647
BIGINT	大整数	8	-9223372036854775808〜9223372036854775807

注意：

• 取值范围：-2的(N-1)次方 至 2的(N-1)次方-1，N是存储空间的位数，1byte = 8 bit。

• 整数类型有可选的UNSIGNED ,表示无符号数值。例如：TINYINT 和 TINYINT UNSIGNED，前者取值范围是-128 ~ 127，后者取值范围是0~255。

• 显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如 INT(1) 和 INT(11) ，只表示在MySQL的一些交互工具（如：MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，两种是相同的。

2、小数类型

类型	说明	存储空间(byte)
FLOAT	单精度浮点数	4
DOUBLE	双精度浮点数	8
DECIMAL (M, D)	压缩的“严格”定点数	M+2

注意：

• 浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数（小数点左边和右边）；D称为标度，表示小数的位数。
• FLOAT 和 DOUBLE 在不指定精度时，默认会按照存入的实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。
• 不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

二、日期和时间类型

类型	日期格式	日期范围	存储空间(byte)
YEAR	YYYY	1901 ~ 2155	1
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC	4

DATETIME和TIMESTAMP的不同点

• 存储范围不同
• 时区相关
  • datetime存储与时区无关（准确来说datetime只支持一个时区，就是存储时当前服务器的时区），而timestamp存储的是与时区有关。
  • MySQL在存储timestamp时，会先将时间从当前服务器的时区转换为UTC（世界协调时）以进行存储，然后查询时从UTC转换为当前时区以进行返回。也就是说使用timestamp进行存储的时间返回的时候会随着数据库的时区而发生改变。而datetime的存储则与时区无关，数据是什么就存储什么，也就返回什么。
• 存储大小

  • 在5.6.4之前，datetime存储占用8个字节，而timestamp是占用4字节；但是在5.6.4之后，由于这两个类型允许有小数部分，所以占用的存储空间和以前不同；

  • MySQL规范规定，datetime的非小数部分需要5个字节，而不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度。

    https://baijiahao.baidu.com/s？id=1671113933155288895&wfr=spider&for=pc

三、字符串类型

类型	说明	存储空间(byte)
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255，M是指定长度
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255，L是实际长度
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

1、CHAR和VARCHAR区别

• 定长和变长：
  char 表示定长，长度固定，varchar表示变长，长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。

• 性能不同
  因其长度固定，char的存取速度还是要比varchar要快，方便程序的存储与查找，但是char会占据多余的存储空间，所谓以空间换时间；varchar则刚好相反，以时间换空间。

• 存储容量不同
  char ，存储容量范围是 0～255 个字符，和编码无关。
  varchar，最多能存放 65532 个字符。varchar的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。整体最大长度是 65,532字节。

插入值	CHAR(4)	存储空间(byte)	VARCHAR(4)	存储空间(byte)
’ ’	’ ’	4	‘’	1
‘ab’	'ab ’	4	‘ab’	3
‘abc’	'abc ’	4	‘abc’	4
‘abcd’	‘abcd’	4	‘abcd’	5
‘abcdef’	‘abcd’	4	‘abcd’	5

2、TEXT

​ TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

3、ENUM

• ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。

-- eg
`gender` enum('boy','girl') NOT NULL DEFAULT "boy";

• ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

• ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。

值	索引
NULL	NULL
‘’	0
boy	1
girl	2

• ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

4、SET

• SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )

• 与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

• 与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

提示：

如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

四、二进制类型

类型名称	说明	存储空间(byte)
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

1、bit

​ 位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b’101’，其效果与分配 b’000101’ 相同。

2、BINARY 和 VARBINARY

​ BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。

3、BLOB

• BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非进制字符串（字符字符串）。
• 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。
• BLOB 列是字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。MySQL对BLOB和TEXT列进行排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024。当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。

五、数据类型的选取

1、数值类型：

​ 对于数值类型列，如果要存储的数字是整数（没有小数部分），则使用整数类型；如果要存储的数字是小数（带有小数部分），则可以选用 DECIMAL 或浮点类型，但是一般选择 FLOAT 类型（浮点类型的一种）。

​ 正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。

货币、金钱：DECIMAL(M,2)

2、日期和时间类型：

• MySQL 对于不同种类的日期和时间都提供了数据类型，需要用哪种就用哪种。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。

• TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

​ MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分，DATETIME 有时间部分。如果时间部分是可选的，那么可以使用 DATE 列来记录日期，再用一个单独的 TIME 列来记录时间。然后，设置 TIME 列可以为 NULL。SQL 语句如下：

CREATE TABLE mytb1 (
  date DATE NOT NULL, #日期是必需的
  time TIME NULL #时间可选(可能为NULL)
);

3、字符串类型：

​ 字符串类型没有像数字类型列那样的“取值范围"，但它们都有长度的概念。如果需要存储的字符串短于 256 个字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串，则可以选用 VARCHAR 或某种更长的 TEXT 类型。

存储引擎对于选择 CHAR 和 VARCHAR 的影响:

• 对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
• 对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

ENUM：从多个值中选取一个，比如，性别字段。

SET：取多个值，比如，兴趣爱好。

4、二进制类型：

​ BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。

六、Java 和 MySQL 数据类型对照表

类型名称	显示长度	数据库类型	JAVA类型	JDBC类型索引(int)
VARCHAR	L+N	VARCHAR	java.lang.String	12
CHAR	N	CHAR	java.lang.String	1
BLOB	L+N	BLOB	java.lang.byte[]	-4
TEXT	65535	VARCHAR	java.lang.String	-1
INTEGER	4	INTEGER UNSIGNED	java.lang.Long	4
TINYINT	3	TINYINT UNSIGNED	java.lang.Integer	-6
SMALLINT	5	SMALLINT UNSIGNED	java.lang.Integer	5
MEDIUMINT	8	MEDIUMINT UNSIGNED	java.lang.Integer	4
BIT	1	BIT	java.lang.Boolean	-7
BIGINT	20	BIGINT UNSIGNED	java.math.BigInteger	-5
FLOAT	4+8	FLOAT	java.lang.Float	7
DOUBLE	22	DOUBLE	java.lang.Double	8
DECIMAL	11	DECIMAL	java.math.BigDecimal	3
BOOLEAN	1	同TINYINT
ID	11	PK (INTEGER UNSIGNED)	java.lang.Long	4
DATE	10	DATE	java.sql.Date	91
TIME	8	TIME	java.sql.Time	92
DATETIME	19	DATETIME	java.sql.Timestamp	93
TIMESTAMP	19	TIMESTAMP	java.sql.Timestamp	93
YEAR	4	YEAR	java.sql.Date	91