知识库

Loading

0 评论 / 0 点赞 / 233 阅读  常见问题 最后更新: 2022-10-13 作者: 飞致云 总字数: 28425

1 前言

DataEase 系统数据库 5.7 迁移至 8.0 出现报错:Illegal mix of collations 初步判断,数据库链接的编码和数据库 sever 的编码不同导致,可能是函数与视图所使用的的字符集与表的不一致导致的。
image-1665627723928

2 原因

问题原因是函数和视图编码使用 MySQL8 默认的 utf8mb4_0900_ai_ci。

查询函数的字符集,会发现 collation_connection 使用的是默认 utf8mb4_0900_ai_ci

mysql> SHOW  CREATE  FUNCTION CHECK_TREE_NO_MANAGE_PRIVILEGE\G

image-1665627718912

图中已经被修改成 utf8mb4_general_ci。

3 解决方法

3.1 编辑 my.cnf

vi  /etc/my.cnf

#增加如下:

[mysqld]

character_set_server=utf8mb4

collation-server=utf8mb4_general_ci

 

[client]

default-character-set=utf8mb4

3.2 重启数据库

3.3 查询数据字符集

# show VARIABLES LIKE '%collation%';

mysql> show VARIABLES LIKE '%collation%';

+-------------------------------+--------------------+

| Variable_name                 | Value              |

+-------------------------------+--------------------+

| collation_connection          | utf8mb4_0900_ai_ci |

| collation_database            | utf8mb4_general_ci |

| collation_server              | utf8mb4_general_ci |

| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |

+-------------------------------+--------------------+
#对字符集进行设置

set collation_database=utf8mb4_general_ci;

set collation_connection=utf8mb4_general_ci;

set collation_server=utf8mb4_general_ci;

set default_collation_for_utf8mb4=utf8mb4_general_ci;

3.4 命令窗口不关,执行创建视图和函数的 SQL,对视图和函数进行重建

-- ----------------------------
-- View structure for v_auth_model
-- ----------------------------

