MySQL数据分析进阶（十一）数据类型

※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）

【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2

目录：

第十一章：数据类型

1、DATA TYPERS——数据类型

2、STRING TYPES——字符串类型

3、INTEGER TYPES——整数类型

4、FIXED-POINT AND FLOATING-POINT TYPES——定点和浮点类型

5、BOOLEAN TYPES——布尔类型

6、ENUM AND SET TYPES——枚举和集合类型

7、DATE AND TIME TYPES——日期和时间类型

8、BLOB TYPES——BLOB类型

9、JSON TYPE——JOSN类型

---

第十一章：数据类型

1、DATA TYPERS——数据类型

了解数据类型以及何时使用那种数据类型是非常重要的

2、STRING TYPES——字符串类型

CHAR (X) ：存储固定长度字符串

VARCHAR (X)：存储可变长度字符串（用户名、名字、密码、电邮、地址）

虽然都是字符串，也可以存储数值（邮政编码、电话号码），因为对这些数值不做数学运算

此外，这些值可能包含连字符或括号组合几位数字

将大多数VARCHAR列设置为统一标准长度

VARCHAR(50)：50段字符——用户名、密码

VARCHAR(255)：255长字符——地址

这种一致性简化了数据库的维护，不必经常检查每列的长度

VARCHAR：最长存储65535个字符串

如果想要存储更长的值，将会被截断

MEDIUMTEXT：1600万个字符

中文本字符串，并且对存储JSON对象、SCV字符串和短中长度的书很好使

LONGTEXT：4GB

长文本串，可存储最多4GB的文本数据，储存教本或者许多年的日志文件

TINYTEXT：255字符

微文本类型，存储最多255子字符

TEXT：65000个字符

和VARCHAR一样，可以存储最多65000个字符

但对于这一长度范围的字符，最好使用VARCHAR类型，因为可以被编入索引

English(1)：英文字母占用1个字节

European、Middle-eastern(2)：欧洲和中东语言占用2个字节

Asian(3)：亚洲语言，中文、日文占用3个字节

如果列类型是CHAR（10），MySQL会为那列的值留出30字节

3、INTEGER TYPES——整数类型

使用整数来存储1234没有小数点的整数

使用越多字节，可以存储的数字越大

TINYINT（微整型）-（1b）-（-128，127）

占用1个字节，存储-128到127的数值

如果标志一个数值列为无符号，就只能存储整数

UNSIGNED TINYINT（无符号微整型）-（0，255）

可以存储0-255的数字，存储年龄这样的正数时有用

如果为列标记为无符号，可以防止负数被意外存储进数据库

SMALLINT（小整型）-（2b）-（-32K，32K）

占用2个字节，存储-32K到32K的数值

MEDIUMINT（中整型）-（3b）-（-8M，8M）

占用3个字节，存储-8M到8M的数值

INT（整型/整数）-（4b）-（-2B，2B）

占用4个字节，存储-2M到2M的数值

BIGINT（大整型）-（8b）-（-9Z，9Z）

占用8个字节，存储-9Z到9Z的数值

如果存储一个超过范围的数值，系统将提醒错误

MySQL整数类型可在此处查找：mysql integer types

除了UNSIGNED TINYINT（无符号微整型），数值类型另一个属性

ZEROFILL（补零）：想用0覆盖数值时有用，这样就能获得一样的位数

定义一个数值列时，可以指定在括号中显示大小

INT（4）=> 0001

这只影响MySQL显示这些值的方式，而不是存储

🔺最好使用尽量小并足以满足需求的数据类型

这样数据库大小会更小，查询也执行得更快

数据字节会需要在磁盘和内存之间来回传送

4、FIXED-POINT AND FLOATING-POINT TYPES——定点和浮点类型

MySQL中存储带小数点的数字类型有3种

①DECIMAL（p，s）（小数型）：存储定点数，在小数点后有固定位数的数字（货币值）

使用小数型需要提供零个参数：精度、小数位数

精度：确定最大位数，介于1-65之间

小数位：确定小数点后的位数

DECIMAL（9，2） =>  1234567.89

最多可以存储9为数字，小数点后有两位，小数点前七位

DECIMAL其他同义词：DEC、NUMERIC、FIXED

②FLOAT（浮点类型）-（4b）

③DUBLE（双精度类型）-（8b）

用于科学计算，计算非常大或非常小的数字

这两种类型不存储准确值，而是取近似值

