-
認(rèn)證插件更新
查看全部 -
用戶創(chuàng)建和授權(quán)
查看全部 -
！查看全部
-
賬戶與安全 優(yōu)化器索引 json增強(qiáng)
查看全部 -
#使用遞歸CTE生成斐波那契數(shù)列:0,1,1,2,3,5,8,...
#1.限定最大值
with recursive cte(m, n) as (
select 0, 1
union all
select n, m+n from cte where n<100
) select m from cte;
#2.限定位數(shù)
with recursive cte(id, m, n) as (
select 0, 0, 1
union all
select id+1, n, m+n from cte where id<10
) select m from cte;
查看全部 -
測(cè)試表
create table t3(c1 varchar(10), c2 varchar(10));
create index idx on t3(c1);創(chuàng)建普通索引
create index fun_idx on t3( (UPPER(c2)) );創(chuàng)建函數(shù)索引語法，如c2轉(zhuǎn)換為大寫的結(jié)果作為索引
show index from t3\G
explain select * from t3 where upper(c2) = 'ABC'; 如果沒設(shè)置函數(shù)索引就是where全表查；反之可以走索引，如用戶名查詢
json建索引 直接建 json會(huì)超長(zhǎng)，所以 index((CAST(data->>'$.name' as char(30))))
查看全部 -
一、賬戶與安全
? ? 1、強(qiáng)制分開用戶創(chuàng)建和用戶授權(quán)
? ? #、prompt 字符：可以更改mysql命令提示符
二、認(rèn)證插件更新
? ? 1、之前版本是mysql_native_password，8.0版本是caching_sha2_password
???? ??? mysql> show variables like '%default%';
???? ??? +---------------------------------+-----------------------+
???? ??? | Variable_name | Value |
???? ??? +---------------------------------+-----------------------+
???? ??? | default_authentication_plugin |?caching_sha2_password?|? ? 2、由于認(rèn)證插件的更新，客戶端需要升級(jí)才可以連接到8.0，或者服務(wù)器修改用戶認(rèn)證插件：
???? ? ? alter user?root@'%'?identified with mysql_native_password by '123';
三、密碼管理
? ? 1、8.0版本開始限制重復(fù)使用以前的密碼
???? ? ? password_history=3 ????? ? ?--不允許和最近三次密碼一樣
???? ? ? password_reuse_interval=90 --不允許和最近90天內(nèi)的密碼一樣
???? ? ? password_require_current=ON --修改密碼需要提供當(dāng)前密碼
???? ? ? 語句：
???? ????? ? ?alter user?user@host?identified by 'new_password' replace 'cur_password';
? ? 2、這三個(gè)變量可以全局設(shè)置或者利用alter user?user@host?identified by '123' password_history 針對(duì)用戶設(shè)置
? ? 3、歷史密碼都是保存在mysql.password_history
? ? #、set persist var=value? --對(duì)變量持久化修改
????原理：將persist設(shè)置的變量寫入到數(shù)據(jù)目錄下的配置文件中（json格式），服務(wù)器啟動(dòng)時(shí)也會(huì)讀取該配置文件
四、角色管理
? ? 1、8.0版本提供了角色管理功能，角色是一組權(quán)限的集合，即把一組權(quán)限放在一起并起一個(gè)名字，就成為了一個(gè)角色
? ? 2、角色分配步驟
???? ? ? 創(chuàng)建角色
???? ???? ????? ? ?create roll 'new_role'；? ? ?--創(chuàng)建了一個(gè)用戶
???? ? ? 給角色分配權(quán)限
???? ???? ????? ? ?grant insert,select on test.* to 'new_role';
???? ? ? 給用戶指定角色
???? ???? ????? ? ?grant [default] 'new_role' to 'user'@host; --不使用default的話，默認(rèn)登錄后需要用set role激活角色，使用default后就已經(jīng)激活
???? ? ? ?指定用戶
???? ???? ????? ? ?set role 'new_role';
? ? 3、查看用戶權(quán)限
???? ? ? show grant for 'user'@host using 'new_role';
? ? 4、顯式當(dāng)前用戶使用的角色
???? ? ? select current_role();
五、優(yōu)化器索引
? ? 1、隱藏索引（invisible index）
???? ????? ? ?不會(huì)被優(yōu)化器使用，但是仍然需要進(jìn)行維護(hù)
???? ????? ? ?create index index_name on tab(col) invisible;
? ? ??? #、如果想設(shè)置優(yōu)化器對(duì)隱藏索引可見，可以修改optimizer_switch中的use_invisible_index=on;（可以會(huì)話級(jí)別修改）
???? ? ?#、修改索引可見性
???? ????? ? ?alter table tab alter index index_name visible;
???? ? ?#、主鍵不能設(shè)置為不可見
???? ? ? 使用場(chǎng)景：
???? ???? ?????軟刪除? ? ?--刪除一個(gè)索引，并不用真的刪除，將其設(shè)置為隱藏索引即可
? ? 2、降序索引
???? ? ? 8.0版本中只有innodb的Btree支持降序索引
???? ? ? 8.0版本中不再對(duì)group by操作進(jìn)行隱式排序
? ? 3、函數(shù)索引
???? ? ? ?支持在索引中使用函數(shù)（表達(dá)式）的值
???? ? ? ?支持降序索引，支持json數(shù)據(jù)的索引
???? ? ? ?函數(shù)索引基于虛擬列功能實(shí)現(xiàn)
? ? 虛擬列建立：
? ? ? ? ?alter table tab add c3 varchar(30) generated always as (upper(c2));
? ? 創(chuàng)建：
???? ? ? create index index_name on tab(? (upper(c2))? )
六、通用表表達(dá)式（CTE）
? ? 1、即，with子句：
???? ? ? with cte_name as （select * from tab）
???? ? ? ?高級(jí)用法：
???? ? ? ????? ? ?with etc1(col1) as(select co1 from tab1 ),
???? ???? ???? ????? ? ?with etc2(col2) as(select co1*2 from etc1)
? ? 2、遞歸cte
???? ? ? with recursive cte_name(n) as(
???? ???? ? select 1
???? ??? ??? union all
???? ????? ? ?select n+1 from cte where n<5
???? ??? ??? )
???? ? ? ?select * from ct;
? ? 例：
???? ? ??mysql> with recursive etc(n) as( select 1 union select n*(n+1) from etc where n<=5) select * from etc;
???? ??? +------+
???? ??? | n |
???? ??? +------+
???? ??? | 1 |
???? ??? | 2 |
???? ??? | 6 |
???? ??? +------+???? mysql> with recursive cte(id,name,path) as ( select id ,name ,cast(id as char(200)) from t where boss is null union all select t.id ,t.name ,concat(ep.id,',',t.id) from cte as ep join t on ep.id=t.boss ) select * from cte;
???? ??? +------+------+------+
???? ??? | id | name | path |
???? ??? +------+------+------+
???? ??? | 6 | f | 6 |
???? ??? | 3 | c | 6,3 |
???? ??? | 1 | a | 3,1 |
???? ??? | 4 | d | 1,4 |
? ? ? ? ?| 5 | e | 4,5 |
???? ??? | 2 | b | 5,2 |
???? ??? +------+------+------+? ? 3、遞歸限制
???? ? ? 正常操作應(yīng)該在cte中設(shè)置一個(gè)停止條件，否則將會(huì)陷入死循環(huán)，但是mysql為了避免死循環(huán)的發(fā)生，對(duì)遞歸深度有了限制，cte_max_recursion_depth、max_execution_time
? ? 例1：階乘
???? ????MySQL [test]> with recursive cte as( select 1 x,2 y union all select x*y,y+1 from cte where x<200 ) select * from cte;
???? ??? ??? +------+------+
???? ??? ? ? ?|x | y |
???? ??? ??? +------+------+
???? ??? ??? | 1 | 2 |
???? ??? ??? | 2 | 3 |
???? ??? ??? | 6 | 4 |
???? ??? ??? | 24 | 5 |
???? ??? ??? | 120 | 6 |
???? ??? ??? | 720 | 7 |
???? ??? ??? +------+------+? ? 例2：斐波那契數(shù)列
???? ????MySQL [test]> with recursive cte as(select 1 x,1 y union all select y x,x+y y from cte where cte.x<20) select * from cte;
???? ??? +------+------+
???? ??? | x | y |
???? ??? +------+------+
???? ??? | 1 | 1 |
???? ??? | 1 | 2 |
???? ??? | 2 | 3 |
???? ??? | 3 | 5 |
???? ??? | 5 | 8 |
???? ??? | 8 | 13 |
???? ??? | 13 | 21 |
???? ??? | 21 | 34 |
???? ??? +------+------+七、每門課程的第一名
???? ????MySQL [test]> select * from score a where? (select distinct count(*) num from score b where? a.course=b.course and a.id!=b.id and b.score>a.score) = 0;?? ? ---0為第一，1為第二，2為第三
八、窗口函數(shù)
? ? 1、聚合函數(shù)都可以用窗口函數(shù)改寫，如：
???? ???? ????mysql> select *,avg(score)over(partition by stuid) from score;
? ? 2、專用窗口函數(shù)
???? ? ? ?ROW_NUMBER()/RANK()/DENSE_RANK()/PERCENT_RANK()? ? ?--獲取排名
???? ? ? ?FIRST_VALUE()/LAST_VALUE/LEAD()/LAG()? ? ?
???? ?????CUME_DIST()/NTH_VALUE()/NTILE()
? ? 3、ROW_NUMBER():分組后內(nèi)部編號(hào)，編號(hào)至于前后位置相關(guān)，與內(nèi)容無關(guān)
? ? 4、RANK():分組后內(nèi)部編號(hào)，排序后編號(hào)，如果值一樣則編號(hào)一樣
? ? 5、窗口定義
???? ? ? window_function(expr)
???? ? ? ?OVER(PARTITION BY ...
???? ??? ??? ??? ??? ?ORDER BY ...
???? ??? ??? ??? ??? ? frame_clause...)
???? ? ? ?CURRENT ROW :當(dāng)前處理的行
???? ? ? ?M PRECEDING? ? ?:當(dāng)前處理行第前M行
???? ?????N? FOLLOWING? ?:當(dāng)前處理行第后N行
???? ? ? ?UNBOUNDED PRECEDING? ? ?:分組內(nèi)部最前沿
???? ? ? ?UNBOUNDED FOLLOWING? ? ?:分組內(nèi)最下沿
? ? 6、高級(jí)定義（可以省去多次寫窗口內(nèi)容）
???? ? ? ?window_function1(expr)
???? ? ? ? OVER w as 'col1',
?????? ? ?window_function2(expr)
?????? ? ?OVER w as 'col2'
?????? ? ?FROM table
?????? ? ?WINDOW w AS(PARTITION BY col ORDER BY col ROW 1 PRECEDING AND 1 FOLLOWING);
九、集成數(shù)據(jù)字典
?????? ? ?1、刪除了之前版本的元數(shù)據(jù)文件，如opt、frm文件，對(duì)innodb只剩余了ibd文件
??????????2、系統(tǒng)表（mysql）和數(shù)據(jù)字典全部改為innodb存儲(chǔ)引擎
?????? ? ?3、支持ddl原子性
?????? ? ?4、簡(jiǎn)化了information_schema的實(shí)現(xiàn)，提高了訪問性能
?????? ? ?5、提供了序列化字典信息（SDI）的支持，以及ibd2sdi工具
?????? ? ?6、innodb_read_only影響所有存儲(chǔ)引擎，因?yàn)閿?shù)據(jù)字典是innodb存儲(chǔ)引擎的；對(duì)普通用戶數(shù)據(jù)字典是不可見，無法查詢和修改
?????? ? 7、自增列持久化，將自增列計(jì)數(shù)器的最大值寫入redo log，同時(shí)在每次檢查點(diǎn)將其寫入引擎私有的系統(tǒng)表，會(huì)感知到每次對(duì)自增列中值的修改
?????? ? ?8、死鎖檢查控制
??????????????mysql> show variables like '%innodb_deadlock%';
???????????????? +------------------------+-------+
???????????????? | Variable_name | Value |
???????????? ???? +------------------------+-------+
???????????????? | innodb_deadlock_detect | ON |
???????????????? +------------------------+-------+?????????? ? ?死鎖檢測(cè)會(huì)有性能消耗，在高并發(fā)場(chǎng)景下可以考慮關(guān)閉死鎖檢測(cè)，以提高系統(tǒng)性能
?????? ? ?9、鎖定語句新增選項(xiàng)（僅針對(duì)行鎖起作用）
?????????? ? ?select ... for share/select for update [NOWAIT|SKIP LOCKED]
?????????? ? ?NOWAIT:如果請(qǐng)求的行被其它事務(wù)鎖定，語句立即返回錯(cuò)誤信息
?????????? ? ?SKIP LOCKED,從返回的結(jié)果集中移除被鎖定的行，只返回未被鎖定的行
?????? ? ?10、支持部分快速DDL，ALTER TABLE ... ALGORITHM=INSTANT;
?????? ? ?11、新增靜態(tài)變量innodb_dedicated_server：可以自動(dòng)配置innodb_buffer_pool_size/innodb_log_buffer_size等
?????? ? ?12、默認(rèn)創(chuàng)建兩個(gè)undo表空間，不再使用共享表空間
十、json內(nèi)聯(lián)路徑操作符
? ? 1、column>>path? = json_unquote(column->path)
十一、json聚合函數(shù)
? ? 1、json_arrayagg(),用于生產(chǎn)json數(shù)組，將多行數(shù)據(jù)組合成json數(shù)組
? ? 2、json_objectagg(),用于生成json對(duì)象，對(duì)于同一屬性的對(duì)個(gè)值，選取最后一個(gè)值
十二、json使用函數(shù)
? ? 1、json_pretty()：輸出json內(nèi)容時(shí)，進(jìn)行格式化
? ? 2、json_storage_size()：返回json數(shù)據(jù)占用的存儲(chǔ)空間
十三、json合并函數(shù)
? ? 1、json_merge_patch()：用于將兩個(gè)json對(duì)象合并為一個(gè)對(duì)象，如果有相同節(jié)點(diǎn)，則第二個(gè)中的覆蓋第一個(gè)json中的節(jié)點(diǎn)
? ? 2、json_merge_preserv()：用于將兩個(gè)json對(duì)象合并為一個(gè)對(duì)象，如果有相同節(jié)點(diǎn)，都會(huì)保留并將值合并為數(shù)組
十四、json表函數(shù)
? ? 1、json_table()：將json數(shù)據(jù)轉(zhuǎn)換為關(guān)系表
查看全部 -
8.0 新增角色管理功能（可先將權(quán)限賦給角色，再將角色賦給用戶）
create role 'xxx_role'; //角色在mysql.user中創(chuàng)建一個(gè)沒有密碼的用戶
grant XXX,XXX,XXX on XXXdb.* to 'xxx_role';
grant 'xxx_role' to 'user1';
show grants for 'user1';
show grants for 'user1' using 'xxx_role';
set default role 'xxx_role' to 'user1'; // 修改用戶使用的默認(rèn)角色，否則用戶登錄后默認(rèn)角色為NONE，用戶有多個(gè)角色需要默認(rèn)啟用，使用set default role all to 'user1';
select * from mysql.default_roles;
revoke XXX,XXX,XXX on XXXdb.* from 'xxx_role'; //回收角色權(quán)限
查看全部 -
MySQL5.7自增列bug
查看全部 -
原子DDL操作
查看全部 -
移動(dòng)平均值
查看全部 -
生成利潤(rùn)累計(jì)和
查看全部 -
set persist global password_history=6查看全部
-
角色是一組權(quán)限的集合
查看全部 -
隱藏索引不會(huì)被優(yōu)化器使用，但仍然需要進(jìn)行維護(hù)。
應(yīng)用場(chǎng)景：軟刪除、灰度發(fā)布。
查看全部
舉報(bào)