MySQL [数据 类型]

一：整数类型

MySQL整数类型有5个：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT

范围计算公式为：
整数类型有可选的UNSIGNED属性：表示非负值，可使正数的范围扩大一倍，例如：TINYINT的存储范围是：-128 ~ 127，TINYINT UNSIGNED的存储范围是：0 ~ 255。

那么在设计数据库表字段，并定义为整数类型时，应注意：【重点，敲黑板】

• 一般不用指定整数类型宽度，如ITN(20)，TINYINT(2)…等，除非设定Zero Fill属性，用来填充字符个数。
  字段不会出现负值时，将其设定为unsigned，能扩大正数的存储范围，同时也能免去负值带来的干扰。
  整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。所以定义字段类型时，先考虑整型。
  表的标识列最好选择整数类型，因为很快并且可以使用AUTO_INCREMENT（自增）。
  阿里巴巴Java开发手册中，建表规约 - 第9条，对设计数据库表强制要求如下：
  

二：实数类型

MySQL的实数类型有3个：FLOAT，DOUBLE，DECIMAL

实数是带有小数部分的数字。对比如下：

说明：DECIMAL比DOUBLE，FLOAT存储空间大，存储值范围大，精度高，但是他俩比DECIMAL计算更快（FLOAT/DOUBLE使用CPU原生浮点运算，DECIMAL使用MySQL(>=5.0版本)自身实现的DECIMAL高精度计算）。

那么在设计数据库表字段，并定义为实数类型时，应注意：【重点，敲黑板】

• 尽量只在只在对小数进行精确计算时，使用DECIMAL类型，例如财务数据。
  只指定数据类型，不指定精度（MySQL中FLOAT/DOUBLE/DECIMAL都可以指定精度，但会影响列的空间消耗）。
  使用DECIMAL存储，但数据量很大(上千万条或更多)，可以考虑使用BIGINT代替DECIMAL，把小数乘以相应倍数存在BIGINT中，这样可以同时避免：浮点存储计算不精确和DECIMAL精确计算代价高的问题。
  使用DECIMAL存储，但存储的数据范围超过了DECIMAL的范围，建议将数据拆成整数和小数分开存储。
  
  *

  三：字符串类型

  MySQL的字符串类型：VARCHAR、CHAR、BLOB 和 TEXT

  
  1、VARCHAR 类型**
  【存储空间】
  VARCHAR类型是最常见的字符串数据类型，用于存储可变长字符串，一般比定长类型更节省空间，原因：VARCHAR存储时仅使用必要的空间，越短的字符串使用越少的空间。

定义库表的字段类型时，VARCHAR(100)表示什么？

VARCHAR(100)表示：能存储100个字符，注意字符数是100个，不管你存储的是汉字，符号，字母等等，它的总个数不能超过100个。

VARCHAR(100)中的 100 不表示存储空间的大小，那么存储空间大小怎么计算呢？

        首先VARCHAR需要使用 1 或 2 个额外字节记录字符串的长度，这里的字符串长度是指占用的空间大小，单位是字节。
    如果存储的数据最大长度(空间大小) <= 255字节，则只额外使用 1 个字节记录长度(1个字节8位最大表示的无符号数就是255),否则使用 2 个字节记录。

存储的数据是多大字节？

    这就跟我们具体存什么东西以及编码相关了，比如：
    utf-8编码(小于5.5.3版本)：
        存汉字：一个汉字 = 3个字节
        存英文：一个字母 = 1个字节
    gbk编码：
        存汉字：一个汉字 = 2个字节
        存英文：一个字母 = 1个字节
    utf8bm4字符集（大于5.5.3版本，注意是字符集，后面详细讨论）：
        存普通常用汉字：一个汉字 = 3个字节  (普通汉字：包含在Basic Multiling Plane(BMP)字符中的汉字)
        不常用汉字：一个汉字 = 4个字节
        常用的表情字符emoji：一个表情字符emoji = 4个字节
        存英文：一个字母 = 1个字节

