mysql 数据类型详解 mysql数据类型包括哪些

目录

一、整数类型数据

二、小数类型数据

三、时间和日期型数据

TIME 类型

DATE 类型

DATETIME 类型

TIMESTAMP 类型

四、字符串型数据

CHAR 和 VARCHAR 类型

TEXT 类型

ENUM 类型

SET 类型

五、二进制型数据

BIT 类型

BINARY 和 VARBINARY 类型

BLOB 类型

---

一、整数类型数据

        整数类型又称数值型数据，数值型数据类型主要用来存储数字。不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。

        MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。

类型名称	说明	有符号的最大值	存储需求	无符号的最大值
TINYINT	很小的整数	-128〜127	1个字节	0 〜255
SMALLINT	小的整数	-32768〜32767	2个宇节	0〜65535
MEDIUMINT	中等大小的整数	-8388608〜8388607	3个字节	0〜16777215
INT (INTEGHR)	普通大小的整数	-2147483648〜2147483647	4个字节	0〜4294967295
BIGINT	大整数	-9223372036854775808〜9223372036854775807	8个字节	0〜18446744073709551615

        例如： TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；TINYINT 有符号数的最大值为 27-1，即 127。

        注意：显示宽度和数据类型的取值范围是无关的。显示宽度指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，year 字段插入 19999，当使用 SELECT 查询该列值的时候，MySQL 显示的将是完整的带有 5 位数字的 19999，而不是 4 位数字的值。

        整数类型的默认显示宽度为11个字符，包括可能出现的符号。如果指定了ZEROFILL属性，则会在数字前面填充0以达到指定的显示宽度。例如，INT(6) ZEROFILL可以将数字显示为000123。

        其他整型数据类型也可以在定义表结构时指定所需的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。

二、小数类型数据

        MySQL中使用浮点数和定点数来表示小数。浮点类型有单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。       

         浮点类型和定点类型都可以用(M, D)来表示，其中 M 称为精度，表示总共的位数；D 称为标度，表示小数的位数。浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

类型名称	说明	存储需求	有符号取值范围	无符号取值范围
FLOAT	单精度浮点数	4 个字节	-3.402823466E+38～-1.175494351E-38	0 和 -1.175494351E-38～-3.402823466E+38
DOUBLE	双精度浮点数	8 个字节	0和-1.7976931348623157E+308～-2.2250738585072014E-308	0 和 -2.2250738585072014E-308～-1.7976931348623157E+308
DECIMAL (M, D)，DEC	压缩的“严格”定点数	M+2 个字节

        DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

注意：不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

        FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

        浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

三、时间和日期型数据

        MySQL中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、IMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。每一个类型都有合法的取值范围，当指定不合法的值时，系统将“零”值插入数据库中。（时间相关的数据可以加引号' '，也可以不加）

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

TIME 类型

        TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间，还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。可以使用各种格式指定 TIME 值，如下所示。

• 'D HH：MM：SS' 格式的字符串。还可以使用这些“非严格”的语法：'HH：MM：SS'、'HH：MM'、'D HH' 或 'SS'。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
• 'HHMMSS' 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，'101112' 被理解为'10：11：12'，但是 '106112' 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

        注意：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。例如，读者可能认为 '1112' 和 1112 表示 11：12：00，但 MySQL 将它们解释为 00：11：12。同样 '12' 和 12 被解释为00：00：12。

DATE 类型

        在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

• 以 'YYYY-MM-DD' 或 'YYYYMMDD' 字符中格式表示的日期，取值范围为 '1000-01-01'～'9999-12-3'。如，输入 '2015-12-31' 或 '20151231'，插入数据库的日期为2015-12-31。
• 以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，MySQL 两位年值转换规则：'00～69' 范围的年值转换为 '2000~2069'，'70~99' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。
• 以 YYMMDD 表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，70～99 范围的年值转换为 1970～1999。
• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

        注意：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的，这些值也可以正确地插入数据库。

DATETIME 类型

        在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
• 以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。其他同上。
• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。

        注意：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，'98-12-31 11：30：45'、'98.12.31 11+30+35'、'98/12/31 11*30*45' 和 '98@12@31 11^30^45' 是等价的，这些值都可以正确地插入数据库。

