MySQL中数据类型

1、字符类型

• List itemchar是固定长度的字符类型，它的处理速度比varchar快，缺点是浪费存储空间，当实际存储的值小于指定的长度时会以空格来填充，对于长度变化不大并且对查询速度有较高的要求可以选择char。适合存储用户密码的MD5哈希值，手机号，性别，因为它的长度总是一样的。对于经常改变的值，char也好于varchar,因为固定长度的行不容易产生碎片，对于很短的列，char的效率也高于varchar。char(1)字符串对于单字节字符集只会占用一个字节，但是varchar(1)则会占用2个字节，因为1个字节用来存储长度信息 。如果存储的字符串长度几乎相等，使用char定长字符串类型。

• List itemvarchar是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。varchar的长度是字符长度，而不是字节长度。varchar还会使用额外的存储空间来记录可变字符串的长度

• List item列的最大长度小于255则只需要额外占用一个字节来记录字符串的长度

• List item列的最大长度大于255则需要额外占用两个字节来记录字符串的长度
  不同存储引擎对char和varchar的使用原则不同，myisam:建议使用固定长度的数据列代替可变长度。innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高
  2、数值类型

• List item金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度

• List item如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确

• List item如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围

• List item不建议使用ENUM、SET类型，使用TINYINT来代替
  

• List itemdecimal不会经度丢失，但是会占用更多的存储空间，占用空间越大就需要越多的磁盘IO，磁盘IO是影响MySQL性能的一个最重要的因素，所以还需慎用，对于金额直接使用bigint存储分
  

误区：
创建表时我们经常看到长度这一列，例如 tinyint(2)，对于整型来说小括号中的2不是指的存储长度，而是指的零填充，对于字符串指的是长度，zerofill零填充，当数据的长度小于指定的长度时，会使用0来填充缺失的长度。

3、日期类型

• List item如果应用只需要记录年份，那么仅用一个字节的year类型。

• List item如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31

• List item如果记录时间时分秒使用它time类型

• List item如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多

• List item如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应

• List itemdatetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6)

• List itemtimestamp与datetime，两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期；都可以使用自动更新CURRENT_TIMESTAMP

• List item对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

• List itemtimestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’

• List itemdatetime占用8个字节 ：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大

注意：

禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算；也尽量不要使用int来存储时间戳

4、是否为null
MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。

• List item在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”， 而空值”“是不占用空间的。

• List item含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。

• List item联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义

注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。

1. NULL<>NULL的返回结果是NULL，而不是false。
2. NULL=NULL的返回结果是NULL，而不是true。
3. NULL<>1的返回结果是NULL，而不是true。
   5、存储引擎
   常用的存储引擎的选择有MYISAM、InnoDB、MEMORY，不同的存储引擎支持的功能不一样，MySQL5.5之后默认的是InnoDB。绝大部分场景都是使用InnoDB引擎。

• List itemMYISAM 不支持事务, 不支持外键，其优势是访问速度快，对事务完整性没有要求或者以select、insert为主的应用程序可以选择这个引擎，支持全文索引，表锁，注意：MYISAM 在删除数据时好像类似于逻辑删除，需要定时物理删除，清理碎片：optimize table 名称；

• List itemInnoDB 支持事务，不支持全文索引，标锁，支持外键

• List itemMEMORY：查询速度极快，数据在内存中不持久化，数据库重启数据就消失，类似于缓存的作用memcache
  表引擎取决于实际应用场景；日志及报表类表建议用myisam，只读的表；与交易，审核，金额相关的表建议用innodb引擎。

mysql5.0之后以通过alter table语句来修改存储引擎。alter table tbl_user engine = innodb;