MySQL基础-数据类型

概要

MySQL 数据类型又叫 “列类型” 、“字段类型” 。数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。MySQL 数据类型大致分为 数值类型，字符串/文本类型，二进制类型与时间/日期类型 ，如以下三张表所示：
数值类型

类型	大小（字节）	范围（signed）	范围（unsigned）
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615
FLOAT	4
DOUBLE	8
DECIMAL	M+2	取决于M	依赖于M

字符串类型

类型名称	说明	存储需求
CHAR	固定长度非二进制字符串	取决于字符集类型和指定的字符数，最多 255 个字符。
VARCHAR	变长非二进制字符串	取决于字符集类型和指定的字符数，最多 65532 个字节。
TINYTEXT	非常小的非二进制字符串	2^8 字节；
TEXT	小的非二进制字符串	2^16 字节，约 64 KB
MEDIUMTEXT	中等大小的非二进制字符串	2^24 字节，约 16 MB
LONGTEXT	大的非二进制字符串	2^32 字节，约 4 GB
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

时间/日期类型

类型名称	日期格式	日期范围	存储需求
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	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

二进制类型

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

（橙色标记为常用类型）

数值类型

• 整形类型都支持声明：Type [(M)] [UNSIGNED] [ZEROFILL] ；其中 M 指输出宽度，当数据长度大于宽度时，原样输出。当 M 和 ZEROFILL 同时指定时，才能看见 M 的效果。
  浮点类型都支持声明：Type [(M,D)] [UNSIGNED] [ZEROFILL] ；其中 M 指小数总位数，D 指小数点后位数。如果M和D被省略，根据硬件允许的限制来保存值。
  DECIMAL(定点)类型：DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] ，M 最大为 65，D 最大为 30，且 D 不得超过 M；如果D被省略， 默认是0。如果M被省略， 默认是10。DECIMAL 是定点数，不会像浮点数那样存在误差问题，广泛用于金融相关的计算中。关于 DECIMAL 定点数的讨论，参见笔者另一篇文章定点数与浮点数

  ###################################################整形测试
  mysql> CREATE TABLE demo_1(num INT(5) ZEROFILL );
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> INSERT INTO demo_1 VALUES(34);
  Query OK, 1 row affected (0.01 sec)
  
  mysql> SELECT * FROM demo_1;
  +-------+
  | num   |
  +-------+
  | 00034 |
  +-------+
  1 row in set (0.00 sec)
  
  mysql> INSERT INTO demo_1 VALUES(1234567);
  Query OK, 1 row affected (0.01 sec)
  
  mysql> SELECT * FROM demo_1;
  +---------+
  | num     |
  +---------+
  |   00034 |       # 输出宽度为5
  | 1234567 |       # 超过M则原样输出
  +---------+
  2 rows in set (0.00 sec)
  #####################################################浮点测试
  mysql> CREATE TABLE demo_3 (dig DOUBLE(7,3) );
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> INSERT INTO demo_3 VALUES(12345.6789);
  ERROR 1264 (22003): Out of range value for column 'dig' at row 1 #不能超过(7,3)范围
  
  mysql> INSERT INTO demo_3 VALUES(1235.679);
  Query OK, 1 row affected (0.01 sec)

• FLOAT 只能精确到小数点后大约 7 位；DOUBLE 可用精确到小数点后大约 15 位。

  mysql> CREATE TABLE test_08(
      -> dig FLOAT);
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO test_08 VALUES(0.123456789);#输入9位小数
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM test_08;
  +----------+
  | dig      |
  +----------+
  | 0.123457 | #输出6位小数
  +----------+
  1 row in set (0.00 sec)
  
  mysql> CREATE TABLE test_09(
      -> dig DOUBLE);
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO test_09 VALUES(0.123456789123456789);#输入18位小数
  Query OK, 1 row affected (0.00 sec)
  mysql> SELECT * FROM test_09;
  +---------------------+
  | dig                 |
  +---------------------+
  | 0.12345678912345678 | #输出17位小数
  +---------------------+
  1 row in set (0.00 sec)

• MySQL 使用带符号的 BIGINT 或 DOUBLE 值进行所有运算 ，因此除了位函数，不应使用大于 63位 的无符号的大整数！如果这样做，结果中的最后几位可能出错，这是由于将 BIGINT 值转换为 DOUBLE 进行四舍五入时造成的错误（BIGINT 与 DOUBLE 混合运算时）。
  当两个操作数均为整数值时，-、+和* 操作符使用 BIGINT 运算 。这说明如果乘两个大整数，当结果大于 9223372036854775807(63位) 时，会得到意想不到的结果。

