MySql imports CSV files or tab-split files

Sometimes there is a need to import some data from another library to another library, and the data is stored in a CSV file. We have two ways for MySql database: one is to import it with commands, and the other is that many MySql clients (such as Heidisql, Sequel pro) provide us with this function. Let's introduce the two import methods respectively.

MySql command import

Preparatory work:

Create a table first:

The statement of the table is as follows:

CREATE TABLE `Cityname` (`id`INT (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `ename` VARCHAR (12) NULL DEFAULT NULL COMMENT 'province abbreviation', `ecityname` VARCHAR (12) NULL DEFAULT NULL COMMENT 'city name', `ccityname`VARCHAR (12) NULL DEFAULT NULL COMMENT 'city Chinese', PRIMARY KEY (`id`) COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=81

Prepare the CSV file to import:

First, let's create a new excel file with the following contents:

Then we convert the excel file to a csv file. The operation is as follows: file-> Save as:

Note: fields are separated by commas by default. The primary key in the document can be left blank, and the order should be consistent with the order of the fields in the database. In addition, we noticed that the coding format of our database is UTF-8, and our CSV file contains Chinese. If we import it directly, the Chinese may not be able to import to the database. We need to change the encoding format of the CSV file to UTF-8. We have an easy way to convert CSV format. CSV files can be edited with notepad. Use notepad to open editing, and then save as when you choose some encoding format on OK.

Import command

The copy code is as follows: LOAD DATA INFILE'd:\\ Document\\ Download\\ test0142.csv' INTO TABLE city_china FIELDS TERMINATED BY', 'ENCLOSED BY' "'LINES TERMINATED BY'\ n' IGNORE 1 ROWS

LOAD DATA INFILE is followed by the path to the file to be imported, INTO TABLE table name FIELDS TERMINATED BY division field (here the default is a comma, you can change to other division) ENCLOSED BY semicolon division, the default line on the LINES TERMINATED BY newline IGNORE 1 ROWS ignore the first line (in this case, the first behavior of the corresponding field in the database, if the first line of your file is data, then do not ignore the first line).

MySql client

Here I am using the HeiDiSql client as an example. After connecting to the database, there is a button on the menu on the upper left (to the left of the two villains), as shown in the figure:

Click, and the following page pops up:

Click Import to OK.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.