这次直接导入数据表和生成视图,结果在生成视图后出现了1449的错误,原SQL语句如下:
CREATE ALGORITHM = UNDEFINED DEFINER = `skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `sysusersauthorities` AS SELECT DISTINCT
`ur`.`userId` AS `userId`,
`a`.`authorityId` AS `authorityId`
FROM
(
(
`sysusersroles` `ur`
JOIN `sysrolesauthorities` `ra`
)
JOIN `sysauthorities` `a`
)
WHERE
(
(
`ur`.`roleId` = `ra`.`roleId`
)
AND (
`a`.`authorityPath` LIKE CONVERT (
concat(
'%-',
concat(`ra`.`authorityId`, ''),
'-%'
) USING gbk
)
)
)
在网上搜索过的方法尝试无效后,同事直接删除了ALGORITHM = UNDEFINED DEFINER = `skip-grants user`@`skip-grants host` SQL SECURITY DEFINER 这一句,结果视图就能正常使用了。原理不是很清楚,记一下。