Mysql的数据类型 #

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

（1）数值类型：

包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECIMAL。

（2）日期/时间类型：

包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

（3）字符串类型：

包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。

字符串类型又分为文本字符串和二进制字符串。

数值类型 #

数值类型整型 #

根据占用字节数可以求出每一种数据类型的取值范围。

例如：

TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；T

INYINT 有符号数的最大值为 27-1，即 127。

具体如下表所示：

显示宽度与占用字节数 #

在我们创建数据表的时候，常使用显示宽度，如：

CREATE TABLE tb1( 
	id int(4),
	name varchar(20),
	year int(4)
);

⚡ int(M) 中的M即显示宽度。

显示宽度和数据类型的取值范围是无关的。

显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；

如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。

例如:

向year字段插入一个数值19999，当使用SELECT查询该列值的时候，MySQL显示的将是完整的带有5位数字的19999，而不是4位数字的值。

再或者，int(4) 会占用4字节的存储空间，并且不是说可以显示的最大值是4位，最大值依然是2147483647。

数值类型浮点型 #

MySQL中使用浮点数和定点数来表示小数。

浮点数类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。

定点数类型只有一种：DECIMAL。

🌹 浮点数类型和定点数类型都可以用（M，N）来表示。

其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。

其取值范围为 M（1～255）和 D（1～30，且不能大于 M-2）。

M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。

DECIMAL 的默认 D 值为 0、M 值为 10。

具体见下表：

⚡ 从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

日期时间类型 #

MySQL)中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将零值插入数据库中。

具体如下表：

YEAR #

YEAR类型是一个单字节类型，用于表示年，在存储时只需要1字节。

1️⃣ 以4位字符串或者4位数字格式表示的YEAR，范围为‘1901’～‘2155’。输入格式为‘YYYY’或者YYYY。例如，输入‘2010’或2010，插入到数据库的值均为2010。

2️⃣ 以2位字符串格式表示的YEAR，范围为‘00’到‘99’。‘00’～‘69’和‘70’～‘99’范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为2000。

3️⃣ 以2位数字表示的YEAR，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。注意：在这里0值将被转换为0000，而不是2000。

操作演示：

# 创建一个新的数据库 db_year
MySQL root@(none):db1> CREATE DATABASE IF NOT EXISTS db_year;
Query OK, 1 row affected
Time: 0.002s

# 使用数据库
MySQL root@(none):db1> use db_year;
You are now connected to database "db_year" as user "root"
Time: 0.000s

# 创建一张表 tb_year,有一个字段y,类型为YEAR
MySQL root@(none):db_year> CREATE TABLE IF NOT EXISTS tb_year(y YEAR);
Query OK, 0 rows affected
Time: 0.006s

# 插入测试数据
MySQL root@(none):db_year> INSERT INTO tb_year (y) VALUES (2000),('2001'),(88),('66');
Query OK, 4 row affected
Time: 0.025s


# 查看测试数据
MySQL root@(none):db_year> SELECT * FROM tb_year;
+------+
| y    |
+------+
| 2000 |
| 2001 |
| 1988 |
| 2066 |
+------+
4 rows in set
Time: 0.008s

TIME #

TIME类型用在只需要时间信息的值，在存储时需要3字节，格式为‘HH:MM:SS’。

其中，HH表示小时，MM表示分钟，SS表示秒。TIME类型的取值范围为-838:59:59 ～838:59:59。

小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间（必须小于24小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可以大于24小时，或者甚至为负）。

TIME类型表示方式：

• ‘D HH:MM:SS’格式的字符串。
• ‘HHMMSS’格式的，没有间隔符的字符串或者HHMMSS格式的数值。

DATE #

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3字节。

日期格式为‘YYYY-MM-DD’。其中，YYYY表示年，MM表示月，DD表示日。

在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。

• 推荐以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为‘1000-01-01’～‘9999-12-3’。
• 使用CURRENT_DATE或者NOW()，插入当前系统日期。

DATETIME(推荐使用) #

DATETIME类型用于需要同时包含日期和时间信息的值，在存储时需要8字节。

日期格式为‘YYYY-MM-DD HH:MM:SS’。其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。

在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME的日期格式即可。

TIMESTAMP #

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

datetime与timestamp的区别 #

• datetime的默认值为null，timestamp的默认值不为null，且为系统当前时间（current_timestatmp）。如果不做特殊处理，且update没有指定该列更新，则默认更新为当前时间。
• datetime占用8个字节，timestamp占用4个字节。timestamp利用率更高。
• 二者存储方式不一样，对于timestamp，它把客户端插入的时间从当前时区转化为世界标准时间（UTC）进行存储，查询时，逆向返回。但对于datetime，基本上存什么是什么。
• 二者范围不一样。timestamp范围：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’； datetime范围：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。原因是，timestamp占用4字节，能表示最大的时间毫秒为2的31次方减1，也就是2147483647，换成时间刚好是2038-01-19 03:14:07.999999。

字符串类型 #

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。

在基础教程中，我们主要介绍文本字符串，对于二进制数据会在高级篇介绍。

文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM和SET。

具体见下表，括号中的M表示可以为其指定长度：

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR和VARCHAR类型 #

CHAR(M)为固定长度字符串，在定义时指定字符串列长。

当保存时在右侧填充空格，以达到指定的长度。

M表示列长度，M的范围是0~255个字符。

例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。

VARCHAR(M)是长度可变的字符串，M表示最大列长度。M的范围是0~65535。

VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。

例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。

VARCHAR在值保存和检索时尾部的空格仍保留。