51工具盒子

依楼听风雨
笑看云卷云舒,淡观潮起潮落
当前位置：工具盒子 > 开发笔记 > 正文

【常见问题】DataEase 报错 Illegal mix of collations 的解决方法

2024-12-16 分类：开发笔记 阅读(118) 评论(0)

1 前言 {#1-%E5%89%8D%E8%A8%80}

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

2 原因 {#2-%E5%8E%9F%E5%9B%A0}

问题原因是函数和视图编码使用 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-%E8%A7%A3%E5%86%B3%E6%96%B9%E6%B3%95}

3.1 编辑 my.cnf {#3.1-%E7%BC%96%E8%BE%91-my.cnf}

vi  /etc/my.cnf

#增加如下：


[mysqld]


character_set_server=utf8mb4


collation-server=utf8mb4_general_ci


[client]


default-character-set=utf8mb4

3.2 重启数据库 {#3.2-%E9%87%8D%E5%90%AF%E6%95%B0%E6%8D%AE%E5%BA%93}

3.3 查询数据字符集 {#3.3-%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%AD%97%E7%AC%A6%E9%9B%86}

# 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，对视图和函数进行重建 {#3.4-%E5%91%BD%E4%BB%A4%E7%AA%97%E5%8F%A3%E4%B8%8D%E5%85%B3%EF%BC%8C%E6%89%A7%E8%A1%8C%E5%88%9B%E5%BB%BA%E8%A7%86%E5%9B%BE%E5%92%8C%E5%87%BD%E6%95%B0%E7%9A%84-sql%EF%BC%8C%E5%AF%B9%E8%A7%86%E5%9B%BE%E5%92%8C%E5%87%BD%E6%95%B0%E8%BF%9B%E8%A1%8C%E9%87%8D%E5%BB%BA}

-- ----------------------------
-- 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;

赞(10)
未经允许不得转载：工具盒子 » 【常见问题】DataEase 报错 Illegal mix of collations 的解决方法
标签：

厉飞雨

众生皆苦，唯有自渡！

相关推荐

最新文章

猜你喜欢

快捷分类

云服务器 日常运维 在线免费 开源工具 科学上网 开发笔记 区块链 白嫖帮 新视野 人工智能 网赚思路 Python笔记 Php笔记 Java笔记 链知识 币知识 IOS笔记 Android笔记 Mysql C语言笔记 前端开发 R笔记 Go笔记 mybatis笔记 Golang笔记 数据库 市场营销 软件教程 vps Rust笔记 软件使用 经验分享 Stable Diffusion chatgpt Midjourney 操作系统 Photoscape 加密货币 挖矿 Coze Meta 网络安全 网络运营 开源软件 Github ComfyUI 数字人 Docker笔记 Claude Oracle web3 linux windows mac redis git笔记 nginx kafka maven spring RocketMQ Zabbix ubuntu centos kubesphere ffmpeg jenkins hbase JavaScript笔记 WordPress nginx Hadoop vuejs mongo SQL Server PostgreSQL SQLite ElasticSearch Spark netty zookeeper RabbitMQ ClickHoust cocos2d gradle tomcat Markdown MinIO react Hexo git intellij idea android studio svn C++ spdlog nodejs C WordPress 宝塔面板 vim shell logstash 软件渗透 NeoDB CSS3 HTML Lua笔记 数据算法 嵌入式 debian 红帽RHEL Neo4j jquery nas 1panel aapanel 浏览器 谷歌 .net 微信 telegram Cloudflare 大数据 rust layui JumpServer DataEase TiDB DM数据库 虚拟机 elk sqlmap nacos Firefox C# Swagger Foxmail navicat DeepSeek