整数类型
整数类型 | 占用储存 | 记录的数字范围 |
---|---|---|
TINYINT | 1B | [-128,127] |
SMALLINT | 2B | [-32K,32K] |
MEDIUMINT | 3B | [-8M,8M] |
INT | 4B | [-2B,2B] |
BIGINT | 8B | [-9Z,9Z] |
我们用括号表示显示位数,如INT(4)表示0001,注意这只影响MySQL如何显示数字而不影响如何保存数字
定点数类型和浮点数类型
定点数:
DECIMAL(m,n) m为最大的有效数字位数,n为小数点后小数位数
例:DECIMAL(9, 2) 为1234567.89 总共最多9位,小数点后两位,整数部分最多7位
注:DEC / NUMERIC / FIXED同理,但几乎不用
浮点数:
- FLOAT 浮点数类型,占用4B
- DOUBLE 双精度浮点数,占用8B
浮点数不是精确值而是近似值,这也正是它能表示更大范围数值的原因
字符串类型
常用:
- CHAR 固定长度的字符串
- VARCHAR 可变字符串,最多能储存 64KB, 约 65k 个字符
储存较大文本:
- MEDIUMTEXT 最大储存16MB(约16百万的英文字符),适合储存JSON对象,CS视图字符串,中短长度的书籍
- LONGTEXT 最大储存4GB,适合储存书籍和以年记的日志
不常用:
- TINYTEXT 最大储存 255 Bytes
- TEXT 最大储存 64KB
- 英文字符占1个字节
- 欧洲和中东语言字符占2个字节
- 像中日这样的亚洲语言的字符占3个字节
布尔类型
布尔值其实本质上就是 TINYINT 的另一种表现形式,TRUE / FALSE 实质上就是 1 / 0
日期和时间类型
MySQL 有4种储存日期事件的类型:
- DATE 有日期没时间
- TIME 有时间没日期
- TIMESTAMP 常用来记录一行数据的的插入或最后更新时间,占4B,最晚记录2038年
- DATETIME 占8B,如果要储存超过2038年的日期时间,就要用 DATETIME
注:YEAR类型专门储存四位的年份
枚举和集合类型(不建议使用)
ENUM()
从固定一系列值中取一个值
SET()
SET和ENUM()类似,SET是从固定一系列值中取多个值而非一个值
二进制大对象类型
尽量别用数据库来存文件,除非这样做确实有必要而且上面这些问题已经被考虑到了
占用储存 | 最大可储存 |
---|---|
TINYBOLB | 255B |
BLOB | 65KB |
MEDIUM BLOB | 16MB |
LONG BLOB | 4GB |
JSON类型
添加JSON:
第一种(同JavaScript相同)
UPDATE products
SET properties = '
{
"dimensions": [1, 2, 3],
"weight": 10,
"manufacturer": {"name": "sony"}
}
'
WHERE product_id = 1;
第二种(JSON_OBJECT()):
UPDATE products
SET properties = JSON_OBJECT(
'weight', 10,
'dimensions', JSON_ARRAY(1, 2, 3),
'manufacturer', JSON_OBJECT('name', 'sony')
)
WHERE product_id = 1;
查看JSON:
第一种(JSON_EXTRACT()):
SELECT product_id, JSON_EXTRACT(properties, '$.weight') AS weight
FROM products
WHERE product_id = 1;
第二种:
SELECT properties -> '$.weight' AS weight
FROM products
WHERE product_id = 1;
SELECT properties -> '$.dimensions[0]'
-- 数组可以索引
SELECT properties ->> '$.manufacturer.name'
-- >删除引号
修改JSON(JSON_SET()):
UPDATE products
SET properties = JSON_SET(
properties,
'$.weight', 20, -- 修改weight属性
'$.age', 10 -- 增加age属性
)
WHERE product_id = 1;
删除JSON(JSON_REMOVE()):
UPDATE products
SET properties = JSON_REMOVE(
properties,
'$.weight',
'$.age'
)
WHERE product_id = 1;