字符串类型

• CHAR 和 VARCHAR 类型声明的长度表示你想要保存的 最大字符数 ，而非字节数。需要注意，CHAR 的字符最大储存为 255 个；VARCHAR 的最大储存数量为 (65535-3)/3=21844 (utf8下) ，除以 3 可能是因为 Mysql 为 utf8 字符集设置的平均字节大小为 3（说“平均”是因为utf8是变长字符集，字符所占字节数从1-5都有）；VARCHAR 的最大有效长度由最大行大小和使用的字符集确定，整体最大长度是 65,532 字节，减 3 是因为 VARCHAR 是变长的，也就是说其按照实际占用空间来分配(但不可超过你指定的长度) ，所以需要 3 个字节来记录长度；相反，CHAR 是固定长度的，当保存CHAR值时，在它们的右边填充空格以达到指定的长度 。另外，MySQL中的所有CHAR 和 VARCHAR 值比较时不需要考虑任何尾部空格；保存 VARCHAR 时，尾部的空格会保留。 如果分配给 CHAR 或 VARCHAR 列的值超过列的最大长度，则对值进行裁剪以使其适合，如果被裁掉的字符是空格，则会产生一条警告。如果裁剪非空格字符，则报错。

  mysql> CREATE TABLE demo4(data VARCHAR(21845));
  ERROR 1118 (42000): Row size too large. 
  mysql> CREATE TABLE demo4(data VARCHAR(21844));#utf8下，VARCHAR最大字符数为21844
  Query OK, 0 rows affected (0.02 sec)           #gbk下，VARCHAR最大字符数为(65535-3)/2
  ###################################################
  mysql> CREATE TABLE demo5(data CHAR(5));
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO demo5 VALUES("year"); #尾部填充一个空格
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM demo5 WHERE data="year";
  +------+
  | data |
  +------+
  | year |     #仍然可查询到year，说明CHAR检索时忽略了尾部的空格
  +------+
  1 row in set (0.00 sec)
  ###################################################
  mysql> CREATE TABLE demo7(data VARCHAR(8));
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO demo7 VALUES("DAY       ");
  Query OK, 1 row affected, 1 warning (0.01 sec)
  mysql> SELECT * FROM demo7 WHERE data="DAY";
  +----------+
  | data     |
  +----------+
  | DAY      | #仍然可查询到DAY，说明VARCHAR检索时忽略了尾部的空格
  +----------+ #DAY后有一串空白，说明保存时保留了尾部的空格。
  1 row in set (0.00 sec)
  ###################################################
  mysql> INSERT INTO demo7 VALUES("DAY                ");
  Query OK, 1 row affected, 1 warning (0.01 sec) #超出长度的空格被裁剪，发出警告
  mysql> INSERT INTO demo7 VALUES("DAYDAYHAPPY ");
  ERROR 1406 (22001): Data too long for column 'data' at row 1 #超出长度的非空格被裁减，报错

• 当数据是定长时，比如手机号，身份证等，推荐使用 CHAR；当数据不定长时，比如留言，文章等，推荐使用 VARCHAR。CHAR 的查询速度比 VARCHAR 快 。

• 与 CHAR 和 VARCHAR 不同，使用 TEXT 类型时无需指定存储长度。此外，当检索或插入文本数据时，MySQL 不会删除或填充空格。 请注意，TEXT 数据不存储在数据库服务器的内存中，因此，每当查询 TEXT 数据时，MySQL都必须从磁盘读取它，这与 CHAR 和 VARCHAR 相比要慢得多。 由于 TEXT 也是不定长类型，所以也需要记录长度：TINYTEXT 需要 1 个字节记录，TEXT 需要 2 个字节，MEDIUMTEXT 需要 3 个字节，LONGTEXT 则需要四个字节。

二进制类型

• BIT 数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1到64 。

• 与CHAR和VARCHAR不同，BINARY和VARBINARY的长度是字节长度而不是字符长度。

• BIT 以二进制方式输出，如下图：

  

  注意，在命令行中输出不可见：

  mysql> CREATE TABLE demo_4 (dig BIT(8) );
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> INSERT INTO demo_4 VALUES(129);
  Query OK, 1 row affected (0.01 sec)
  
  mysql> SELECT * FROM demo_4;
  +------+
  | dig  |
  +------+
  |      |
  +------+
  1 row in set (0.00 sec)

• BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含的是 字节数组 (类似于 Qt 中的 QByteArray) 。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，也不包含校对规则，并且排序和比较基于字节而非字符。

  mysql> CREATE TABLE demo_5 (data BINARY);
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO demo_6 VALUES("中国");
  Query OK, 1 row affected (0.00 sec)
  mysql> SELECT * FROM demo_6;
  +------------+
  | data       |
  +------------+
  | 中国        |
  +------------+
  2 rows in set (0.00 sec)

  额…翻车了…因为字节数组没有字符集，所以也不应该输出“中国”呀…暂且认为是 Mysql 太贴心了吧…在 Qt 中，输出中文的字节数组，只会显示字节数据：

  

  当保存 BINARY 值时，在它们右边填充值以达到指定长度 。填充值是 0x00(\0)。插入值时在右侧添加 0x00 ，并且选择时不删除尾部的字节。

  mysql> CREATE TABLE demo_6 (data BINARY(10)); #大小10个字节
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO demo_6 VALUES("hello"); #前五个字节为hello，后五个字节为0x00
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM demo_6 WHERE data = "hello";#只有五个字节，hello
  Empty set (0.00 sec)   #匹配不到是因为匹配时后面的0x00也要参与
  mysql> SELECT * FROM demo_6 WHERE data="hello\0\0\0\0\0";
  +------------+
  | data       |
  +------------+
  | hello      |
  +------------+
  1 row in set (0.00 sec)

  当保存 VARBINARY ,插入时不填充字符，选择时不裁剪字节。

  mysql> CREATE TABLE demo_7 (data VARBINARY(10));
  Query OK, 0 rows affected (0.02 sec)
  mysql> INSERT INTO demo_7 VALUES("中国");
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM demo_7 WHERE data="中国";
  +--------+
  | data   |
  +--------+
  | 中国   |
  +--------+
  1 row in set (0.00 sec)

• 在大多数方面，可以将 BLOB 列视为能够足够大的 VARBINARY，BLOB 列没有字符集，并且排序和比较基于列值字节的数值值。

时间/日期类型

• 试图插入一个不合法的日期，MySQL将给出警告或错误：

  mysql> CREATE TABLE test_0(ddl DATE);
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO test_0 VALUE("2022-11-31");
  ERROR 1292 (22007): Incorrect date value: '2022-11-31' for column 'ddl' at row 1 #11月没有31号

  可以使用 ALLOW_INVALID_DATES SQL 模式让 MySQL 接受某些日期，比如 2002-00-00，这在需要保存一个你不知道确切日期的生日时非常有用 。如果你不想在日期中出现零，可以使用 NO_ZERO_IN_DATE SQL模式。在严格模式，非法日期不被接受。

• 存储年份时，可以只输入后两位，比如 2022 -> 22 ； 但两位年值的日期会令人模糊，因为世纪不知道。MySQL使用以下规则解释两位年值：

  • 70-99 范围的年值转换为 1970-1999

  • 00-69 范围的年值转换为 2000-2069

  mysql> INSERT INTO test_0 VALUE("22-10-30");
  Query OK, 1 row affected (0.01 sec)
  mysql> INSERT INTO test_0 VALUE("70-10-30");
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM test_0;
  +------------+
  | ddl        |
  +------------+
  | 2022-10-30 |
  | 1970-10-30 |
  +------------+
  4 rows in set (0.00 sec)

• MySQL 以标准输出格式 YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 检索给定日期或时间类型的值，但它尽力解释你指定的各种输入值格式：

  • 允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，98-12-31 11:30:45 、98.12.31 11+30+45 、98/12/31 11*30*45 和 98@12@31 11^30^45 是等价的。
  • YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，19970523091528 和 970523091528 被解释为 1997-05-23 09:15:28 ，但 971122129015 是不合法的(它有一个没有意义的分钟部分)，将报错或变为 0000-00-00 00:00:00 。强烈建议加入分隔符！
  • 对于包括日期部分间割符的字符串值，如果日和月的值小于 10，不需要指定两位数。1979-6-9 与 1979-06-09 是相同的。同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值小于 10，不需要指定两位数。1979-10-30 1:2:3 与 1979-10-30 01:02:03 相同。