DROP VIEW IF EXISTS `v_auth_model`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_auth_model` AS select `sys_user`.`user_id` AS `id`,`sys_user`.`username` AS `name`,`sys_user`.`username` AS `label`,'0' AS `pid`,'leaf' AS `node_type`,'user' AS `model_type`,'user' AS `model_inner_type`,'target' AS `auth_type`,`sys_user`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `sys_user` where (`sys_user`.`is_admin` <> 1) union all select `sys_role`.`role_id` AS `id`,`sys_role`.`name` AS `name`,`sys_role`.`name` AS `label`,'0' AS `pid`,'leaf' AS `node_type`,'role' AS `model_type`,'role' AS `model_inner_type`,'target' AS `auth_type`,`sys_role`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `sys_role` union all select `sys_dept`.`dept_id` AS `id`,`sys_dept`.`name` AS `name`,`sys_dept`.`name` AS `lable`,(cast(`sys_dept`.`pid` as char charset utf8mb4) collate utf8mb4_general_ci) AS `pid`,if((`sys_dept`.`sub_count` = 0),'leaf','spine') AS `node_type`,'dept' AS `model_type`,'dept' AS `model_inner_type`,'target' AS `auth_type`,`sys_dept`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `sys_dept` union all select `datasource`.`id` AS `id`,`datasource`.`name` AS `NAME`,`datasource`.`name` AS `label`,'0' AS `pid`,'leaf' AS `node_type`,'link' AS `model_type`,`datasource`.`type` AS `model_inner_type`,'source' AS `auth_type`,`datasource`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `datasource` union all select `dataset_group`.`id` AS `id`,`dataset_group`.`name` AS `NAME`,`dataset_group`.`name` AS `lable`,if((`dataset_group`.`pid` is null),'0',`dataset_group`.`pid`) AS `pid`,'spine' AS `node_type`,'dataset' AS `model_type`,`dataset_group`.`type` AS `model_inner_type`,'source' AS `auth_type`,`dataset_group`.`create_by` AS `create_by`,`dataset_group`.`level` AS `level`,0 AS `mode`,'0' AS `data_source_id` from `dataset_group` union all select `dataset_table`.`id` AS `id`,`dataset_table`.`name` AS `NAME`,`dataset_table`.`name` AS `lable`,`dataset_table`.`scene_id` AS `pid`,'leaf' AS `node_type`,'dataset' AS `model_type`,`dataset_table`.`type` AS `model_inner_type`,'source' AS `auth_type`,`dataset_table`.`create_by` AS `create_by`,0 AS `level`,`dataset_table`.`mode` AS `mode`,`dataset_table`.`data_source_id` AS `data_source_id` from `dataset_table` union all select `panel_group`.`id` AS `id`,`panel_group`.`name` AS `NAME`,`panel_group`.`name` AS `label`,(case `panel_group`.`id` when 'panel_list' then '0' when 'default_panel' then '0' else `panel_group`.`pid` end) AS `pid`,if((`panel_group`.`node_type` = 'folder'),'spine','leaf') AS `node_type`,'panel' AS `model_type`,`panel_group`.`panel_type` AS `model_inner_type`,'source' AS `auth_type`,`panel_group`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `panel_group` union all select `sys_menu`.`menu_id` AS `menu_id`,`sys_menu`.`title` AS `name`,`sys_menu`.`title` AS `label`,`sys_menu`.`pid` AS `pid`,if((`sys_menu`.`sub_count` > 0),'spine','leaf') AS `node_type`,'menu' AS `model_type`,(case `sys_menu`.`type` when 0 then 'folder' when 1 then 'menu' when 2 then 'button' end) AS `model_inner_type`,'source' AS `auth_type`,`sys_menu`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `sys_menu` where ((`sys_menu`.`i_frame` <> 1) or (`sys_menu`.`i_frame` is null)) union all select `plugin_sys_menu`.`menu_id` AS `menu_id`,`plugin_sys_menu`.`title` AS `name`,`plugin_sys_menu`.`title` AS `label`,`plugin_sys_menu`.`pid` AS `pid`,if((`plugin_sys_menu`.`sub_count` > 0),'spine','leaf') AS `node_type`,'menu' AS `model_type`,(case `plugin_sys_menu`.`type` when 0 then 'folder' when 1 then 'menu' when 2 then 'button' end) AS `model_inner_type`,'source' AS `auth_type`,`plugin_sys_menu`.`create_by` AS `create_by`,0 AS `level`,0 AS `mode`,'0' AS `data_source_id` from `plugin_sys_menu` where ((`plugin_sys_menu`.`i_frame` <> 1) or (`plugin_sys_menu`.`i_frame` is null));

-- ----------------------------
-- View structure for v_auth_privilege
-- ----------------------------
DROP VIEW IF EXISTS `v_auth_privilege`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_auth_privilege` AS select `sys_auth`.`auth_source` AS `auth_source`,`sys_auth`.`auth_source_type` AS `auth_source_type`,group_concat(`sys_auth_detail`.`privilege_extend` separator ',') AS `privileges` from (`sys_auth` left join `sys_auth_detail` on((`sys_auth`.`id` = `sys_auth_detail`.`auth_id`))) where ((`sys_auth_detail`.`privilege_value` = 1) and (((`sys_auth`.`auth_target_type` = 'dept') and (`sys_auth`.`auth_target` = (select `sys_user`.`dept_id` from `sys_user` where (`sys_user`.`user_id` = '4')))) or ((`sys_auth`.`auth_target_type` = 'user') and (`sys_auth`.`auth_target` = '4')) or ((`sys_auth`.`auth_target_type` = 'role') and (`sys_auth`.`auth_target` = (select `sys_users_roles`.`role_id` from `sys_users_roles` where (`sys_users_roles`.`user_id` = '4')))))) group by `sys_auth`.`auth_source`,`sys_auth`.`auth_source_type`;

