Msql資料庫表匯入Sqlite資料庫表，含mysql匯出亂碼解決的方法

1、首先從Mysql資料庫匯出到一個csv格式的檔案裡面即可：

mysql> select * from city limit 20 into outfile "d:\city3.csv" FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n';

上面語句的意思就是把每條資料的field用“,”分開，這個就是csv格式了：

1,Kabul,,Kabol,17800002,Qandahar,,Qandahar,2375003,Herat,,Herat,1868004,Mazar-e-Sharif,,Balkh,1278005,Amsterdam,,Noord-Holland,7312006,Rotterdam,,Zuid-Holland,5933217,Haag,,Zuid-Holland,4409008,Utrecht,,Utrecht,2343239,Eindhoven,,Noord-Brabant,20184310,Tilburg,,Noord-Brabant,19323811,Groningen,NLD,Groningen,17270112,Breda,NLD,Noord-Brabant,16039813,Apeldoorn,NLD,Gelderland,15349114,Nijmegen,NLD,Gelderland,15246315,Enschede,NLD,Overijssel,14954416,Haarlem,NLD,Noord-Holland,14877217,Almere,NLD,Flevoland,14246518,Arnhem,NLD,Gelderland,13802019,Zaanstad,NLD,Noord-Holland,13562120,鍚冮キ,NLD,Noord-Brabant,129170

2、匯入到Sqlite：

sqlite> .separator ","sqlite> .import "d:\city3.csv" city

結果如下：

sqlite> select * from city;1,Kabul,,Kabol,17800002,Qandahar,,Qandahar,2375003,Herat,,Herat,1868004,Mazar-e-Sharif,,Balkh,1278005,Amsterdam,,Noord-Holland,7312006,Rotterdam,,Zuid-Holland,5933217,Haag,,Zuid-Holland,4409008,Utrecht,,Utrecht,2343239,Eindhoven,,Noord-Brabant,20184310,Tilburg,,Noord-Brabant,19323811,Groningen,NLD,Groningen,17270112,Breda,NLD,Noord-Brabant,16039813,Apeldoorn,NLD,Gelderland,15349114,Nijmegen,NLD,Gelderland,15246315,Enschede,NLD,Overijssel,14954416,Haarlem,NLD,Noord-Holland,14877217,Almere,NLD,Flevoland,14246518,Arnhem,NLD,Gelderland,13802019,Zaanstad,NLD,Noord-Holland,13562120,吃饭,NLD,Noord-Brabant,129170

再dump一次看看過程：

sqlite> .dumpPRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE "working" (first integer primary key autoincrement, second text);INSERT INTO "working" VALUES(1,'woyaochifan');INSERT INTO "working" VALUES(2,'woyaochifan');INSERT INTO "working" VALUES(4,'xizao');INSERT INTO "working" VALUES(5,NULL);CREATE TABLE city (id  integer primary key, name  text, countrycode  text, distict  text, population  integer);INSERT INTO "city" VALUES(1,'Kabul','','Kabol',1780000);INSERT INTO "city" VALUES(2,'Qandahar','','Qandahar',237500);INSERT INTO "city" VALUES(3,'Herat','','Herat',186800);INSERT INTO "city" VALUES(4,'Mazar-e-Sharif','','Balkh',127800);INSERT INTO "city" VALUES(5,'Amsterdam','','Noord-Holland',731200);INSERT INTO "city" VALUES(6,'Rotterdam','','Zuid-Holland',593321);INSERT INTO "city" VALUES(7,'Haag','','Zuid-Holland',440900);INSERT INTO "city" VALUES(8,'Utrecht','','Utrecht',234323);INSERT INTO "city" VALUES(9,'Eindhoven','','Noord-Brabant',201843);INSERT INTO "city" VALUES(10,'Tilburg','','Noord-Brabant',193238);INSERT INTO "city" VALUES(11,'Groningen','NLD','Groningen',172701);INSERT INTO "city" VALUES(12,'Breda','NLD','Noord-Brabant',160398);INSERT INTO "city" VALUES(13,'Apeldoorn','NLD','Gelderland',153491);INSERT INTO "city" VALUES(14,'Nijmegen','NLD','Gelderland',152463);INSERT INTO "city" VALUES(15,'Enschede','NLD','Overijssel',149544);INSERT INTO "city" VALUES(16,'Haarlem','NLD','Noord-Holland',148772);INSERT INTO "city" VALUES(17,'Almere','NLD','Flevoland',142465);INSERT INTO "city" VALUES(18,'Arnhem','NLD','Gelderland',138020);INSERT INTO "city" VALUES(19,'Zaanstad','NLD','Noord-Holland',135621);INSERT INTO "city" VALUES(20,'吃饭','NLD','Noord-Brabant',129170);DELETE FROM sqlite_sequence;INSERT INTO "sqlite_sequence" VALUES('working',5);COMMIT;sqlite>

 

----------------------------------------------------------------------------------------------------------------------------

 

下面說一下mysql匯出中文亂碼的解決方案。

首先，用workbench工具開啟，然後查詢出相應的列表，如：

儲存在案頭為tt1.csv，右鍵用“記事本”開啟，這個不是亂碼：

 

然後建立一個檔案，叫做my.csv，把剛才的記事本裡面的東西複製粘貼過去，就可以了。儲存！

注意裡面的NULL的處理。