MySQL 数据类型

以下各节描述了MySQL支持的数据类型。

MySQL数据类型

MySQL支持三种类型的数据类型:字符串数字日期/时间数据类型。

字符串数据类型

字符串数据类型通常用于存储名称,地址,描述或任何包含字母和数字的值,包括二进制数据,例如图像或音频文件。

CHAR和VARCHAR类型

CHAR数据类型允许存储最大为255个字符的固定长度字符串。而VARCHAR数据类型允许存储最大为65,535个字符的可变长度字符串(在MySQL 5.0.3之前限制为255个字符)。

CHAR和VARCHAR数据类型声明的长度表示希望存储的最大字符数。例如,CHAR(5)最多可以容纳5个字符。

CHAR和VARCHAR数据类型之间的主要区别是它们存储数据的方式。当值存储在CHAR列中时,将用指定长度的空格右填充,但在VARCHAR列中存储值时将不对其进行填充。这意味着,如果将值“ ab” CHAR(4)存储在VARCHAR(4)列中,则该值将被存储为“ ab”,而相同的值将被存储为“ ab”。

提示:使用CHAR数据类型来存储具有固定长度的值,例如国家/地区代码。对于名称或标题等长度可变的值,VARCHAR可以节省空间。

注意:VARCHAR的有效最大长度受最大行大小(65,535字节,由所有列共享)和所使用的字符集的限制。

BINARY和VARBINARY类型

BINARY和VARBINARY类型类似于CHAR和VARCHAR,不同之处在于它们包含二进制字符串而不是非二进制字符串。   BINARY和VARBINARY的最大允许长度与CHAR和VARCHAR相同,不同之处在于BINARY和VARBINARY的长度以字节为单位,而不是以字符为单位。

TEXT和BLOB类型

TEXT和BLOB数据类型专门用于保存大量数据。  TEXT数据类型用于存储长字符串,如描述,博客评论等。

BLOB是一个二进制大对象,可以容纳可变数量的数据。 当您需要在数据库中存储二进制媒体文件(例如图像或音频文件)时,此功能特别有用。

四种BLOB类型是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们的区别仅在于它们所能容纳的值的最大长度。同样,这四种文本类型是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应于四种BLOB类型,具有相同的最大长度和存储需求。

下表列出了成对出现的MySQL字符串数据类型。无论类型是二进制还是非二进制,最大长度都以字节为单位。

二进制数据类型非二进制数据类型最大长度
BINARYCHAR255
VARBINARYVARCHAR65,535
TINYBLOBTINYTEXT255
BLOBTEXT65,535
MEDIUMBLOBMEDIUMTEXT16,777,215
LONGBLOBLONGTEXT4,294,967,295

注意:对于非二进制字符串数据类型,列长度值通常称为字符数而不是字节数。这意味着包含多字节字符的字符串的最大字符数更少。

枚举(ENUM)类型

ENUM数据类型允许您指定可以存储在列中的可能值的列表。 例如,指定为性别ENUM('male','female')NOT NULL的列可以具有以下任意值:'','male'或'female'。 您可以在ENUM列表中最多指定65,535个不同的值。 如果您在ENUM列中插入无效值(即,在允许值列表中不存在的字符串),则会插入空字符串。

SET类型

SET数据类型允许指定要插入到列中的值列表,比如枚举。但是,与只允许选择一个值的ENUM数据类型不同,SET数据类型允许从指定的值列表中选择多个值。

例如,指定为option SET('one', 'two') NOT NULL的列可以存储以下任何值:",'one', 'two'或'one,two'。用逗号分隔的多个值(,)。对于SET数据类型,最多可以指定64个不同的值。

数值数据类型

数值数据类型通常用于存储价格,薪水等数据。

INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT类型

MySQL支持SQL标准整数类型INTEGER(或INT)和SMALLINT。MySQL也支持整数类型TINYINT,MEDIUMINT和BIGINT作为扩展SQL标准。下表显示了每种整数类型的范围。

数据类型范围(签名)范围(无符号)
TINYINT-128 至 1270 至 255
SMALLINT-32768 至 327670 至 65535
MEDIUMINT-8388608 至 83886070 至 16777215
INT-2147483648 至 21474836470 至 4294967295
BIGINT-9223372036854775808 至 92233720368547758070 至 18446744073709551615

