【MySQL】数据类型

发布时间:2024年01月14日


一、MySQL中的数据类型

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

常见数据类型的属性,如下:

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

二、整数类型

整数类型字节有符号数取值范围无符号数取值范围
TINYINT1-128~1270~255
SMALLINT2-32768~327670~65535
MEDIUMINT3-8388608~83886070~16777215
INT、INTEGER4-2147483648~21474836470~4294967295
BIGINT8-9223372036854775808~92233720368547758070~18446744073709551615

可选属性:

2.1. M

表示显示宽度,M的取值范围是(0, 255)。

显示宽度与类型可以存储的值范围无关
需要配合“ZEROFILL”使用,表示用“0”填满宽度,否则指定显示宽度无效。
不指定每种类型都会有默认的
从MySQL 8.0.17开始,整数数据类型不推荐使用显示宽度属性。

举例:MySQL5.7中显式如下,MySQL8中不再显式范围

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
)
mysql> SELECT * FROM test_int2;
+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
|      1 |    123 |  00123 |
| 123456 | 123456 |   NULL |
| 123456 | 123456 | 123456 |
+--------+--------+--------+
3 rows in set (0.00 sec)

2.2 UNSIGNED

UNSIGNED 无符号类型(非负)

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

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)

2.3 ZEROFILL

zerofill 补零(即0填充)
某列是ZEROFILL,那么MySQL会自动为当前列添加UNSIGNED属性


2.4 总结与适用场景

int(M),必须和UNSIGNED ZEROFILL一起使用才有意义

如果整数值超过M位,就按照实际位数存储,只是无须再用字符 0 进行填充
M 的值跟 int(M) 所占多少存储空间并无任何关系

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

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

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

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

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

系统故障产生的成本远远超过增加几个字段存储空间所产生的成本

三、浮点类型

浮点数和定点数类型的特点是可以处理小数

  • FLOAT 表示单精度浮点数;
  • DOUBLE 表示双精度浮点数;
    1

FLOAT 占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围也大。
无论有没有符号,MySQL 的浮点数都会存储表示符号的部分。

浮点数类型有个缺陷,就是不精准。
(因为是使用二进制的方式来进行存储,无法用一个二进制数来精确表达)
只好在取值允许的范围内进行四舍五入
因为浮点数是不准确的,所以我们要避免使用“=”来判断两个数是否相等

四、定点数 DECIMAL

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

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

  • DECIMAL(M,D)的最大取值范围与DOUBLE类型一样
  • 定点数在MySQL内部是以字符串的形式进行存储,这就决定了它一定是精准的。
  • 不指定精度和标度时,其默认为DECIMAL(10,0)
  • 浮点数 vs 定点数
    • 浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等)
    • 定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)

五、日期与时间类型

  • YEAR类型通常用来表示年
  • DATE类型通常用来表示年、月、日
  • TIME类型通常用来表示时、分、秒
  • DATETIME类型通常用来表示年、月、日、时、分、秒
  • TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒
类型名称字节日期格式最小值最大值
YEAR1YYYY或YY19012155
TIME时间3HH:MM:SS-838:59:59838:59:59
DATE日期3YYYY-MM-DD1000-01-019999-12-03
DATETIME日期时间8YYYY-MM-DD HH:MM:SS1000-01-01 00:00:009999-12-31 23:59:59
TIMESTAMP日期时间4YYYY-MM-DD HH:MM:SS1970-01-01 00:00:00 UTC2038-01-19 03:14:07UTC

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

1. YEAR

  • MySQL5.5.27开始 , 2位格式的YEAR已经不推荐使用,默认格式为YYYY
  • 最小值为1901,最大值为2155
  • 从MySQL 8.0.19开始,不推荐使用指定显示宽度,直接 YEAR
  • 当取值为01到69时,表示2001到2069;
  • 当取值为70到99时,表示1970到1999;
  • 当取值整数的0或00添加的话,那么是0000年;
  • 当取值是日期/字符串的’0’添加的话,是2000年。

举例:

