数据类型简介
- 数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段插入的数据内容。
- 不同的数据类型也决定了 MySQL 在存储它们的时候使用的方式，以及在使用它们的时候选择什么运算符号进行运算。
- 数值数据类型：TINYINT 、SMALLINT 、MEDIUMINT 、INT 、BIGINT 、FLOAT 、DOUBLE 、DECIMAL。
- 日期/时间类型：YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP。
- 字符串类型：CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM 、SET。
数值类型
- 数值类型主要用来存储数字，不同的数值类型提供不同的取值范围，可以存储的值范围越大，所需要的存储空间也越大。
- 数值类型分为：①整数类型 ②浮点数类型 ③定点数类型。
整数类型
类型名称 |
说明 |
存储需求 |
有符号的取值范围 |
无符号的取值范围 |
TINYINT |
很小的整数 |
1个字节 |
-128~127 |
0~255 |
小的整数 |
2个字节 |
-32768~32767 |
0 ~ 65535 |
|
MEDIUMINT |
中等大小的整数 |
3个字节 |
-8388608 ~ 8388607 |
0 ~ 16777215 |
INT |
普通大小的整数 |
4个字节 |
-2147483648 ~ 2147483647 |
0 ~ 4294967295 |
BIGINT |
大整数 |
8个字节 |
-9223372036854775808 ~9223372036854775807 |
0 ~18446744073709551615 |
浮点数类型和定点数类型
- MySQL 中使用浮点数和定点数来表示小数，浮点数有两种类型：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点数只有 DECIMAL。
- 浮点数和定点数都可以用 (M，N) 来表示，其中 M 是精度，表示总共的位数，N 是标度，表示小数的位数。
- DECIMAL 实际是以字符串形式存放的，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL类型会比较好。
- 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围，它的缺点是会引起精度问题。
类型名称 |
说明 存储需求 |
有符号的取值范围 |
无符号的取值范围 |
FLOAT |
单精度浮点数 |
4个字节 |
-3.402823466EE+38 ~ -1.175494251E-38 |
DOUBLE |
双精度浮点数 |
8个字节 |
-1.7976931348623157E+308 ~-2.2250738585072014E-308 |
DECIMAL |
压缩的“严格”定点数 |
M+2个字节 不固定 |
不固定 |
日期/时间类型
- MySQL 有多种表示日期的数据类型，比如，当只记录年信息的时候，可以使用 YEAR 类型，而没有必要使用 DATE 类型。
- 每一个类型都有合法的取值范围，当指定确实不合法的值时系统将 "零" 值插入到数据库中。
类型名称 |
日期格式 |
日期范围 |
存储需求 |
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-31 |
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:07UTC |
4字节 |
YEAR
- 格式：以4位字符串格式表示的 YEAR ，范围为 '1901' ~ '2155'。
- 格式：以4位数字格式表示的 YEAR ，范围为 1901 ~ 2155。
- 格式：以2位字符串格式表示的 YEAR ，范围为 '00' ~ '99' ，其中，'00' ~ '69' 被转换为 2000 ~ 2069，'70' ~ '99' 被转换为 1970 ~ 1999。
- 格式：以2位数字格式表示的 YEAR ，范围为 1 ~ 99 ，其中，1 ~ 69 被转换为 2001 ~ 2069 ，70 ~ 99 被转换为 1970 ~ 1999。
TIME
- TIME 类型的格式为 HH:MM:SS ，HH 表示小时，MM 表示分钟，SS 表示秒
- 格式：以 'HHMMSS' 格式表示的 TIME ，例如 '101112' 被理解为 10:11:12 ，但如果插入不合法的时间，如 '109712' ，则被存储为 00:00:00
- 格式：以 'D HH:MM:SS' 字符串格式表示的 TIME ，其中 D 表示日，可以取 0 ~ 34 之间的值，在插入数据库的时候 D 会被转换成小时，如 '2 10:10' 在数据库中表示为 58:10:00 ，即 2x24+10 = 58。
DATE
- DATE 类型的格式为 YYYY-MM-DD ，其中，YYYY 表示年，MM 表示月，DD 表示日。
- 格式：'YYYY-MM-DD' 或 'YYYYMMDD' ，取值范围为 '1000-01-01' ~ '9999-12-3'。
- 格式：'YY-MM-DD' 或 'YYMMDD' ，这里 YY 表示两位的年值，范围为 '00' ~ '99' ，其中，'00' ~ '69' 被转换为 2000 ~ 2069 ，'70' ~ '99' 被转换为 1970 ~ 1999。
- 格式：YY-MM-DD 或 YYMMDD ，数字格式表示的日期，其中 YY 范围为 00 ~ 99 ，其中，00 ~ 69 被转换为 2000 ~ 2069 ，70 ~ 99 被转换为 1970 ~ 1999。
DATETIME
- DATETIME 类型的格式为 YYYY-MM-DD HH:MM:SS ，其中，YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。
- 格式：'YYYY-MM-DD HH:MM:SS' 或 'YYYYMMDDHHMMSS' ，字符串格式，取值范围为 '1000-01-0100:00:00' ~ '9999-12-31 23:59:59'。
- 格式：'YY-MM-DD HH:MM:SS' 或 'YYMMDDHHMMSS' ，字符串格式，其中 YY 范围为 '00' ~ '99' ，其中，'00' ~ '69' 被转换为 2000 ~ 2069 ，'70' ~ '99' 被转换为 1970 ~ 1999。
- 格式：YYYYMMDDHHMMSS 或 YYMMDDHHMMSS ，数字格式，取值范围同上。
TIMESTAMP
- TIMESTAMP 类型的格式为 YYYY-MM-DD HH:MM:SS，显示宽度固定在19个字符。
- TIMESTAMP 与 DATETIME 的区别在于，TIMESTAMP 的取值范围小于 DATETIME 的取值范围。
- TIMESTAMP 的取值范围为 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC ，其中 UTC 是世界标准时间，存储时会对当前时区进行转换，检索时再转换回当前时区。
字符串类型
- 字符串类型用来存储字符串数据，还可以存储比如图片和声音的二进制数据。
- MySQL 支持两种字符串类型：文本字符串和二进制字符串。
文本字符串类型 |
说明 |
存储需求 |
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个成员） |
二进制字符串类型 |
说明 |
存储需求 |
BIT(M) |
位字段类型 |
大约（M+7）/8 个字节 |
BINARY(M) |
固定长度的二进制字符串 |
M个字节 |
VARBINARY(M) |
可变长度的二进制字符串 |
M+1个字节 |
TINYBLOG(M) |
非常小的BLOB |
L+1 字节，在此 L < 2^8 |
BLOB(M) |
小的BLOB |
L+2 字节，在此 L < 2^16 |
MEIDUMBLOB(M) |
中等大小的BLOB |
L+3 字节，在此 L < 2^24 |
LONGBLOB(M) |
非常大的BLOB |
L+4 字节，在此 L < 2^32 |
CHAR 和 VARCHAR
- CHAR(M) 为固定长度的字符串，在定义时指定字符串列长，当保存时在右侧填充空格以达到指定的长度，M 表示列长度，取值范围是 0~255 个字符，例如，CHAR(4) 定义了一个固定长度的字符串列，其包含的字符个数最大为 4，当检索到 CHAR 值时，尾部的空格将被删掉。
- VARCHAR(M) 为可变长度的字符串，M 表示最大列长度，取值范围是 0~65535 ，VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加一（一个字符串结束符） 插入值 CHAR(4) 存储需求 VARCHAR(4) 存储需求 '' ' ' 4个字节 ' 1个字节 'ab' 'ab ' 4个字节'ab' 3个字节 'abc' 'abc' 4个字节 'abc' 4个字节 'abcd' 'abcd' 4个字节 'abcd' 5个字节 'abcde' 'abcd' 4个字节 'abcd' 5个字节。
TEXT
- TINYTEXT 最大长度为 255 个字符。
- TEXT 最大长度为 65536 个字符。
- MEDIUMTEXT 最大长度为 16777215 个字符。
- LONGTEXT 最大长度为 4294967295 个字符。
ENUM
在基本的数据类型中，无外乎就是些数字和字符，但是某些事物是较难用数字和字符来准确地表示的。比如一周有七天，分别是Sunday、Monday、Tuesday、Wednesday、Thursday、Friday 和 Saturday。
如果我们用整数 0、1、2、3、4、5、6 来表示这七天，那么多下来的那些整数该怎么办？
而且这样的设置很容易让数据出错，即取值超出范围。我们能否自创一个数据类型，而数据的取值范围就是这七天呢？
因此有了 ENUM 类型（Enumeration，枚举），它允许用户自己来定义一种数据类型，并且列出该数据类型的取值范围。ENUM 是一个字符串对象，其值为表创建时在列规定中枚举（即列举）的一列值，语法格式为：字段名 ENUM （'值1'，'值2'， ..... '值n'） 字段名指将要定义的字段，值 n 指枚举列表中的第 n 个值，ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动删除。ENUM 值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。
例：创建表n1，level为enum类型分别指定'优秀','合格','不合格'，第一次插入数据时都在取值范围内，而第二次插入取值范围外的数据显示报错，查看表只有三行数据。
1. mysql> create table n1( 2. -> soc int, 3. -> level enum('优秀','合格','不合格')); 4. Query OK, 0 rows affected (0.02 sec) 5. 6. mysql> insert into n1 values(90,'优秀'),(60,'合格'),(50,'不合格'); 7. Query OK, 3 rows affected (0.00 sec) 8. Records: 3 Duplicates: 0 Warnings: 0 9. 10. mysql> insert into n1 values(100,'满分'); 11. ERROR 1265 (01000): Data truncated for column 'level' at row 1 12. mysql> select * from n1; 13. +------+-----------+ 14. | soc | level | 15. +------+-----------+ 16. | 90 | 优秀 | 17. | 60 | 合格 | 18. | 50 | 不合格 | 19. +------+-----------+ 20. 3 rows in set (0.00 sec)
SET
- SET 是一个字符串对象，可以有零个或多个值，SET 列最多可以有 64 个成员，其值为表创建时规定的一列值，语法：SET（'值1'，'值2'，...... '值n'）。
- 与 ENUM 类型相同，SET 值在内部用整数表示，列表中每一个值都有一个索引编号
- 与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。
- 如果插入 SET 字段中列值有重复，则 MySQL 自动删除重复的值，插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示。
例：创建n2表，set集合内指定'a','b','c','d'，第一次插入数据是没报错，但是下边查看时可以看到默认把重复的去除了，而第二次插入数据时，‘x’非集合内数据，所以报错。
1. mysql> create table n2( 2. -> z set('a','b','c','d')); 3. Query OK, 0 rows affected (0.01 sec) 4. 5. mysql> insert into n2 values('a'),('a,b,c,d'),('a,a,b,c,c,d'); 6. Query OK, 3 rows affected (0.01 sec) 7. Records: 3 Duplicates: 0 Warnings: 0 8. 9. mysql> insert into n2 values('a,b,x,a'); 10. ERROR 1265 (01000): Data truncated for column 'z' at row 1 11. mysql> select * from n2; 12. +---------+ 13. | z | 14. +---------+ 15. | a | 16. | a,b,c,d | 17. | a,b,c,d | 18. +---------+ 19. 3 rows in set (0.00 sec)
BIT
- BIT 数据类型用来保存位字段值，即以二进制的形式来保存数据，如保存数据 13，则实际保存的是 13 的二进制值，即 1101。
- BIT 是位字段类型，BIT(M) 中的 M 表示每个值的位数，范围为 1~64 ，如果 M 被省略，则默认为 1 ，如果为 BIT(M) 列分配的值的长度小于 M 位，则在值得左边用 0 填充。
- 如果需要位数至少为 4 位的 BIT 类型，即可定义为 BIT(4) ，则大于 1111 的数据是不能被插入的。
1. mysql> create table n3(b bit(4)); 2. Query OK, 0 rows affected (0.01 sec) 3. 4. mysql> insert into n3 values(2),(9),(15); 5. Query OK, 3 rows affected (0.00 sec) 6. Records: 3 Duplicates: 0 Warnings: 0 7. 8. mysql> select bin(b+0) from n3; 9. +----------+ 10. | bin(b+0) | 11. +----------+ 12. | 10 | 13. | 1001 | 14. | 1111 | 15. +----------+ 16. 3 rows in set (0.01 sec)
BINARY 和 VARBINARY
- BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。
- BINARY 类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充 '\0' 以补齐指定长度。
- VARBINARY 类型的长度是可变的，指定长度之后，其长度可以在 0 到最大值之间。
1. mysql> create table n4( 2. -> b binary(3), 3. -> vb varbinary(30)); 4. Query OK, 0 rows affected (0.01 sec) 5. 6. mysql> insert into n4 values(5,5); 7. Query OK, 1 row affected (0.01 sec) 8. 9. mysql> select * from n4; 10. +------+------+ 11. | b | vb | 12. +------+------+ 13. | 5 | 5 | 14. +------+------+ 15. 1 row in set (0.00 sec) 16. 17. mysql> select length(b),length(vb) from n4; 18. +-----------+------------+ 19. | length(b) | length(vb) | 20. +-----------+------------+ 21. | 3 | 1 | 22. +-----------+------------+ 23. 1 row in set (0.00 sec)
BLOB
- BLOB 用来存储可变数量的二进制字符串，分为 TINYBLOB 、BLOB 、MEDIUMBLOB 、LONGBLOB 四种类型。
- BLOB 存储的是二进制字符串，TEXT 存储的是文本字符串。
- BLOB 没有字符集，并且排序和比较基于列值字节的数值；TEXT 有一个字符集，并且根据字符集对值进行排序和比较 数据类型 |存储范围| ---|---|---| TINYBLOB |最大长度为255 BLOB |最大长度为65535MEDIUMBLOB| 最大长度为16777215 LONGBLOB |最大长度为4294967295。
