mysql常见用法及mysql8

MYSQL

---

一、mysql多行数据合并成一行

1.GROUP_CONCAT（）中的值为你要合并的数据的字段名;
SEPARATOR 函数是用来分隔这些要合并的数据的；
' '中是你要用哪个符号来分隔；
2.必须要用GROUP BY 语句来进行分组管理，不然所有的数据都会被合并成一条记录


举例：一个学校有多个销售人员，销售人员之间用”,“隔开,查询结果如下图所示
SELECT
	bo.*,
	bu.`name` username,
	bu.phone userphone,
	GROUP_CONCAT( bu1.`name` SEPARATOR ',' ) salesman 
FROM
	base_org bo
	LEFT JOIN base_userinfo bu ON bu.orgid = bo.id 
	AND bu.type = 1 AND bu.isdel = 0
	LEFT JOIN base_user_school bus ON bus.orgid = bo.id
	LEFT JOIN base_userinfo bu1 ON bu1.id = bus.userid 
	AND bu1.type = 2  AND bu1.isdel = 0
WHERE
	FIND_IN_SET(
		bo.id,
	queryChildrenAreaInfo ( 1 )) 
	AND bo.type = '学校' 
	AND bo.isdel = 0 
GROUP BY bo.id

二、通用表表达式

MySQL8.0开始支持通用表表达式（CTE），即WITH子句。

派生表
select * from (select 1) as dt ;

通用表表达式
with cte as (select 1) select * from cte ;

递归CTE在查询中引用自己的定义，使用RECURSIVE

with recursive cte (n) as
(select 1
union all 
select n+1 from cte where n<5
)select * from cte;

递归表达式的查询中需要包含一个终止递归的条件

cte_max_recursion_depth   递归最大执行次数
max_execution_time     递归最大执行时间

三、窗口函数

window_function(expr)
over (partition by … 对数据进行分组
order by … 对分组数据进行排序
frame_clause … 进一步限定窗口
)

ROW_NUMBER()/RANK()/DENSE_RANK()/PERCENT_RANK()
获取排名
FIRST_VALUE()/LAST_VALUE()/LEAD()/LAG()
获取第几个
CUME_DIST()/NTH_VALUE()/NTILE()
累计第几位               百分位

四、InnoDB增强

集成数据字典

8.0删除了之前版本的元数据文件，例如.frm,.opt等。
将系统表和数据字典表全部改为InnoDB存储引擎。
支持原子DDL语句。
简化了INFORMATION_SCHEMA的实现，提高了访问新能。
提供了序列化字段信息（SDI）的支持，以及ibd2sdi工具。
数据字典使用上的差异，例如innodb_read_only影响所有的存储引擎；数据字典表不可见，不能直接查询和修改。

原子DDL操作

8.0开始支持原子DDL操作，其中与表相关的原子DDL只支持Innodb存储引擎
一个原子DDL操作内容包括：更新数据字典，存储引擎层的操作，在binlog中记录DDL操作。
支持与表相关的DDL:数据库、表空间、表、索引的CREATE、ALTER、DROP以及TRUNCATE TABLE。
支持的其他DDL:存储程序、触发器、视图、UDF的CREATE、DROP以及ALTER语句。
支持账户管理相关的DDL:用户和角色的CREATE、ALTER、DROP以及适用的RENAME,以及GRANT和REVOKE语句。

自增列持久化

5.7以及早期版本，InnoDB自增列计数器（AUTO_INCREMENT）的值只存储在内存中。
8.0每次变化时将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表。（解决了自增列可能重复的bug）

死锁检查控制

8.0增加了一个新的动态变量，用于控制系统是否执行InnoDB死锁检查。innodb_deadlock_detect，默认开启
对于高并发的系统，禁用死锁检查可能带来性能的提高。

锁定语句选项

select ... for share 和 select ...for update 中支持NOWAIT、SKIP LOCKED选项
对于NOWAIT,如果请求的行被其他事务锁定时，语句立即返回。
对于SKPIP LOCKED,从返回的结果集中移除被锁定的行。

其他改进功能

支持部分快速DDDL,ALTER TABLE ...ALGORITHM = INSTANT;
InnoDB 临时表使用共享的临时表空间ibtmp1。
新增静态变量innoDB_dedicated_server,自动配置InnoDB内存参数：innodb_buffer_pool_size/innodb_log_file_size等。
新增表INFOERMATION_SCHEMA.INNODB_CACHED_INDEXES,显示每个索引缓存在InnoDB缓冲池中的索引页数
新增视图INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF,为InnoDB表空间提供相关元数据信息。
默认创建2个UNDO表空间，不再使用系统表空间。
支持ALTER TABLESPACE ...RENAME TO 重命名通用表空间。
支持使用innodb_directories选项在服务器停止时将表空间文件移动到新的位置。
InnoDB表空间加密特性支持重做日志和撤销日志。

五、JSON增强

内联路径操作符

8.0增加了JSON操作符column->>path,等价于JSON_UNQUOTE(column->path)或者JSON_UNQUOTE(JSON_EXTRACT(column,path))

JSON聚合函数（8.0 5.7.22）

JSON_ARRAYAGG(),用于生成JSON数组
JSON_OBJECTAGG(),用于生成JSON对象

JSON实用函数（8.0 5.7.22）

JSON_PRETTY()  格式化，美化输出
JSON_STORAGE_SIZE()  数据所占用的存储空间
JSON_STORAGE_FREE() 更新列后可能释放出来的存储空间

JSON合并函数

JSON_MERGE_PATCH()  将两个JSON对象合并成一个对象  存在相同的key,后者value会覆盖前者
JSON_MERGE_PRESERV()  将两个JSON对象合并成一个对象 存在相同的key,两个value会合并成一个数组

JSON表函数

JSON_TABLE()  将JSON数据转换为关系表，可以将该函数的返回结果当作一个普通的表，使用sql进行查询