数值数据类型具有有SIGNED(符号)和UNSIGNED(无符号)的附加属性。数值数据类型在默认情况下是有符号的,其范围从负到正。向数值列添加UNSIGNED属性不允许使用负数,并向上移动范围,使最小值从0开始,而不是从负数开始。

DECIMAL,NUMERIC类型

DECIMAL和NUMERIC数据类型是用来存储准确数值。这些数据类型也称为“定点”或“精确值”类型,通常在需要保持精确精度时使用,例如存储像产品价格这样的货币数据。在MySQL中,NUMERIC被实现为DECIMAL。

在声明DECIMAL或NUMERIC列时,可以指定精度和小数位数,例如DECIMAL(P,S)或NUMERIC(P,S),其中P是精度,S是小数位数。 精度指定可以在列中存储的最大位数(包括小数点后的位数),而小数位数表示可以存储在小数点后的位数。 例如,价格DECIMAL(6,2)列可以存储具有六位数字和两个小数的任何值,即-9999.99到9999.99之间的值。

FLOAT,DOUBLE类型

FLOAT和DOUBLE类型表示近似的数值数据值。这就是为什么这些数据类型也称为“浮点型”或“近似值”类型

MySQL支持的语法:FLOAT(M,D)或DOUBLE PRECISION(M,D)。 在此,(M,D)表示总共可以存储多达M位的值,其中D位可以在小数点后。 例如,显示为FLOAT(7,3)的列显示为-9999.999。   MySQL在存储值时会进行四舍五入,因此,如果将9999.0009插入FLOAT(7,3)列,则近似结果为9999.001。

注意:比较浮点值可能会导致问题,因为它们是近似值,而不是精确值。因此,为了存储可以用于比较的值,如price、salary等,请使用DECIMAL数据类型。

日期(DATE)和时间(TIME)数据类型

日期和时间数据类型通常用于存储数据,例如出生日期,雇用日期,在表内创建或更新记录时的日期和时间等。

DATE类型

DATE数据类型用于存储日期。MySQL DATE以'YYYY-MM-DD'格式存储和检索值,其中YYYY,MM和DD分别是时间的年,月和日部分。支持的范围是“ 1000-01-01”至“ 9999-12-31”。

时间(TIME)类型

TIME数据类型可以被用来存储时间的日或时间间隔。MySQL TIME以以下'HH:MM:SS'格式存储和检索值,其中HH,MM和SS是时间的小时,分钟和秒部分(或'HHH:MM:SS'大小时数的格式)。支持的TIME值范围是'-838:59:59'到'838:59:59'。

小时部分可能很大,因为在MySQL中,该TIME类型不仅可以用于存储一天中的时间(必须少于24小时),而且还可以用于经过的时间或两个事件之间的时间间隔(可能远大于24小时,甚至是负的)。

注意:默认情况下,超出TIME范围但有效的值将被裁剪到范围的最近端点。例如,“ 860:00:00”将转换为“ 838:59:59”。无效的TIME值将转换为“ 00:00:00”。

DATETIME和TIMESTAMP类型

DATETIME和TIMESTAMP数据类型用于以'YYYY-MM-DD HH:MM:SS'格式存储合并的日期和时间值。这些数据类型通常用于存储分发订单时的日期和时间、在表中创建或修改一行时的数据,等等。

这两种数据类型在许多方面类似,但是有一些区别。DATETIME支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。然而,时间戳的范围是'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。

TIMESTAMP和DATETIME也具有特殊的自动初始化和自动更新属性,但是这些属性不适用于MySQL 5.6.5之前的DATETIMEM。

注意: MySQL将TIMESTAMP值从当前时区转换为UTC以进行存储,然后从UTC转换为当前时区以进行检索。

年份(YEAR)类型

YEAR数据类型用于存储在四位数年份值YYYY格式。

可以将其声明为YEAR或YEAR(4)。支持的YEAR值范围是1901至2155。无效YEAR值将转换为0000。

注意:较旧的MySQL版本还允许使用来存储两位数的年份值YEAR(2),但现在已弃用,并且在MySQL 5.7.5中已删除了对它的支持。