03-MySQL数据类型

02-MySQL数据类型

---

MySQL数据类型介绍

1. 数值数据类型
   
   1. 整数类型
      
      • TINYINT
      • SMALLINT
      • MEDIUMINT
      • INT
      • BIGINT
   2. 浮点小数类型
      
      • FLOAT
      • DOUBLE
   3. 定点小数类型
      
      • DECIMAL
2. 日期/时间类型
   
   • YEAR
   • TIME
   • DATE
   • DATETIME
   • TIMESTAMP
3. 字符串类型
   
   • CHAR
   • VARCHAR
   • BINARY
   • VARBINARY
   • BLOB
   • TEXT
   • ENUM
   • SET

---

整数类型

类型名称	说明	存储需求	有符号	无符号
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个字节	-2^63~2^63-1	2^64

计算方法：例如TINYINT需要1个字节（8bits）来存储，那么TINYINT的无符号数的最大值为2^8-1，即255；TINYINT有符号数的最大值为2^7-1，即127

---

浮点数类型和定点数类型

MySQL中小数可以使用浮点数和定点数来表示小数。浮点类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）,定点数只有一种：DECIMAL。

浮点数和定点数都可以使用（M,N）来表示，其中M成为精度，表示总共的位数；N称为标度，表示小数的位数。

类型名称	说明	存储需求
	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL(M,D),DE与C	压缩的“严格”定点数	M+2个字节

无论是定点还是浮点类型，如果用户指定的精度超出了精度范围，则会四舍五入进行处理。

FLOAT和DOUBLE在不指定精度时，默认还会按照实际的精度（由计算机硬件和操作系统决定）

DECIMAL如果不指定精度，默认为（10,0）。

括号内取值，例如DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。

---

日期与时间类型

类型名称	日期格式	存储需求
YEAR	YYYY	1字节
TIME	HH:MM:SS	3字节
DATE	YYYY-MM-DD	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4字节

TIMESTAMP和DATETIME除了存储字节和支持的范围不同之外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关，而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的，存储时对当前时区进行转化，检索时再转换为当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

设置当前的时区为东10区：

mysql> set time_zone='+10:00'

---

文本字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L<=M和1<=M《=255
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个成员）

CHAR和VARCHAR

CHAR（M）是固定长度字符串，在定义时指定字符串列长。M代表列长度

M的范围是0~255，当检索到CHAR值时，尾部的空格将被删除掉

VARCHAR（M）是长度可变的字符串，M表示最大列长度。M的范围是0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。例如,VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符+1个字符串结束符。VARCHAR在值保存和检索时尾部的空格仍然保留。

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
‘’	‘ ’	4字节	‘’	1字节
‘ab’	‘ab ‘	4字节	‘ab’	3字节
‘abc’	‘abc ‘	4字节	‘abc’	5字节
‘abcd’	‘abcd’	4字节	‘abcd’	5字节
‘abcdef’	‘abcd’	4字节	‘abcd’	5字节

ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中的枚举的一列值。语法格式为：

字段名 enum ('值1'，'值2'，...'值n')

ENUM类型的字段在取值时，只能在指定的枚举中取，而且每次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动被删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

值	索引
NULL	NULL
”	0
first	1
second	2
third	3

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

