<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">java.sql.SQLException: Incorrect <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">string</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">value</span>: <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'\xF0\x9F\x98\x84'</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> column</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
经过网络搜索,总结原因为
因为表情符在某些终端,比如ios5.0以上,是以四字节表示的,而传统的utf8只能保存3字节,所以报错了。
解决办法:修改mysql字符集为utf8mb4
解决步骤:
第一步:修改数据库字符集
第二步:升级最新数据库驱动
- 1.1修改数据库字符集:
<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">alter</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">database</span> databasename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CHARACTER</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> utf8mb4;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
- 1.2修改表字符集:
<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">alter</span> tbale tablename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CHARACTER</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> utf8mb4;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
- 1.3修改mysql的init文件
<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">[client] default-character-<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> = utf8mb4 [mysql] <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">default</span>-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">character</span>-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> = utf8mb4 [mysqld] <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">character</span>-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span>-client-handshake = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FALSE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">character</span>-<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span>-server = utf8mb4 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">collation</span>-server = utf8mb4_unicode_ci init_connect=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SET NAMES utf8mb4'</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li></ul>
然后,重启mysql。
经过以上修改,使用以下语句:
<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"> <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SHOW</span> VARIABLES <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> Variable_name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LIKE</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'character\_set\_%'</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">OR</span> Variable_name <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LIKE</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'collation%'</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>
查看数据库的字符集都为utf8mb4了。
- 2.在升级最新的mysql数据库驱动(mysql-connector-java 6.0.3)后出现了新错误:
<code class="hljs livecodeserver has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">time</span> zone <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">value</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'?й???????'</span> is unrecognized <span class="hljs-operator" style="box-sizing: border-box;">or</span> represents more than <span class="hljs-constant" style="box-sizing: border-box;">one</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">time</span> zone. You must configure either <span class="hljs-operator" style="box-sizing: border-box;">the</span> server <span class="hljs-operator" style="box-sizing: border-box;">or</span> JDBC driver (via <span class="hljs-operator" style="box-sizing: border-box;">the</span> serverTimezone configuration property) <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">to</span> use <span class="hljs-operator" style="box-sizing: border-box;">a</span> more specifc <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">time</span> zone <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">value</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span> you want <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">to</span> utilize <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">time</span> zone support.</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>
原因有三个:
- 2.1 新driverClassName的路径改为com.mysql.cj.jdbc.Driver了
- 2.2 数据库连接url上添加 serverTimezone=GMT
- 2.3 数据库连接url上添加 userSSL=false
至此,所有的问题全部解决。
参考:
http://blog.csdn.net/wwtang9527/article/details/40947469
http://blog.csdn.net/sinat_33201781/article/details/51830688