Mysql数据类型详解
MySQL数据类型包括
1.整数类型，浮点数类型和定点数类型
2.时间和日期类型
3.字符串类型
4.二进制类型
整数类型
标准的SQL中支持INTEGER,SMALLINT这两类整数类型，MySQL除了这两个还有其他的，见下表(1字节等于8位 2^8=256 2^16=65536...)
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 默认宽度 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 4 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 6 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 8 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 11 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 20 |
PS：MySQL中BOOLEAN类型就是TINYINT类型
INT和INTEGER类型的字节数和取值范围是一样的，其实，在MySQL中INT和INTEGER类型是一样的
为什么TINYINT的无符号范围是(0,255)，因为TINYINT的占1个字节，1个字节是8位，那么TINYINT无符号数最大值就是28-1，即255，TINYINT有符号数最大值是27-1，即127.
同理，其他类型也是这么算的
MySQL支持数据类型的名称后面显示宽度，其基本形式为 数据类型(宽度值)
问：为什么TINYINT的默认宽度是4，而不是3，最大值是255吗？
答：如果是有符号的情况，就会有-128的值，它的宽度是4.
当插入的数据显示宽度大于设置的显示宽度时，数据依然可以插入；例如在INT(4)中插入111111仍然可以显示111111，所以显示宽度并不能限制数据的最大值，数据的最大值是由数据类型决定的，那么INT(4)到底起什么作用呢。当给字段设置INT(4) ZEROFILL时，ZEROFILL参数表示数字不足的显示空间由0来填补，注意，加上ZEROFILL参数后，字段将自带UNSIGNED属性，只能表示无符号整数
整数类型还有一个AUTO_INCREMENT属性，该属性可以使字段成为自增字段
浮点类型和定点数类型
MySQL中浮点数类型包括FLOAT(单精度浮点数)，DOUBLE(双精度浮点数)，DECIMAL(定点数)类型
在MySQL中可以指定浮点数和定点数的精度，其基本形式为
数据类型 (M,D) 其中M参数称为精度，是数据的总长度，小数点不占位置；D参数称为标度，是小数点后的位数；例如1234.56这个数的类型应该为FLOAT(6,2);
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| FLOAT | 4 字节 | (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
上述指定小数精度的方法虽然适合于浮点数和定点数，但不是浮点数的标准用法；建议使用浮点数时，如果不是实际情况需要，最好不要使用浮点数，不然可能会影响数据的迁移；对于定点数而已，DECIMAL是定点数的标准格式，一般情况下可以选择这种数据类型
PS：所谓浮点数就是小数点后的位数是不定的，浮动的.....
如果插入值的精度高于实际的精度，系统会自动进行四舍五入处理，例如向FLOAT(6,2),DOUBLE(6,2),DECIMAL(6,2)中分别插入3.143，3.145，3.1435;其结果为3.14，3.15，3.14 ；同时最后一个类型的数据插入会发出警告；
如果我们不指定类型的精度，浮点数和定点数有其默认的精度，FLOAT和DOUBLE会保存实际精度，这与系统和硬件精度有关；DECIMAL默认为整数(四舍五入)；例如我们插入3.143，3.145，3.5435，显示结果为3.143，3.145，4；
定点数是以字符串形式存储的
日期和时间类型
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 4 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038-01-19 11:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
首先 DATETIM和TIMESTAMP类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.
其次，TIMESTAMP类型在默认情况下，insert、update(如果有更新) 数据时，TIMESTAMP类型字段会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
第三，TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响
字段的TIMESTAMP类型默认会补充为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
补充：
1、TIMESTAMP列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为null。
2、TIMESTAMP列不可以设置值，只能由数据库自动去修改。
3、一个表可以存在多个TIMESTAMP列，但只有一个列会根据数据更新而改变为数据库系统当前值。因此，一个表中有多个TIMESTAMP 列是没有意义，实际上一个表只设定一个TIMESTAMP列。
4、TIMESTAMP列的默认值是CURRENT_TIMESTAMP常量值。当纪录数据发生变化的时候，TIMESTAMP列会自动将其值设定为CURRENT_TIMESTAMP。
所以一般来说，我比较倾向选择DATETIME，至于你说到索引的问题，选择DATETIME作为索引，如果碰到大量数据查询慢的情况，也可以分区表解决。
问：为什么YEAR类型只占用1个字节
答：YEAR类型的取值范围是1901到2155和0000，共256种情况，个人认为实际存储的是0-255的值，前面讲过这个范围正好占1个字节
YEAR超出取值访问的值都为0000
字符串类型
字符串类型指CHAR、VARCHAR、ENUM、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| ENUM | 0-65535个 | |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR和VARCHAR的区别
char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：
char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）
ENUM枚举类型
基本形式为 属性名 ENUM('值1','值2','值N');
ENUM类型只能取列表中的一个元素，其取值列表中最多用65535个值，如果ENUM类型加上了NOT NULL属性，则其默认的值为取值列表中第一个元素，如果没加NOT NULL，则允许插入NULL值