mysql> create table tmp9( myenm enum('first','second','third'));
Query OK, 0 rows affected (0.06 sec)
mysql> desc tmp9;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type                           | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| myenm | enum('first','second','third') | YES  |     | NULL    |       |
+-------+--------------------------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into tmp9 values('first'),('first'),('second'),('third'),(NUL
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select myenm ,myenm+0 from tmp9;
+--------+---------+
| myenm  | myenm+0 |
+--------+---------+
| first  |       1 |
| first  |       1 |
| second |       2 |
| third  |       3 |
| NULL   |    NULL |
+--------+---------+
5 rows in set (0.00 sec)

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

SET

SET是一个字符串对象，可以有零个或多个值，SET列最多可以有64个成员，其值为表创建时规定的一列值。当创建表时，SET成员值的尾部空格将自动被删除。但与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。

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

-- 创建临时表中使用set
mysql> create table temp10(myset set('a','b','c','d'));
Query OK, 0 rows affected (0.03 sec)

-- 插入数据时用逗号隔开
mysql> insert into temp10 values ('a'),('a,b,a'),('c,b,a');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看表结构
mysql> desc temp10;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| myset | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from temp10;
+-------+
| myset |
+-------+
| a     |
| a,b   |
| a,b,c |
+-------+
3 rows in set (0.00 sec)

从结果中可以看出，对于SET来说，插入的值为重复的值时，则只取一个，例如之前插入的”a,b,a”中，实际插入的只是”a,b”。如果插入了不按顺序排列的值，则自动按照顺序插入，例如”c,b,a”,存入数据库中的为”a,b,c”；如果插入了未定义的值，该值将被阻止插入。

二进制字符串类型

类型名称	说明	存储需求
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

BIT类型

BIT类型是为字段类型。M表示每个值的位数，范围为1~64。如果M被省略，默认为1。如果BIT(M)列分配的值的长度小于M位，在值的左边用0填充。

如：BIT(6)分配一个值b’101’，其效果与分配b’000101’相同。BIT数据类型用来保存位字段值，例如：以二进制的形式保存数据13，13的二进制位1101，在这里需要至少为4位的BIT类型。大于二进制1111的数据是不能插入BIT(4)类型的字段中的。

BINARY 和VARBINARY类型

BINARY 和VARBINARY类型类似于CHAR和VARCHAR，不同的是他们包含二进制字符串。其使用的语法格式如下：

列名称 BINARY (M) 或者 VARBINARY（M）

BINARY类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充‘\0’补齐以达到指定长度。

例如：指定列数据类型为BINARY（3）,当插入”a”时，存储的内容实际上是”a\0\0”，当插入”ab”时，实际存储的内容为“ab\0”,不管存储的内容是否达到指定的长度，其存储空间均为指定的M

VARBINARY类型的长度是可变的。指定好长度后，其长度可以在0到最大值之间。

例如：指定列数据类型为VARBINARY(20)，如果插入的值的长度只有10，则实际存储为10+1，即其实际占用的空间为字符串的实际长度+1 /

BLOB类型

BLOB类型是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4中：

数据类型	存储范围
TINYBLOB	最大长度为255（2^8-1）B
BLOB	最大长度为65535(2^16-1) B
MEDIUMBLOB	最大长度为16777215（2^24-1）B
LONGBLOB	最大长度为4GB（2^32-1）B

BLOB列存储的是二进制字符串（字节字符串）：TEXT列存储的是非二进制字符串。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序。

---

5.2如何选择数据类型

1.浮点数和整数

• 如果不需要小数部分，使用整数来存储，反之使用浮点数
• 对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入
• DOUBLE类型精度比FLOAT类型高，所以要求存储精度较高时，使用DOUBLE

2.浮点数和定点数

• 长度一定的情况下，浮点数能表示更大的数据范围，但容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储

3.日期与时间类型

• 由于TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期组好使用DATETIME
• 需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，TIMESTAMP在空间上比DATETIME更有效

4.CHAR和VARCHAR

• CHAR是固定长度字符，VARCHAR是可变长度字符；处理速度上CHAR更快，缺点是浪费空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之使用VARCHAR。
• CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格
• 存储引擎对于选择CHAR和VARCHAR的影响
• 对于MyISAM存储引擎：最好使用固定长度的数据列代替可变长度的数据列，可以使表静态化，从而使数据检索更快，用空间换时间。
• 对于InnoDB存储引擎：使用可变长度的数据列，因为InnoDB数据表的存储格式部分固定长度和可变长度。

5.ENUM和SET

• ENUM只能去单值，它的数据列表是一个枚举集合，它的合法取值最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。例如“男”，“女”
• SET可取多值，它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候使用。

6.BLOB和TEXT

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