2.1. 数据类型

字段类型选择原则

1. 使用可以正确存储数据的最小类型，占用更少的磁盘、内存、CPU。

2. 尽量选择简单的数据类型，整型比字符操作代价低、时间应该使用MySQL内建类型而不是字符串，ip应用整型，32位整数。

3. 尽量避免NULL，指定列为NOT NULL，可为NULL的列很难优化，索引、索引统计、比较都比较复杂，也会占用更多的存储资源，MySQL中也需要特殊处理。

2.1.1整数类型

• 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别占用8，16，24，32，64位。

• 可选UNSIGNED属性，无符号，正数，值可以扩大一倍。

• 指定宽度，INT（11），没有意义，不会限制值的范围，只是规定交互工具的显示字符个数。

2.1.2 实数类型

• 类型：FLOAT、DOUBLE、DECIMAL，4字节、8字节、支持65个数字的字符串。

• DECIMAL可以存储比BIGINT大的数、也可以用于小数的精确计算。

• DECIMAL可以指定小数点前后允许的最大位数，DECIMAL(18,9)，前后各9位。

2.1.3 字符串类型

2.1.3.1 类型：VARCHAR、CHAR

• VARCHAR：用于存储可变长字符串，仅使用必要的空间，需要1-2个字节记录字符串长度，最大长度<=255使用1个字节，否则使用两个。适用于字符串长度不一致，差距较大，更新少的情况。

• CHAR：定长存储，存储时会删除末尾空格，适合存储短字符串、所有值接近一个长度、经常变更的数据。

• 变更频繁不适用VARCHAR的原因，如果更新后的字符串比原有字符串长则可能会产生空间碎片。

2.1.3.2 二进制字符串：BINARY、VARBINARY（定长、可变长，与CHAR、VARCHAR类似）

• 存储二进制字符串，BINARY填充时使用\0，而非空格。比较速度比CHAR快。

• 超长字符串类型：BLOB、TEXT，分别使用二进制和字符串存储。（很少使用）

2.1.3.3 TEXT家族：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT

• BLOB家族：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB

• 原理：值太大时，使用外部存储区进行存储，表中使用1-4个字节存储一个指针。

• 排序：对每列的前max sort length字节的字符串进行排序（配置文件），或者使用 order by sustring(col,length)

• 查询如果需要 TEXT BLOB 类型排序，一定要使用 order by sustring(col,length)。

2.1.3.4 枚举 (ENUM) 代替字符串

• 枚举保存一个字符串 Set， 行内值保存为整数，与枚举值一一对应。
• 优点是查询时 值比较 比 String 类型快很多，可以提高查询速率，仅限于 ENUM 与 ENUM 列比较， ENUM 与 string 列比较会比 string string 慢，原因是每次比较，ENUM 需要再去查询对应的 String。故 ENUM ENUM > string string > ENUM string。
• 缺点是字符串 Set 是固定的，删除 String 需要使用 alter table 对表结构进行变更，添加 String 不需要。
• ENUM 使用方式

  create table enum_test(
    e    enum('fish', 'apple', 'dog') not null
  )
  

2.1.4 日期与事件类型

• TimeStamp 只能表示 1970-> 2038 年，显示的值依赖于时区。从 1970 年开始的秒数，以 DateTime 格式展示。
• MySQL提供 From_UnixTime() 函数把 Unix 时间戳转化为日期， Unix_TimeStamp() 把日期转换为 Unix 时间戳。
• DateTime 保留文本表示的日期与时间。
• TimeStamp 比 DateTime 空间效率高，TimeStamp 使用 4 个字节，DateTime 则为字符串。
• 应尽量使用 TimeStamp 以存储时间。

  2.1.5 位数据类型

• BIT 底层实际上是字符类型，’00111001’ 值返回 会变成’57’,不建议使用。还不如在服务内存中做位运算，DB里存整形来的实在。
• SET：集合类型，有点类似于 ENUM 变更时也需要更高表结构

  2.1.6 特殊类型数据

• 使用无符号整数存储 IP 地址，MySQL提供 INET_ATON() 和 INET_NTOA() 在 Ip 与 无符号整型之间转换。