MySQL数据类型

igxiaoshan Lv5
  2023-10-30 14:15:41 2023-10-30 14:15:41 创建   2023-10-30 14:19:24 2023-10-30 14:19:24 更新      4k 字  15 分钟  

数据类型

类型 类型举例
整数类型 TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)、BIGINT
浮点类型 FLOAT, DOUBLE
定点数类型 DECIMAL
位类型 BIT
日期和时间类型 YEAR, TIME, DATE, DATATIME, TIMESTAMP
文本字符串类型 CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
枚举类型 ENUM
集合类型 SET
二进制字符串类型 BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
JSON类型 JSON对象, JSON数组
空间数据类型 单值类型: GEOMETRY, POINT, LINESTRING, POLYGON;集合类型: MULTIPOINT, MULTILINESPRING, MULTIPOLYGON, GEOMETRYCOLLECTION

常见数据类型的属性

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET ‘字符集’ 指定一个字符集

整数类型

类型介绍

整数类型一共有5种,包括TINYINT,SMALLINT,MEDIUMINT,INT(INTEGER)和BIGINT

整数类型 字节 有符号数取值范围 无符号数取值范围
TINYINT 1 -128-127 0-255
SMALLINT 2 -32768-32767 0-65535
MEDIUMINT 3 -8388608-8388607 0-16777215
INT(INTEGER) 4 -2147483648-2147483647 0-4294967295
BIGINT 8 -9223372036854775808-9223372036854775807 0-18446744073709551615

可选属性

整数类型的可选属性有三个

  • M

    M: 表示宽度,M的取值范围是(0-255).例如:int(5):当数据宽度小于5位的时候,在数字前面需要用字符填满宽度.该项功能需要配合ZEROFILL使用,表示用”0”填满宽度,否则指定显示宽度无效

如果设置显示宽度,那么插入的数据宽度超过显示宽度的限制,会不会截断或者插入失败?

答案: 不会对插入的数据有任何影响,还是按照类型的实际宽度进行保存,即显示宽度与类型可以存储的值范围无关.从MySQL8.0.17开始,整数数据类型不推荐使用显示宽度属性.

整数数据类型可以在定义表结构时指定所需要的的显示宽度,如果不指定,则系统会为每一种类型指定默认的宽度值.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 举例
CREATE TABLE test_int1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

DESC test_int1;

+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
| y | smallint(6) | YES | | NULL | |
| z | mediumint(9) | YES | | NULL | |
| m | int(11) | YES | | NULL | |
| n | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

  • UNSIGNED

UNSIGNED: 无符号类型(非负), 所有的整数数据类型都有一个可选的属性UNSIGNED(无符号属性),无符号整数类型的最小取值为0.所以,如果需要在MySQL数据库中保存非负整数值时,可以将整数类型设置为无符号类型.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。

CREATE TABLE test_int3(
f1 INT UNSIGNED
);

mysql> DESC test_int3;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1 | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

  • ZEROFILL

ZEROFILL: 0填充,(如果某列是zerofill,那么MySQL会自动为当前列添加UNSIGNED属性),如果指定了ZEROFILL知识表示不够M位时,用0在左边填充,如果超过M位,只要不超过数据存储范围即可.

原来,在int(M)中,M的值跟int(M)所占用多少存储空间并无任何关系.int(3),int(4),int(8)在磁盘上都是占用4bytes的存储空间.也就是说,int(M),必须和 UNSIGNED ZEROFILL 一起使用才有意义.如果整数值超过M位,就按照实际位数存储.只是无须再用字符0进行填充.

适用场景

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

如何选择

在评估用哪种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：一方 面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间， 使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起 系统错误 ，影响可靠性。

举个例子，商品编号采用的数据类型是 INT。原因就在于，客户门店中流通的商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。

如果使用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范围65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。

你要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，我建议你首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。

浮点类型

类型介绍

浮点数和定点数类型的特点是可以处理小数,你可以把整数看成小数的一个特例.因此,浮点数和定点数使用场景比整数大多了.MySQL支持的浮点数类型分别是FLOAT,DOUBLE,REAL

  • FLOAT表示单精度浮点数
  • DOUBLE表示双精度浮点数
类型 字节 有符号数取值范围 无符号数取值范围
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)

REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那 么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，可以通过以下 SQL 语句实现：

1
SET sql_mode = “REAL_AS_FLOAT”;

定点数类型

类型介绍

MySQL中,定点类型只有 DECIMAL一种

数据类型 字节 含义
DECIMAL(M,D),DEC,NUMERIC M+2字节 有效范围由M和D决定

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

  • 定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。

  • 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

浮点数 vs 定点数

  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）

  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

位类型: BIT

类型介绍

BIT类型中存储的是二进制值，类似010110。

二进制字符串类型 长度 长度范围 占用空间
BIT(M) M 1<= M <= 64 约为(M+7)/8个字节

BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的位数，位数最小值为1，最大值为64。

使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取。

使用b+0查询数据时，可以直接查询出存储的十进制数据的值。

日期与时间类型

类型介绍

MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

  • YEAR类型通常用来表示年
  • DATE类型通常用来表示年,月,日
  • TIME类型通常用来表示时,分,秒
  • DATETIME类型通常用来表示年,月,日,时,分,秒
  • TIMESTAMP类型通常用来表示带时区的年,月,日,时,分,秒
类型 名称 字节 日期格式 最小值 最大值
YEAR 年 1 YYYY或者YY 1901 2155
TIME 时间 3 HH:MM:SS -838:59:59 838:59:59
DATE 日期 3 YYYY-MM-DD 1000-01-01 9999-12-03
DATETIME 日期时间 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 日期时间 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 UTC 2038-01-19 03:14:07 UTC

为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 呢？原因是 MySQL 设计的 TIME 类型，不光表 示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

可以看到，不同数据类型表示的时间内容不同、取值范围不同，而且占用的字节数也不一样，你要根据实际需要灵活选取。

DATETIME 类型

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间。在格式上为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。

TIMESTAMP 类型

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

TIMESTAMP和DATETIME的区别

  • TIMESTAMP 存储空间比较小,表示的日期时间范围也比较小
  • 底层存储方式不同, TIMESTAMP 底层存储的是毫秒值, 距离 1970-01-01 00:00:00 0 毫秒的毫秒值
  • 两个日期比较大小或者日期计算时, TIMESTAMP 更方便,更快
  • TIMESTAMP 和时区有关. TIMESTAMP 会根据用户的时区不同,显示不同的结果. 而 DATETIME 则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的

文本字符串类型

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。

文本字符串类型 值的长度 长度范围 占用的存储空间
CHAR(M) M 0<= M <=255 M个字节
VARCAHR(M) M 0<= M <= 65535 M+1个字节
TINYTEXT L 0<= L <= 255 L+2个字节
TEXT L 0<= L <= 65535 L+2个字节
MEDIUMTEXT L 0<= L <= 16777215 L+3个字节
LONGTEXT L 0<= L <= 4294967295 L+4个字节
ENUM L 1<= L <=65535 1或2个字节
SET L 0<= L <=64 1,2,3,4或8个字节

CHAR和VARCAHR类型

CHAR和VARCHAR类型都可以存储比较短的字符串

文本字符串类型 特点 值的长度 长度范围 占用的存储空间
CHAR(M) 固定长度 M 0<= M <=255 M个字节
VARCAHR(M) 可变长度 M 0<= M <= 65535 M+1个字节

  • CHAR 类型

    • CHAR(M) 类型一般需要预先定义字符串长度,如果不指定M,则表示默认长度是1个字节
    • 如果保存时,数据的时间长度比CHAR类型声明的长度小,则会在右侧填充空格以达到指定的长度.当MySQL检索CHAR 类型的数据时,CHAR类型的字段会去除尾部的空格
    • 定义CHAR类型字段时,声明的长度即为CHAR类型字段所占的存储空间的字节数

  • VARCHAR 类型

    • VARCHAR(M) 定义时,必须指定长度M,否则报错
    • MySQL4.0版本一下,VARCHAR(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字占3个字节);MySQL5.0版本以上,VARCHAR(20):指的是20个字符
    • 检索VARCHAR类型的字段数据时,会保留数据尾部的空格. VARCHAR类型的字段所占用的存储空间为字符串时间长度加1个字节

CHAR 和 VARCHAR 类型

类型 特点 空间上 时间上 适用场景
CAHR(M) 固定长度 浪费存储空间 效率高 存储不大,速度要求高
VARCHAR(M) 可变长度 节省存储空间 效率低 非CHAR场景
  • 情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个 byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
  • 情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
  • 情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
  • 情况4：具体存储引擎中的情况：
    • MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长 度(VARCHAR)的数据列。这样使得整个表静态化，从而使 数据检索更快 ，用空间换时间。
    • MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用 CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
    • InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的,其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

JSON 类型

JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

此页目录
MySQL数据类型
  1. 数据类型
    1. 常见数据类型的属性
  2. 整数类型
    1. 类型介绍
    2. 可选属性
    3. 适用场景
    4. 如何选择
  3. 浮点类型
    1. 类型介绍
  4. 定点数类型
    1. 类型介绍
  5. 位类型: BIT
    1. 类型介绍
  6. 日期与时间类型
    1. 类型介绍
    2. DATETIME 类型
    3. TIMESTAMP 类型
    4. TIMESTAMP和DATETIME的区别
  7. 文本字符串类型
    1. CHAR和VARCAHR类型
    2. CHAR 类型
    3. VARCHAR 类型
    4. CHAR 和 VARCHAR 类型
  8. JSON 类型