mysql错误-UncategorizedSQLException 1449

最新推荐文章于 2023-03-21 07:49:16 发布

原创最新推荐文章于 2023-03-21 07:49:16 发布 · 9.5k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种在MySQL中创建视图时遇到的错误及其解决方案。错误源于使用了不正确的DEFINER子句，文章提供了修改前后的SQL语句对比，并解释了如何避免这一问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

错误：

UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1449];

解决：

出错的意思是数据库访问时没有‘@’这个用户.这主要是在新建视图和存储过程时，我直截在mysql可视图形工具里copy代码到服务器上去运行，mysql里会自动生成一些代码，比如copy出来的视图语句：
DROP VIEW IF EXISTS `testdb`.`v4bbs`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `v4bbs` AS select `a`.`topic_id` AS `did`,`a`.`topic_type` AS `topic_type`,`a`.`especial_flag` AS `topic_level`,`a`.`title` AS `title`,`a`.`url` AS `url`,`a`.`content` AS `content`,concat(`b`.`chinese_name`,_utf8' ',`c`.`chinese_name`,_utf8' ',`d`.`chinese_name`) AS `addr`,`a`.`author_id` AS `author_id`,`e`.`nikename` AS `author_nick`,`a`.`appear_date` AS `pub_time`,`a`.`restor_count` AS `reply`,`a`.`last_restor_user_id` AS `last_reply_id`,`a`.`last_restor_nickName` AS `last_reply_nick`,`a`.`last_restor_date` AS `last_reply_time`,`a`.`last_update_date` AS `last_modify_time`,`a`.`fingerprint` AS `fp` from ((((`tb4topic` `a` join `tb4position` `b`) join `tb4position` `c`) join `tb4position` `d`) join `tb4user` `e`) where ((`a`.`pos_id_1` = `b`.`position_id`) and (`a`.`pos_id_2` = `c`.`position_id`) and (`a`.`pos_id_3` = `d`.`position_id`) and (`a`.`author_id` = `e`.`user_id`) and ((`a`.`state` = 1) or (`a`.`state` = 2)));

应该改成:

DROP VIEW IF EXISTS `testdb`.`v4bbs`;
CREATE VIEW `v4bbs` AS select `a`.`topic_id` AS `did`,`a`.`topic_type` AS `topic_type`,`a`.`especial_flag` AS `topic_level`,`a`.`title` AS `title`,`a`.`url` AS `url`,`a`.`content` AS `content`,concat(`b`.`chinese_name`,_utf8' ',`c`.`chinese_name`,_utf8' ',`d`.`chinese_name`) AS `addr`,`a`.`author_id` AS `author_id`,`e`.`nikename` AS `author_nick`,`a`.`appear_date` AS `pub_time`,`a`.`restor_count` AS `reply`,`a`.`last_restor_user_id` AS `last_reply_id`,`a`.`last_restor_nickName` AS `last_reply_nick`,`a`.`last_restor_date` AS `last_reply_time`,`a`.`last_update_date` AS `last_modify_time`,`a`.`fingerprint` AS `fp` from ((((`tb4topic` `a` join `tb4position` `b`) join `tb4position` `c`) join `tb4position` `d`) join `tb4user` `e`) where ((`a`.`pos_id_1` = `b`.`position_id`) and (`a`.`pos_id_2` = `c`.`position_id`) and (`a`.`pos_id_3` = `d`.`position_id`) and (`a`.`author_id`