-- ----------------------------
-- View structure for v_history_chart_view
-- ----------------------------
DROP VIEW IF EXISTS `v_history_chart_view`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_history_chart_view` AS select `chart_group`.`id` AS `id`,`chart_group`.`id` AS `inner_id`,`chart_group`.`name` AS `NAME`,`chart_group`.`name` AS `label`,`chart_group`.`pid` AS `pid`,`chart_group`.`type` AS `model_inner_type`,'spine' AS `node_type`,'view' AS `model_type`,1 AS `mode` from `chart_group` union all select distinct `chart_view`.`id` AS `id`,`chart_view`.`id` AS `inner_id`,`chart_view`.`name` AS `NAME`,`chart_view`.`name` AS `label`,`chart_view`.`scene_id` AS `pid`,`chart_view`.`type` AS `model_inner_type`,'leaf' AS `node_type`,'view' AS `model_type`,1 AS `mode` from `chart_view` where (`chart_view`.`chart_type` = 'public');

-- ----------------------------
-- Function structure for CHECK_TREE_NO_MANAGE_PRIVILEGE
-- ----------------------------
DROP FUNCTION IF EXISTS `CHECK_TREE_NO_MANAGE_PRIVILEGE`;
delimiter ;;
CREATE FUNCTION `CHECK_TREE_NO_MANAGE_PRIVILEGE`(userId varchar(255),modelType varchar(255),nodeId varchar(255))
 RETURNS int
  READS SQL DATA 
BEGIN

DECLARE privilegeType INTEGER;
DECLARE allTreeIds longtext;
DECLARE allPrivilegeTreeIds longtext;
DECLARE result INTEGER;

select privilege_type into privilegeType from sys_auth_detail where auth_id =modelType and privilege_extend ='manage';
select GET_V_AUTH_MODEL_WITH_CHILDREN( nodeId ,modelType) into allTreeIds;
select GET_V_AUTH_MODEL_WITH_PRIVILEGE(userId,modelType,privilegeType) into allPrivilegeTreeIds;
select count(id) into result from v_auth_model where v_auth_model.model_type=modelType and FIND_IN_SET(v_auth_model.id,allTreeIds) and (!FIND_IN_SET(v_auth_model.id,allPrivilegeTreeIds) or ISNULL(allPrivilegeTreeIds));

RETURN result;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for copy_auth
-- ----------------------------
DROP FUNCTION IF EXISTS `copy_auth`;
delimiter ;;
CREATE FUNCTION `copy_auth`(authSource varchar(255),authSourceType varchar(255),authUser varchar(255))
 RETURNS varchar(255) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE authId varchar(255);

DECLARE userId  varchar(255);

DECLARE copyId  varchar(255);

DECLARE selectPid  varchar(255);

select uuid() into authId;

select uuid() into copyId;

select max(sys_user.user_id) into userId from sys_user where username= authUser;

SELECT  pid  into selectPid FROM v_auth_model WHERE id = authSource  AND model_type = authSourceType;

delete from sys_auth_detail where auth_id in (
select id from  sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType
);

delete from sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType;

INSERT INTO sys_auth (
 id,
 auth_source,
 auth_source_type,
 auth_target,
 auth_target_type,
 auth_time,
 auth_user
)
VALUES
 (
  authId,
  authSource,
  authSourceType,
  userId,
  'user',
 unix_timestamp(
 now())* 1000,'auto');

 INSERT INTO  sys_auth_detail (
            id,
            auth_id,
            privilege_name,
            privilege_type,
            privilege_value,
            privilege_extend,
            remark,
            create_user,
            create_time
        ) SELECT
        uuid() AS id,
        authId AS auth_id,
        sys_auth_detail.privilege_name,
        sys_auth_detail.privilege_type,
        1,
        sys_auth_detail.privilege_extend,
        sys_auth_detail.remark,
        'auto' AS create_user,
        unix_timestamp(now())* 1000 AS create_time
        FROM
            sys_auth_detail where auth_id =authSourceType;

/**继承第一父级权限**/

insert into sys_auth(
id,
 auth_source,
 auth_source_type,
 auth_target,
 auth_target_type,
 auth_time,
 auth_user,
 copy_from,
 copy_id
)
SELECT
 uuid() as id,
 authSource as auth_source,
 authSourceType as auth_source_type,
 auth_target,
 auth_target_type,
 NOW()* 1000 as auth_time,
 'auto' as auth_user,
 id as copy_from,
 copyId as copy_id
FROM
 sys_auth
WHERE
 auth_source =selectPid
 AND auth_source_type = authSourceType
 and  concat(auth_target,'-',auth_target_type) !=CONCAT(userId,'-','user');

INSERT INTO sys_auth_detail (
 id,
 auth_id,
 privilege_name,
 privilege_type,
 privilege_value,
 privilege_extend,
 remark,
 create_user,
 create_time,
 copy_from,
 copy_id
) SELECT
uuid() AS id,
sa_copy.t_id AS auth_id,
sys_auth_detail.privilege_name,
sys_auth_detail.privilege_type,
sys_auth_detail.privilege_value,
sys_auth_detail.privilege_extend,
sys_auth_detail.remark,
'auto' AS create_user,
unix_timestamp(
now())* 1000 AS create_time,
id AS copy_from,
copyId AS copy_id
FROM
 sys_auth_detail
 INNER JOIN (
 SELECT
  id AS t_id,
  copy_from AS s_id
 FROM
  sys_auth
 WHERE
  copy_id = copyId
 ) sa_copy ON sys_auth_detail.auth_id = sa_copy.s_id;

RETURN 'success';

END
;;
delimiter ;

-- ----------------------------
-- Function structure for delete_auth_source
-- ----------------------------
DROP FUNCTION IF EXISTS `delete_auth_source`;
delimiter ;;
CREATE FUNCTION `delete_auth_source`(authSource varchar(255),authSourceType varchar(255))
 RETURNS varchar(255) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

delete from sys_auth_detail where auth_id in (
select id from  sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType
);

delete from sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType;

RETURN 'success';

END
;;
delimiter ;

-- ----------------------------
-- Function structure for delete_auth_target
-- ----------------------------
DROP FUNCTION IF EXISTS `delete_auth_target`;
delimiter ;;
CREATE FUNCTION `delete_auth_target`(authTarget varchar(255),authTargetType varchar(255))
 RETURNS varchar(255) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

delete from sys_auth_detail where auth_id in (
select id from  sys_auth where sys_auth.auth_target=authTarget and sys_auth.auth_target_type=authTargetType
);

delete from sys_auth where sys_auth.auth_target=authTarget and sys_auth.auth_target_type=authTargetType;

RETURN 'sucess';

END
;;
delimiter ;

-- ----------------------------
-- Function structure for get_auths
-- ----------------------------
DROP FUNCTION IF EXISTS `get_auths`;
delimiter ;;
CREATE FUNCTION `get_auths`(authSource varchar(255),modelType varchar(255),userId varchar(255))
 RETURNS longtext CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTemp longtext;

SELECT
	group_concat( DISTINCT sys_auth_detail.privilege_extend) into oTemp
FROM
	(
		`sys_auth`
		LEFT JOIN `sys_auth_detail` ON ((
				`sys_auth`.`id` = `sys_auth_detail`.`auth_id`
			)))
			where sys_auth_detail.privilege_value =1
			and sys_auth.auth_source=authSource
			AND (
				(
					sys_auth.auth_target_type = 'dept'
					AND sys_auth.auth_target in ( SELECT dept_id FROM sys_user WHERE user_id = userId )
				)
				OR (
					sys_auth.auth_target_type = 'user'
					AND sys_auth.auth_target = userId
				)
				OR (
					sys_auth.auth_target_type = 'role'
					AND sys_auth.auth_target in ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
				)
			)
GROUP BY
	`sys_auth`.`auth_source`,
	`sys_auth`.`auth_source_type`;

RETURN oTemp;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for get_auth_children_count
-- ----------------------------
DROP FUNCTION IF EXISTS `get_auth_children_count`;
delimiter ;;
CREATE FUNCTION `get_auth_children_count`(pidInfo varchar(255),modelType varchar(255),userName varchar(255))
 RETURNS varchar(255) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE childrenCount INTEGER;

select count(1)-1 into childrenCount from v_auth_model where FIND_IN_SET(
		v_auth_model.id,
	GET_V_AUTH_MODEL_WITH_CHILDREN ( pidInfo, modelType ))
	AND create_by = userName
	AND v_auth_model.node_type = 'leaf';

RETURN childrenCount;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_CHART_GROUP_WITH_CHILDREN
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_CHART_GROUP_WITH_CHILDREN`;
delimiter ;;
CREATE FUNCTION `GET_CHART_GROUP_WITH_CHILDREN`(parentId varchar(8000))
 RETURNS longtext CHARSET utf8mb3
  READS SQL DATA 
