MySQL 常用数据类型说明

最新推荐文章于 2024-05-07 17:19:39 发布

Mao.O

最新推荐文章于 2024-05-07 17:19:39 发布

阅读量2.2k

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库 MySQL数据类型

本文链接：https://blog.csdn.net/m0_60155232/article/details/127007011

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL中常用的数据类型

数据类型	类型举例
整型	TINYINT,SMALLINT,INT....
浮点型	FLOAT,DOUBLE
定点数类型	DECIMAL
日期时间类型	DATE,TIME,YEAR,DATETIMA...
文本字符串类型	CHAR(M),VARCHAR(M),TEXT...
枚举类型	ENUE

整型

整数类型一共有 5 种，包括 TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT （INTEGER）和 BIGINT 。这5种类型的区别就是他们的取值范围不同。

整型类型取值范围表

整型声明

原始整型的声明：

CREATE TABLE t_date_type(
id TINYINT,
age INT 
)

在声明整型的时候，我们还可以为其添加一些属性来修饰，以达到某些限制。

整型属性

整型常见的属性：

举例：带有属性的整型类型的声明：

属性 UNSIGNED

UNSIGNED:无符号类型，声明了此关键字，此字段最小值为0，即：非负数。则如果在声明表字段时，明确知道该字段非负数，则添加此字段。如id。

CREATE TABLE t_date_type(
age INT UNSIGNED  //为字段添加属性
)

整型的选择

由于MySQL中整型类型很多，如何选择哪一个呢？下面给出大概使用范围：

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

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

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

INT 、 INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，

用得最多。比如商品编号。

BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、

大型门户网站点击量等。

在实际生成中，需要考虑存储空间和可靠性，整型数据类型有不同的取值存储范围，取值范围字节数小的节省空间毋庸置疑，但是如果一味追求节省空间，将整型类型定义小了，导致使用存储时超出了存储范围，引起系统故障就得不偿失了，所以要权衡利弊，用内存空间可以换来可靠性。

浮点型

浮点数的特点是可以处理小数，你可以把整数看成小数的一个特例。因此浮点数使用的范围要比整型更广些，MySQL支持的浮点数类型为：FLOAT , DOUBLE , REAL。

FLOAT 表示单精度浮点数；

DOUBLE 表示双精度浮点数；

两者取值范围

如果声明时使用REAL，那么默认就是 DOUBLE类型。如果你把 SQL 模式设定为：REAL_AS_FLOAT ”，那么，MySQL 就认为

REAL 是 FLOAT 。启用 “REAL_AS_FLOAT”的方式：

SET sql_mode = "REAL_AS_FLOAT";

需要注意：

从 MySQL 8.0.17 开始， FLOAT(M,D) 和 DOUBLE(M,D) 用法在官方文档中已经明确不推荐使用 ，将来可能被移除。另外，关于浮点型FLOAT 和 DOUBLE 的 UNSIGNED（无符号）也不推荐使用了，将来也可能被移除。将由(DECIMAL)定点数替代。

浮点型精度偏差说明

CREATE TABLE test_float(
num FLOAT
);

INSERT INTO test_float
VALUES(0.25),(0.44),(0.41);
#求和，结果应该为1.1
SELECT SUM(num) FROM test_float;#1.0999999940395355（存在精度偏差）

**********************
CREATE TABLE test_double(
num DOUBLE
)

INSERT INTO test_double
VALUES(0.25),(0.44),(0.41)

SELECT SUM(num) FROM test_double#1.0999999999999999

结果显然有误差，浮点类型那是因为底层存储二级制时，浮点数据无法用一个二进制数来精确表达，只能在取值允许的范围内进行四舍五入。

因此我们要避免使用"="号来判断两数是否相等。要想使用精度更高的，应该使用DECIMAL。

定点数类型

MySQL中定点数就只有一种：DECIMAL。

定义：DECIMAL(M,D) ,其中：D称为精度，M称为标度。

0<=M<=65 , 0<=D<=30 。

例如，定义 DECIMAL （5,2）的类型，表示该列取值范围是 -999.99~999.99 。

DECIMAL（M，D）有效范围由M和D决定。最大取值范围和DOUBLE一样。

DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。

浮点数和定点数的区别

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

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

时间日期类型

时间日期类型是很常用的一个类型，MySQL有多种表示日期和时间的数据类型。

不同的版本可能有所差异，MySQL8.0版本支持的日期和时间。

类型主要有：DATE类型、TIME类型、DATETIME类型和YEAR类型。

DATE：类型通常用来表示年、月、日。

TIME：类型通常用来表示时、分、秒。

