centOS7下mysql插入中文字符报错问题解决方法
<p>在刚装完mysql，就建立了数据库abc,然后新建一个abc表，插入英文没有问题，但是插入中文就有问题，会报错：</p>
<p>
ERROR 1366 (HY000): Incorrect string value: '\xE4\xBD\x99\xE9\x93\xB6...'</p>
<p>
应该是数据库编码问题，所以应该改数据库编码</p>
<p>
这里有2中方法。一种是直接敲代码设置，一种是在CentOS7中修改文件/usr/my.cnf，</p>
<p>
<span><strong>第一种方法：</strong></span></p>
<p>
列出数据库的编码表</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_217865">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> show variables </code><code class="sql color1">like</code> <code class="sql string">'%char%'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<img style="max-width:100%!important;height:auto!important;"title="centOS7下mysql插入中文字符报错问题解决方法" alt="centOS7下mysql插入中文字符报错问题解决方法" src="https://zhuji.jb51.net/uploads/img/202305/2379049e68ca1e468b776868303f0400.jpg"></p>
<p>
修改编码格式：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_603735">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">set</code> <code class="sql plain">character_set_database=utf8;</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">set</code> <code class="sql plain">character_set_server=utf8;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
等</p>
<p>
就是保留 character_set_filesystem和character_sets_dir的原有默认值不变，其他全都改为utf8就行</p>
<p>
然后把原来创建的数据库删除重新建立一个，再创建一个表，就可以插入中文啦。。。</p>
<p>
<img style="max-width:100%!important;height:auto!important;"title="centOS7下mysql插入中文字符报错问题解决方法" alt="centOS7下mysql插入中文字符报错问题解决方法" src="https://zhuji.jb51.net/uploads/img/202305/13212fced01251ae861c2b80b154dbfb.jpg"></p>
<p>
<span><strong>第二种方法：在CentOS7中修改文件/usr/my.cnf，</strong></span></p>
<p>
打开配置文件：</p>
<p>
vi /etc/my.cnf;</p>
<p>
在，，下分别添加如下内容</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_424761">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">character_set_server = utf8</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="bash plain"></code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="bash plain">default-character-</code><code class="bash functions">set</code><code class="bash plain">=utf8</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="bash plain"></code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="bash plain">default-character-</code><code class="bash functions">set</code><code class="bash plain">=utf8</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
备注：</p>
<p>
编辑文件用到的快捷键：</p>
<p>
进入编辑模式编辑</p>
<p>
i</p>
<p>
退出编辑模式</p>
<p>
Esc</p>
<p>
退出文件并保存</p>
<p>
:wq</p>
<p>
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。</p>
<p>
原文链接：http://www.centoscn.com/CentosBug/softbug/2017/0103/8394.html</p>
頁:
[1]