BEGIN

DECLARE oTemp LONGTEXT;

DECLARE oTempChild LONGTEXT;

SET oTemp = '';

SET oTempChild = CAST(parentId AS CHAR);

WHILE oTempChild IS NOT NULL

DO

SET oTemp = CONCAT(oTemp,',',oTempChild);

SELECT GROUP_CONCAT(id) INTO oTempChild FROM v_history_chart_view WHERE FIND_IN_SET(pid,oTempChild) > 0;

END WHILE;

RETURN oTemp;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_CHART_VIEW_COPY_NAME
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_CHART_VIEW_COPY_NAME`;
delimiter ;;
CREATE FUNCTION `GET_CHART_VIEW_COPY_NAME`(chartId varchar(255),pid varchar(255))
 RETURNS varchar(255) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE chartName varchar(255);

DECLARE regexpInfo varchar(255);

DECLARE chartNameCount INTEGER;

select `name`  into chartName from chart_view where id =chartId;
/**
因为名称存在()等特殊字符,所以不能直接用REGEXP进行查找,qrtz_locks
1.用like 'chartName%' 过滤可能的数据项
2.REPLACE(name,chartName,'') REGEXP '-copy\\(([0-9])+\\)$' 过滤去掉chartName后的字符以 -copy(/d) 结尾的数据
3.(LENGTH(REPLACE(name,chartName,''))-LENGTH(replace(REPLACE(name,chartName,''),'-',''))=1) 确定只出现一次 ‘-’ 防止多次copy
**/
select (count(1)+1) into chartNameCount from chart_view
where (LENGTH(REPLACE(name,chartName,''))-LENGTH(replace(REPLACE(name,chartName,''),'-',''))=1)
and REPLACE(name,chartName,'') REGEXP '-copy\\(([0-9])+\\)$' and name like CONCAT(chartName,'%') and chart_view.scene_id=pid ;

RETURN concat(chartName,'-copy(',chartNameCount,')');

END
;;
delimiter ;

-- ----------------------------
-- Function structure for get_grant_auths
-- ----------------------------
DROP FUNCTION IF EXISTS `get_grant_auths`;
delimiter ;;
CREATE FUNCTION `get_grant_auths`(modelType VARCHAR ( 255 ),
	userId VARCHAR ( 255 ))
 RETURNS longtext CHARSET utf8mb4
  READS SQL DATA 
BEGIN
	DECLARE
		oTemp LONGTEXT;
	SELECT
		GROUP_CONCAT( DISTINCT v_auth_model.id ) into oTemp
	FROM
		v_auth_model
		LEFT JOIN sys_auth ON v_auth_model.id = sys_auth.auth_source
		AND v_auth_model.model_type = sys_auth.auth_source_type
		LEFT JOIN sys_auth_detail ON sys_auth.id = sys_auth_detail.auth_id
	WHERE
		privilege_type = 15
		AND privilege_value = 1
		AND v_auth_model.model_type = modelType
		AND (
			(
				sys_auth.auth_target_type = 'dept'
				AND sys_auth.auth_target IN ( SELECT dept_id FROM sys_user WHERE user_id = userId )
			)
			OR (
				sys_auth.auth_target_type = 'user'
				AND sys_auth.auth_target = userId
			)
			OR (
				sys_auth.auth_target_type = 'role'
				AND sys_auth.auth_target IN ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
			)
		);
	RETURN oTemp;

	END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_PANEL_GROUP_WITH_CHILDREN
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_PANEL_GROUP_WITH_CHILDREN`;
delimiter ;;
CREATE FUNCTION `GET_PANEL_GROUP_WITH_CHILDREN`(parentId varchar(8000))
 RETURNS varchar(8000) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTemp VARCHAR(8000);