5、BOOLEAN TYPES——布尔类型

有时需要存储一个是或否类型的值，就可以使用Boolean值来分真或假

BOOL、BOOLEAN

这种数据类型是微整型的同义词

一个Boolean列可以编写的代码：

TURE关键字本质可以用1表示，FALSE用0表示

6、ENUM AND SET TYPES——枚举和集合类型

①枚举类型的使用法：

想要将某一列的值，限制在一个有限字符串列表范围内

ENUM（'small','medium','large'）

只允许出现小、中、大三个值

尽量避免使用

改变枚举的组成项会很费功夫

比如修改后第二天又想添加一个新值进去，或者添加一个新项MySQL会依据重建这整张表，如果这张表有上百万的记录，就很费时间

无法为每项添加其他属性

如果想为每种规格添加实际尺寸，就无法做到（只能大中小）

不便于设置更多规格

如果想获取所有可能的规格，并在应用程序的下拉列表中展现需要做很多额外工作

枚举项是不可重复使用

如果有另一个表，并希望在那张表中重复使用这些值，在那张表需要重新定义这些枚举项

如果想在好几处对其中某个枚举项进行更改，也必须在好几处应该这一更改

最好的方法：单独建立一张size表

这张表可以存储所有规格和任何其他属性（尺寸），可以在多出重复使用这张表，只需要写一个选择语句（查询表，例如payment_methods）

②集合类型

SUM（…）：可以存储多个确定允许范围的系列值

（同样也不是很好用）

7、DATE AND TIME TYPES——日期和时间类型

MySQL存储日期和时间值的数据类型有4种：

DATE：存储一个没有时间成分的日期

TIME：存储一个时间值

DATETIME、TIMESTAMP（时间戳）、YEAR：存储四位数年份

TIMESTAMP、DATETIM都可用来记某行插入或者最近更新的时间

TIMESTAMP是4个字节，只能存储到2038年以前的日期（UP TO 2038）

DATETIM是8个字节，可以超过2038年的日期

8、BLOB TYPES——BLOB类型

使用BLOB（二进制长对象）类型来存储大型二进制数据

（如图像、视频、PDF、WORD文件，几乎覆盖了所有二进制数据）

MySQL中BLOB类型有4种：

根据能够存储的最大数据量来区别开

①TINYBLOB：最大能存储255b的二进制数据

②BLOB：存储最高达65KB的二进制数据

③MEDIUMBLOB：存储最高达16MB的二进制数据

④LONGBLOB：存储最高达4GB的二进制数据

一般来说最好不要把文件存在数据库中，因为关系型数据库是为了处理结构化关系型数据设计的，而不是二进制

如果把文件存储到数据中

⚠数据库大小会迅速增加

⚠二弱化数据备份功能

⚠出现性能问题（把图像从数据库提取出来，总是比从系统文件中读取慢）

⚠必须额外写代码

9、JSON TYPE——JOSN类型

MySQL也可以存储JSON文档

JSON或JavaScript对象表示法：一种通过网络存储和传输数据的轻量级格式

在网络和移动应用中被大量应用：移动应用程序通过JSON将数据发送到后端

Key：字符串（要带上引号）

Value：可以是任意值（字符串、数字、布尔值、数组或者其他对象）

方法一

在product表中设置properties（属性），用来存储每个产品的其他属性

要在这张表添加几列且只有部分列可以与每个产品匹配就不是很合适

使用JSON能够轻松地存储每个产品的多个键值对

（Workbench的版本号需要是8以上的，否则会得到错误提示）

在{  }之间添加一个或多个键值

方法二：

使用MySQL内部函数JSON_OBJECT、JSON_ARRAY

现在可以从JSON对象中提取单独的键值对

这就是JSON比VARCHAR的好处,，因为对字符串来说，在JSON对象中提取单独的键值对真的很难

普通写法：

JSON_EXTRACT：特殊运算符

第一个参数是JSON对象

$表示当前的JSON文档

加一个句点来访问单独的属性或者建

简短写法:

->  列路径运算符

❗如果处理的是一个值为数组的健

访问数组中的单独项目：在[  ]内指定想要检索的项目的索引，第一个数字输入0

❗如果处理的是一个值为嵌套对象

（按理应该显示sony，暂不知原因……）

❗如果想修改weight的数字，不想重置整个对象，只想更新weight属性

JSON_SET：修改和新增JSON的对象

 

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

————TBC