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`)
) engine=innodb;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`)
) engine=innodb;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 '用戶標簽'
) engine=innodb;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 `rank`from `tb_emp`
) `temp` where `rank` between 4 and 6;
說明：上面使用的函數
row_number()可以為每條記錄生成一個行號，在實際工作中可以根據需要將其替換為rank()或dense_rank()函數，三者的區別可以參考官方文檔或閱讀《通俗易懂的學會：SQL窗口函數》進行了解。在MySQL 8以前的版本，我們可以通過下面的方式來完成類似的操作。select `rank`, `ename`, `sal` from (select @a:=@a+1 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 `rank`from `tb_emp`
) as `temp` natural join `tb_dept` where `rank`<=2;
說明：在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.dno=t2.dno and t2.sal>t1.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 服務、部署高可用架構等，這一系列的問題在這里都沒有辦法逐一展開來討論，那就留到有需要的時候再進行講解吧，各位讀者也可以自行探索。
)
—— 金寶貝兒童教育機構介紹網頁(2個頁面))
![[原型資源分享]經典產品餓了么UI模版部件庫](http://pic.xiahunao.cn/[原型資源分享]經典產品餓了么UI模版部件庫)
)
)