DECLARE oTempChild VARCHAR(8000);

SET oTemp = '';

SET oTempChild = CAST(parentId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;

WHILE oTempChild IS NOT NULL

DO

SET oTemp = CONCAT(oTemp,',',oTempChild);

SELECT GROUP_CONCAT(id) INTO oTempChild FROM panel_group WHERE FIND_IN_SET(pid,oTempChild) > 0;

END WHILE;

RETURN oTemp;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_PANEL_TEMPLATE_WITH_CHILDREN
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_PANEL_TEMPLATE_WITH_CHILDREN`;
delimiter ;;
CREATE FUNCTION `GET_PANEL_TEMPLATE_WITH_CHILDREN`(parentId varchar(8000))
 RETURNS varchar(8000) CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTemp VARCHAR(8000);

DECLARE oTempChild VARCHAR(8000);

SET oTemp = '';

SET oTempChild = CAST(parentId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;


WHILE oTempChild IS NOT NULL

DO

SET oTemp = CONCAT(oTemp,',',oTempChild);

SELECT GROUP_CONCAT(id) INTO oTempChild FROM panel_template WHERE FIND_IN_SET(pid,oTempChild) > 0;

END WHILE;

RETURN oTemp;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_PANEL_WITH_PRIVILEGE_AND_MOBILE
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_PANEL_WITH_PRIVILEGE_AND_MOBILE`;
delimiter ;;
CREATE FUNCTION `GET_PANEL_WITH_PRIVILEGE_AND_MOBILE`(userId longtext,modelType varchar(255),privilegeType varchar(255))
 RETURNS longtext CHARSET utf8mb3
  READS SQL DATA 
BEGIN

DECLARE oTempLeafIds longtext;
select GROUP_CONCAT(auth_source) into oTempLeafIds from (
SELECT
			sys_auth.auth_source_type,
			sys_auth.auth_source
		FROM
			sys_auth
			LEFT JOIN sys_auth_detail ON sys_auth.id = sys_auth_detail.auth_id
		WHERE
			sys_auth_detail.privilege_type = privilegeType
			and sys_auth.auth_source_type = modelType
			AND (
				(
					sys_auth.auth_target_type = 'dept'
					AND sys_auth.auth_target in ( SELECT dept_id FROM sys_user WHERE user_id = userId )
				)
				OR (
					sys_auth.auth_target_type = 'user'
					AND sys_auth.auth_target = userId
				)
				OR (
					sys_auth.auth_target_type = 'role'
					AND sys_auth.auth_target in ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
				)
				OR (1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))
			)
			and sys_auth.auth_source in (select id from panel_group where mobile_layout='1')
		GROUP BY
			sys_auth.auth_source_type,
			sys_auth.auth_source
			having  (sum( sys_auth_detail.privilege_value )> 0 or 1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))) temp;
