数字类型

整形类型

整形中分为signed有符号(默认)和unsigned无符号。

type-number.png

  • 一般情况下不建议设置为无符号，因为会导致一些问题

    例如：MySQL要求设置为unsigned的两个值相减的结果仍然为unsigned，否则会导致抛出[22001][1690] Data truncation: BIGINT UNSIGNED value is out of range in 'xxx'异常。
    如果确实要使用，可以通过设置SET sql_mode='NO_UNSIGNED_SUBTRACTION';来关闭这个检查。

  • 自增类型作为主键时，务必要使用BIGINT，而不是INT

    因为INT最大值为2147483647，很容易达到上限，达到上限后再次插入会导致主键冲突。

  • MySQL8.0之前，自增整型会有回溯问题

    当删除ID为4的记录后，如果MySQL服务器重启，那么下一个插入的ID依旧可能为4

浮点类型和高精度类型

  • 浮点类型：FLOAT和DOUBLE在存储时会有精度问题，且在MySQL后续版本中可能会被废弃，建议使用高精度DECIMAL类型

    DECIMAL类型在存储时通过DECIMAL(precision,scale)来设置其长度和小数点后的位数。

  • 涉及到金额的字段，建议使用BIGINT整型类型，而不是高精度DECIMAL类型

    使用DECIMAL类型时，其长度不好确定，导致存储空间碎片化，而使用整型，其长度统一，便于数据库整理。
    type-number.png
    同时，整型类型的计算速度更快。

字符串类型

  • 一般情况下，建议使用VARCHAR类型，而不是CHAR类型，因为对于变长字符集utf8mb4来说，底层都是变长存储

  • 默认情况下，VARCHAR类型可以设置在最大字符数为65535/所选字符集下一个字符存储的字节数

    例如：当选用utf8mb4字符集时，一个字符使用4个字节存储，当设置的长度超过65535/4=16383时，会抛出［42000]［1074］ Column length too big for column 'xxx' (max=16383); use BLOB or TEXT instead.异常。

  • 建议将字符集设置为utf8mb4，以支持emoji表情等特殊字符

  • 排序规则一般选用大小写不敏感的utf8mb4_0900_ai_ci

    ci：大小写不敏感(默认)
    cs：大小写敏感
    ai：重音不敏感
    as：重音敏感
    bin：二进制排序

  • 修改表和字段字符集的命令

    1
    ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

  • MySQL高版本中，性别字段建议使用char(1)类型，并通过check约束来限制其值

    1
    CONSTRAINT `user_chk_1` CHECK (((`sex`=_utf8mb4'F') OR (`sex`=_utf8mb4'M')))

  • 密码等隐私字段，建议通过动态盐+动态加密算法来加密存储

日期时间类型

  • TIMESTAMP最大的优点是带有时区信息，如果业务需要不同国家的时间，建议使用TIMESTAMP类型

  • TIMESTAMP类型存储上限为2038-01-19 03:14:07，需要注意潜在风险

  • 使用TIMESTAMP类型时，建议显式设置时区，不要使用默认系统时区，会存在较大的性能问题

    推荐在配置文件中设置time_zone='+8:00'

  • 如无特殊需求，日期建议使用DATETIME类型，而不是TIMESTAMP和INT类型

    在使用时可以通过DATETIME(N)中的N来设置毫秒的精度，N的范围为0-6，默认为0
    插入或更新时的默认时间可以通过CURRENT_TIMESTAMP(N)来设置
    TIMESTAMP如果设置毫秒数则占用7个字节，而DATETIME无论是否设置毫秒数都占用8个字节，所以在存储空间上几乎没有太大区别