mysql導(dǎo)入文件中文亂碼怎么辦

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

mysql導(dǎo)入文件中文亂碼怎么辦

推薦:《mysql視頻教程

繼續(xù)昨天的問題,數(shù)據(jù)庫配置好后,查詢表發(fā)現(xiàn)中文亂碼了,看了網(wǎng)上的幾個方法也沒解決。
感覺是導(dǎo)入的sql文件問題,那么逆向的思考,用命令創(chuàng)建個數(shù)據(jù)庫,添加些數(shù)據(jù),然后導(dǎo)出來看看什么情況。
向表內(nèi)插入數(shù)據(jù)的時候發(fā)現(xiàn)報錯:
ERROR 1366 (HY000): Incorrect string value: ‘xE6xB5x8BxE8xAFx95’ for column ‘bookname’ at row 1

mysql導(dǎo)入文件中文亂碼怎么辦

mysql導(dǎo)入文件中文亂碼怎么辦

這就奇怪了,查看表結(jié)構(gòu):
show create table book;

mysql導(dǎo)入文件中文亂碼怎么辦

mysql導(dǎo)入文件中文亂碼怎么辦

看到了一個不想看到的編碼格式latin1,果斷改掉,通過命令:alter table book default character set utf8;
改完看到一個神清氣爽的結(jié)果,表的編碼改過來了,但還存在一個“什么鬼?”,字段有個亂碼latin1 :

mysql導(dǎo)入文件中文亂碼怎么辦

mysql導(dǎo)入文件中文亂碼怎么辦

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

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

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

mysql導(dǎo)入文件中文亂碼怎么辦

那么開始進行下一步,導(dǎo)出sql文件,進入到mysql的bin目錄下,開始通過命令導(dǎo)出,這個過程需要輸入密碼

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

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

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 這個東西是怎么出來的…
然后改了編碼,查詢,還是不對,想起來之前創(chuàng)建數(shù)據(jù)庫,可能是忘記設(shè)置編碼,導(dǎo)入的東西本來就亂碼了,也或者是my.ini配置不對,從頭開始用正確流程試試吧。

先查看數(shù)據(jù)庫的編碼設(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-winx64sharecharsets | +--------------------------+----------------------------------------+

開始改,通過一系列的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-winx64sharecharsets | +--------------------------+----------------------------------------+

創(chuàng)建數(shù)據(jù)庫并制定編碼:CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci;
使用:use test2
在導(dǎo)入數(shù)據(jù)庫文件之前,制定編碼set names utf8;
導(dǎo)入:source F:xxxxxxxxmydb.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                     | 技術(shù)負責人         | |      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 | 大發(fā)送到      | 101,0,104,105,116                                                       | NULL                         | 阿斯蒂芬           | |      40 | test          | 102,0,107,109,154,155,145,146,148                                                       | NULL

實戰(zhàn)項目可以繼續(xù)進行,遂生法喜。

以上就是

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點贊5 分享