RETURN oTempLeafIds;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_V_AUTH_MODEL_ID_P_USE
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_V_AUTH_MODEL_ID_P_USE`;
delimiter ;;
CREATE FUNCTION `GET_V_AUTH_MODEL_ID_P_USE`(userId longtext,modelType varchar(255))
 RETURNS longtext CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTempLeafIds longtext;
DECLARE oTempAllIds longtext;

select GET_V_AUTH_MODEL_WITH_PRIVILEGE(userId,modelType,1) into oTempLeafIds;

select GROUP_CONCAT(id) into oTempAllIds from (select GET_V_AUTH_MODEL_WITH_PARENT ( oTempLeafIds ,modelType) cids) t, v_auth_model where v_auth_model.model_type=modelType and FIND_IN_SET(v_auth_model.id,cids) order by id asc;

RETURN oTempAllIds;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_V_AUTH_MODEL_ID_P_USE_MOBILE
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_V_AUTH_MODEL_ID_P_USE_MOBILE`;
delimiter ;;
CREATE FUNCTION `GET_V_AUTH_MODEL_ID_P_USE_MOBILE`(userId longtext,modelType varchar(255))
 RETURNS longtext CHARSET utf8mb3
  READS SQL DATA 
BEGIN

DECLARE oTempLeafIds longtext;
DECLARE oTempAllIds longtext;

select GET_PANEL_WITH_PRIVILEGE_AND_MOBILE(userId,modelType,1) into oTempLeafIds;

select GROUP_CONCAT(id) into oTempAllIds from (select GET_V_AUTH_MODEL_WITH_PARENT ( oTempLeafIds ,modelType) cids) t, v_auth_model where v_auth_model.model_type=modelType and FIND_IN_SET(v_auth_model.id,cids) order by id asc;

RETURN oTempAllIds;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_V_AUTH_MODEL_WITH_CHILDREN
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_V_AUTH_MODEL_WITH_CHILDREN`;
delimiter ;;
CREATE FUNCTION `GET_V_AUTH_MODEL_WITH_CHILDREN`(parentId longtext,modelType varchar(255))
 RETURNS longtext CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTemp longtext;

DECLARE oTempChild longtext;

SET oTemp = '';

SET oTempChild = CAST(parentId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;

WHILE oTempChild IS NOT NULL

DO

SET oTemp = CONCAT(oTemp,',',oTempChild);

SELECT GROUP_CONCAT(id) INTO oTempChild FROM V_AUTH_MODEL WHERE FIND_IN_SET(pid,oTempChild) > 0 and V_AUTH_MODEL.model_type=modelType order by id asc;

END WHILE;

RETURN oTemp;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_V_AUTH_MODEL_WITH_PARENT
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_V_AUTH_MODEL_WITH_PARENT`;
delimiter ;;
CREATE FUNCTION `GET_V_AUTH_MODEL_WITH_PARENT`(childrenId longtext,modelType varchar(255))
 RETURNS longtext CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTemp longtext;

