MySQL 5.6学习笔记（数据类型）

时间：2024-02-16 12:37:02

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

  1. 数值数据类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数类型FLOAT的DOUBLE、定点小数类型DECIMAL。
  2. 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
  3. 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。

1. 整数类型

类型名称存储需求 取值范围（有符号）
tinyint(m) 1个字节  -128~127
smallint(m) 2个字节  -32768~32767
mediumint(m) 3个字节  -8388608~8388607
int(m) 4个字节  -2147483648~2147483647
bigint(m) 8个字节  -9223372036854775808~9223372036854775807

m表示该数据类型指定的显示宽度，指定能够显示的数值中数字的个数。比如说，定义：year int(4)，声明一个只显示4位数字宽度表示年的字段。

显示宽度和数据类型的取值范围是无关的。

2. 浮点数据类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。它们都可以用(m,n)来表示，其中m称为精度，表示总共的位数；n称为标度，是表示小数的位数。

类型名称存储需求取值范围（有符号）
float(m,d) 单精度浮点型，4字节 -3.402823466E+38~-1.175494351E-38
double(m,d) 双精度浮点型，8字节 -1.7976931348623157E+308~-2.2250738585072014E-308
decimal(m,d),dec 压缩的“严格”定点数，m+2个字节  可能最大取值范围与double一致

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

3. 日期时间类型

当只记录年信息的时候，可以只使用YEAR类型，而没必要使用DATE类型。当指定确实不合法的值时系统将“零”值插入到数据库中。

类型名称日期格式取值范围 存储需求
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-31 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 1970-01-01 00:00:01~2038-01-19 03:14:07 4字节

time类型的取值范围如此大的原因是time不仅可以表示一天的时间，还可以是一个大的时间段。

timestamp值的存储是以UTC（世界标准时时间）格式保存的，存储时对当前时区进行转换，检索时再转换为当前时区。即查询时，根据当前时区的不同，显示的时间值不不同的。

4. 字符串

类型名称说明 存储需求
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个成员） 

enum列总有一个默认值。如果将enum列声明为null，null值则为该列的一个有效值，并且默认值为null。如果enum列被声明为not null，其默认值为允许的值列表的第1个元素。

5. 二进制类型

类型名称说明 存储需求
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+3字节，在此L<2^32

BLOB列存储的是二进制大对象（字节字符串）；TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

6. 如何选择数据类型

  1. 如果不需要小数部分，则使用整数来保存数据；如果需要小数部分，则使用浮点数类型。其中浮点DOUBLE精度比FLOAT要高。
  2. 浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况上，浮点数能表示更大的数据范围。但是由于浮点数容易产生误差，因此在对精确度要求比较高时，建议使用DECIMAL来存储。
  3. 日期与时间的选择要简单，需要什么类型就用什么类型。需要记录年份，就用YEAR；需要记录时间，就用TIME即可。不要扩大数据类型的范围。
  4. TIMESTAMP与DATETIME相比，还有一个功能，在插入值时，如果没有指定TIMESTAMP列的值，MySQL会把TIMESTAMP设置为当前时间；另外，空间上TIMESTAMP也有优势。
  5. CHAR和VARCHAR的区别：CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但浪费存储空间。
  6. ENUM和SET：ENUM合法取值列表最多允许有65535个成员，非常适合“男”、“女”、“未知”这样的取值。SET最多可以取64个成员，空字符串也是合法取值，记录一个人的爱好时，用SET比较合适。
  7. BLOB与TEXT：BLOB是二进制，一般存储图片、音频信息等；TEXT只能存储纯文本信息。

 

相关文章