>首页> IT >

mysql字段类型有哪些

时间:2022-04-14 15:36:17       来源:PHP中文网

本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。

数据库中的每个字段都应该有适当的数据类型,用于限制或允许该列中存储的数据。例如,字段中存储的为数字,则相应的数据类型应该为数值类型。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能,所以在设计表时,应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事,这样做可能会导致数据丢失。因此,在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 字段的数据类型有大概可以分为 5 种,分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

1、整数类型

整数类型又称数值型数据,数值型数据类型主要用来存储整数数字。

MySQL 提供了多种数值型数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,所需的存储空间也会越大。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

类型名称说明存储需求
TINYINT很小的整数1个字节
SMALLINT小的整数2个宇节
MEDIUMINT中等大小的整数3个字节
INT (INTEGHR)普通大小的整数4个字节
BIGINT大整数8个字节

从上表中可以看到,不同类型的整数存储所需的字节数不相同,占用字节数最小的是 TINYINT 类型,占用字节最大的是 BIGINT 类型,占用的字节越多的类型所能表示的数值范围越大。

根据占用字节数可以求出每一种数据类型的取值范围。例如,TINYINT 需要 1 个字节(8bit)来存储,那么 TINYINT 无符号数的最大值为 28-1,即 255;TINYINT 有符号数的最大值为 27-1,即 127。

2、浮点数和定点数类型

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。

下表中列出了 MySQL 中的小数类型和存储需求。

类型名称说明存储需求
FLOAT单精度浮点数4 个字节
DOUBLE双精度浮点数8 个字节
DECIMAL (M, D),DEC压缩的“严格”定点数M+2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的,DECIMAL 可能的最大取值范围与 DOUBLE 相同,但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D,则取值范围将随 M 的变大而变大。

从上表中可以看到,DECIMAL 的存储空间并不是固定的,而由精度值 M 决定,占用 M+2 个字节。

FLOAT 类型的取值范围如下:

有符号的取值范围:-3.402823466E+38~-1.175494351E-38。

无符号的取值范围:0 和 -1.175494351E-38~-3.402823466E+38。

DOUBLE 类型的取值范围如下:

有符号的取值范围:-1.7976931348623157E+308~-2.2250738585072014E-308。

无符号的取值范围:0 和 -2.2250738585072014E-308~-1.7976931348623157E+308。

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

浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的范围;缺点是会引起精度问题。

最后再强调一下:在 MySQL 中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

3、日期/时间类型

MySQL 中有多处表示日期的数据类型:YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候,可以只使用 YEAR 类型。

每一个类型都有合法的取值范围,当指定确定不合法的值时,系统将“零”值插入数据库中。

下表中列出了 MySQL 中的日期与时间类型。

类型名称日期格式日期范围存储需求
YEARYYYY1901 ~ 21551 个字节
TIMEHH:MM:SS-838:59:59 ~ 838:59:593 个字节
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33 个字节
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598 个字节
TIMESTAMPYYYY-MM-DD HH:MM:SS1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC4 个字节

YEAR 类型

YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR,如下所示:

以 4 位字符串或者 4 位数字格式表示的 YEAR,范围为 "1901"~"2155"。输入格式为 "YYYY" 或者 YYYY,例如,输入 "2010" 或 2010,插入数据库的值均为 2010。

以 2 位字符串格式表示的 YEAR,范围为 "00" 到 "99"。"00"~"69" 和 "70"~"99" 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 YEAR 值。"0" 与 "00" 的作用相同。插入超过取值范围的值将被转换为 2000。

以 2 位数字表示的 YEAR,范围为 1~99。1~99 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 YEAR 值。注意,在这里 0 值将被转换为 0000,而不是 2000。

TIME 类型

TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。

TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于 24 小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 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。

DATE 类型

DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 "YYYY-MM-DD",其中 YYYY 表示年,MM 表示月,DD 表示日。

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

以 "YYYY-MM-DD" 或者 "YYYYMMDD" 字符中格式表示的日期,取值范围为 "1000-01-01"~"9999-12-3"。例如,输入 "2015-12-31" 或者 "20151231",插入数据库的日期为2015-12-31。

