MySQL系列(二)--数据类型
如何选择优化的数据类型：
当一个列有多种数据类型可以选择，选择顺序：数字类型、日期/二进制类型、字符类型，相同级别的数据类型，优先选择占用空间小的类型
1、通常更小的更好
相同级别的数据类型，选择占据空间更小的数据类型。更小的数据类型通常更快，因为占用更少的磁盘、内存和CPU缓存，处理时需要的
CPU周期也更少，但是要确保需要存储值的范围
2、简单更好
一个列可以选择多种数据类型
1、优先选择整形，整型比字符操作的代价更低
2、其次是日期和二进制类型
3、最后是字符类型
4、使用MySQL内置的类型date、time、datetime而不是String存储时间和日期
5、使用整型存储IP地址
3、尽量避免NULL
1、定义表结构是没有指定列为NOT NULL，默认都是可以为NULL的，除非是真的需要存储NULL值，否则最好指定列为NOT NULL
2、如果查询可为NULL的列，对MySQL来说更难优化，因为可为NUL的列使索引、索引统计和值比较都更复杂。可为NULL的列使用更多的存储空
间，在MySQL中也要特殊处理。
3、但是InnoDB使用单独的位(bit)存储NULL值，所以对于稀疏数据（很多值为NULL，只有少数行的列有非NULL值）有更好的空间效率，但是不
适用于MyISAM
所以，选择数据类型是，第一步需要确认合适的大类型：数字、字符串、时间等。
选择具体类型
MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同、物理空间不同
例如：DATETIME和TIMESTAMP都可以存储时间和日期，但是后者只使用前者一半的存储空间，并且会根据时区变化，有自动更新能力。
整数类型：
存储范围：从-2^(N-1)到2^(N-1)-1，N为存储的位数
| TINYINT | -128 | 127 |
| SMALLINT | -32768 | 32767 |
| MEDIUMINT | -8388608 | 8388607 |
| INT | -2147483648 | 2147483647 |
| BIGINT | -9223372036854775808 | 9223372036854775807 |
可选UNSIGNED属性，表示不允许负值，大概可以使正数的上限提高一倍，例如TINYINT UNSIGNED存储的范围是0~255，而TINYINT的存储范围
是-128~127
然而整数计算一般使用64位的BIGINT,即使在32位的环境（一些聚合函数是例外）
宽度：
宽度对大多数应用都是无意义的，只是显示字符的个数，对于存储和计算来说，INT(1)和 INT(20)是相同的，不要以为int(2)就是存储
两位，能节省空间。
如果int(3)，插入的数据为123456，显示还是123456。如果不足3位，左边补空格，也可以选择UNSIGNED ZEROFILL，左边补0。
浮点数/定点类型:
float和double都是浮点型，保存数据可以不指定精度，默认会按照实际的精度来显示。
decimal为定点型，如果不指定精度，默认为(10,0)。
(M,D)，M为精度，D为标度。M表示该值的总共长度，D表示小数点后面的长度。
整数部分不能超过定义的位数，否则出现异常。小数部分可以，但是只保留指定位数，会进行四舍五入。
CPU不支持DECIMAL的直接计算，而是MySQL服务器本身实现了DECIMAL的高精度计算，相对而言，CPU支持原生浮点计算，所以浮点计算明显更快。
MySQL5.0+将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如，DECIMAL(18,9)小数点两边各保存9个数字，一共使用9个字节
，小数点占用1个字节，最多65个数字
浮点型相比DECIMAL存储同样范围的值时，使用更少的空间，所以只有对小数进行精确计算时才使用DECIMAL--例如存储财务数据。因为浮点型
很可能出现误差。但是数据量很大的时候，可以使用BIGINT代替。
PS：浮点型是非精确的数据类型
时间类型:
DATETIME：
以YYYY-MM-DD HH:MM:SS.[.fraction]格式存储时间，默认精确到秒，可以通过datetime(6)=YYYY-MM-DD HH:MM:SS.fraction来表示微妙，微妙
最大是6位
与时区无关，占用8个字节的存储空间
TimeStamp：
以YYYY-MM-DD HH:MM:SS.[.fraction]格式存储时间，从1970-01-01到2038-01-19
与时区相关，占用4个字节，实际上以int类型存储
在行被修改的时候可以自动修改timestamp列的值，一般是数据修改时间
如果存在多个timestamp列，默认只会更新第一个timestamp列
测试：
DROP TABLE IF EXISTS temp; CREATE TABLE temp (t1 datetime, t2 TIMESTAMP); INSERT INTO temp VALUES(NOW(),NOW()); SELECT * FROM temp; SET time_zone = '-10:00'; ALTER TABLE temp MODIFY t1 datetime(3), MODIFY t2 TIMESTAMP(3);
Date：
占用的字节数比字符串、datetime、int要少，只需要3个字节
可以利用日期时间函数进行计算
Time：
只存储时间类型
日期类型使用注意事项：
不要使用字符串存储时间类型，因为字符串类型比日期类型占用空间大，日期类型可以进行对比，而且有丰富的函数可以使用
int类型不如timestamp类型
CURRENT_DATE() 返回当前日期值，不包括时间部分
NOW() 返回时间和日期值
varchar和char：
varchar存储特点：
1、char会将字符串末尾的空格自动删除，首部的空格不会删除，varchar不会
2、varchar(n)，n是字符的长度，而不是字节的长度，例如存放"abcdefg"，设置varchar(7)
3、列的最大长度小于255只占用一个额外字节用于记录字符串长度，如果大于255，会占用2个额外字节
varchar长度选择：
1、使用最小符合需求的长度
2、不要后期才对varchar长度进行修改，在MySQL5.7之前，长度修改会导致锁表，5.7之后，字段长度在255以内，才不会锁表
varchar(5)和varchar(200)存储'hello'的空间开销是一样的，性能不同：
更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内布置。尤其是使用内存临时表进行排序或操作时会特别糟糕。
还有利用磁盘临时表进行排序的时候。
varchar使用场景：
1、列的最大长度比平均长度大很多，因为varchar长度是可变的
2、列很少被更新，长度更新，会产生页分裂的情况，产生存储碎片
3、使用多字符集存储字符串，例如utf8，中文3个字节，英文一个字节
char存储特点：
1、char类型是定长的
2、char类型的列会删除末尾的空格
3、char类型的最大宽度255
CHAR使用场景：
1、几乎定长的字符串，例如密码的md5值（定长）、身份证号码和手机号等
2、短字符串，例如Boolean类型Y和N，char要1byte，而varchar要2byte，因为还有一个计算长度的额外字节
3、经常更新的字符串列，因为不容易产生碎片
二进制字符串类型：
BLOB和TEXT类型：为存储更大的数据而设计，分别采用二进制和字符方式存储，但是尽量避免使用
字符类型： TINYTEXT/SMALLTEXT/TEXT/MEDIUMTEXT/LONGTEXT
二进制类型： TINYBLOB/SMALLBLOB/BLOB/MEDIUMBLOB/LONGBLOB
两种类型区别：
BLOB类型存储的是二进制数据，没有排序规则（和其它类型的排序不同,通常对每一列的最前max_sort_length字节进行排序，可以根据需求进行
配置或者使用ORDER BY SUSTRING(column,length)）和字符集，而TEXT类型有字符集和排序规则
