The user specified as a definer (‘dfgx‘@‘%‘) does not exist

问题描述

mysql查询视图时，报错：The user specified as a definer ('dfgx'@'%') does not exist

查看视图的创建语句如下：

create view example_view
as 
select *from bxm_rule_type;

---

原因分析：

与创建视图的DEFINER与SQL SECURITY这两个属性有关

使用sql语句查看完整的视图创建语句

show create view example_view;

结果如下：

CREATE ALGORITHM = UNDEFINED DEFINER = `dfgx` @`%` SQL SECURITY DEFINER VIEW `example_view` AS SELECT
`bxm_rule_type`.`ID` AS `ID`,
`bxm_rule_type`.`TYPE_CODE` AS `TYPE_CODE`,
`bxm_rule_type`.`TYPE_NAME` AS `TYPE_NAME`,
`bxm_rule_type`.`P_CODE` AS `P_CODE`,
`bxm_rule_type`.`CREATE_TIME` AS `CREATE_TIME`,
`bxm_rule_type`.`UPDATE_TIME` AS `UPDATE_TIME`,
`bxm_rule_type`.`DELETE_FLAG` AS `DELETE_FLAG`,
`bxm_rule_type`.`CREATE_USER` AS `CREATE_USER`,
`bxm_rule_type`.`MODIFIER_USER` AS `MODIFIER_USER`,
`bxm_rule_type`.`TENANT_ID` AS `TENANT_ID`,
`bxm_rule_type`.`ATTR1` AS `ATTR1`,
`bxm_rule_type`.`ATTR2` AS `ATTR2`,
`bxm_rule_type`.`ATTR3` AS `ATTR3` 
FROM
	`bxm_rule_type`

可以发现，如果创建视图不指定DEFINER与SQL SECURITY两个属性，DEFINER默认取的就是创建视图时当前连接mysql的用户，SQL SECURITY的值就是DEFINER

如果程序中mysql的jdbc连接的用户与视图的DEFINER不一致，就会报如上错误

---

解决方案：

方案1：将来程序jdbc连接的用户与视图的DEFINER保持一致

方案2：创建视图时，将SQL SECURITY的值设置为INVOKER

补充：DEFINER与SQL SECURITY参考如下网站

https://zhuanlan.zhihu.com/p/397872004?utm_id=0