字段类型

布尔、整数、实数、字符串、日期时间、枚举

分类 字段类型 column 空间占用 位 范围 适用场景
整数 微整型 tinyInt 1字节 8位 0~255 、 -128~127 状态值，枚举值
整数 小整型 smallInt 2字节 16位 0~65535 排序值，权重
整数 中整型 mediumInt 3字节 24位 -8388608~8388607、0~16777215 F
整数 整型 int 4字节 32位 0~4294967295(42.9亿) 小量数据自增列, 低精度金额
整数 长整型 bigint 8字节 64位 0~18446744073709551615 分布式ID，大量数据自增列
—— —— —— —— —— —— ——
实数 单精度非精准浮点数 float(m,d) 4字节 32 m>d, m≤24 接受误差
实数 双精度非精准浮点数 double(m,d) 8字节 64 m>d, m≤65, d<30 接受误差,理论值比bigInt大
实数 精准浮点数 decimal(m,d) 8字节 64 默认decimal(10,0),范围等同double 通常用于财务计算
—— —— —— —— —— —— ——
字符串 定长字符串 char(n)     255个字符(字母、数字以及特殊字符) 手机号、身份证号、定长ID号、MD5
字符串 变长字符串 varchar(n)     最多 65535(表中所有列共享) 个字节 字长偏差较大的字符串，建议n≥20
位 2 bit 4 5 6 单个字符
—— —— —— —— —— —— ——
日期时间 日期 year 1 8 4位:1901/2155、2位:70/69(1970/2069) 年份值
日期时间 日期 date 3 24 1000-01-01/9999-12-31 日期值
日期时间 时间 time 3 24 -838:59:59/838:59:59 时间值或持续时间
日期时间 时间戳 timestamp 4 32 1970-01-01 00:00:00/2037 年某时 2037年以内的数据
日期时间 日期时间 datetime 8 64 1000-01-01 00:00:00/9999-12-31 23:59:59 超过2037年的时间数据
—— —— —— —— —— —— ——
文本 小型文本 tinyText 0~255字节   0~255 短文本字符串
文本 文本 text 0-65535字节   0~65535 长文本数据
文本 中型文本 mediumText 0-16777215字节   0~16777215 中等长度文本数据
文本 极大文本 longText 0-4294967295字节   0~4294967295 极大文本数据
—— —— —— —— —— —— ——
二进制文本 小型二进制字符串 tinyBlob 0~255字节   0~255 不超过255个字符的二进制字符串
二进制文本 二进制字符串 blob 0-65535字节   0~65535 二进制形式的长文本数据
二进制文本 中型二进制字符串 mediumBlob 0-16777215字节   0~16777215 二进制形式的中等长度文本数据
二进制文本 极大二进制字符串 longBlob 0-4294967295字节   0~4294967295 二进制形式的极大文本数据
—— —— —— —— —— —— ——
枚举 枚举类型 enum 4 5 0~65535 65535个以内枚举值,极少更新,支持按指定顺序存储
枚举 枚举类型 set 4 0~64 最多只能包含64个列表项，可存储一个以上的值 64个以内枚举值,极少更新
—— —— —— —— —— —— ——
布尔类型   boolean 1   ture、false 非此即彼
分类 字段类型 column 空间占用 位 范围 适用场景
1 2 3 4 5 6 7

整数类型

无符号数值范围：0 ~ 2<sup>n</sup> - 1
有符号数值范围：-2<sup>n-1</sup> ~2<sup>n-1</sup> - 1
int(n),无论n等于多少,int永远占4个字节；
n表示显示宽度，设置unsigned zerofill的前提下 不足用0补，数字长度超过n则显示整个数字

浮点数

float和double在不指定精度时，默认会按照实际的精度来显⽰，
DECIMAL在不指定精度时，默认整数为10，⼩数为0
decimal类型的数据，如果数据超过了精度和标度值，系统会报错
近似值：decimal采⽤的是四舍五⼊，float和double采⽤的是四舍六⼊五成双

四舍六⼊五成双:

5以下舍弃,5以上进位
5后⾯是否还有不为0的任何数字

    如果有，则直接进位
    如果没有

        看5前⾯的数字
        
            若是奇数则进位，
            若是偶数则将5舍掉

日期时间

timestamp满足使用时，比datetime更优
datetime 建议的默认时间 1970-01-01 08:00:01, 兼容大数据处理(Java认为timestamp的最小时间值)

varchar(n) 如果值的长度过大，则被转换为 TEXT 类型

*不建议使用的类型：

  • bit
  • BLOBs (Binary Large OBjects)

bigIng类型

Tip

mysql 的 bigInt 类型 数值 传递给前段时 需要强转为 string 类型，否则会丢失最后一位精度(四舍五入)

mysql 的 字段(如：goods_sn) char/varchar 类型 存储的值是 binInt 数值时， 字段作为查询条件，需要显式转换数据类型为 string,否则会出现异常

goods_sn = ‘291512717661962240', 会查询到 goods_sn = ‘291512717661962241‘的记录

SELECT `goods_sn`, `tenant_id`, `column_type`, max( id ) AS id 
FROM `ic_product_variation_edit_log` 
WHERE `tenant_id` = 500001 AND `goods_sn` = 291512717661962240  
GROUP BY `goods_sn`, `tenant_id`, `column_type`

| goods_sn | tenant_id | column_type | id | | —- | —- | —- | —- | | 291512717661962241 | 500001 | last_purchase_at | 1998 | | 291512717661962241 | 500001 | re_purchase_price | 1984 |

binInt 数值作为 字符串列的查询参数时,应显式转换为字符串

SELECT `goods_sn`, `tenant_id`, `column_type`, max( id ) AS id 
FROM `ic_product_variation_edit_log` 
WHERE `tenant_id` = 500001 AND `goods_sn` = '291512717661962240'
GROUP BY `goods_sn`, `tenant_id`, `column_type`

| goods_sn | tenant_id | column_type | id | | —- | —- | —- | —- | | (N/A) | (N/A) | (N/A) | (N/A) |

json

  • 存储前建议排序，比如存商品规格，应避免重复，排序后更方便
  • 注意数据类型格式化，避免 [{"goods_sn": "326540296949071872", "quantity": "1"}] 和 [{"goods_sn": "326540296949071872", "quantity": 1}]