1. 字符串类型
1.0 支持国际字符种类
- 英文字母:1字节,1byte
- 中东和欧洲语言:2字节
- 中文、日文:3字节
- 如果列类型为char(10),mysql会为这列留出30字节(按中文和日文每个字符3个字节留出空间。)
1.1 char()
1.2 varchar()
- 存储可变长度字符串
- 最大长度:65KB,65535个字母,存储更长的值的话会被截断
- 存储内容:
- varchar(50) :短字符串,用户名,密码等
- varchar(255) :地址
1.3 mediumtext
- 最大存储:16MB,1600万个字母
- 存储内容:json对象、scv字符串、中短长度文本
1.4 longtext
- 最大存储:4GB的文本数据
- 存储内容:多年的日志文件等
1.5 tinytext
1.6 text
- 最大存储:64KB,65000个字母,和varchar一样。
- 对于此长度范围的字符,最好使用varchar类型
2. 整数类型
2.0 数值类型的属性
2.0.1 无符号 unsigned
- 无符号微整型,可以防止负数被意外存储进数据库。
- 如果标记一个数值列为无符号的,就只能存储正数
2.0.2 补零 zerofill
- 用0覆盖数值,总能拥有一样的数位。这只会影响mysql显示这些值的方式,而不影响存储
- int(4):0001
2.1 tinyint:1字节,-128~127
- unsigned tinyint:0~255
- 存储:年龄等
2.2 smallint:2字节
2.3 mediumint:3字节
2.4 int:4字节
2.5 bigint:8字节
3. 定点和浮点类型
3.1 decimal(p,s)
- 别名:dec、numeric、fixed
- 两个参数:精度和小数位数
- 精度p: 明确最大位数,可介于1~65之间
- 小数位数s:明确小数点后的位数
- decimal(9,2)代表可以存储9位数字,小数点前有7位,小数点后有2位
- 小数型:存储定点数,在小数点后有固定位数的数字
- 存储:货币值等
3.2 float4:浮点,4字节
- 用于科学计算,不存储准确值,而取近似值
- 可以存储非常大或非常小的数字
3.3 double:双精度,8字节
- 用于科学计算,不存储准确值,而取近似值
- 可以存储非常大或非常小的数字
4. 布尔型
4.1 bool / boolean
- 真 true:可用1表示
- 假 false:可用0表示
update posts
set is_published = 1 / true;
set is_published = 0 / false;
5. 枚举和集合类型
5.1 enum
- 将某列的值限制在某些选择范围内。
- 比如“规格”列中只允许有“小”,“中”,“大”三个值
enum('small', 'medium', 'large');
- 最好尽量避免使用枚举类型
- 原因1:改变枚举的组成项很麻烦。比如在enum中添加或修改一个新的选项,mysql会重建整张表,当表中的数据较多时会耗费大量时间。
- 原因2:无法为enum中的每个选项添加其他属性。例如,无法为每种规格添加实际尺寸。
- 原因3:获取所有可能的选项,并在应用程序的下拉列表中显示会比较麻烦
- 原因4:枚举项无法重复使用。若在另外一个表中重复使用这些枚举项,需要在这张表中重新定义这些枚举项。如果想对好几处的枚举项进行更改,必须在好几个表中进行更改,比较麻烦。
5.2 查询表
- 推荐使用查询表
- 单独建立一张叫“规格”的表(查询表)
- 表中可以存储所有规格和任何其他属性,如尺寸等。
- 可以在多处重复使用这张表。
- 获取所有规格:只需要写一个选择语句即可
5.3 集合 set
- 可以存储多个值。也尽量避免使用。
- 与枚举类似,先确定允许范围的系列值,可以在一列存储多个值。
6. 日期和时间类型
6.1 date
6.2 time
6.3 datetime 日期时间型
- 存储:日期时间型,记录某行插入或最近更新的时间
- 8个字节,存储2038年以后的时间,用datetime类型
6.4 timestamp 时间戳
- 时间戳:记录某行插入或最近更新的时间
- 4个字节,只能存储到2038年以前的日期
6.5 year
7. blob:二进制长对象
- 存储:大型二进制数据,如图像、视频、pdf、word文件等
- 一般来说,最好不要把文件存在数据库中。关系型数据库是为了处理结构化关系型数据设计的,而不是二进制数据。
- 把文件存在数据库中,数据库大小会迅速增加,这将弱化数据备份功能;出现性能问题,因为把图像从数据库中提取出来,比从文件系统中读取慢
- 为了在数据库中读取或存储图像,需要些额外的代码
7.1 tinyblob:255字节
7.2 blob:65KB字节
7.3 mediumblob:16MB字节
7.4 longblob:4GB字节
8. json类型
8.1 json对象
- json使用大括号定义一个对象
- 大括号中:有一个或多个键值对 。
- 键是字符串,要带上引号。
- 值可以是任意项,字符串、数字、布尔值、数组或其他对象都可以。
- 能够嵌套json对象
8.2 创建json对象
8.2.1 用{}创建json对象
update products
set properties = '
{
"dimensions": [1,2,3],
"weight": 10,
"manufacturer": {"name": "sony"}
}
'
where product_id = 1;
8.2.2 json_object创建json对象
update products
set properties = json_object(
'weight', 10,
'dimensions', json_array(1, 2 ,3),
'manufacturer', json_object('name', 'sony')
)
where product_id = 1;
select *
from products;
8.3 提取json对象
8.3.0 路径
- 路径,‘$.weight’
* $:表示当前的json文档
* . 表示访问单独的属性或键
8.3.1 json_extract:从json对象中提取单独键值对
- 只想提取json对象中的某个键值对:json_extract
- json_extract(参数1,参数2)
- 参数1:所要提取的列
- 参数2: 路径,‘$.weight’
select product_id,
json_extract(properties, '$weight') as weight
from products
where product_id = 1;
8.3.2 列路径运算符->:从json对象中提取单独键值对
select product_id,
properties -> '$.weight'
from products
where product_id = 1;
8.3.3 提取值为数组的键
- 如果处理的是值为数组的键,也可以访问数组中的单独项目
- 在路径中的提取的键名后面加方括号,方括号中写索引即可
- properties -> ‘$.dimensions[0]’
select product_id,
properties -> '$.dimensons[0]'
from products
where product_id = 1;
8.3.4 提取值为json对象的嵌套对象
- 处理嵌套对象
- properties -> ‘$.manufacturer.name’,这样得到的结果是“sony”,带有引号。
- 如果想得到不带有引号的sony: properties -> >‘$.manufacturer.name’, 多写一个大于号
select product_id,
properties -> '$.manufacturer.name'
from products
where product_id = 1;
select product_id,
properties ->> '$.manufacturer.name'
from products
where properties ->> '$.manufacturer.name' = 'sony';
8.4 json_set:更新属性/添加新属性
- json_set(json对象,属性1,新值,属性2,新值……):只更改某属性或添加新属性,不想重置整个对象
update products
set properties = json_set(
properties,
'$.weight', 20,
'$.age', 10
)
where product_id = 1;
select product_id, properties
from products
where properties ->> '$.manufacturer.name' = 'sony';
8.5 json_remove:删除一个或多个属性
- json_remove(json对象,属性1,属性2,……):选择一个json对象,修改并返回一个新的json对象
update products
set properties = json_remove(
properties,
'$.age'
)
where product_id = 1;
select product_id, properties
from products
where properties ->> '$.manufacturer.name' = 'sony';