DECLARE oTempParent longtext;

SET oTemp = '';

SET oTempParent = CAST(childrenId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;

WHILE oTempParent IS NOT NULL

DO

SET oTemp = CONCAT(oTemp,',',oTempParent);

SELECT GROUP_CONCAT(distinct pid) INTO oTempParent FROM V_AUTH_MODEL WHERE FIND_IN_SET(id,oTempParent) > 0 and V_AUTH_MODEL.model_type=modelType order by pid asc;

END WHILE;

RETURN oTemp;

END
;;
delimiter ;

-- ----------------------------
-- Function structure for GET_V_AUTH_MODEL_WITH_PRIVILEGE
-- ----------------------------
DROP FUNCTION IF EXISTS `GET_V_AUTH_MODEL_WITH_PRIVILEGE`;
delimiter ;;
CREATE FUNCTION `GET_V_AUTH_MODEL_WITH_PRIVILEGE`(userId longtext,modelType varchar(255),privilegeType varchar(255))
 RETURNS longtext CHARSET utf8mb4
  READS SQL DATA 
BEGIN

DECLARE oTempLeafIds longtext;
select GROUP_CONCAT(auth_source) into oTempLeafIds from (
SELECT
			sys_auth.auth_source_type,
			sys_auth.auth_source
		FROM
			sys_auth
			LEFT JOIN sys_auth_detail ON sys_auth.id = sys_auth_detail.auth_id
		WHERE
			sys_auth_detail.privilege_type = privilegeType
			and sys_auth.auth_source_type = modelType
			AND (
				(
					sys_auth.auth_target_type = 'dept'
					AND sys_auth.auth_target in ( SELECT dept_id FROM sys_user WHERE user_id = userId )
				)
				OR (
					sys_auth.auth_target_type = 'user'
					AND sys_auth.auth_target = userId
				)
				OR (
					sys_auth.auth_target_type = 'role'
					AND sys_auth.auth_target in ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
				)
				OR (1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))
			)
		GROUP BY
			sys_auth.auth_source_type,
			sys_auth.auth_source
			having  (sum( sys_auth_detail.privilege_value )> 0 or 1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))) temp;
RETURN oTempLeafIds;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table chart_group
-- ----------------------------
DROP TRIGGER IF EXISTS `new_auth_chart_group`;
delimiter ;;
CREATE TRIGGER `new_auth_chart_group` AFTER INSERT ON `chart_group` FOR EACH ROW select copy_auth(NEW.id,'chart',NEW.create_by) into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table chart_group
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_chart_group`;
delimiter ;;
CREATE TRIGGER `delete_auth_chart_group` AFTER DELETE ON `chart_group` FOR EACH ROW select delete_auth_source(OLD.id,'chart') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table dataset_group
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_dataset_group`;
delimiter ;;
CREATE TRIGGER `delete_auth_dataset_group` AFTER DELETE ON `dataset_group` FOR EACH ROW select delete_auth_source(OLD.id,'dataset') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table dataset_table
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_dataset_table`;
delimiter ;;
CREATE TRIGGER `delete_auth_dataset_table` AFTER DELETE ON `dataset_table` FOR EACH ROW select delete_auth_source(OLD.id,'dataset') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table datasource
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_link`;
delimiter ;;
CREATE TRIGGER `delete_auth_link` AFTER DELETE ON `datasource` FOR EACH ROW select delete_auth_source(OLD.id,'link') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table panel_group
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_panel`;
delimiter ;;
CREATE TRIGGER `delete_auth_panel` AFTER DELETE ON `panel_group` FOR EACH ROW select delete_auth_source(OLD.id,'panel') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table sys_dept
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_dept_target`;
delimiter ;;
CREATE TRIGGER `delete_auth_dept_target` AFTER DELETE ON `sys_dept` FOR EACH ROW select delete_auth_target(OLD.dept_id,'dept') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table sys_role
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_role_target`;
delimiter ;;
CREATE TRIGGER `delete_auth_role_target` AFTER DELETE ON `sys_role` FOR EACH ROW select delete_auth_target(OLD.role_id,'role') into @ee
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table sys_user
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_auth_user_target`;
delimiter ;;
CREATE TRIGGER `delete_auth_user_target` AFTER DELETE ON `sys_user` FOR EACH ROW select delete_auth_target(OLD.user_id,'user') into @ee
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;


文章目录
其他资源