以 "YY-MM-DD" 或者 "YYMMDD" 字符串格式表示日期,在这里YY表示两位的年值。MySQL 解释两位年值的规则:"00~69" 范围的年值转换为 "2000~2069","70~99" 范围的年值转换为 "1970~1999"。例如,输入 "15-12-31",插入数据库的日期为 2015-12-31;输入 "991231",插入数据库的日期为 1999-12-31。

以 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069,80~99 范围的年值转换为 1980~1999。例如,输入 151231,插入数据库的日期为 2015-12-31,输入 991231,插入数据库的日期为 1999-12-31。

使用 CURRENT_DATE 或者 NOW(),插入当前系统日期。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 "YYYY-MM-DD HH:MM:SS",其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。

在给 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 表示两位的年值。与前面相同,"00~79" 范围的年值转换为 "2000~2079","80~99" 范围的年值转换为 "1980~1999"。例如,输入 "14-12-31 05:05:05",插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。

以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如,输入 20141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 140505050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 "1970-01-01 00:00:01"UTC~"2038-01-19 03:14:07"UTC。在插入数据时,要保证在合法的取值范围内。

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

DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;

而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。

提示:如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值,结果值的时间部分被设置为 "00:00:00",因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值,结果值的时间部分被删除,因此DATE 值未包含时间信息。

4、字符串类型

字符串类型用来存储字符串数据,还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较,还可以进行正则表达式的匹配查找。

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

下表中列出了 MySQL 中的字符串数据类型,括号中的M表示可以为其指定长度。

类型名称说明存储需求
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) 为固定长度字符串,在定义时指定字符串列长。当保存时,在右侧填充空格以达到指定的长度。M 表示列的长度,范围是 0~255 个字符。

例如,CHAR(4) 定义了一个固定长度的字符串列,包含的字符个数最大为 4。当检索到 CHAR 值时,尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串,M 表示最大列的长度,M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。

例如,VARCHAR(50) 定义了一个最大长度为 50 的字符串,如果插入的字符串只有 10 个字符,则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

TEXT 类型

TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格。

TEXT 类型分为 4 种:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

TINYTEXT 表示长度为 255(28-1)字符的 TEXT 列。

TEXT 表示长度为 65535(216-1)字符的 TEXT 列。

MEDIUMTEXT 表示长度为 16777215(224-1)字符的 TEXT 列。

LONGTEXT 表示长度为 4294967295 或 4GB(232-1)字符的 TEXT 列。

ENUM 类型

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

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

字段名指将要定义的字段,值 n 指枚举列表中第 n 个值。

ENUM 类型的字段在取值时,能在指定的枚举列表中获取,而且一次只能取一个。如果创建的成员中有空格,尾部的空格将自动被删除。

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

SET 类型

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

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

与 ENUM 类型相同,SET 值在内部用整数表示,列表中每个值都有一个索引编号。当创建表时,SET 成员值的尾部空格将自动删除。

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

5、二进制类型

MySQL 支持两类字符型数据:文本字符串和二进制字符串。上节《MySQL字符串类型》我们讲了文本字符串,这节我们来讲解二进制字符串。

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

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

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

BIT 类型

位字段类型。M 表示每个值的位数,范围为 1~64。如果 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(M) 或者 VARBINARY(M)

BINARY 类型的长度是固定的,指定长度后,不足最大长度的,将在它们右边填充 “\0” 补齐,以达到指定长度。例如,指定列数据类型为 BINARY(3),当插入 a 时,存储的内容实际为 “\a0\0”,当插入 ab 时,实际存储的内容为“ab\0”,无论存储的内容是否达到指定的长度,存储空间均为指定的值 M。

VARBINARY 类型的长度是可变的,指定好长度之后,长度可以在 0 到最大值之间。例如,指定列数据类型为 VARBINARY(20),如果插入的值长度只有 10,则实际存储空间为 10 加 1,实际占用的空间为字符串的实际长度加 1。

BLOB 类型

BLOB 是一个二进制的对象,用来存储可变数量的数据。BLOB 类型分为 4 种:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB

【相关推荐:mysql视频教程】

以上就是mysql字段类型有哪些的详细内容,更多请关注php中文网其它相关文章!

关键词: 数据类型 二进制字符 字符串类型