关于MySQL的utf8和utf8bm4之间的爱恨情仇：

    MySQL在5.5.3版本之前使用uft8字符集，MySQL的 'utf8' 实际上不是真正的UTF-8。(可认为是MySQL的一个BUG)
    标准的 UTF-8 字符集编码可以用 1 ~ 4 个字节去编码 21 位字符，几乎包含了世界上所能看到的语言了，但是MySQL的'utf8'支持的字符最大长度是 3 个字节，而标准的 UTF-8 >支持的字符最大长度是 4 个字节。
    也就是说：MySQL的'utf8'只支持到了标准的 UTF-8 字符集中的部分，即基本多文本平面(Basic Multiling Plane(BMP))，包含了控制符，拉丁文，中，日，韩等绝大多数国际字符，但并不是所有，不支持：手机端常用的表情字符emoji和一些不常用的汉字，这些需要4个字节才能编码出来。
    在2010年，MySQL在5.5.3版本后增加了utf8mb4 字符编码，mb4：most bytes 4(最多4字节)，用来兼容之前MySQL-'utf8'不支持的四字节字符，是它的超集。
    现在MySQL版本都到8以上了，所以一般在设计数据库表时，默认都使用utf8mb4字符集。

【库表设计注意点】
1. 一般情况下，数据库的表/字段，请使用utf8bm4编码的字符集。
2. 字符串列的最大长度比平均长度大很多，列更新很少，适合用VARCHAR类型

2、CHAR 类型
【存储空间】
CHAR类型是定长的，CHAR(10)表示可以存储10个字符，MySQL根据定义的字符数分配足够的存储空间。
对比一下CHAR(1) 与 VARCHAR(1) 存储空间:
存储单字节字符(如一个英文字母)：
CHAR(1) : 1个字节
VARCHAR(1) : 1个字节 + 额外记录长度(占用空间大小)的1字节 = 2字节

存储CHAR值时，MySQL会删除所有的末尾空格，不会删除前面和中间的空格；存储VARCHAR值时(MySQL>=5.0版本)，则会保留末尾空格。

【性能】

• 非常短的列：CHAR 比 VARCHAR 在存储空间上更有效率。
• 经常变更的列：CHAR 不易产生碎片，比 VARCHAR 更好。

【库表设计注意点】
很短的字符串，或者所有字符串都接近同一个长度（如密码的MD5值是定长），适合定义为CHAR类型。


3、BLOB 和 TEXT 类型**
BLOB 和 TEXT 都是用来存储很大的数据，BLOB采用二进制存储，TEXT采用字符方式存储。
MySQL把每个 BLOB 和 TEXT值当作一个独立的对象处理，存储引擎在存储时通常会做特殊处理。
怎么个特殊处理呢？
当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储：每个值在表行内用1 ~ 4字节存储一个指针，指向外部存储区域存储的实际值。
对比：

• BLOB: 存二进制数据，没有排序规则或字符集
• TEXT：存字符串，有排序规则和字符集，只对每列的最前max_sort_length字节而不是整个字符串做排序。

若只需要排序前面一小部分字符，可以减小max_sort_length的配置，或使用ORDER BY SUSTRING(column, length)。
**

四：日期和时间类型

MySQL提供了两种相似的日期类型：DATETIME 和 TIMESTAMP

我们从以下三个方面对比这两种日期类型：

• 时间范围
• 存储空间
• 显示格式

【时间范围】
DATETIME：能保存大范围的值，从1001年到9999年，精度为秒。
TIMESTAMP：保存了从1970年1月1日零点(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同，只能表示的范围是：1970年 ~ 2038年。
【存储空间】
DATETIME：把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。
TIMESTAMP：使用4字节的存储空间。
【显示格式】
DATETIME：2008-01-16 22:37:08(MySQL默认情况下)
TIMESTAMP：2008-01-16 22:37:08(MySQL>=4.1版本，按照DATETIME方式格式化)，显示的值依赖时区。

怎么个依赖时区呢？
举例：TIMESTAMP类型字段存储值为0，则在美国东部时区显示为：1969-12-31 19:00:00(不是1970-01-01 00:00:00)，因为与格林尼治时间差了5个小时。

如果在多个时区存储或访问数据，TIMESTAMP提供的值与时区有关系，DATETIME则保留文本表示的日期和时间，跟时区无关，所以它们很不一样。

那么在设计数据库表字段，并定义为日期和时间类型时，应注意：【重点，敲黑板~】

• 除特殊之外，一般情况尽量使用TIMESTAMP，因为比DATETIME空间效率更高。
  一般不要把Unix时间戳存储为整数值，不方便处理。
  MySQL能存储的最小时间粒度为秒，如果要存储比秒更小粒度的日期和时间，可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。