MySQL中常用的数据类型

整型

整型声明

整型属性

整型的选择

浮点型

定点数类型

浮点数和定点数的区别

时间日期类型

DATE类型

TIME类型

DATETIME类型

YEAR类型

文本字符串

CHAR与VARCHAR类型

TEXT类型

编辑

枚举类型(ENUM)

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 1INSERT INTO test_enum
VALUES('春'),('1'),(2);#可以由索引添加，索引从1开始SELECT * FROM test_enum; #输出：春，春，夏