MySQL從5.7一躍直接到8.0,本次的版本更新,在功能上主要有以下6點:
1,賬戶與安全
2,優化器索引
3,通用表表達式
4,視窗函數
5,InnoDB 增強
6,JSON 增強
一、賬戶與安全
1.使用者的建立與授權 在MySQL5.7的版本: > grant all privileges on *.* to '使用者名'@'主機' identified by '密碼'; 在MySQL8.0需要分開執行: >create user '使用者名'@'主機' identified by '密碼'; >grant all privileges on *.* to '使用者名'@'主機'; 用以前的一條指令在8.0裡面建立使用者,會出現sql文法錯誤
2.認證插件更新 MySQL5.7預設身份插件是mysql_native_password MySQL8.0預設的身份插件是caching_sha2_password 檢視身份認證插件指令:show variables like 'default_authentication_plugin%'; 身份認證插件可以通過以下2中方式改變: 1)系統變量default_authentication_plugin去改變,在my.ini檔案的[mysqld]下面設定default_authentication_plugin=mysql_native_password即可 2)如果希望隻是某一個使用者通過mysql_native_password的方式認證,可以修改資料庫mysql下面的user表的字段,執行以下指令: >alter user '使用者名'@'主機' identified width mysql_native_password by '密碼';
3.密碼管理 MySQL8.0的密碼管理政策有3個變量 password_history 修改密碼不允許與最近幾次使用或的密碼重複,預設是0,即不限制 password_reuse_interval 修改密碼不允許與最近多少天的使用過的密碼重複,預設是0,即不限制 password_require_current 修改密碼是否需要提供目前的登入密碼,預設是OFF,即不需要;如果需要,則設定成ON 查詢目前MySQL密碼管理政策相關變量,使用以下指令: >show variables like 'password%'; 1)設定全局的密碼管理政策,在my.ini配置檔案中,設定以上3個變量的值這種設定方式,需要重新開機mysql伺服器;某些生産環境不允許重新開機,MySQL8.0提供了關鍵字persist,持久化,執行以下指令: >set persist password_history=6; 這條指令會在資料目錄下生成新的配置檔案(/var/lib/mysql/mysqld-auto.cnf),下次伺服器重新開機的時候除了讀取全局配置檔案,還會讀取這個配置檔案,這條配置就會被讀入進而達到持久化的目的 2)針對某一個使用者單獨設定密碼管理政策 >alter user '使用者名'@'主機' password history 5; 這樣,這個使用者的password_history 就被設定成了5,檢視一下: >show user,host,Password_reuse_history from user; 檢視某一張的字段的所有字段,使用以下指令: >desc 表名;
4.角色管理 角色:一組權限的集合 一組權限賦予某個角色,再把某個角色賦予某個使用者,那使用者就擁有角色對應的權限 1)建立一個角色 >create role '角色1'; 2)為這個角色賦予相應權限 >grant insert,update on *.* to '角色1'; 3)建立一個使用者 >create user '使用者1' identified by '使用者1的密碼'; 4)為這個使用者賦予角色的權限 >grant '角色1' on *.* to '使用者1'; 執行完上面4步,使用者1就擁有了插入與更新的權限 5)再建立1個使用者 >create user '使用者2' identified by '使用者2的密碼'; 6)為這個使用者賦予同樣的角色 >grant '角色1' on *.* to '使用者2'; 執行完上面2步,使用者2也用了角色1的權限,即插入與更新 檢視使用者權限,執行以下指令: >show grants for '使用者名'; 7)啟用角色,設定了角色,如果不啟用,使用者登入的時候,依舊沒有該角色的權限 >set default role '角色名' to '使用者名'; 8)如果一個使用者有多個角色,使用以下指令 >set default role all to '使用者名'; MySQL中與使用者角色相關的表:mysql.default_roles、mysql.role_edges,有興趣的朋友可以進去檢視下。 9)撤銷權限 >revoke insert,update on *.* from '角色名';
二、優化器索引
1.隐藏索引(invisible index) 隐藏索引不會被優化器使用,但仍需要維護 應用場景: 1)軟删除 删除索引,線上上,如果删除錯了索引,隻能通過建立索引的方式将其添加回來,對于一些大的資料庫而言,是比較耗性能的;為了避免删錯,可以先将其設定為不可見,優化器這時候就不會使用它,但是背景仍然在維護,确定後,再删除。 2)灰階釋出 與軟删除差不多,如果想要測試一些索引的功能或者随後可能會使用到這個索引,可以先将其設定為隐藏索引,對于現有的查詢不會産生影響,測試後,确定需要該索引,可以将其設定成可見索引。 建立隐藏索引,執行如下指令(如果是不隐藏,則不需要後面的invisible關鍵字): >create index 索引名稱 on 表名(字段名) invisible; 查詢某一張表的索引,執行如下指令: >show index from 表名; 使用explain語句檢視查詢優化器對索引的使用情況 >explain select * from 表名 where 條件; 查詢優化器有很多開關,有一個是use_invisible_indexes(是否使用隐藏索引),預設是off(不适用),将其設定成on,即可使用隐藏索引。檢視目前查詢優化器的所有開關變臉,執行如下指令: >select @@optimizer_switch; 設定已經存在的索引為可見或者隐藏,執行如下指令: >alter table 表名 alter index 索引名 visible; >alter table 表名 alter index 索引名 invisible; 主鍵不可以設定為隐藏是以。
2.降序索引(descending index) MySQL8.0開始真正支援降序索引,隻有InnoDB引擎支援降序是以,且必須是BTREE降序索引,MySQL8.0不在對group by操作進行隐式排序。
3.函數索引 索引中使用函數表達式 支援JSON資料節點的索引 函數索引是基于虛拟列的功能實作的假設使用者表(tb_user)的的使用者登入賬号(username)不需要區分大小寫,則可以建立一個函數索引>create index username_upper_index on tb_user((upper(username)));這樣在查詢的時候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就會使用索引。上面的函數索引,也可以通過MySQL5.7已有的虛拟計算列來模拟,為使用者表(tb_user)建立新的一列(new_column),這一列是計算列,不需要指派,它的值就是username的大寫。>alter tbale tb_user add column new_column varchar(10) generated always as (upper(username));然後給new_column建立一個索引,可以達到模拟MySQL8.0中的函數索引的效果。
三、通用表表達式
1.非遞歸 CTE 派生表:select * from (select 1) as dt; 通用表表達式:with cte as (select 1) select * from cte; with cte1(id) as (select 1),cte2 as (select id+1 from cte1) select * from cte1 join cte2;
2.遞歸 CTE
四、視窗函數
五、InnoDB增強
1.內建資料字段
2.原子ddl操作 MySQL5.7執行drop指令 drop table t1,t2; 如果t1存在,t2不存在,會提示t2表不存在,但是t1表仍然會被删除。 MySQL8.0執行同樣的drop指令,會提示t2表不存在,而且t1表不會被删除,保證了原子性。 ddl操作(針對表)的原子性前提是該表使用的存儲引擎是InnoDB
3.自增列持久化 解決了之前的版本,主鍵重複的問題。 MySQL5.7及其以前的版本,MySQL伺服器重新開機,會重新掃描表的主鍵最大值,如果之前已經删除過id=100的資料,但是表中目前記錄的最大值如果是99,那麼經過掃描,下一條記錄的id是100,而不是101。 MySQL8.0則是每次在變化的時候,都會将自增計數器的最大值寫入redo log,同時在每次檢查點将其寫入引擎私有的系統表。則不會出現自增主鍵重複的問題。
4.死鎖檢查控制5.鎖定語句選項
六、JSON增強
1.内聯路徑操作符 column->>path等價于之前的:JSON_UNQUOTE(column -> path)JSON_UNQUOTE(JSON_EXTRACT(column,path))
2.JSON聚合函數MySQL8.0和MySQL5.7.22增加了2個聚合函數 1)JSON_ARRAYAGG(),将多行資料組合成json數組
示例:select o_id,json_arrayagg(attribute) as attributes from t group by o_id;
2)JSON_OBJECTAGG(),用于生成json對象 示例:select o_id json_objectagg(attribute,value) as attributes from t group by o_id;
注意:json的聚合函數針對重複key,會使用最後的覆寫前面已有的值,如果下面的o_id=3,它的color有2個值,一個green,一個yellow,使用生成json的聚合函數的時候,前面的green會被覆寫掉。
3.JSON實用函數 1)JSON_PRETTY() 輸出json資料的時候,格式化。 select json_object('id',3,'name','Barney');
select json_pretty(json_object('id',3,'name','Barney'));
2)JSON_STORAGE_SIZE() json資料所占用的存儲空間(機關:位元組) 3)JSON_STORAGE_FREE() json資料更新後所釋放的空間(機關:位元組)
4.JSON合并函數MySQL8.0廢棄了JSON_MERGE()函數,推薦使用以下兩個函數合并JSON資料 1)JSON_MERGE_PATCH() 2)JSON_MERGE_PRESERV()上面兩個函數都是JSON資料合并,最大的差別就是前者遇到相同key的時候會用後面的覆寫前面的,後者會都保留,看下面的截圖:
5.JSON表函數 MySQL8.0新增了JSON_TABLE()函數,将JSON資料轉換成關系表,可以将該函數的傳回結果當做一個普通的臨時表進行sql查詢