首頁 >資料庫 >mysql教程 >mysql匯入檔案中文亂碼怎麼辦

mysql匯入檔案中文亂碼怎麼辦

藏色散人
藏色散人原創
2020-10-28 09:58:195286瀏覽

mysql導入檔案中文亂碼的解決方法:先建立資料庫並製定編碼;然後在匯入資料庫檔案之前,先制定編碼set names utf8即可。

mysql匯入檔案中文亂碼怎麼辦

推薦:《mysql影片教學

繼續昨天的問題,資料庫配置好後,查詢表發現中文亂碼了,看了網路上的幾個方法也沒解決。
感覺是導入的sql檔案問題,那麼逆向的思考,用指令建立個資料庫,加入些數據,然後匯出來看看什麼情況。
向表內插入資料的時候發現報錯:
ERROR 1366 (HY000): Incorrect string value: '\xE6\xB5\x8B\xE8\xAF\x95' for column 'bookname' at row 1

mysql匯入檔案中文亂碼怎麼辦



mysql匯入檔案中文亂碼怎麼辦



這就很奇怪了,看表格結構:
show create table book;



mysql匯入檔案中文亂碼怎麼辦

mysql匯入檔案中文亂碼怎麼辦



##看到了一個不想看到的編碼格式latin1,果斷改掉,透過指令:
alter table book default character set utf8;

改完看到一個神清氣爽的結果,表的編碼改過來了,但還存在一個“什麼鬼?”,字段有個亂碼latin1 :



mysql匯入檔案中文亂碼怎麼辦

mysql匯入檔案中文亂碼怎麼辦






mysql匯入檔案中文亂碼怎麼辦



果斷再改,透過指令:
alter table book change bookname bookname varchar(32) character set utf8;

#改完後也不看了,直接插資料試試:<pre class="brush:php;toolbar:false">insert into book (id,bookname,size,price) values (1,&quot;测试&quot;,2,3) ;</pre>插入成功,看資料是不是還中文亂碼,發現並不亂碼了:



#########那麼開始進行下一步,匯出sql文件,進入到mysql的bin目錄下,開始透過指令匯出,這個過程需要輸入密碼###
E:\mysql-5.7.28-winx64\bin>mysqldump -u root -p test > test.sql
Enter password: *******
###匯出的文件,表的編碼格式utf8,與先前匯入文件的比較並看不出什麼問題。 ###那麼,就是之前匯入檔案的過程,建立資料庫的過程除了問題,查看之前匯入的mydb.sql 的mydb資料庫結構,果然不對:###
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;######然後再查看表格的編碼,發現有鬼:###
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 這個東西是怎麼出來的...###然後改了編碼,查詢,還是不對,想起來之前創建資料庫,可能是忘記設定編碼,導入的東西本來就亂碼了,也或者是​​my.ini配置不對,從頭開始用正確流程試試吧。 ######先查看資料庫的編碼設定:###show variables like 'character%';######發現很混亂:###
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\ |
+--------------------------+----------------------------------------+
###開始改,經過一連串的set# #####set character_set_client = utf8;######得到結果###
+--------------------------+----------------------------------------+
| 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\ |
+--------------------------+----------------------------------------+

创建数据库并制定编码:CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci;
使用:use test2
在导入数据库文件之前,制定编码set names utf8;
导入:source F:xxxx\xxxx\mydb.sql;
不算漫长的等待之后,查询,不乱码了

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                     | 技术负责人         |
|      31 | 测试角色      | 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 | 测试角色描述       |
|      34 | 测试角色2     | 0,105,116,142,143,122
                                                      | NULL
                        | 测试描述12         |
|      39 | 大发送到      | 101,0,104,105,116
                                                      | NULL
                        | 阿斯蒂芬           |
|      40 | test          | 102,0,107,109,154,155,145,146,148
                                                      | NULL

实战项目可以继续进行,遂生法喜。

以上是mysql匯入檔案中文亂碼怎麼辦的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn