org.springframework.jdbc.BadSqlGrammarException: Cause: java.sql.SQLException: Column count 异常

解决MySQL插入异常
最新推荐文章于 2025-03-21 11:05:31 发布
最新推荐文章于 2025-03-21 11:05:31 发布 · 164 阅读 · 0
文章标签：

#数据库 #java #开发工具

开发中运行mysql脚本，发现提示mysql提示Column count doesn't match value count at row 1异常，坚持后发现是由于写的SQL语句里列的数目和后面的值的数目不一致，

比如insert into 表名 (field1,field2,field3) values('a','b')这样前面的是三列，后面却只有二个值，这就会出现这个异常的。


org.springframework.jdbc.BadSqlGrammarException:

### Error updating database. Cause: java.sql.SQLException: Column count doesn't match value count at row 1
### The error may involve com.jq.mall.product.biz.dao.ProductDAO.insertProduct-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Column count doesn't match value count at row 1
; bad SQL grammar []; nested exception is java.sql.SQLException: Column count doesn't match value count at row 1
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
at $Proxy6.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:56)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
at $Proxy7.insertProduct(Unknown Source)
at com.jq.mall.product.biz.service.impl.ProductServiceImpl.updateOrSaveProduct(ProductServiceImpl.java:97)
at com.jq.mall.product.biz.service.impl.MyTestCase.test(MyTestCase.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:79)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1021)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:29)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:118)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:107)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
... 26 more


