MySQL数据类型详解

MySQL数据类型预览：

整型类型：
具体类型	长度(byte)	范围	说明
tinyint	1	-128~127  0~255(unsigned)
smallint	2	-32768~32767  0~65535(unsigned)
mediumint	3	-8388608~8388607  0~16777215(unsigned)
int	4	-2147483648~2147483647  0~4294967295(unsigned)
bigint	8	-2^63 ~ 2^63-1   0~2^64-1(unsigned)
decimal(M,D)	M+2	默认为10 最大65	以二进制格式存储值,存储精确的数值数据
numeric(M,D)	M+2	默认为10 最大65	以二进制格式存储值,存储精确的数值数据,在MySQL中,NUMERIC实现为DECIMAL
float(M,D)	4	0~23	存储近似数字数据值
double(M,D)	8	24~53	存储近似数字数据值
bit(M)	5	1~64	用于存储比特值,左侧用零填

字符串类型：
具体类型	长度(byte)	范围	说明
char(M)	M	0~255	固定长度，其他用空格填充
varchar(M)	M	0~65535	65535在所有列中共享长度，utf8编码中长度为21,845
binary(M)	M		以字节为单位
varbinary(M)	M		以字节为单位
text(M)		0~65535	长度为65535字节，存放文本
blob(M)		0~65535	使用指针方式存储二进制数据

日期类型：
具体类型	长度(byte)	范围	说明
year	1	1901~2155
date	4	'1000-01-01'~ '9999-12-31'
time	3		以字节为单位
timestamp	4	'1970-01-01 00:00:01'~'2038-01-19 03:14:07'	timestamp只允许出现一次，取出总是转换为当地时区
datetime	8	'1000-01-01 00:00:00'~'9999-12-31 23:59:59'

其他类型：
具体类型	长度(byte)	范围	说明
enum	1或2个字节	0~65535	最多可以包含65535个元素
set		0~64	是一个字符串对象，可以包含零个或多个值

数值类型存储方式：

所有的数值类型，在内存中都转化为二进制数值：
如存储1这个数值，用int类型存储，那么在内存中的存储方式为：
00000000 00000000 00000000 00000001
因为int类型占用4字节，而一字节等于8比特，那么存储长度则为32比特的长度。
如果使用tinyint类型存储，那么在内存中的存储方式为：
00000001

有符号类型：
由于符号要占用掉一个比特位，如果存储-1这个数值，那么在内存中存储方式为：
11111111

负数在内存中都是以补码方式存储的，如-1：
1的原码是00000001，那么取反得到反码：11111110，加一得到补码：11111111

数值类型后面的括号是用来限定显示宽度，仅仅影响显示效果，不会限制存储长度，比较，计算等等

字符串类型存储方式：

cahr和varchar的主要区别：
char类型声明为”定长字符类型”，它严格限定空间长度，括号里限定的是字符的长度，严格存储M个字符，如果不足则以空格不全，如果超过长度，则会报错。
varchar声明为"变长字符串类型"，它存储数据时使用额外的字节的bit位来标记某个字节是否存储了数据。在utf8编码中，规定了一个字符占用三个字节，那么它最多存储65536/3=21845个utf8字符
char与varchar存储长度比较：

值	CHAR(4)	需要存储	VARCHAR(4)	需要存储
''	'    '	4字节	''	1个字节
'ab'	'ab  '	4字节	'ab'	3个字节
'abcd'	'abcd'	4字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4字节	'abcd'	5个字节

日期类型存储方式：

日期时间型数据存储时需要使用引号包围，避免和数值类型的数据产生歧义。格式也较为宽松，可以使用任意分隔符来表示

ENUM数据类型：

ENUM是一个字符串对象，可以允许从列表中选择值，定义方式为ENUM(‘value1’,‘value2’…)，这些值在表创建时在列规范中显式枚举。它具有以下优点：

1、在列具有有限的可能值集的情况下的紧凑数据存储。您指定为输入值的字符串将自动编码为数字。

2、可读的查询和输出。数字将转换回查询结果中的相应字符串。
在存储是忽略大小写，空格会被截断，同时enum在存储时是通过index方式进行存储
如：ENUM(NULL, ‘’, ‘a’,‘b’,‘c’,’’)：

值	index
NULL	NULL
''	0
'a'	1
'b'	2
'c'	3
''	0

不建议使用enum数据类型，因为它不会在存储保存在适当 TINYINT或 SMALLINT类型，很容易混淆，且在修改类型时会使用copy算法使修改值变得更为复杂

数据类型属性：zerofill：

使用zerofill类型修饰字段后，不足字段显示部分将使用0来代替空格填充，启用zerofill后将自动设置unsigned。zerofill一般只在设置了列的显示宽度后一起使用