INSERT INTO test_year
VALUES('45','71');

INSERT INTO test_year
VALUES(0,'0');

mysql> SELECT * FROM test_year;
+------+------+
| f1   | f2   |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)

2. TIME

  • 可以使用带有冒号的字符串,比如’D HH:MM:SS'、‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、'D HH’或’SS’格式,都能被正确地插入TIME类型的字段中。
    • 其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。
    • 当使用带有冒号并且不带D的字符串表示时间时,表示当天的时间,比如12:10表示12:10:00,而不是00:12:10。
  • 可以使用不带有冒号的字符串或者数字,格式为’HHMMSS’或者HHMMSS
    • 如果插入一个不合法的字符串或者数字,MySQL在存储数据时,会将其自动转化为00:00:00进行存储。
    • 比如1210,MySQL会将最右边的两位解析成秒,表示00:12:10,而不是12:10:00。
  • 使用CURRENT_TIME()或者NOW(),会插入当前系统的时间

举例:

INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');

INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);

INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());

3. DATE

  • YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期,
    • 其最小取值为1000-01-01,最大取值为9999-12-03。
    • YYYYMMDD格式会被转化为YYYY-MM-DD格式。
  • YY-MM-DD格式或者YYMMDD格式表示的字符串日期,
    • 此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:
      • 当年份取值为00到69时,会被转化为2000到2069;
      • 当年份取值为70到99时,会被转化为1970到1999。
  • 使用CURRENT_DATE()或者NOW()函数,会插入当前系统的日期

举例:

INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);

INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');

INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301); 

INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());

4. DATETIME

  • YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时,最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
    • YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,会被转化为YYYY-MM-DD HH:MM:SS格式。
  • YY-MM-DD HH:MM:SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。
  • 使用函数CURRENT_TIMESTAMP()NOW(),可以向DATETIME类型的字段插入系统的当前日期和时间

举例:

INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');

INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');

INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
 
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());

5. TIMESTAMP

TIMESTAMP存储的时间范围比DATETIME要小很多,
只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。

其中,UTC表示世界统一时间,也叫作世界标准时间。
存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。
因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间

#修改当前的时区
SET time_zone = '+9:00';

总结

TIMESTAMP和DATETIME的区别:

  • TIMESTAMP存储空间比较小,表示的日期时间范围也比较小
  • 底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
  • 两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
  • TIMESTAMP和时区有关。
    • TIMESTAMP会根据用户的时区不同,显示不同的结果。
    • 而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。
INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');

INSERT INTO temp_time VALUES(NOW(),NOW());

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

使用场景:
用的最多的日期时间类型,就是 DATETIME

此外,一般存注册时间、商品发布时间等,不建议使用DATETIME存储,而是使用时间戳,因为DATETIME虽然直观,但不便于计算。

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1635932762 |
+------------------+
1 row in set (0.00 sec)

六、文本字符串类型

6.1

CHAR与VARCHAR类型

