如何解決mysql導(dǎo)入文件中文亂碼的問(wèn)題

小編給大家分享一下如何解決mysql導(dǎo)入文件中文亂碼的問(wèn)題，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

mysql導(dǎo)入文件中文亂碼的解決辦法：首先創(chuàng)建數(shù)據(jù)庫(kù)并制定編碼；然后在導(dǎo)入數(shù)據(jù)庫(kù)文件之前，制定編碼set names utf8即可。

繼續(xù)昨天的問(wèn)題，數(shù)據(jù)庫(kù)配置好后，查詢(xún)表發(fā)現(xiàn)中文亂碼了，看了網(wǎng)上的幾個(gè)方法也沒(méi)解決。
感覺(jué)是導(dǎo)入的sql文件問(wèn)題，那么逆向的思考，用命令創(chuàng)建個(gè)數(shù)據(jù)庫(kù)，添加些數(shù)據(jù)，然后導(dǎo)出來(lái)看看什么情況。
向表內(nèi)插入數(shù)據(jù)的時(shí)候發(fā)現(xiàn)報(bào)錯(cuò)：
ERROR 1366 (HY000): Incorrect string value: '\xE6\xB5\x8B\xE8\xAF\x95' for column 'bookname' at row 1

果斷再改，通過(guò)命令：alter table book change bookname bookname varchar(32) character set utf8;
改完后也不看了，直接插數(shù)據(jù)試試：

insert into book
(id,bookname,size,price)
values
(1,"測(cè)試",2,3)
;

插入成功，查看數(shù)據(jù)是不是還中文亂碼，發(fā)現(xiàn)并不亂碼了：

那么開(kāi)始進(jìn)行下一步，導(dǎo)出sql文件，進(jìn)入到mysql的bin目錄下，開(kāi)始通過(guò)命令導(dǎo)出，這個(gè)過(guò)程需要輸入密碼

E:\mysql-5.7.28-winx64\bin>mysqldump -u root -p test > test.sql
Enter password: *******

導(dǎo)出的文件，表的編碼格式utf8，與之前導(dǎo)入文件的比較并看不出什么問(wèn)題。
那么，就是之前導(dǎo)入文件的過(guò)程，創(chuàng)建數(shù)據(jù)庫(kù)的過(guò)程除了問(wèn)題，查看之前導(dǎo)入的mydb.sql 的mydb數(shù)據(jù)庫(kù)結(jié)構(gòu)，果然不對(duì)：

mysql> use mydb;
Database changed
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

遂修改之：alert database mydb character set utf8;
然后再查看表的編碼，發(fā)現(xiàn)有鬼：

mysql> show create table sp_user_cart;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sp_user_cart | CREATE TABLE `sp_user_cart` (
  `cart_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '涓婚敭',
  `user_id` int(11) unsigned NOT NULL COMMENT '瀛﹀憳id',
  `cart_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '璐?墿杞﹁?鎯呬俊鎭?紝浜岀淮鏁扮粍搴忓垪鍖栦俊鎭',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `delete_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

utf8mb4_unicode_ci 這個(gè)東西是怎么出來(lái)的...
然后改了編碼，查詢(xún)，還是不對(duì)，想起來(lái)之前創(chuàng)建數(shù)據(jù)庫(kù)，可能是忘記設(shè)置編碼，導(dǎo)入的東西本來(lái)就亂碼了，也或者是my.ini配置不對(duì)，從頭開(kāi)始用正確流程試試吧。

先查看數(shù)據(jù)庫(kù)的編碼設(shè)置:show variables like 'character%';
發(fā)現(xiàn)很混亂：

mysql> show variables like 'character%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | gbk                                    |
| character_set_connection | gbk                                    |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | gbk                                    |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | D:\mysql-5.7.29-winx64\share\charsets\ |
+--------------------------+----------------------------------------+

開(kāi)始改，通過(guò)一系列的set
set character_set_client = utf8;
得到結(jié)果

+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_filesystem | utf8                                   |
| character_set_results    | utf8                                   |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | D:\mysql-5.7.29-winx64\share\charsets\ |
+--------------------------+----------------------------------------+

創(chuàng)建數(shù)據(jù)庫(kù)并制定編碼：CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci;
使用：use test2
在導(dǎo)入數(shù)據(jù)庫(kù)文件之前，制定編碼set names utf8;
導(dǎo)入：source F:xxxx\xxxx\mydb.sql;
不算漫長(zhǎng)的等待之后，查詢(xún)，不亂碼了

mysql> select * from sp_role;
+---------+---------------+---------------------------------------------------------------------------------------------
------------------------------------------------------+-----------------------------------------------------------------
------------------------+--------------------+
| role_id | role_name     | ps_ids
                                                      | ps_ca
                        | role_desc          |
+---------+---------------+---------------------------------------------------------------------------------------------
------------------------------------------------------+-----------------------------------------------------------------
------------------------+--------------------+
|      30 | 主管          | 101,0,104,116,115,142,143,144,121,122,123,149,102,107,109,103,111,129,130,134,135,138,139,14
0,141,112,147,125,110,131,132,133,136,137,145,146,148 | Goods-index,Goods-tianjia,Category-index,Order-showlist,Brand-in
dex                     | 技術(shù)負(fù)責(zé)人         |
|      31 | 測(cè)試角色      | 101,0,104,105,116,117,115,142,143,144,121,122,123,149,103,111,129,134,138,112,147
                                                      | Goods-showlist,Goods-tianjia,Category-showlist,Order-showlist,Or
der-dayin,Order-tianjia | 測(cè)試角色描述       |
|      34 | 測(cè)試角色2     | 0,105,116,142,143,122
                                                      | NULL
                        | 測(cè)試描述12         |
|      39 | 大發(fā)送到      | 101,0,104,105,116
                                                      | NULL
                        | 阿斯蒂芬           |
|      40 | test          | 102,0,107,109,154,155,145,146,148
                                                      | NULL

實(shí)戰(zhàn)項(xiàng)目可以繼續(xù)進(jìn)行，遂生法喜。

以上是如何解決mysql導(dǎo)入文件中文亂碼的問(wèn)題的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！