SQL 数据类型

数据类型

整数类型

关键词	最大长度
TINYINT	[-128, 127]
UNSIGNED TINYINT	[0, 255]
SMALLINT	[-32k, 32k]
MEDIUMINT	[-8M, 8M]
INT	[-2B, 2B]
BIGINT	[-9Z, 9Z]

整数类型多为定长换句话说,如果你定义了一个长度为3的INT类型，输入一个1会默认前边加两个0。也就是占了额外的空间开销。所以如果你需要存储一个人的年龄可以尽量使用UNSIGNED TINYINT。

浮点数类型

DECIMAL(p,s),p的范围为1-65，s是小数点后的位数。

如果你定义了一个DECIMAL(6,2)指的是小数点前4位，小数点后2两位。

有的数据库中也是用DEC/NUMERIC/FIXED等来代替DECIMAL。

双精度的浮点数常用于进行科学计算。他们并不存储准确值。有两个FLOAT/DOUBLE前者占四字节，后者占八字节。

字符串类型

关键字	最大长度
CHAR
VARCHAR	64KB(65,535)
TINYTEXT	255bytes
TEXT	64KB
MEDIUMTEXT	16MB
LONGTEXT	4GB

下面是一些我写代码的小技巧：

非定长数据的短字符串设置长度为VARCHAR(50)(用户名，密码)，长字符串设置为VARCHAR（255）（地址，简介）。

类型是支持国际字符的其中英文占一个字符，中文UNIONCODE占两个字符，UTF8占三个字符。

在一定的长度范围内我们最好采用VARCHAR类型，因为VARCHAR类型可以被编入索引之中。

布尔类型

BOOL/BOOLEAN,都可以用来表示真假。

我们用TRUE或者1来表示真的，FLASE或者0表示假的。

我一般用TINYINT来存储状态，令1表示存在，0表示不存在。

日期类型

关键词	解释	例子
DATE	日期	2021-01-01
TIME	时间	01:02:03
TIMESTAMP	时间戳	因为长度问题只能到2038年
DATETIME	日期加时间	2021-01-01 01:02:03

枚举类型

比如我们有一个型号字段只能有三个值small,medium,big

我们就需要通过ENUM('small','medium','big')的方式定义该字段。

个人感觉枚举类型要尽量少用。原因是因为后续想要改变它的组成可能要颇费功夫。你可以通过建立型号表与连接表来实现这个功能，并且使用更加灵活。

集合类型

SET换掉ENUM就是集合了。同样不推荐使用。

二进制类型

主要是为了将音频视频等文件类型化作二进制存取。

关键词	大小
TINYBLOB	255b
BLOB	65KB
MEDIUMBLOB	16MB
LONGBLOB	4GB

我并没有用过这个数据类型，我觉得文件应该存在他们应该存在的地方，此处使用可能会增加数据库的负担。

甚至可能要写一系列的代码将文件先转化为数据库。

JSON类型

这个类型是MySQL8以后存在的，对JSON类型有所了解的人应该知道这个是干嘛的，它可以用于存储对象。

个人喜欢用它来放配置文件。

update table1
set properties=JSON_OBJECT(
    'key1', 10,
    'key2', JSON_ARRAY(1,2,3),
    'key3', JSON_OBJECT('name','wang')
)
where id=1;

JSON_ARRAY()会解析为JSON数组，JSON_OBJECT()解析为JSON对象。

select id,JSON_EXTRACT(properties,'$.key1'),JSON_EXTRACT(properties,'$.key2[0]'),JSON_EXTRACT(properties,'$.key3.name')
from table1;
--简写
select id,properties->'$.key1',properties->'$.key3.name',properties->>'$.key3.name'
from table1;

JSON_EXTRACT()方法可以解析JSON串获取对象，第一个参数是字段名，第二个参数是想要查询的键名(会返回值)。$相当于这段json对象。

双箭头可以去掉查询到的字符串的双引号。

更改JSON值

update table1
set properties=JSON_SET(
    properties,
    '$.key1', 12,
    '$.key2',
    '$.key4', "22"
)
where id=1;

JSON_SET的第一个参数是字段名，后边是需要更改的键路径紧跟着是它的值。

注意,已经存在的键会更新值，不存在的键会增加进去。如果一个键路径后边并没有跟值，那就会删去这个键。