【MySQL】数据类型

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

• 最大存储：255 bytes，255个字母

1.6 text

• 最大存储：64KB，65000个字母，和varchar一样。
• 对于此长度范围的字符，最好使用varchar类型

2. 整数类型

• 每种整数类型占用字节数和存储值范围的表格查询
• 5种整数类型，其字节数和值的范围不同
• 选择尽量小并足以满足需求的数据类型，这样会使数据库更小，查询更快

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字节

• 存储：255字节的二进制数据

7.2 blob：65KB字节

• 存储：65KB字节的二进制数据

7.3 mediumblob：16MB字节

• 存储：16MB字节的二进制数据

7.4 longblob：4GB字节

• 存储： 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对象

• json_array()创建数组

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';