TIMESTAMP 类型

        TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，但是 TIMESTAMP 的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

   注意：协调世界时又称为世界标准时间，简称 UTC。

        TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，与时区无关；
• 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

四、字符串型数据

        字符串类型用来存储字符串数据，还能存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。MySQL中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

        VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（ L），而不是取决于类型的最大可能尺寸。例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR 和 VARCHAR 类型

        CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

        VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。VARCHAR 在值保存和检索时尾部的空格仍保留。

TEXT 类型

        TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

        注意：使用TEXT类型可能会影响查询性能，因为这些字段需要额外的内存和磁盘空间。此外，TEXT类型不支持varchar一样的索引，如果要在TEXT字段上进行高效的搜索，则需要使用全文本索引或其他技术。虽然TINYTEXT类型可以节约空间，但一些操作（如排序和比较）可能需要更多的时间，因为MySQL将在内存中缓存整个字段。因此，需要权衡存储空间和查询性能。

ENUM 类型

        ENUM 是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )

        字段名指将要定义的字段，如性别，值 n 指枚举列表中第 n 个值。
        如枚举集合包含“男”和“女”两个固定值。在存储“性别”字段时，只能输入这两个值中的一个，否则将会报错。如果创建的成员中有空格，尾部的空格将自动被删除。ENUM类型能够有效地约束字段的取值范围，但是它可能并不适合存储大量需要枚举的值，枚举类型的性能可能随着值得数量的增加而下降。

        ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

        例如，定义 ENUM 类型的列（'first'，'second'，'third'），该列可以取的值和每个值的索引如下表所示。

值	NULL	''	’first	second	third
索引	NULL	0	1	2	3

        ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。

CREATE TABLE user(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
hobbies SET('绘画', '音乐', '体育', '旅游'));

#查询所有爱好中包含“体育”的用户。
SELECT * FROM user WHERE FIND_IN_SET('体育',hobbies)>0;

        注意：ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

SET 类型

        SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )

        与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

CREATE TABLE employees(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(30),
language_known SET('English', 'Spanish', 'French', 'German', 'Italian'));

#在INSERT语句中插入值时，可以使用逗号分隔的多个值：
INSERT INTO employees (id, name, language_known) VALUES (1, 'John Smith', 'English, panish, French');


#在查询时，可以使用FIND_IN_SET()函数来查找SET数据类型中的一个或多个值：
SELECT * FROM employees WHERE FIND_IN_SET('English', language_known) > 0;

      注意：插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值没有顺序；插入不正确的值，默认情况下，MySQL 将忽视并给出警告；不能插入空SET。

五、二进制型数据

       二进制数据适合存储各种非文本格式的数据，例如图像、音频和视频等文件，因为这些文件通常很大且不易于通过文本形式进行存储和处理。同时，二进制数据可以通过编程来处理和解析，使得可以轻松地将它们嵌入到各种应用程序中。 MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

        下表中列出了 MySQL 中的二进制数据类型，括号中的 M 表示可以为其指定长度。

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

BIT 类型

        位字段类型。M 表示每个值的位数，范围为 1～64。一个BIT字段可以存储0或1，也可以存储NULL值。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b'101'，其效果与分配 b'000101' 相同。

        BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

        注意：默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。

BINARY 和 VARBINARY 类型

        BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。

        BINARY 类型的长度是固定的，不足最大长度的，将在它们右边填充 “\0” 补齐。例如， BINARY(3)，当插入 a 时，存储的内容实际为 “a\0\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。 VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。例如， VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1。

BLOB 类型

        BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它们可容纳值的最大长度不同。

数据类型	存储范围
TINYBLOB	最大长度为255 (28-1)字节
BLOB	最大长度为65535 (216-1)字节
MEDIUMBLOB	最大长度为16777215 (224-1)字节
LONGBLOB	最大长度为4294967295或4GB (231-1)字节

        BLOB代表二进制大对象，通常用于存储二进制数据，例如图片、声音、视频、程序文件等。BLOB以二进制形式存储数据，因此它们可以存储任何值，包括非文本值。而TEXT代表文本类型，这意味着它们只能存储文本字符串。TEXT可以存储大量文本数据，包括XML文档、HTML文件、JSON文件、配置文件等。