用易语言做网站抢购软件,wordpress当前分类链接地址,长春经济技术开发区人才网,域名权重是什么意思MySQL 新特性
JSON类型
很多开发者在使用关系型数据库做数据持久化的时候#xff0c;常常感到结构化的存储缺乏灵活性#xff0c;因为必须事先设计好所有的列以及对应的数据类型。在业务发展和变化的过程中#xff0c;如果需要修改表结构#xff0c;这绝对是比较麻烦和难…MySQL 新特性
JSON类型
很多开发者在使用关系型数据库做数据持久化的时候常常感到结构化的存储缺乏灵活性因为必须事先设计好所有的列以及对应的数据类型。在业务发展和变化的过程中如果需要修改表结构这绝对是比较麻烦和难受的事情。从 MySQL 5.7 版本开始MySQL引入了对 JSON 数据类型的支持MySQL 8.0 解决了 JSON 的日志性能瓶颈问题用好 JSON 类型其实就是打破了关系型数据库和非关系型数据库之间的界限为数据持久化操作带来了更多的便捷。
JSON 类型主要分为 JSON 对象和 JSON数组两种如下所示。
JSON 对象
{name: 骆昊, tel: 13122335566, QQ: 957658}JSON 数组
[1, 2, 3][{name: 骆昊, tel: 13122335566}, {name: 王大锤, QQ: 123456}]哪些地方需要用到JSON类型呢举一个简单的例子现在很多产品的用户登录都支持多种方式例如手机号、微信、QQ、新浪微博等但是一般情况下我们又不会要求用户提供所有的这些信息那么用传统的设计方式就需要设计多个列来对应多种登录方式可能还需要允许这些列存在空值这显然不是很好的选择另一方面如果产品又增加了一种登录方式那么就必然要修改之前的表结构这就更让人痛苦了。但是有了 JSON 类型刚才的问题就迎刃而解了我们可以做出如下所示的设计。
create table tb_test
(
user_id bigint unsigned,
login_info json,
primary key (user_id)
) engineinnodb;insert into tb_test values (1, {tel: 13122335566, QQ: 654321, wechat: jackfrued}),(2, {tel: 13599876543, weibo: wangdachui123});如果要查询用户的手机和微信号可以用如下所示的 SQL 语句。
select user_id,json_unquote(json_extract(login_info, $.tel)) as 手机号,json_unquote(json_extract(login_info, $.wechat)) as 微信
from tb_test;---------------------------------
| user_id | 手机号 | 微信 |
---------------------------------
| 1 | 13122335566 | jackfrued |
| 2 | 13599876543 | NULL |
---------------------------------因为支持 JSON 类型MySQL 也提供了配套的处理 JSON 数据的函数就像上面用到的json_extract和json_unquote。当然上面的 SQL 还有更为便捷的写法如下所示。
select user_id,login_info - $.tel as 手机号,login_info - $.wechat as 微信
from tb_test;再举个例子如果我们的产品要实现用户画像功能给用户打标签然后基于用户画像给用户推荐平台的服务或消费品之类的东西我们也可以使用 JSON 类型来保存用户画像数据示意代码如下所示。
创建画像标签表。
create table tb_tags
(
tag_id int unsigned not null comment 标签ID,
tag_name varchar(20) not null comment 标签名,
primary key (tag_id)
) engineinnodb;insert into tb_tags (tag_id, tag_name)
values(1, 70后),(2, 80后),(3, 90后),(4, 00后),(5, 爱运动),(6, 高学历),(7, 小资),(8, 有房),(9, 有车),(10, 爱看电影),(11, 爱网购),(12, 常点外卖);为用户打标签。
create table tb_users_tags
(
user_id bigint unsigned not null comment 用户ID,
user_tags json not null comment 用户标签
) engineinnodb;insert into tb_users_tags values (1, [2, 6, 8, 10]),(2, [3, 10, 12]),(3, [3, 8, 9, 11]);接下来我们通过一组查询来了解 JSON 类型的巧妙之处。 查询爱看电影有10这个标签的用户ID。 select * from tb_users where 10 member of (user_tags-$);查询爱看电影有10这个标签的80后有2这个标签用户ID。 select * from tb_users where json_contains(user_tags-$, [2, 10]); 查询爱看电影或80后或90后的用户ID。 select user_id from tb_users_tags where json_overlaps(user_tags-$, [2, 3, 10]);说明上面的查询用到了member of谓词和两个 JSON 函数json_contains可以检查 JSON 数组是否包含了指定的元素而json_overlaps可以检查 JSON 数组是否与指定的数组有重叠部分。 窗口函数
MySQL 从8.0开始支持窗口函数大多数商业数据库和一些开源数据库早已提供了对窗口函数的支持有的也将其称之为 OLAP联机分析和处理函数听名字就知道跟统计和分析相关。为了帮助大家理解窗口函数我们先说说窗口的概念。
窗口可以理解为记录的集合窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数。窗口函数和我们上面讲到的聚合函数比较容易混淆二者的区别主要在于聚合函数是将多条记录聚合为一条记录窗口函数是每条记录都会执行执行后记录条数不会变。窗口函数不仅仅是几个函数它是一套完整的语法函数只是该语法的一部分基本语法如下所示
窗口函数 over (partition by 用于分组的列名 order by 用户排序的列名)上面语法中窗口函数的位置可以放以下两种函数
专用窗口函数包括lead、lag、first_value、last_value、rank、dense_rank和row_number等。聚合函数包括sum、avg、max、min和count等。
下面为大家举几个使用窗口函数的简单例子我们先用如下所示的 SQL 建库建表。
-- 创建名为hrs的数据库并指定默认的字符集
create database hrs default charset utf8mb4;-- 切换到hrs数据库
use hrs;-- 创建部门表
create table tb_dept
(
dno int not null comment 编号,
dname varchar(10) not null comment 名称,
dloc varchar(20) not null comment 所在地,
primary key (dno)
);-- 插入4个部门
insert into tb_dept values (10, 会计部, 北京),(20, 研发部, 成都),(30, 销售部, 重庆),(40, 运维部, 深圳);-- 创建员工表
create table tb_emp
(
eno int not null comment 员工编号,
ename varchar(20) not null comment 员工姓名,
job varchar(20) not null comment 员工职位,
mgr int comment 主管编号,
sal int not null comment 员工月薪,
comm int comment 每月补贴,
dno int not null comment 所在部门编号,
primary key (eno),
constraint fk_emp_mgr foreign key (mgr) references tb_emp (eno),
constraint fk_emp_dno foreign key (dno) references tb_dept (dno)
);-- 插入14个员工
insert into tb_emp values (7800, 张三丰, 总裁, null, 9000, 1200, 20),(2056, 乔峰, 分析师, 7800, 5000, 1500, 20),(3088, 李莫愁, 设计师, 2056, 3500, 800, 20),(3211, 张无忌, 程序员, 2056, 3200, null, 20),(3233, 丘处机, 程序员, 2056, 3400, null, 20),(3251, 张翠山, 程序员, 2056, 4000, null, 20),(5566, 宋远桥, 会计师, 7800, 4000, 1000, 10),(5234, 郭靖, 出纳, 5566, 2000, null, 10),(3344, 黄蓉, 销售主管, 7800, 3000, 800, 30),(1359, 胡一刀, 销售员, 3344, 1800, 200, 30),(4466, 苗人凤, 销售员, 3344, 2500, null, 30),(3244, 欧阳锋, 程序员, 3088, 3200, null, 20),(3577, 杨过, 会计, 5566, 2200, null, 10),(3588, 朱九真, 会计, 5566, 2500, null, 10);例子1查询按月薪从高到低排在第4到第6名的员工的姓名和月薪。
select * from (select ename, sal,row_number() over (order by sal desc) as rankfrom tb_emp
) temp where rank between 4 and 6;说明上面使用的函数row_number()可以为每条记录生成一个行号在实际工作中可以根据需要将其替换为rank()或dense_rank()函数三者的区别可以参考官方文档或阅读《通俗易懂的学会SQL窗口函数》进行了解。在MySQL 8以前的版本我们可以通过下面的方式来完成类似的操作。 select rank, ename, sal from (select a:a1 as rank, ename, sal from tb_emp, (select a:0) as t1 order by sal desc
) t2 where rank between 4 and 6;例子2查询每个部门月薪最高的两名的员工的姓名和部门名称。
select ename, sal, dname
from (select ename, sal, dno,rank() over (partition by dno order by sal desc) as rankfrom tb_emp
) as temp natural join tb_dept where rank2;说明在MySQL 8以前的版本我们可以通过下面的方式来完成类似的操作。 select ename, sal, dname from tb_emp as t1 natural join tb_dept where ( select count(*) from tb_emp as t2 where t1.dnot2.dno and t2.salt1.sal )2 order by dno asc, sal desc; 其他内容
范式理论
范式理论是设计关系型数据库中二维表的指导思想。
第一范式数据表的每个列的值域都是由原子值组成的不能够再分割。第二范式数据表里的所有数据都要和该数据表的键主键与候选键有完全依赖关系。第三范式所有非键属性都只和候选键有相关性也就是说非键属性之间应该是独立无关的。 说明实际工作中出于效率的考虑我们在设计表时很有可能做出反范式设计即故意降低方式级别增加冗余数据来获得更好的操作性能。 数据完整性 实体完整性 - 每个实体都是独一无二的 主键primary key / 唯一约束unique 引用完整性参照完整性- 关系中不允许引用不存在的实体 外键foreign key 域domain完整性 - 数据是有效的 数据类型及长度 非空约束not null 默认值约束default 检查约束check 说明在 MySQL 8.x 以前检查约束并不起作用。
数据一致性 事务一系列对数据库进行读/写的操作这些操作要么全都成功要么全都失败。 事务的 ACID 特性 原子性事务作为一个整体被执行包含在其中的对数据库的操作要么全部被执行要么都不执行一致性事务应确保数据库的状态从一个一致状态转变为另一个一致状态隔离性多个事务并发执行时一个事务的执行不应影响其他事务的执行持久性已被提交的事务对数据库的修改应该永久保存在数据库中 MySQL 中的事务操作 开启事务环境 start transaction提交事务 commit回滚事务 rollback查看事务隔离级别 show variables like transaction_isolation;----------------------------------------
| Variable_name | Value |
----------------------------------------
| transaction_isolation | REPEATABLE-READ |
----------------------------------------可以看出MySQL 默认的事务隔离级别是REPEATABLE-READ。 修改当前会话事务隔离级别 set session transaction isolation level read committed;重新查看事务隔离级别结果如下所示。 ---------------------------------------
| Variable_name | Value |
---------------------------------------
| transaction_isolation | READ-COMMITTED |
---------------------------------------关系型数据库的事务是一个很大的话题因为当存在多个并发事务访问数据时就有可能出现三类读数据的问题脏读、不可重复读、幻读和两类更新数据的问题第一类丢失更新、第二类丢失更新。想了解这五类问题的可以阅读我发布在 CSDN 网站上的《Java面试题全集上》一文的第80题。为了避免这些问题关系型数据库底层是有对应的锁机制的按锁定对象不同可以分为表级锁和行级锁按并发事务锁定关系可以分为共享锁和独占锁。然而直接使用锁是非常麻烦的为此数据库为用户提供了自动锁机制只要用户指定适当的事务隔离级别数据库就会通过分析 SQL 语句然后为事务访问的资源加上合适的锁。此外数据库还会维护这些锁通过各种手段提高系统的性能这些对用户来说都是透明的。想了解 MySQL 事务和锁的细节知识推荐大家阅读进阶读物《高性能MySQL》这也是数据库方面的经典书籍。
ANSI/ISO SQL 92标准定义了4个等级的事务隔离级别如下表所示。需要说明的是事务隔离级别和数据访问的并发性是对立的事务隔离级别越高并发性就越差。所以要根据具体的应用来确定到底使用哪种事务隔离级别这个地方没有万能的原则。 总结
关于 SQL 和 MySQL 的知识肯定远远不止上面列出的这些比如 SQL 本身的优化、MySQL 性能调优、MySQL 运维相关工具、MySQL 数据的备份和恢复、监控 MySQL 服务、部署高可用架构等这一系列的问题在这里都没有办法逐一展开来讨论那就留到有需要的时候再进行讲解吧各位读者也可以自行探索。
