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

This article will explain in detail the method of importing data into mysql for you. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The method of importing data into mysql: 1. Use "mysql-u username-p password"

< sql文件"语句导入数据；2、使用"source sql文件的路径"语句导入数据；3、使用mysqlimport导入数据。 1、mysql 命令导入 使用 mysql 命令导入语法格式为： mysql -u用户名 -p密码 < 要导入的数据库数据sql文件 实例： # mysql -uroot -p123456 < student.sql 以上命令将将备份的整个数据库 student.sql 导入。 2、source 命令导入 source 命令导入数据库需要先登录到数库终端： mysql>

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)

4. Import data using mysqlimport

The mysqlimport client provides a command line interface for LOAD DATA INFILEQL statements. Most of the options for mysqlimport correspond directly to the LOAD DATA INFILE clause.

To import data from the file dump.txt into the mytbl data Table, you can use the following command:

$mysqlimport-u root-p-- local mytbl dump.txtpassword *

The mysqlimport command can specify options to set the specified format, and the command statement format is as follows:

$mysqlimport-u root-p-local-- fields-terminated-by= ":"\-- lines-terminated-by= "\ r\ n" mytbl dump.txtpassword *

Use the-- columns option in the mysqlimport statement to set the order of the columns:

$mysqlimport-u root-p-- local-- columns=b,c,a\ mytbl dump.txtpassword * so much for mysql's method of importing data into it. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.