开发者社区> 问答> 正文

sql 执行 错误

执行这样的sql

alter table pj_usr_auth add constraint FK_Reference_8 foreign key (usr) references usr (usr) on delete restrict on update restrict;

错误

2016-03-29 21:04:42,423 [ERROR][com.alibaba.druid.filter.stat.StatFilter] - merge sql error, dbType mysql, sql : alter table pj_usr_auth add constraint FK_Reference_8 foreign key (usr) references usr (usr) on delete restrict on update restrict; com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'rict on update restrict;',expect ON, actual ON on at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:231) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:407) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:145) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:140) at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:53) at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145) at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:630) at com.alibaba.druid.filter.stat.StatFilter.internalBeforeStatementExecute(StatFilter.java:397) at com.alibaba.druid.filter.stat.StatFilter.statementExecuteBefore(StatFilter.java:345) at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:185) at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487) at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:137) at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:421) at org.nutz.dao.impl.sql.run.NutDaoExecutor._runStatement(NutDaoExecutor.java:321) at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:52) at org.nutz.plugins.cache.dao.CachedNutDaoExecutor.exec(CachedNutDaoExecutor.java:105) at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:316) at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:59) at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:239) at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:271) at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:235) at com.rekoe.MvcSetup.init(MvcSetup.java:79) at org.nutz.mvc.impl.NutLoading.evalSetup(NutLoading.java:276) at org.nutz.mvc.impl.NutLoading.load(NutLoading.java:120) at org.nutz.mvc.ActionHandler.(ActionHandler.java:19) at org.nutz.mvc.NutFilter._init(NutFilter.java:87) at org.nutz.mvc.NutFilter.init(NutFilter.java:65) at com.rekoe.mvc.RkCmsNutFilter.init(RkCmsNutFilter.java:22) at org.apache.catalina.core.ApplicationFilterConfig.initFilter(ApplicationFilterConfig.java:277) at org.apache.catalina.core.ApplicationFilterConfig.getFilter(ApplicationFilterConfig.java:258) at org.apache.catalina.core.ApplicationFilterConfig.setFilterDef(ApplicationFilterConfig.java:382) at org.apache.catalina.core.ApplicationFilterConfig.(ApplicationFilterConfig.java:103) at org.apache.catalina.core.StandardContext.filterStart(StandardContext.java:4650) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5306) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744) 2016-03-29 21:04:42,669 [ERROR][com.alibaba.druid.pool.DruidDataSource] - discard connection

原提问者GitHub用户Rekoe

展开
收起
山海行 2023-07-05 22:01:07 64 0
3 条回答
写回答
取消 提交回答
  • 北京阿里云ACE会长

    根据错误日志显示,Druid SQL解析器在解析这个SQL语句时遇到了语法错误。具体来说,错误发生在on delete restrict on update restrict这段语句中,Druid SQL解析器期望的是ON关键字,但实际上出现了两次ON关键字,导致解析器无法正确解析该语句。

    该SQL语句中的ON DELETE RESTRICT和ON UPDATE RESTRICT是定义外键约束时常用的语句,用于指定在删除或更新主表记录时如何处理与之相关联的从表记录。正确的语法应该是:

    pgsql
    Copy
    ALTER TABLE pj_usr_auth ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (usr) REFERENCES usr (usr) ON DELETE RESTRICT ON UPDATE RESTRICT;
    需要注意的是,在MySQL中,外键约束的默认行为是RESTRICT,因此可以省略ON DELETE RESTRICT ON UPDATE RESTRICT这段语句,得到如下简化的语句:

    Copy
    ALTER TABLE pj_usr_auth ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (usr) REFERENCES usr (usr);
    如果仍然遇到类似的语法错误,可以尝试使用MySQL命令行工具或其他SQL客户端工具执行这个SQL语句,以验证该SQL语句是否正确。同时,也可以检查Druid SQL解析器的版本和配置,以确定是否存在配置或版本问题。

    2023-07-29 23:23:10
    赞同 展开评论 打赏
  • 问题已修复,请用新版本 https://github.com/alibaba/druid/releases/tag/1.1.14

    原回答者GitHub用户wenshao

    2023-07-06 12:30:19
    赞同 展开评论 打赏
  • 根据错误日志,您的SQL语句在Druid连接池中执行时出现了语法错误。具体错误信息是:com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'rict on update restrict;',expect ON, actual ON。根据错误信息来看,Druid解析器在解析SQL语句时发现了语法错误,并且提示了错误的位置。

    根据您提供的SQL语句来看,您使用的是MySQL数据库,而MySQL的外键约束语法是不包含ON DELETE RESTRICTON UPDATE RESTRICT这样的语句的。正确的MySQL外键约束语法应该是类似下面的形式:

    ALTER TABLE pj_usr_auth
    ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (usr) REFERENCES usr(usr);
    

    您可以尝试使用上述语句来执行ALTER TABLE操作,应该就不会再出现语法错误了。

    2023-07-06 09:16:28
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
低代码开发师(初级)实战教程 立即下载
冬季实战营第三期:MySQL数据库进阶实战 立即下载
阿里巴巴DevOps 最佳实践手册 立即下载