DATETIME ：类型通常用来表示年、月、日、时、分、秒。

YEAR：类型通常用来表示年。

DATE类型

CREATE TABLE test_date(
h_date DATE 
);
INSERT INTO test_date()
VALUES('2022-02-02'),('2022-02-02'),('22-02-02'),('70-02-02'),('220202'),('700202');

SELECT * FROM test_date;

DATE 类型表示日期，没有时间部分，格式为 YYYY - MM - DD ，其中， YYYY 表示年份， MM 表示月份， DD 表示日期。

DATE最小取值为 1000-01-01 ，最大取值为 9999-12-03。

1.在插入数据时，可以用 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期。YYYYMMDD格式会被转化为YYYY-MM-DD格式。

2.以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期。

此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69 时，会被转化为2000到2069 ；当年份取值为70到99时，会被转化为1970到1999。

**使用 CURRENT_DATE() ，会返回当前的年月日。

TIME类型

CREATE TABLE test_time(
cr_time  TIME
);

INSERT INTO test_time
VALUES('10:28:56'),('1 10:28:56'),('28:56'),('1 28:56'),('1 28'),('56');

SELECT * FROM test_time;

TIME类型用来表示时间，不包含日期部分。可以使用 “HH:MM:SS” 格式来表示 TIME 类型。

向TIME类型的字段插入数据时，也可以使用几种不同的格式：

1.可以使用带有冒号的字符串，比如' D HH:MM:SS' 、 ' HH:MM:SS ' 、 ' HH:MM ' 、 ' D HH:MM ' 、 ' D HH ' 或 ' SS '格式。其中 D表示天数，其最小值为 0 ，最大值为 34 。

2.当输入D HH:MM:SS时，D会被转化为小时，计算格式为 D*24+HH。

3.当仅输入两位数时，表示秒数。

DATETIME类型

CREATE TABLE test_datetime( 
dt DATETIME
);

INSERT INTO test_datetime
VALUES('2022-09-27 10:09:45')

SELECT * FROM test_datetime;

可以使用DATE，TIME时间格式进行匹配

DATETIME在格式上为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:S。

以 YYYY - MM - DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入 DATETIME 类型的字段时，最小值为1000-01-01 00:00:00 ，最大值为 9999-12-03 23:59:59 。

使用NOW()方法会输出年月日时分秒。

YEAR类型

CREATE TABLE test_year(
y YEAR
);

INSERT INTO test_year
VALUES('2022'),('69'),('70'),(0),('0');

SELECT * FROM test_year;

YEAR类型用来表示年份。在添加时，YEAR有以下几种存储格式：

1.以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。

2.以2位字符串格式表示YEAR类型，最小值为00，最大值为99。

        当取值为01 到 69 时，表示 2001 到 2069 ；

        当取值为70 到 99 时，表示 1970 到 1999 ；

        当取值整数的0 或 00 添加的话，那么是 0000 年；

        当取值是日期/ 字符串的 '0' 添加的话，是 2000 年。

文本字符串

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

CHAR与VARCHAR类型

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

CHAR类型

CREATE TABLE test_char(
c1 CHAR,
c2 CHAR(5)
)
DESC test_char;

如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。

INSERT INTO test_char(c2)
VALUES('a  ');

SELECT CHAR_LENGTH(c2) FROM test_char;#输出为1

VARCHAR类型

CREATE TABLE test_varchar(
v VARCHAR(25)
);

INSERT INTO test_varchar
VALUES('a  ');

SELECT CHAR_LENGTH(v) FROM test_varchar;#输出为3

VARCHAR(M) 定义时，必须指定长度 M ，否则报错

检索VARCHAR类型的字段数据时，会保留数据尾部的空格。 VARCHAR 类型的字段所占用的存储空间为字符串实际长度加1 个字节。

CHAR和VARCHAR对比

TEXT类型

CREATE TABLE test_text(
t TEXT
);

INSERT INTO test_text
VALUES('a  ');

SELECT CHAR_LENGTH(t) FROM test_text;

在 MySQL 中， TEXT 用来保存文本类型的字符串，总共包含 4 种类型，分别为 TINYTEXT 、 TEXT 、MEDIUMTEXT 和 LONGTEXT 类型。

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

四种TEXT类型

注意：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键

枚举类型(ENUM)

CREATE TABLE test_enum(
e ENUM('春','夏','秋','冬')
);

INSERT INTO test_enum
VALUES('天');#报错：Data truncated for column 'e' at row 1

INSERT INTO test_enum
VALUES('春'),('1'),(2);#可以由索引添加，索引从1开始

SELECT * FROM test_enum; #输出：春，春，夏