# MySQL支持的数据类型

## 数值类型

#### 整数

| 整数类型        | 字节 | 最小值                            | 最大值                                               |
| ----------- | -- | ------------------------------ | ------------------------------------------------- |
| TINYINT     | 1  | 有符号 -128,无符号 0                 | 有符号 127,无符号255                                    |
| SMALLINT    | 2  | 有符号 -32768,无符号 0               | 有符号 32767,无符号 65536                               |
| MEDIUMINT   | 3  | 有符号 -8388608,无符号 0             | 有符号 8388607, 无符号 1677215                          |
| INT,INTEGER | 4  | 有符号 -2147483648, 无符号 0         | 有符号 2147483647,无符号 4294967295                     |
| BIGINT      | 8  | 有符号 -9223372036854775808, 无符号0 | 有符号 9223372036854775807, 无符号 18446744073709551615 |

**显示宽度**

MySQL支持在整数类型名称后面的小括号内指定显示宽度，一般配合zerofill使用。也就是在数字位数不够的空间用字符'0'填满。如果插入大于宽度限制的值，不会对插入的数据有热呢影响，还是按照类型的实际精度进行保存。

```
mysql> alter table t1 modify id1 int(5) zerofill

mysql> select * from t1;
+---------+
| id1     |
#---------+
|   00001 |
| 1111111 |
+---------+
```

**符号**

所有的整数类型都有一个可选属性UNSIGNED(无符号)，如果需要在字段里面保存非负数或者需要较大的上限值时，可选用此选项。

如果一个列指定位zerofill，则MySQL自动为该列添加UNSIGNED属性。

**自增**

整数类型在需要产生唯一标识符或顺序值时，可使用AOTU\_INCREMENT属性。值一般从1开始，每行增加1.

一个表最多只能有一个AUTO\_INCREMENT列。对于任何想要使用AUTO\_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。

## 小数

#### 浮点数

| 浮点类型   | 字节 | 最小值                      | 最大值                       |
| ------ | -- | ------------------------ | ------------------------- |
| FLAOT  | 4  | ±1.175494315E-38         | ±3.402823466E+38          |
| DOUBLE | 8  | ±2.2250738585072014E-308 | ± 1.7976931348623157E+308 |

#### 定点数

| 定点数类型                 | 字节  | 描述                                     |
| --------------------- | --- | -------------------------------------- |
| DEC(M,D),DECIMAL(M,D) | M+2 | 最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定 |

#### 精度

浮点数和定点数都可以用类型名称后加"(M,D)"的方式来进行表示，"(M,D)"表示该值一共显示M位数字(整数位+小数位)，其中D位位于小数点后面，M和D又称为精度和标度。

浮点数后面跟"(M,D)"的用法是非标准用法。

float和double在不制定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。

#### 数据存放

定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

## 位类型

| 位类型    | 字节  | 最小值    | 最大值    |
| ------ | --- | ------ | ------ |
| BIT(M) | 1-8 | BIT(1) | BIT(8) |

对于BIT类型，用于存放位字段值(二进制)，BIT(M)可以用来存放多位二进制数，M范围从1-64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()或者hex()函数进行读取。

```
select hex(column) from table;
```

## 日期时间类型

| 日期和时间类型   | 字节 | 最小值                 | 最大值                 |
| --------- | -- | ------------------- | ------------------- |
| DATE      | 4  | 1000-01-01          | 9999-12-31          |
| DATETIME  | 8  | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4  | 19700101080001      | 2038年的某个时刻          |
| TIME      | 3  | -838:59:59          | 838:59:59           |
| YEAR      | 1  | 1901                | 2155                |

这些数据类型的主要区别如下

* 如果只表示年，通常用YEAR表示
* 如果要用来表示年月日，通常用DATE表示
* 如果要用来表示年月日时分秒，通常用DATETIME表示
* 如果只用来表示时分秒，通常用TIME来表示
* 如果需要经常插入或者更新日期为当前系统时间，通常使用TIMESTAMP

#### TIMESTAMP和DATETIME的区别

* TIMESTAMP支持的时间防范围小
* TIMESTAMP的插入和查询都受当地时区的影响，更能反映出实际的日期。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差。
* TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。

#### 允许插入的格式

* YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许不严格语法，即任何标点符都可以用做日期部分或时间部分之间的间隔符。
* YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串
* YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字
* 函数返回的结果，其值适合DATETIME、DATE或TIMESTAMP上下文。

## 字符串类型

基于MySQL5.0版本

| 字符串类型        | 字节 | 描述及存储需求                      |
| ------------ | -- | ---------------------------- |
| CHAR(M)      | M  | M为0-255之间的整数                 |
| VARCHAR(M)   |    | M为0-65535之间的整数，值的长度+1个字节     |
| TINYBLOB     |    | 允许长度0-255字节，值的长度+1个字节        |
| BLOB         |    | 允许长度0-65535字节，值的长度+2个字节      |
| MEDIUMBLOB   |    | 允许长度0-167772150字节，值的长度+3个字节  |
| LONGBLOB     |    | 允许长度0-4294967295字节，值的长度+4个字节 |
| TINYTEXT     |    | 允许长度0-255字节，值的长度+2字节         |
| TEXT         |    | 允许长度0-65535字节，值的长度+2字节       |
| MEDIUMTEXT   |    | 允许长度0-167772150字节，值的长度+3字节   |
| LONGTEXT     |    | 允许长度0-4294967295字节，值的长度+4字节  |
| VARBINARY(M) |    | 允许长度0-M个字节的边长字节字符串，值的长度+1个字节 |
| BINARY(M)    |    | 允许长度0-M个字节的定长字节字符串           |

#### TEXT和BLOB

BLOB保存二进制数据，TEXT保存字符数据。

#### CHAR和VARCHAR类型

CHAR列的长度固定为创建表时声明的长度，长度可以为从0-255的任何值，不论存储多少个字符，都会占用声明时的长度空间。

VARCHAR列的值为可变长字符串，长度可以制定为0-65535之间的值。存储的长度按实际字符数+1。

在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

```
mysql> CREATE TABLE vc(v VARCHAR(4), c CHAR(4));
mysql> INSERT INTO vc VALUES ('ab  ','ab  ');
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
```

#### BINARY和VARBINARY类型

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

## ENUM类型

枚举类型的值范围需要在创建表时通过枚举方式显式指定，对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储。最多允许又65535分成员。ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。

```
mysql> create table t (gender enum('M','F'));
mysql> INSERT INTO t VALUES('M'),('1'),('f'),(NULL);
mysql> select * from t;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+
```

从上面例子中，可以看出ENUM类型是忽略大小写的。

## SET类型

SET和ENUM类型非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。

* 1-8成员的集合，占1个字节
* 9-16成员的集合，占2个字节
* 17-24成员的集合，占3个字节
* 25-32成员的集合，占4个字节
* 33-64成员的集合，占8个字节

```
mysql> CREATE TABLE t3(col set ('a','b','c','d'));
mysql> INSERT INTO t3 values('a,b'),('a,d,a'),('a');
mysql> select * from t3;
+------+
| col  |
+------+
| a,b  |
| a,d  |
| a    |
+------+
```

SET类型可以从允许值集合中选择任意1个或多个元素进行组合，所以对于输入的值只要是在允许值的组合范围内，都可以正确地注入到SET类型的列中。对于包含重复成员的集合将只取一次。
