12. 数据类型

俄罗斯刺沙蓬
139 阅读3分钟

字符串类型

数据类型长度备注
char(x)固定长度x为1到255之间的整数
varchar(x)可变长度x为1到65535之间的整数，最多65535个字符，64KB
mediumtext最长16MB
longtext最长4GB
tinytext最长255b
text最长64KB
注意： 为了维护数据库的一致性，可变长的字符串长度设置最好也能分类。varchar(50) 表示短字符串，varchar(255) 表示中等长度字符串，可以不用经常检查

国际字符：所有这些字符串类型都支持国际字符，其中：

  • 英文字符占1个字节
  • 欧洲和中东语言字符占2个字节
  • 像中日这样的亚洲语言的字符占3个字节 所以，如果一列数据的类型为 CHAR(10)，MySQL会预留30字节给那一列的值

整数类型

数据类型长度备注
tinyint1b[-128, 127]
unsigned tinyint1b[0, 255]
smallint2b[-32k, 32k]
mediumint3b[-8M, 8M]
int4b[-2B, 2B]
注意： 如果试图存入超过范围的数字，MySQL会报错

MySQL 还可以根据 INT 后面的数字来决定显示成什么样子，但是，这并不影响数字在MySQL中的存储

例如：1 int(4) => 0001

用最小的能满足需求的数据类型，这样数据库大小会更小，查询执行也会更快。

顶点和浮点类型

数据类型类型备注
decimal(p, s)定点数类型p，s分别表示精度(1~65)和小数位数
dec定点数类型同上
numeric定点数类型同上
fixed定点数类型同上
float浮点数类型 4b
double双精度浮点数类型 8b

如果需要记录精确的数字，比如货币金额，就是用 DECIMAL 类型
如果要进行科学计算，要处理很大或很小的数据，而且精确值不重要的话，就用 FLOAT 或 DOUBLE

布尔类型

  • bool
  • boolean

枚举和集合类型

ENUM() 从固定一系列值中取一个值

ENUM('small','medium','large') 表示这个类型中的值是括号内元素之一

SET() 和 ENUM() 类似，但是可以存多个值

不建议使用：

  1. 两者的类型不好复用，一旦某个地方修改，所有其他的地方都要修改，不如使用一张关系表，只需要多添加一条查询语句就可以实现
  2. 如果要修改enum类型中的值，会导致重建整张表，如果表中的数据很多，会非常耗费时间。

日期和时间类型

数据类型大小作用
date有日期没时间
time有时间没日期
datetime8b包含日期和时间
timestamp4b时间戳，常用来记录一行数据的的插入或最后更新时间
year专门储存四位的年份

blob类型

用 Blob 类型来储存大的二进制数据，包括PDF，图像，视频等等几乎所有的二进制的文件
具体来说，MySQL里共有4种 Blob 类型，它们的区别在于可储存的最大文件大小：

数据类型大小
tinyblob255b
blob65KB
mediumblob16MB
longblob4GB

一般来说最好不要把文件存在数据库中，因为关系型数据库是为了处理结构化关系型数据库设计的 会导致以下问题：

  1. 迅速增加数据库的大小
  2. 弱化数据备份功能
  3. 出现性能问题
  4. 在数据库中读取或者存储图像必须额外写代码

JSON类型

update products
set properties = '
{
	"dimensions":[1,2,3],
    "weight":10,
    "manufacturer":{
		"name":"sony"
    }
}
'
where product_id = 1;

上面的语句可以用MySQL内置的函数替换

update products
set properties = json_object(
	'weight','10',
    'dimensions',json_array(1,2,3),
    'manufacturer',json_object('name','sony')
)
where product_id = 1;

还可以通过已经有的 MySQL 解析函数解析 JSON 格式

select product_id,json_extract(properties,'$.weight') as weight
from products
where product_id = 1;

还可以使用 -> 更简洁，后面可以继续通过 点 来调用

select product_id,properties -> '$.weight'
from products
where product_id = 1;

但是上面的结果数据都是由引号的，可以使用 ->> 来去掉，只返回值

select product_id,properties ->> '$.weight'
from products
where product_id = 1;

在查询语句里面也可以直接使用

select product_id,properties ->> '$.weight'
from products
where properties ->> '$.weight' = '10';

更新 json 中的某些值 下面的 json_set 和 json_remove 都是拾取一个 json 对象，修改并返回一个新的 json 对象。

我们其实是把这些属性设置到了一个由这个函数返回的新json对象上

update products
set properties = json_set(
	properties,
    '$.weight', 20,
    '$.age',10
)
where product_id = 1;

update products
set properties = json_remove(
	properties,
    '$.weight', 20,
    '$.age',10
)
where product_id = 1;
avatar
文章
阅读
粉丝