• 在一定程度上，可以将一个日期类型的值分配给一个不同的日期类型。但是，值可能会更改或丢失一些信息：

  • 如果你为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 00:00:00 ，因为 DATE 值未包含时间信息。
  • 如果你为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因为 DATE 值未包含时间信息。
  • TIMESTAMP 值不能早于 1970 或晚于 2037 。这说明一个日期，例如 1968-01-01 ，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP 值却无效，如果分配给这样一个对象将被转换为 0。

  #####为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值
  mysql> CREATE TABLE test_0(ddl DATE);
  Query OK, 0 rows affected (0.01 sec)
  mysql> INSERT INTO test_0 VALUE("2021-12-29 11:30:49");
  mysql> SELECT * FROM test_0;
  +------------+
  | ddl        |
  +------------+
  | 2021-12-29 |
  +------------+
  #####为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值
  mysql> CREATE TABLE test_1 (ddl DATETIME);
  Query OK, 0 rows affected (0.02 sec)
  mysql> INSERT INTO test_1 VALUES("2022-12-29");
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM test_1;
  +---------------------+
  | ddl                 |
  +---------------------+
  | 2022-12-29 00:00:00 |
  +---------------------+
  #####
  mysql> CREATE TABLE test_2(data TIMESTAMP);
  Query OK, 0 rows affected (0.02 sec)
  mysql> INSERT INTO test_2 VALUES("2022-12-29 12:43:49");
  Query OK, 1 row affected (0.00 sec)     
  mysql> SELECT * FROM test_2;  #1970~2037之间的日期可赋值给时间戳
  +---------------------+
  | data                |
  +---------------------+
  | 2022-12-29 12:43:49 |  
  +---------------------+
  1 row in set (0.00 sec)
  mysql> INSERT INTO test_2 VALUES("2045-12-29 12:43:49");#1970~2037之外的日期不可赋值给时间戳
  ERROR 1292 (22007): Incorrect datetime value: '2045-12-29 12:43:49' for column 'data' at row 1

• 时间戳取值范围包含约 22 亿个数值，因此在 MySQL 内部使用 4 个字节 INT 类型来存放时间戳数据：

  1、在存储时间戳数据时，先将本地时区时间转换为 UTC 时区时间，再将 UTC 时区时间转换为 INT 格式的毫秒值(使用UNIX_TIMESTAMP函数)，然后存放到数据库中。
  2、在读取时间戳数据时，先将INT格式的毫秒值转换为 UTC 时区时间(使用 FROM_UNIXTIME 函数)，然后再转换为本地时区时间，最后返回给客户端。

• 时间戳自动将当前时间填入数据库(可用于登入时间的记录)：

  mysql> CREATE TABLE test_5(ddl DATE, log_time TIMESTAMP
      -> NOT NULL DEFAULT CURRENT_TIMESTAMP
      -> ON UPDATE CURRENT_TIMESTAMP);  #2，3行代码设置自动记录写入时间。
  Query OK, 0 rows affected (0.02 sec)
  mysql> INSERT INTO test_5(DDL) VALUES("2023-11-11");#只手动写入ddl
  Query OK, 1 row affected (0.01 sec)
  mysql> SELECT * FROM test_5;
  +------------+---------------------+
  | ddl        | log_time            |
  +------------+---------------------+
  | 2023-11-11 | 2022-09-01 17:35:45 | #写入时间自动被记录
  +------------+---------------------+
  1 row in set (0.00 sec)

其他

ZEROFILL属性

ZEROFILL 属性只用于整形。在插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补 0 ；如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性 。ZEROFILL 仅仅只是显示属性，与内存无关。

mysql> CREATE TABLE test_5(
    id INT ZEROFILL);
Query OK, 0 rows affected (7.03 sec)

mysql> INSERT INTO test_5 VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_5 ;
+------------+
| id         |
+------------+
| 0000000001 |
+------------+
1 row in set (0.00 sec)

严格/非严格模式

当要在一个数值列内保存一个超出该列允许范围的值时，MySQL 的操作取决于此时有效的 SQL 模式。如果模式未设置，MySQL 将值裁剪到范围的相应端点，并保存裁减好的值。但是，如果模式设置为 traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据 SQL 标准插入会失败。如果 INT 列是 UNSIGNED，列范围的大小相同，但其端点会变为到 0 和 4294967295。若此时你试图保存 -9999999999 和 9999999999，以非严格模式保存到列中的值是 0 和 4294967296。如果在浮点或定点列中分配的值超过指定(或默认)精度和标度规定的范围，MySQL以非严格模式保存表示范围相应端点的值。

当MySQL没有工作在严格模式时，对于 ALTER TABLE、LOAD DATA INFILE、UPDATE 和多行 INSERT 语句，由于裁剪发生的转换将报告为警告。当 MySQL 工作在严格模式时，这些语句将失败，并且部分或全部值不会插入或更改，取决于是否表为事务表和其它因素。最近的 Mysql 都默认采用严格模式。

参考：Mysql5.1手册(点此下载)，TEXT简介，韩顺平Mysql，C语言中文网，