iteye_18451
关注
全网最详细的org.springframework.jdbc.UncategorizedSQLException的多种解决方法
念兮为美
02-02 1万+
全网最详细的org.springframework.jdbc.UncategorizedSQLException的多种解决方法，比如语法错误，字段名称对不上，字段类型不一致，数据表的限制，字符集的限制等。
解决bad SQL grammar []； nested exception is java.sql.SQLSyntaxErrorException
m0_74823336的博客
12-09 2269
确保底层SQL与上层调用的代码都没有逻辑问题，所以“bad SQL grammar []”应该是由配置文件导致的，检查项目的。这种配置的使用对于一些特殊的SQL语句执行场景非常有帮助，但需要谨慎使用，确保SQL语句的合法性和安全性。在使用Spring Boot连接MySQL数据库时，有时候会遇到“Bad SQL Grammar”错误，这种错误通常在执行SQL语句时发生。本文针对的是对于执行多条SQL语句的情况。MySQL连接配置中有一些常用的参数，这些参数可以在数据库连接字符串（URL）中进行配置。
bad SQL grammar []； nested exception is java.sql.SQLException: Column count doesn‘t match value coun
m0_58875823的博客
09-23 2324
新手写SQl的时候，很容犯的低级错误。有时候字段比较多，直接在框架中写insert语句的时候，就很容易出现这种错误，其实很简单。仔细检查下insert into 括号里字段的个数以及顺序是否和values 里的传参的个数以及顺序是否一致。希望刷到的朋友，看了会对你有帮助，祝你代码没有BUG！
org.springframework.jdbc.BadSqlGrammarException: Cause: java.sql.SQLException: Column count 异常
weixin_33827965的博客
11-07 468
开发中运行mysql脚本，发现提示mysql提示Column count doesn't match value count at row 1异常，坚持后发现是由于写的SQL语句里列的数目和后面的值的数目不一致，比如insert into 表名 (field1,field2,field3) values('a','b')这样前面的是三列，后面却只有二个...
异常：nested exception is java.sql.SQLException: Column count doesn‘t match value count at row 1
qq_41962328的博客
07-06 5122
错误信息：org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [insert into emp values(null,'张三',24,'男')]; nested exception is java.sql.SQLException: Column count doesn't match value count at row 1 原因：数据库表的字段名数量和你插入数据的成员信息字段数量不匹配
org.springframework.jdbc.BadSqlGrammarException
an74520的专栏
06-14 445
org.springframework.jdbc.BadSqlGrammarException
Caused by: org.springframework.jdbc.BadSqlGrammarException:
07-10
我们遇到了一个异常：`org.springframework.jdbc.BadSqlGrammarException`。这个异常通常发生在Spring框架执行SQL语句时，SQL语法有问题或者执行的操作与数据库状态不匹配（比如表或列不存在）。下面我将详细解释...
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into Student value(null,?,?)]; nested exception is java.sql.SQLException: Column count doesn't match value count at row 1
03-31
org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax... ``` #### 5. ...
rg.springframework.jdbc.UncategorizedSQLException: ### Error querying database. Cause: java.sql.SQLException: Cannot convert string '%\xAC\xED\x00\x05s...' from binary to utf8mb4 ### The error may exist in file [D:\GMY\gmy-cloud\gmy-module-logistics\gmy-module-logistics-biz\target\classes\mapper\LogisticsSupplyMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT COUNT(*) AS total FROM logistics_inquiry l1 WHERE 1 = 1 AND l1.inquiry_place_origin LIKE CONCAT('%', ?, '%') AND l1.inquiry_destination LIKE CONCAT('%', ?, '%') ### Cause: java.sql.SQLException: Cannot convert string '%\xAC\xED\x00\x05s...' from binary to utf8mb4 ; uncategorized SQLException; SQL state [HY000]; error code [3854]; Cannot convert string '%\xAC\xED\x00\x05s...' from binary to utf8mb4; nested exception is java.sql.SQLException: Cannot convert string '%\xAC\xED\x00\x05s...' from binary to utf8mb4 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93) ~[mybatis-spring-2.1.2.jar:2.1.2] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439) ~[mybatis-spring-2.1.2.jar:2.1.2] at com.sun.proxy.$Proxy161.selectList(Unknown Source) ~[na:na] at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.1.2.jar:2.1.2] at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:119) ~[mybatis-plus-core-3.5.10.1.jar:3.5.10.1] at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:84) ~[mybatis-plus-core-3.5.10.1.jar:3.5.10.1] at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:155) ~[mybatis-plus-core-3.5.10.1.jar:3.5.10.1] at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:92) ~[mybatis-plus-core-3.5.10.1.jar:3.5.10.1] at com.sun.proxy.$Proxy455.searchInquiry(Unknown Source) ~[na:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_281] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_281] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_281] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_281] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89) ~[spring-aop-5.3.39.jar:5.3.39] at cn.iocoder.gmy.framework.tenant.core.aop.TenantIgnoreAspect.around(TenantIgnoreAspect.java:35) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_281] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_281] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_281] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_281] at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241) ~[spring-aop-5.3.39.jar:5.3.39] at com.sun.proxy.$Proxy457.searchInquiry(Unknown Source) ~[na:na] at cn.iocoder.gmy.module.logistics.service.home.LogisticsSupplyServiceImpl.searchInquiry(LogisticsSupplyServiceImpl.java:30) ~[classes/:na] at cn.iocoder.gmy.module.logistics.controller.home.LogisticsSupplyController.searchInquiry(LogisticsSupplyController.java:34) ~[classes/:na] at cn.iocoder.gmy.module.logistics.controller.home.LogisticsSupplyController$$FastClassBySpringCGLIB$$feaf5011.invoke(<generated>) ~[classes/:na] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.39.jar:5.3.39] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123) ~[spring-context-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89) ~[spring-aop-5.3.39.jar:5.3.39] at cn.iocoder.gmy.framework.tenant.core.aop.TenantIgnoreAspect.around(TenantIgnoreAspect.java:35) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_281] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_281] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_281] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_281] at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762) ~[spring-aop-5.3.39.jar:5.3.39] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.39.jar:5.3.39] at org.springf
最新发布
09-09
在Spring框架下MyBatis查询数据库时出现 `Cannot convert string '%\xAC\xED\x00\x05s...' from binary to utf8mb4` 错误，通常是由于字符集设置不匹配或者数据库连接配置问题导致的。以下是一些可能的解决方法： #...
bad SQL grammar []； nested exception is java.sql.SQLException: Operand should contain 1 column(s)
Mesdfd的博客
03-21 263
解决了半天都没发现问题：好像看起来没啥问题哈。
Caused by: java.sql.SQLException: Column count doesn‘t match value count at row 1
boyandddd的博客
10-11 217
在values中忘记一个逗号
java mysql报表T加1_MySQL报错: java.sql.SQLException: Column count doesn't match value count at row 1...
weixin_39763640的博客
02-06 520
项目使用了Spring和MyBatis，在往mysql数据库里插入数据时报错如下：org.springframework.jdbc.BadSqlGrammarException:### Error updating database. Cause: java.sql.SQLException: Column count doesn't match value count at row 1### ...
Cause: java.sql.SQLException: Column count doesn't match value count at row 1(列计数与第1行的值计数不匹配)
ChangXinZaiCi的博客
04-03 4255
Cause: java.sql.SQLException: Column count doesn’t match value count at row 1; bad SQL grammar []; nested exception is java.sql.SQLException: Column count doesn’t match value count at row 1] with root...
； bad SQL grammar []； nested exception is java.sql.SQLSyntaxErrorException: You have an error in you
m0_55909411的博客
08-11 3803
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in you问题
解决myBatis执行多条sql问题：bad SQL grammar []； nested exception is java.sql.SQLSyntaxErrorException
shi_AXing_a的博客
05-15 1437
一开始提示报错，以为是代码或者sql问题，但是打印的sql语句也是正常的。
Column count doesn't match value count at row 1问题解决
热门推荐
wmx690的博客
02-05 2万+
INSERT INTO tableA select * FROM tableB where   条件 ### Cause: java.sql.SQLException: Column count doesn't match value count at row 1 ; bad SQL grammar []; nested exception is java.sql.SQLException:
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值