在用MySQL存储微信用户的昵称时，报了如下错误：
<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
解决步骤：
第一步：修改数据库字符集
第二步：升级最新数据库驱动
<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>
<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>
<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_ciinit_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
0 0
