How to import an msql database table to an SQLite database table, including MySQL export garbled data

1. First export data from the MySQL database to a CSV file:

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

The preceding statement separates the fields of each data entry with commas (,), which is in CSV format:

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, linear regression, NLD, Noord-Brabant, 129170

2. Import to SQLite:

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

The result is as follows:

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 again to see the process:

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>

 

Bytes ----------------------------------------------------------------------------------------------------------------------------

 

The following describes how to export Chinese garbled characters in MySQL.

First, open it with the workbench tool and query the corresponding list, such:

Tt1.csv is saved on the table. Right-click it and choose notepad. This is not garbled:

 

Create a new file named my.csv and copy and paste the content in the notepad. Save!

Note the processing of null in it.