MySQL数据库的数据类型主要分为4种,分别是数值类型、日期/时间类型、字符串类型以及二进制类型。
一、数值类型
数值类型主要分为整数类型和小数类型。
- 整数类型
类型名称 | 存储需求 | 取值范围 |
---|---|---|
TinyInt | 1个字节 | -128〜127 |
SmallInt | 2个字节数 | -32768〜32767 |
MediumInt | 3个字节 | -8388608〜8388607 |
Int | 4个字节 | -2147483648〜2147483647 |
BigInt | 8个字节 | -9223372036854775808〜9223372036854775807 |
由上表可知,不同类型的整数存储的字节数也是不同的,并且随着字节数的增大数据类型的取值范围也随之增大。
- 小数类型
小数类型主要分为浮点数型和定点数型。
类型名称 | 说明 | 存储需求 |
---|---|---|
Float | 单精度浮点数 | 4个字节 |
Double | 双精度浮点数 | 8个字节 |
Decimal(M,D)DEC | 压缩的“严格”定点数 | M+2个字节 |
浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。
从上表可以看出,Decimal类型的存储空间的大小是不固定的,而是取决于M。Folat和Double在不指定精度时,会按照实际的精度(由计算机硬件和操作系统决定),而当Decimal没有指定精度时,默认范围为(10,0)。不论是浮点类型还是定点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
二、日期和时间类型
MySQL的日期和时间类型如下表所示:
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
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个字节 |
1、Year
不同格式的表现形式
- 两位数字的表现形式。范围为 1~99。1~99 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 Year 值。注意,在这里 0 值将被转换为 0000,而不是 2000。
- 两位字符的表现形式。范围为 ‘00’ 到 ‘99’。‘00’~’69’ 和 ‘70’~’99’ 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 Year 值。插入超过取值范围的值将被转换为 2000。
- 4 位字符串或者 4 位数字格式的表现形式。范围为 ‘1901’~’2155’,输入格式为 ‘YYYY’ 或者 YYYY。
2、Time
HH 表示小时,MM 表示分钟,SS 表示秒。取值范围为 -838:59:59~838:59:59。‘D HH:MM:SS’ 格式。D表示日,在导入数据库时,D被转化为小时保存’D*24+HH’。
3、Date
日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示年,MM 表示月,DD 表示日。
不同格式的表示方式:
- ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示。取值范围为 ‘1000-01-01’~’9999-12-3’。
- ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示。'YY’表示年的两个字符,与上文中Year的取值相同。
- YYMMDD 数字格式表示。与上文中YEAR的数字格式的表现形式一致。
- 使用 current_date 或者 now(),插入当前系统日期。
4、DateTime
DateTime相当于Date 和Time两种类型的结合。日期格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。每个变量的取值范围都与上文相同。
5、TimeStamp
日期格式为 YYYY-MM-DD HH:MM:SS,TimeStamp 的取值范围为 '1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC。TimeStamp 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
三、字符串类型
MySQL数据库的字符串类型主要包括:Char、VarChar、TinyTex、Text、MediumText、LongText、Enum、Set。
1、Char与VarChar
Char(M):固定长度字符串,M表示指定列的长度。当保存时,在右侧填充空格以达到指定的长度,当达到最大字符长度时,尾部空格会被删除。
VarChar(M):长度可变的字符串,M表示最大列的长度。VarChar 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。
例如:我们插入不同的字符串并固定字符串类型的M值,并观察其存储需求。
插入值 | char(4) | 存储需求 | varchar(4) | 存储需求 |
---|---|---|---|---|
‘’ | ’ ’ | 4 | ‘’ | 1 |
‘ab’ | 'ab ’ | 4 | ‘ab’ | 3 |
‘abc’ | 'abc ’ | 4 | ‘abc’ | 4 |
‘abcd’ | ‘abcd’ | 4 | ‘abcd’ | 5 |
‘abcde’ | ‘abcd’ | 4 | ‘abcd’ | 5 |
2、Text类型
Text列保存非二进制字符串,如文章内容、评论等,当保存或查询Text值时,不删除尾部空格。
Text类型 | 数据长度 |
---|---|
TinyText | 255 |
Text | 65535 |
MediumText | 16777215 |
LongText | 4GB |
3、Enum和Set类型
Enum:Enum类型的字段在取值时,能在指定的枚举列表中获取,而且一次只能取一个,每个枚举值均有一个索引,列表所允许的成员值从1号开始编号,枚举最多可以有 65535 个元素。
语法格式如下:
<字段名>Enum(‘值1’,‘值2’,…,‘值n’)
例如:定义 Enum 类型的列(‘apple’,‘banana’,‘orange’),该列可以取的值和每个值的索引如下表所示。
值 | 索引 |
---|---|
apple | 1 |
banana | 2 |
orange | 3 |
Set 类型:与Enum类型相同,不同的是,set类型可以从定义的列值中选择多个字符的联合。
语法格式如下:
set(‘值1’,‘值2’,…,‘值n’)
四、二进制类型
二进制类型主要包括Bit、Binary、VarBinary、TinyBlob、Blob、MediumBlob、LongBlob。
1、Bit(M)
位字段类型,M 表示每个值的位数,范围为 1~64。当M 被省略,默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位,在值的左边用 0 填充。
2、Binary 和VarBinary
binary和varbinary类似于char和varchar。binary 类型的长度是固定的,指定长度后,不足最大长度的,将在它们右边填充 “\0” 补齐,以达到指定长度。varbinary 类型的长度是可变的,指定好长度之后,长度可以在 0 到最大值之间。
3、Blob类型
Blob 主要用于存储可变数量的二进制类型的数据,例如一些图片以及音频信息等。
类型 | 大小(单位:字节) |
---|---|
TinyBlob | 255 |
Blob | 65K |
MediumBlob | 16M |
LongBlob | 4G |