字段设计

开发者在进行字段设计时，主要需要考虑字段的类型，字段的长度。本文给出几个场景字符类型的推荐值，旨在帮助开发者规范字段设计。

MySQL 模式

• 数值型字段。

  推荐使用 bigint 类型替代 int、smallint 等整型类型，防止以后范围超限。

• 字符型字段。

  • 所有动态字符串建议全部使用 VARCHAR(N) 类型。

  • 仅仅只有单字符的字段使用 CHAR(1)。表达是与否概念的字段，建议使用 CHAR(1) 类型以节省空间（1 代表 TRUE，0 代表 FALSE），值的内容要统一，所有应用值要统一，例如：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。

  注意

  NUMBER(1) 也可以表达是与否的概念，但占用的空间更大些。

  • 列的类型禁止使用 NVARCHAR、NCLOB 等类型。

  注意

  字符数据类型的列可以存储所有字母数字值，但是 NUMBER 数据类型的列只能存储数字值。

• 日期时间字段。

  • 有时间精度要求的业务，可以使用 datetime(6)。

  • 对精度没要求的，设置为 datetime 即可。

  • 如果将来有国际化需求，建议使用 timestamp。

  • 不推荐使用字符作为时间字段的数据类型，在使用的时候容易造成隐式类型转换

• 数据字段的选择推荐。

  特别是在大表上(百万级)，推荐如下使用方法：

  • 业务内各表时间字段务必统一，推荐使用 DATE 类型，对于精度较高的业务可以使用 TIMESTAMP 类型。

  • IP 所在表如果是大表，推荐使用 NUMBER 数值类型存储，可节省存储空间。前端进行转换。使用数值范围大小跟网段数据保持一致。

  • 根据业务需要，IPv4 和 IPv6 也可以分字段存储，采用 VARCHAR(N) 存储。

Oracle 模式

• 数值型字段

  • 推荐使用 NUMBER 类型存储。当 NUMBER 存储变长、十进制精度的定点数时，写法为NUMBER(p,s)；当 NUMBER 存储浮点数时，写法为 NUMBER。

  • 小数类型的字段推荐使用 DECIMAL 不推荐使用 BINARY_FLOAT 和 BINARY_DOUBLE，BINARY_FLOAT 和 BINARY_DOUBLE 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。

  • 隐式类型转换时的优先级

    BINARY_DOUBLE 的优先级最高，其次是 BINARY_FLOAT，最后是 NUMBER。

  • 数值字段的取值范围

    类型	取值范围	长度(字节数)
    NUMBER	1.0 E-130F ~ 1.0 E +126 F（不包括1.0 E +126 F）	4~40
    BINARY_FLOAT	1.17549E-38F ~ 3.40282E+38F	4
    BINARY_DOUBLE	2.22507485850720E-308 ~ 1.79769313486231E+308	8

• 字符型字段。

  • 推荐使用 VARCHAR2。

  • 比较 VARCHAR2 类型时，按照非填充空格的模式进行比较；而比较 CHAR 类型时，按照填充空格的模式比较。

  • 日期时间字段。

    • TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 两个类型是感知时区的，请注意时区差。

    • 不推荐使用字符作为时间字段的数据类型，在使用的时候容易造成隐式类型转换。

其他

• 自增列字段：必须使用 bigint 类型，禁止使用 int 类型，以防止存储溢出。

• 禁止使用外键自引用和级联删除更新的表字段约束定义，以避免重复删除的问题。

• 尽量避免使用枚举列类型：enum('x','y','z'), 应使用字符串类型替代。

• 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

• 字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循：

  • 不是频繁修改的字段

  • 不是超长字段

• 发生隐式类型转换时，数值类型的优先级低于时间类型，高于字符和所有其他数据类型。

• 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

  无符号值可以避免误存负数，且扩大了表示范围，不同的数值范围推荐不同的数据类型，示例如下：

  对象	年龄区间	类型	表示范围
  人	150 岁之内	unsigned tinyint	无符号值：0 到 255。
  龟	数百岁	unsigned smallint	无符号值：0 到 65535。
  恐龙化石	约数千万年	unsigned int	无符号值：0 到约 43亿。
  太阳	约 50 亿年	unsigned bigint	无符号值：0 到约 10的 19 次方。