Mysql数据类型与选择规则（mysql5.7）_数据库数据类型怎么选

概述

基于mysql5.7。
mysql数据类型大致可以分为四类：数值型、字符串型、日期和时间类型、空间类型。常用的如数值型的tinyint、int、bigint,字符串型的char、varchar、text，日期型的date、datetime、timestemp等，详细见下图：

详细分析

上表的的一些类型存储规则依赖存储引擎（MyISAM、InnoDB、NDB等）和编码（GBK、UTF8等），（不过大分部用不到，可以先不了解），具体可以看看官方文档

1. 整数型
对于 int、tinyint、bigint、smallint,mysql允许用unsigned 和 zerofill关键字修饰。

UNSIGNED修饰符规定字段只保存正值,即无符号,而mysql字段默认是有符号的。因为不需要保存数字的正、负符号，可以在储时节约一个"位"的空间(即翻一倍)。从而增大这个字段可以存储的值的范围。注意这个修饰符要紧跟在数值类型后面;
!!!
ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL数据库存储负值,如果某列设置为zerofill,那它自动就unsigned。

2. char与varchar

char与varchar都是用来存储字符数据的，其存储规则依赖存储引擎（MyISAM、InnoDB、NDB、BDB、Memory等）和编码（GBK、UTF8系列等）。

对于char
char(m)表示最多插入m个字符，不管是英文字母，还是数字或汉字，不足m则在尾部空格填充，除非开启 PAD_CHAR_TO_FULL_LENGTH。

对于varchar
varchar(m)表示最多插入m个字符，不管是英文字母，还是数字或汉字，不足m不需要空格填充，但varchar需要额外空间记录存入字符串的长度，m<255,花费1byte,m>255,花费2byte。

注意
（1）m表示m个字符，而非字节。
在mysql4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节)。

5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节 ，结束位占去了2个字节。

（2）编码的影响
GBK:汉字占2个字节，最大长度不能超过32766;
UTF8:汉字占3个字节，最大长度不能超过21845。

（3）行长度限制
MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示转化为TEXT or BLOB型。

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

二者性能与选择
性能：char直接存储，不足补空格就好，操作简单，检索效率高。
varchar在存储时需要计算其长度，较慢，但节省存储空间。

选择
根据字符的长度来判断，是考虑其长度的是否相近来确定选择char还是varchar，如何字段的长度基本都是一样或者其长度总是近似的可以选用char。
不过即使使用Varchar数据类型，也不能够太过于慷慨！ 比如你只使用到90个字符，VARCHAR(100)与VARCHAR(200)真的相同吗?结果是否定的。虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。

VARCHAR和TEXT、BlOB类型的区别

VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度
，加上1个字节以记录字符串的长度。对于字符串’abcd’，L是4而存储要求是5个字节。
　　
　　BLOB和TEXT类型需要1，2，3或4个字节来记录列值的长度，这取决于类型的最大可能长度。VARCHAR需要定义大小，有65535字节的最大限制；TEXT则不需要。如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。
　　
一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。

BLOB
可以储存图片,TEXT不行，TEXT只能储存纯文本文件。4个TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT对应于4个BLOB类型，并且有同样的最大长度和存储需求。在BLOB和TEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行，而对TEXT值是大小写不敏感的。换句话说，一个TEXT是一个大小写不敏感的BLOB。

char和varchar可以有默认值，text不能指定默认值。

性能上char>varchar>text。

3. 日期型数据

日期时间类型在MySQL 5.6.4之前创建的表所需的存储与从5.6.4创建的表不同。这是由于5.6.4中的更改允许这些类型具有小数部分，这需要0到3个字节。
mysql5.6.5之前只有timestemp可以自动更新为当前时间，但6.5及以上datetime也可以和timestemp一样了。

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，
  name CHAR(5)
);
1
2
3
4
5

即默认当前时间同时行数据(name)变化时,dt和ts自动更新。

常见类型的选择

姓名：char(20)
价格：DECIMAL(7, 3)
产品序列号：unsigned SMALLINT(5)
文章内容: TEXT
手机号：char(11)
自增主键：unsigned bigint(20)
MD5: CHAR(32)
ip: char(15)或者借用inet_aton/inet_ntoa函数
time: int(10)
email:char(32)

合理的选择数据类型

• 选择合理范围内最小的

  我们应该选择最小的数据范围，因为这样可以大大减少磁盘空间及磁盘I/0读写开销，减少内存占用，减少CPU的占用率。

• 选择相对简单的数据类型

  数字类型相对字符串类型要简单的多，尤其是在比较运算时，所以我们应该选择最简单的数据类型，比如说在保存时间时，因为PHP可以良好的处理LINUX时间戳所以我们可以将日期存为int(10)要方便、合适、快速的多。

  但是，工作中随着项目越做越多，业务逻辑的处理越来越难以后，我发现时间类型还是用时间类型本身的字段类型要好一些，因为mysql有着丰富的时间函数供我使用，方便我完成很多与时间相关的逻辑，比如月排行榜，周排行榜，当日热门，生日多少天等等逻辑

• 不要使用null

  为什么这么说呢，因为MYSQL对NULL字段索引优化不佳，增加更多的计算难度，同时在保存与处理NULL类形时，也会做更多的工作，所以从效率上来说，不建议用过多的NULL。有些值他确实有可能没有值，怎么办呢？解决方法是数值弄用整数0，字符串用空来定义默认值即可。

• 字符串类型的使用

  字符串数据类型是一个万能数据类型，可以储存数值、字符串、日期等。
  保存数值类型最好不要用字符串数据类型，这样存储的空间显然是会更大，而且在排序时字符串的9是大于22的，其实如果进行运算时mysql会将字符串转换为数值类型，大大降低效果，而且这种转换是不会走原有的索引的。
  如果明确数据在一个完整的集合中如男，女，那么可以使用set或enum数据类型，这种数据类型在运算及储存时以数值方式操作，所以效率要比字符串更好，同时空间占用更少。

• VARCHAR与CHAR

  VARCHAR是可变长度字符串类型，那么即然长度是可变的就会使用1，2个字节来保存字符的长度，如果长度在255内使用1个字节来保存字符长度，否则使用2个字符来保存长度。由于varchar是根据储存的值来保存数据，所以可以大大节约磁盘空间。
  如果数据经常被执行更新操作，由于VARCHAR是根据内容来进行储存的，所以mysql将做更多的工作来完成更新操作，如果新数据长度大于老数据长度一些存储引擎会进行拆分操作处理。同时varchar会完全保留内部所有数据，最典型的说明就是尾部的空格。
  CHAR固定长度的字符串保存类型，CHAR会去掉尾部的空格。在数据长度相近时使用char类型比较合适，比如md5加密的密码用户名等。
  如果数据经常进行更新修改操作，那么CHAR更好些，因为char长度固定，性能上要快。

• 数值类型的选择

  数值数据类型要比字符串执行更快，区间小的数据类型占用空间更少，处理速度更快，如tinyint可比bigint要快的多

  选择数据类型时要考虑内容长度，比如是保存毫米单位还是米而选择不同的数值类型

• 整数

  整数类型很多比如tinyint、int、smallint、bigint等，那么我们要根据自己需要存储的数据长度决定使用的类型，同时tinyint(10)与tinyint(100)在储存与计算上并无任何差别，区别只是显示层面上，但是我们也要选择适合合适的数据类型长度。可以通过指定zerofill属性查看显示时区别。

• 浮点数与精度数值

  浮点数float在储存空间及运行效率上要优于精度数值类型decimal，但float与double会有舍入错误而decimal则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。