MYSQL笔记之数据类型

数据库由多列字段构成，每个字段都可以指定不同的数据类型，统一的数据类型有利于我们对数据进行后续的分析，下面我将为大家介绍MYSQL中常见的数据类型

数据类型介绍

MYSQL主要有数值类型、日期/时间类型和字符串类型。数值型又分整数型（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）、浮点小数型（FLOAT、DOUBLE）和定点小数型（DECIMAL）；日期/时间类型有YEAR、TIME、DATE、DATETIME和TIMES-TAMP；字符串类型有：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等，字符串类型又分为文本字符串和二进制字符串。

一、数值类型

1.1、下表为数值型数据类型汇总，存储需求，范围，说明：

数值型
类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3 Bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT或INTEGER	4 Bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 Bytes	(-9223372036854775808，922337036854775807)	(0，18446744073709551615)	极大整数值
FLOAT	4 Bytes	(-3.402823466E+38，-1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38)	0，(1.175494351E-38，3.402823466E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.2250738585072014E-308，1.7976931348623157E+308)	0，(2.2250738585072014E-308，1.7976931348623157E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	压缩的“严格”定点数

 整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件，具体怎么添加可查看我上一遍文章（https://blog.csdn.net/xs011/article/details/126057227）。注：显示宽度只拥于显示，并不能限制取值范围和占用空间。例如：INT（3）会占用4个字节的空间，允许的最大值也不是999，而是INT整数型所允许的最大值。

浮点型数据分为两种，单精度浮点型（FLOAT）和双精度浮点型（DOUBLE）；定点数型只有一种（DECIMAL）。浮点数和定点数型都可以用（M,N）来表示，其中，M为精度，表示总共的位数，N为标度，表示小数的位数。注：不管是定点型还是浮点型，如果指定的精度超出精度范围，会四舍五入。

1.2例1

创建表TB1_TEST,其中字段A、B、C、D、E数据类型分别为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，字段A添加自增约束条件，SQL语句如下：

CREATE TABLE TB1_TEST
(A TINYINT(4) PRIMARY KEY AUTO_INCREMENT,
B SMALLINT(6),
C MEDIUMINT(8),
D INT(11),
E BIGINT(11)
);
DESC TB1_TEST;

表TB1_TEST创建完成后使用DESC查看表结构，结果如下：

从上图可以看到创建的表TB_TEST中的字段已经分别设置了不同的数据类型。

1.3例2 

创建数据表TB1_TEST1，其中字段A、B、C的数据类型分别为FLOAT(5,1)、DOUBLE(5,1)、DECIMAL(5,1)，并向表中插入数据5.12、5.17、5.124。SQL语句如下：

CREATE TABLE TB1_TEST1(
A FLOAT(5,1),
B DOUBLE(5,1),
C DECIMAL(5,1)
);
DESC TB1_TEST1;
INSERT INTO TB1_TEST1 VALUES (5.12,5.17,5.124);

表TB1_TEST1创建完成后使用DESC查看表结构，结果如下：

 由上图可知，在创建数据表时，给出2条警告信息，使用SHOW WARNINGS语句查看警告信息可知，建议不为浮点型数据指定标度（小数点位数）；使用DESC语句查看表TB1_TEST1的结构可知相应字段均设置小数型数据成功。向表TB1_TEST1中插入数据时，给出一条警告信息，查看警告信息可知，字段A和字段B在四舍五入是没有警告信息，字段C提示数值被截断。使用SELECT语句查看TB1_TEST1可知插入的数据均四舍五入后只保留一位小数。注：定点数以字符串的形式存储，在对精度要求比较高时使用DECIMAL的类型比较好，浮点型数据在进行减法和比较运算是容易出问题，所以在使用浮点数时需要注意，并尽量避免使用浮点数进行比较。

二、日期/时间类型

2.1、下表为日期/时间型数据类型汇总，存储需求，范围，说明：

日期/时间型
类型	大小( bytes)	范围	格式	用途
YEAR	1	1901-2155	YYYY	年份值
DATE	3	1000-01-01-9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59'-'838:59:59'	HH:MM:SS	时间值或持续时间
DATETIME	8	1000-01-01 00:00:00' - '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

2.2YEAR 

YEAR用于表示年，在插入数据时有三种方式：（1）以4位字符串或4位数字格式表示，范围为1901-2155，输入格式为'YYYY'或YYYY。（2）以2位字符串格式表示，范围为'00'-'99'，'00'-'69'和'70'-'99'的值分别转化为2000-2069和1970-1999范围的YEAR值，‘0’和‘00’的作用相同(个位数均如此)。（3）以2为数字格式表示，范围为1-99,1-69和70-99分别转化为2001-2069和1970-1999的YEAR值，0将被转化为0000。

2.2例3

创建数据表TB1_TEST2，定义字段A、B、C数据类型均为YEAR，分别依次插入（2020、20、'20'）、('2020'、00、'00')、(2020、01、'01')、(2020、85、'85')，SQL语句如下：

CREATE TABLE TB1_TEST2(
A YEAR,
B YEAR,
C YEAR
);
INSERT INTO TB1_TEST2 VALUES(2020,20,'20'),('2020',00,'00'),(2020,01,'01'),(2020,85,'85');
SELECT * FROM TB1_TEST2;

插入数据后使用SELECT语句查看表TB1_TEST2，结果如下图：

 由图可知，所有年份均按照上述规则插入并转化为相应的格式。

2.3DATE

DATE类型用于仅需要日期时，格式为'YYYY-MM-DD'，其中YYYY表示年份，MM表示月份，DD表示日。插入数据时有两种方式：（1）以'YYYY-MM-DD'、'YYYYMMDD'字符串格式或YYYYMMDD数字格式插入。（2）以'YY-MM-DD'、'YYMMDD'字符串格式或YYMMDD数字格式插入，与前面相似YY取00-69和70-99分别转化为2000-2069和1970-1999。（3）使用CURRENT_DATE()或者NOW()，插入当前系统日期。注：使用YYMMDD数字格式插入是如输入1222会转化为001222即2000年12月22日；MYSQL允许“不严格语法”：任何标点符号都可以作为日期部分之间的间隔符，如‘98-12-31’、‘98/12/31’、‘98.12.31’、‘98%12%31’都是等价的，都可以正确的插入到数据库中。

2.3例4

创建表TB1_TEST3，添加字段A格式为DATE，依次使用不同方法插入日期，SQL语法如下：

CREATE TABLE TB1_TEST3(
A DATE
);
INSERT INTO TB1_TEST3 VALUES('2021-12-31'),('20221221'),(20211112),('98-12-31'),('12-12-31'),(201231),('21*12*31'),(CURRENT_DATE()),(NOW());
SELECT * FROM TB1_TEST3;

使用SELECT语句查看表TB1_TEST3可知，用上述方法均能将日期准确插入到数据表中，结果如下图：

 2.4TIME

TIME类型只用于表示时间，格式为'HH:MM:SS'，范围为-838:59:59-838:59:59，小时部分如此大的原因是TIME类型不仅可以用于表示一天的时间（必须是24小时），还可能是某个事件过去的时间或者两个事件之间的时间间隔（可以大于24小时，甚至为负），插入数据时的方法：（1）'D HH:MM:SS'格式的字符串，也可以使用以下语法：'D HH :MM'、'HH:MM'、'SS'等，其中，D表示日，可以取0-34，在插入数据时会转化为小时保存，如'1 06:23:12'转化为'30:23:12'保存；（2）没有间隔符的'HHMMSS'字符串或HHMMSS数字格式，当简写时因注意MYSQL解释值时，假定最右边的为秒，如1212被解释为00:12:12；有分隔符时则相反，如12:12被解释为12:12:00。

2.4例5

创建表TB1_TEST4，添加字段A，数据类型为TIME，依次使用不同方式插入数据，SQL语句如下：

CREATE TABLE TB1_TEST4(
A TIME
);
INSERT INTO TB1_TEST4 VALUES('5 12:24:35'),('2 12:12'),('2 12'),('12'),(3121212),(1212),(12),('121212'),('1121212'),(CURRENT_TIME()),(NOW());
SELECT * FROM TB1_TEST4;

在添加完数据后，使用SELECT语句查看所添加的时间，可以看到，所有时间均成功添加，并符合上述规则，结果见下图：

 2.5DATETIME

DATETIME类型用于需要同时包含日期和时间的值，格式为'YYYY-MM-DD HH: MM:SS'，插入数据是的方法：（1）以‘YYYY-MM-DD HH:MM:SS’、‘YYYYMMDDHHMMSS’的字符串形式或YYYYMMDDHHMMSS的数字形式插入均可，也可将YYYY简写为YY，转化规则与上述年份的转化规则一致，取值范围00-99,00-69和70-99分别转化为2000-2069和1970-1999。注：MYSQL允许“不严格”语法：任何标点符号均可作为日期或时间部分之间的间隔符。

2.5例6

创建表TB1_TEST5，添加字段A，定义数据类型为DATETIME，依次使用上述方法插入数据，MYSQL语句：

CREATE TABLE TB1_TEST5(
A DATETIME
);
INSERT INTO TB1_TEST5 VALUES('2013-12-12 12:12:12'),('21*12*12 12#21#21'),(021212213131),('981212213231'),(1212121212),(CURRENT_TIME()),(NOW());
SELECT * FROM TB1_TEST5;

在创建表插入数据后，使用SELECT语句查看插入的数据，可以看到所有的数据均已准确按照上述规则插入，结果见下图：

2.6 TIMESTAMP

TIMESTAMP的显示格式与DATETIME的相同，显示宽度固定在19个字符，取值范围比DATETIME格式的小，为：'1970-01-01 00:00:01'UTC-'2038-01-19 03:14:07'UTC。其中，UTC为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。注：DATETIME和TIMESTAMP最大的区别在于DATATIME按输入的格式存储时间，TIMESDAMP在存储时以UTC格式保存，查询时，不同的时区显示的时间值是不同的。

2.6例7

创建数据表TB1_TEST7，添加字段A，定义数据类型为TIMESTAMP，依次使用不同的插入方式插入数据，SQL语句：

CREATE TABLE TB1_TEST6(
A TIMESTAMP
);
INSERT INTO TB1_TEST6 VALUES('2013-12-12 12:12:12'),('21*12*12 12#21#21'),(021212213131),('981212213231'),(1212121212),(CURRENT_TIME()),(NOW());
SELECT * FROM TB1_TEST6;

 在创建表插入数据后，使用SELECT语句查看插入的数据，可以看到所有的数据均已准确按照上述规则插入，结果见下图：

三、文本字符串类型

3.1、下表为文本字符串型数据类型汇总，存储需求，说明：

字符串类型
类型	大小	用途
CHAR(M)	0-255 bytes	定长文本字符串
VARCHAR(M)	0-65535 bytes	变长文本字符串
TINYTEXT	0-255 bytes	短文本字符串
TEXT	0-65 535 bytes	长文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据
BLOB	0-65 535 bytes	二进制形式的长文本数据
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据

3.2CHAR和VARCHAR类型

CHAR（M）为固定长度字符串，在定义时指定字符串长度，不管你插入多少字符串均为固定字符串长度，少的在右边用空格填充，在检索时，右边的空格将被删除。VARCHAR是可变长度字符串，在定义时指定最大字符串长度，所占空间为插入字符长度+1，超过指定最大长度时，所占空间为最大字符长度+1。因此，在插入字符串时如果最右边有空格，使用CHAR格式，这个空格在检索时将会被删除，反之，使用VARCHAR格式，空格也被当做字符保存，所有这个空格在检索时不会被删除。

3.3TEXT类型

TEXT类型保存非二进制字符串，如文章内容、评论等，当保存是，最右边的空格将不会被删除。TEXT类型分为4种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间不同，数据长度也不同。

3.4BLOB类型

BLOB类型保存二进制字符串，分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。不同的BLOB类型的存储空间不同，数据类型也不同。

文本字符串类型的数据我将不做任何演示，也不进行深度的讲解，后期会单独拿出来做讲解。