05 MySQL数据库_数据类型

MySQL数据库_数据类型

 

1.数据类型的分类：

• 整数类型：tinyint、smallint、mediumint、int、bigint

类型名称	说明	存储需求	范围（有符号）	范围（无符号）
tinyint	很小的整数	1个字节	-128〜127	0〜255
smallint	小的整数	2个宇节	-32768〜32767	0〜65535
mediumint	中等大小的整数	3个字节	-8388608〜8388607	0〜16777215
int	普通大小的整数	4个字节	-2147483648〜2147483647	0〜4294967295
bigint	大整数	8个字节	-9223372036854775808〜9223372036854775807	0〜18446744073709551615

• 小数类型： （ 浮点数： float、double ；定点数： decimal）

类型名称	说明	存储需求	有效数字位	范围
float	单精度浮点数（四舍五入）	4个字节	小数位8位	-3.40E+38 ~ +3.40E+38
double	双精度浮点数（四舍五入）	8个字节	小数位16位	-1.79E+308 ~ +1.79E+308
decimal(M, D)，DEC	M为数字的最大位数，D为小数点后的位数（不会四舍五入）	M+D个字节	M位，小数位D位	M(1~65)，默认为10；D(0-30)，默认为0

• 日期和时间类型： year、time、date、datetime和timestamp

类型名称	日期格式	存储需求	日期范围
year	YYYY	1个字节	1901〜2155
time	HH:MM:SS	3个字节	-838:59:59〜838:59:59
date	YYYY-MM-DD	3个字节	1000-01-01〜9999-12-3
datetime	YYYY-MM-DD HH:MM:SS	8个字节	1000-01-01 00:00:00〜9999-12-31 23:59:59
timestamp	YYYY-MM-DD HH:MM:SS	4个字节	1970-01-01 00:00:01 UTC〜2038-01-19 03:14:07 UTC

• 字符串类型： 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个成员)

• 二进制类型： bit、binary、varbinary、tinyblob、blob、mediumblob和longblob

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

 

2.数据类型的选择：

• MySQL提供了多种数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。因此应根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。

• 字符串类型是通用的数据类型，任何内容都可以保存在字符串中，包括数字和日期也都可以表示成字符串形式。但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。并且在这种情况下使用数值类型列来存储数字，比使用字符串类型更有效率。

• 在选择数据类型时，首先要考虑这个列存放的值是什么类型的。一般来说，用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。

（1）数值类型：

• 存储的数字是整数，则使用整数类型。如果列的取值范围是1~99999之间的整数，则选择mediumint unsigned类型；如果列的取值范围是-10000~10000之间的整数，则选择smallint类型。

• 存储的数字是整数+小数，则使用小数类型（定点和浮点）。decimal(5,2)所指代的范围是-999.99~999.99，数字的最大位数是5位，小数点右侧是2位，即有两位小数。

（2）日期和时间类型：

• 存储的是年份，则使用year类型。

• 存储的是时间，则使用time类型。

• 存储的是日期，则使用date类型。

• 存储的是日期和时间，则使用timestamp类型或者datetime类型。默认情况下，当插入一条记录但并没有指定timestamp这个列值时，MySQL会把timestamp列设为当前的时间。timestamp类型的值是以UTC（世界标准时间）格式保存的，存储是对当前时区进行转换，检索时再转换回当前时区。而datetime类型的值按实际输入的格式存储，即输入什么就存储什么，与时区无关。

（3）字符串和二进制类型：

• char是固定长度字符，varchar是可变长度字符，所以char的处理速度比varchar的速度要快，但是它的缺点就是浪费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以使用varchar类型来实现。

• char会自动删除插入数据的尾部空格，varchar不会删除尾部空格。

• 对于InnoDB存储引擎，最好使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，所以更适合使用varchar。由于varchar是按照实际的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好。对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列，因此这样可以使整个表静态化，从而使数据检索更快，用空间换时间，所以更适合使用char。

• enum只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用enum。比如，性别字段适合定义，为enum类型，每次只能从“男”或“女”中取一个值。set可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的set值。因此，在需要取多个值的时候，适合使用set类型，比如，要存储一个人兴趣爱好，最好使用set类型。

• text是非二进制字符串，blob是二进制字符串，两者均可存放大容量的信息。text只能存储纯文本文件，而blob主要存储图片、音频信息等。