数据库查询参数乱码

Question

<pre>org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question = '问题' answer = '答案'' at line 4

### The error may exist in file [C:\Users\xmchen\IdeaProjects\mmall\target\mmall\WEB-INF\classes\mappers\UserMapper.xml]

### The error may involve com.mmall.dao.UserMapper.checkAnswer-Inline

### The error occurred while setting parameters

### SQL: SELECT COUNT(1)     from mmall_user     WHERE username = ?     question = ?     answer = ?

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question = '问题'

    answer = '答案'' at line 4

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question = '问题'

    answer = '答案'' at line 4

org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:943)

org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:833)

javax.servlet.http.HttpServlet.service(HttpServlet.java:650)

org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:807)

javax.servlet.http.HttpServlet.service(HttpServlet.java:731)

org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)

</pre></p><p><b>root cause</b> <pre>org.springframework.jdbc.BadSqlGrammarException: 

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question = '问题'

    answer = '答案'' at line 4

### The error may exist in file [C:\Users\xmchen\IdeaProjects\mmall\target\mmall\WEB-INF\classes\mappers\UserMapper.xml]

### The error may involve com.mmall.dao.UserMapper.checkAnswer-Inline

### The error occurred while setting parameters

### SQL: SELECT COUNT(1)     from mmall_user     WHERE username = ?     question = ?     answer = ?

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question = '问题'

    answer = '答案'' at line 4

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question = '问题'

    answer = '答案'' at line 4

org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)

org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)

org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)

com.sun.proxy.$Proxy19.selectOne(Unknown Source)

org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:167)

org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)

org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)

.........

我猜想是不是数据库的编码问题，所以看了一下配置，是好着的呀。。求助！！！！

数据库编码查看结果如下：

mysql> show variables like 'character_set_%';

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | latin1                     |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

Answer 1

提问者 慕尼黑1558251 2017-08-19 00:18:34

问题已经解决，是sql语法错误，不过令人好奇的是，按照视频那样配置，在linux数据库字段时查看到的结果如下：

mysql> select question,answer from mmall_user;

+----------+------------+

| question | answer     |

+----------+------------+

| ??       | ??         |

| ??       | ??         |

| ??       | ??         |

| 105204   | 105204     |

| ??       | ??         |

| ????     | xmchen1111 |

+----------+------------+

6 rows in set (0.00 sec)

虽然显示乱码，可是程序运行时查找并没报错，有点疑惑。。

Answer 2

Geely 2017-08-18 23:48:31

你好，同学tomcat的URIEncoding=UTF-8 配置了么，debug有没有看从前台传过来的是乱码么，

• 提问者 慕尼黑1558251 #1

  这块是我sql语法写错了，看到tomcat日志有乱码就一直找乱码的问题，后来发现之前的配置没有问题，只不过在mysql的字符集配置如果照视频上的那样做，查询到中文字段是乱码，不过不影响查询。