字符串(文本)类型特点长度长度范围占用的存储空间
CHAR(M)固定长度M0 <= M <= 255M个字节
VARCHAR(M)可变长度M0 <= M <= 65535(实际长度 + 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(M)固定长度浪费存储空间效率高存储不大,速度要求高
VARCHAR(M)可变长度节省存储空间效率低非CHAR的情况

情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的,结果得不偿失。

情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。

情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。

情况4:具体存储引擎中的情况

TEXT类型

文本字符串类型特点长度长度范围占用的存储空间
TINYTEXT小文本、可变长度L0 <= L <= 255L + 2 个字节
TEXT文本、可变长度L0 <= L <= 65535L + 2 个字节
MEDIUMTEXT中等文本、可变长度L0 <= L <= 16777215L + 3 个字节
LONGTEXT大文本、可变长度L0 <= L<= 4294967295(相当于4GB)L + 4 个字节

由于实际存储的长度不确定,MySQL 不允许 TEXT 类型的字段做主键
遇到这种情况,只能采用 CHAR(M),或者 VARCHAR(M)。

总结:

TEXT文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用CHAR,VARCHAR来代替。
还有TEXT类型不用加默认值,加了也没用。
而且text和blob类型的数据删除后容易导致“空洞”,使得文件碎片比较多,
所以频繁使用的表不建议包含TEXT类型字段,建议单独分出去,单独用一个表。

七、二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据,
比如可以存储图片、音频和视频等二进制数据。

二进制字符串类型特点值的长度占用空间
BINARY(M)固定长度M (0 <= M <= 255)M个字节
VARBINARY(M)可变长度M(0 <= M <= 65535)M+1个字节

BINARY与VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR,只是它们存储的是二进制字符串。

BINARY (M)为固定长度的二进制字符串,M表示最多能存储的字节数,取值范围是0~255个字符。
如果未指定(M),表示只能存储1个字节
例如BINARY (8),表示最多能存储8个字节,
如果字段值不足(M)个字节,将在右边填充’\0’以补齐指定长度。

VARBINARY (M)为可变长度的二进制字符串,M表示最多能存储的字节数,总字节数不能超过行的字节长度限制65535,
另外还要考虑额外字节开销,VARBINARY类型的数据除了存储数据本身外,
还需要1或2个字节来存储数据的字节数。
VARBINARY类型必须指定(M),否则报错。

举例:

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);
INSERT INTO test_binary1(f1,f2)
VALUES('a','a');

INSERT INTO test_binary1(f1,f2)
VALUES('尚','尚');#失败

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

二进制字符串类型值的长度长度范围占用空间
TINYBLOBL0 <= L <= 255L + 1 个字节
BLOBL0 <= L <= 65535(相当于64KB)L + 2 个字节
MEDIUMBLOBL0 <= L <= 16777215 (相当于16MB)L + 3 个字节
LONGBLOBL0 <= L <= 4294967295(相当于4GB)L + 4 个字节

八、ENUM类型

ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。
设置字段值时,ENUM类型只允许从成员中选取单个值,不能一次选取多个值。

文本字符串类型长度长度范围占用的存储空间
ENUML1 <= L <= 655351或2个字节
  • 当ENUM类型包含1~255个成员时,需要1个字节的存储空间;
  • 当ENUM类型包含256~65535个成员时,需要2个字节的存储空间。
  • ENUM类型的成员个数的上限为65535个。

举例:

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋');

# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');

# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);

# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');

# 当ENUM类型的字段没有声明为NOT NULL时,插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

九、JSON 类型

JSON(JavaScript Object Notation)是一种轻量级的数据交换格式

JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。

举例:

CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js) 
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

通

十、BIT 位类型

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

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

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

十一、SET类型

SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为64
设置字段值时,可以取取值范围内的 0 个或多个值。

十二、空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析
MySQL中使用Geometry(几何)来表示所有地理特征。Geometry指一个点或点的集合,代表世界上任何具有位置的事物。


总结

在定义数据类型时:

  • 如果确定是整数,就用 INT
  • 如果是小数,一定用定点数类型 DECIMAL(M,D)
  • 如果是日期与时间,就用 DATETIME

这样做的好处是,首先确保你的系统不会因为数据类型定义出错。
不过,凡事都是有两面的,可靠性好,并不意味着高效。
比如,TEXT 虽然使用方便,但是效率不如 CHAR(M) 和 VARCHAR(M)。

关于字符串的选择,参考阿里巴巴的《Java开发手册》规范:

阿里巴巴《Java开发手册》之MySQL数据库:

  • 任何字段如果为非负数,必须是 UNSIGNED
  • 强制】小数类型为 DECIMAL,禁止使用 FLOAT 和 DOUBLE。
    • 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小数并分开存储。
  • 强制】如果存储的字符串长度几乎相等,使用 CHAR 定长字符串类型。
  • 强制】VARCHAR 是可变长字符串,不预先分配存储空间,长度不要超过 5000。如果存储长度大于此值,定义字段类型为 TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
文章来源:https://blog.csdn.net/GavinGroves/article/details/135555796
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。