整数型

整数类型 字节数 无符号数的取值范围 有符号数的取值范围
TINYINT 1 0 ~ 255 -128 ~ 127
SMALLINT 2 0 ~ 65535 -32768 ~ 32767
MEDIUMINT 3 0 ~ 16777215 -8388608 ~ 8388607
INT 4 0 ~ 4294967295 -2147483648 ~ 2147483647
INTEGER 4 0 ~ 4294967295 -2147483648 ~ 2147483647
BIGINT 8 0 ~ 18446744073709551615 -9223372036854775808 ~ 9223372036854775807
  1. TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
  2. SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
  3. MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
  4. INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
  5. BIGINT ：只有处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

浮点类型和定点型

类型 字节数 负数的取值范围 非负数的取值范围
FLOAT 4 -3.402823466E+38 ~ -1.175494351E-38 0 和 1.175494351E-38 ~ 3.402823466E+38
DOUBLE 8 -1.7976931348623157E+308 ~ -2.2250738585072014E-308 0 和 2.2250738585072014E-308 ~ 1.7976931348623157E+308
DECIMAL(M, D) 或 DEC(M, D) M+2 同 DOUBLE 型 同 DOUBLE 型

  1. float 数值类型用于表示单精度浮点数值，而 double 数值类型用于表示双精度浮点数值，decimal 是定点型；

  2. 浮点型和定点型可以用类型名称后加（M，D）来表示，M 表示该值的总共长度，D 表示小数点后面的长度，M 和 D 又称为精度和标度

  • FLOAT 与 DOUBLE：(M, D)中 M = 整数位+小数位，D = 小数位。 D <= M <= 255，0 <= D <= 30。在不指定(M, D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。如 float(7,4)的可显示为-999.9999，MySQL 保存值时进行四舍五入，如果插入 999.00009，则结果为 999.0001。

  • DECIMAL：M 是最大位数（精度），范围是 1 到 65，可不指定，默认值是 10。D 是小数点右边的位数（小数位），范围是 0 到 30，并且不能大于 M，可不指定，默认值是 0。

当设定的 float 或 double 的标度超过原来时，因为 float 或 double 本身所占用空间有限，所以超过本能存储位数时会以随机数字填充。

日期和时间类型

类型 字节数 取值范围 零值
YEAR 1 1901 ~ 2155 0000
DATE 4 1000-01-01 ~ 9999-12-31 0000-00-00
TIME 3 -838:59:59 ~ 838:59:59 00:00:00
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 1970-01-01 08:00:01 ~ 2038-01-19 11:14:07 00000000000000

常用的时间函数：

  1. CURDATE() ：获得当前的 DATE, 可直接插入 DATE 类型中；
  2. NOW() ：获得当前的 DATETIME, 可直接插入 DATETIME 和 TIMESTAMP 类型中；
  3. TIME() ：获取参数给定时间串中的时分秒，可直接插入 TIME 类型中；
  4. YEAR() ：获取参数给定时间串中的年份，可直接插入 YEAR 类型中；
  5. MONTH() 、DAY()、HOUR()、MINUTE()、SECOND() 获取参数给定时间串中的月、日、时、分、秒值；

datetime 和 timestamp 的区别

  1. timestamp 如果不给这个字段赋值或赋值为 null，则默认使用当前的系统时间来自动赋值（自动存储记录修改时间）
  2. timestamp 存储的时间范围比 datetime 要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间

字符串类型

CHAR 与 VARCHAR

类型 字节数 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
  1. 如果 CHAR 数据的实际长度比 CHAR 类型声明的长度小，则会在右侧填充空格以达到指定的长度。定义 CHAR 类型字段时，声明的字段长度即为 CHAR 类型字段所占的存储空间的字节数。当 MySQL 检索 CHAR 类型的数据时，CHAR 类型的字段会去除尾部的空格
  2. VARCHAR 类型的字段所占用的存储空间为字符串实际长度加 1 个字节。检索 VARCHAR 类型的字段数据时，会保留数据尾部的空格

TEXT

类型 允许的长度
TINYTEXT 0 ~ 255 字节
TEXT 0 ~ 65535 字节
MEDIUMTEXT 0 ~ 16777215 字节
LONGTEXT 0 ~ 4294967295 字节

在向 TEXT 类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。

允许的长度是指实际存储的字节数，而不是实际的字符个数，比如一个中文字符占两个字节， 那么 TEXT 类型可存储 65535/2 = 32767 个中文字符，而 varchar(100)可存储 100 个中文字符，实际占 200 个字节，但 varchar(65535) 并不能存储 65535 个中文字符，因为已超出范围（varchar 要预留 1 到 2 个字节存储这个字符的长度 65535×2 = 131072，最大只能到 65533）。

如果列声明的长度超过 255，则使用两个字节来存储长度，否则 1 个字节，当不允许非空字段的时候（因为要用一个字节来存储不可为空的标识），当允许非空字段的时候只能到 65532（省下了存储非空的那个字节）。

二进制类型

  1. 二进制类型存储原始的二进制数据（如图片，视频，exe 文件等）。文本类型（TEXT）用来存储字符字符串（如由英文字符、中文字符或其它语言字符组成的字符串）。
  2. 二进制类型没有字符集，并且排序和比较基于列值字节的数值。而 TEXT 类型有字符集，并且根据字符集的校对规则对值进行排序和比较。

BINARY 与 VARBINARY

类型 取值范围
BINARY(M) 字节数为 M，允许长度为 0~M 的定长二进制字符串
VARBINARY(M) 允许长度为 0~M 的变长二进制字符串，字节数为值的长度加 1

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，只是它们存储的是二进制字符串，M 参数指定该二进制数的最大字节长度为 M

  1. BINARY 类型的长度是固定的，在创建表时就指定了，不足最大长度时的空间后边由″\0″补全。
  2. VARBINARY 类型的长度是可变的，在创建表时指定了最大的长度，其长度可以在 0 到最大长度之间，在这个最大值范围内使用多少就分配多少。

注意：当使用 where 检索时，因为 binary 会自动使用 ‘\0’填充字符，此时就需要加上 ‘\0’，对于 varbinary 则不会出现这种情况

BIT

类型 取值范围
BIT(M) M 位二进制数据，M 最大值为 64
  1. 插入数据时，使用 b’位串’ 的方式插入相应值
  2. 查询时，可以使用 bin() 、oct() 、hex() 函数将字段的值转成相应的二进制、八进制和十六进制。
1
2
3
4
5
6
7
create table bit_example(
    b bit(8)
);


insert into bit_example values (b'10100001');
insert into bit_example values (10),('a');

BLOB

类型 取值范围
TINYBLOB 可变长二进制数据，最多 255 个字节
BLOB 可变长二进制数据，最多 (2^16 - 1) 个字节（即 65535 字节）
MEDIUMBLOB 可变长二进制数据，最多 (2^24 - 1) 个字节（即 16777215 字节）
LONGBLOB 可变长二进制数据，最多 (2^32 - 1) 个字节（即 4294967295 字节）

BLOB 是一个二进制大对象 ，可以容纳可变数量的数据。注意的是，在实际工作中往往不会在 MySQL 中使用 BLOB 类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到 MySQL 中。

枚举类型

ENUM 类型又称为枚举类型。在创建表时，ENUM 类型的取值范围以列表的形式指定，其基本形式如下：

1
属性名 ENUM('值 1', '值 2', ..., '值 n')

其中，“属性名”参数指字段的名称，“值 n”参数表示列表中的第 n 个值。ENUM 类型的值只能取列表中的一个元素，其取值列表中最多能有 65535（2 个字节）个值。如果数据值列表在 255 个以内，那么一个字节就够，如果超过 255 但是小于 65535，那么系统采用两个字节保存。

列表中的每个值独有一个顺序排列的编号，MySQL 中存入的是这个编号，而不是列表中的值，默认编号从 1 开始

如果 ENUM 类型加上了 NOT NULL 属性，其默认值为取值列表的第一个元素。如果不加 NOT NULL 属性，ENUM 类型将允许插入 NULL，而且 NULL 为默认值。

1
2
3
4
5
6
7
8
CREATE TABLE `test_gender` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gender` enum('M','F') DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;


insert INTO test_gender (gender) VALUES ('M'),('1'),('2'),(1),('f')

集合类型

在创建表时，SET 类型的取值范围就以列表的形式指定了，其基本形式如下：

1
属性名 SET('值 1', '值 2', ..., '值 n')

其中，属性名参数指字段的名称，“值 n”参数表示列表中的第 n 个值，这些值末尾的空格将会被系统直接删除，其基本形式与 ENUM 类型一样。SET 类型的值可以取列表中的一个元素或者多个元素的组合，取多个元素时，不同元素之间用逗号隔开。SET 类型的值最多只能是由 64（8 个字节）个元素构成的组合（SET 会将选择的置 1，没有选择的置 0；最多 64 个元素正好可以用 8 个字节（64 位）存储所选择的）

1
2
3
4
5
CREATE TABLE myset (
    col SET('a', 'b', 'c', 'd')
);

INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d'), (7);