Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of importing data into MySQL, which has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

1. Mysql command import

Use the mysql command to import the syntax in the format:

Mysql-u username-p password

Create database abc; # create database mysql > use abc; # use the created database mysql > set names utf8; # set encoding mysql > source / home/abc/abc.sql # import the backup database

3. Import data using LOAD DATA

LOAD DATA INFILE statements are provided in MySQL to insert data. The following example reads the file dump.txt from the current directory and inserts the data from that file into the mytbl table of the current database.

Mysql > LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl

If you specify the LOCAL keyword, the file is read by path from the customer host. If not specified, the file reads the file by path on the server.

You can explicitly indicate the delimiters and end-of-line tags for column values in the LOAD DATA statement, but the default tags are locators and newlines.

The syntax of the FIELDS and LINES clauses of both commands is the same. Both clauses are optional, but if both are specified at the same time, the FIELDS clause must appear before the LINES clause.

If the user specifies a FIELDS clause, its clauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) are also optional, but the user must specify at least one of them.

Mysql > LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl-> FIELDS TERMINATED BY':'- > LINES TERMINATED BY'\ r\ n'

By default, LOAD DATA inserts data in the order of the columns in the data file, and if the columns in the data file are inconsistent with the columns in the inserted table, you need to specify the order of the columns.

For example, if the column order in the data file is a _ c, but the column order in the insert table is _, the syntax of data import is as follows:

Mysql > LOAD DATA LOCAL INFILE 'dump.txt'-> INTO TABLE mytbl (b, c, a); Thank you for reading this article carefully. I hope it is helpful for the editor to share the methods of importing data into MySQL. At the same time, I also hope that you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.