Use mysqlimport to import formatted files to MySQL tables

Source: Internet
Author: User
Tags processing text

Create a table Spam



Take a look at the data file to be inserted



MysqlYesExecutable filesMysqlimport,There is anotherMysqlInLoad data,ThisTwoCommand to import formatted data to the database from a file. If a file contains a large amount of data, you can import the data quickly.

MysqlimportThe usage is as follows:

/Usr/local/mysql/bin/mysqlimport [Options]Db_name Textfile1[Textfile2...]

WhereDb_nameName of the database to be inserted,TextfileFor the file where the formatted data to be inserted is located, insert the data in the file to the table with the name corresponding to the remaining name after removing the Suffix from the file name.

For example:Mysqlimport [Omitted] Statistic Spam.txt

WillSpam.txtThe data inserted into the database is namedStatistic, Table nameSpamIn

And the spam.txt file must be in the/usr/local/mysql/var/Statistic/directory [/usr/local/mysql/var/library /]

When I first did not pay attention to the file path, the following error is returned:

[Root @ mx1/tmp/KevinShell/statistic] #/usr/local/mysql/bin/mysqlimport-UUSER-pPASSWD-Hlocalhost-P3306 Statistic Spam.txt
/Usr/local/mysql/bin/mysqlimport: Error: File '/usr/local/mysql/var/Statistic/Spam.txt' not found (Errcode: 2), when using table: spam


After that, the data is successfully inserted.

[Root @ mx1/tmp/KevinShell/statistic] # cp Spam.txt/usr/local/mysql/var/Statistic/
[Root @ mx1/tmp/KevinShell/statistic] #/usr/local/mysql/bin/mysqlimport-UUSER-pPASSWD-Hlocalhost-P3306 Statistic Spam.txt


The data in the spam.txt file is the same as that in the spam.txt file.



The following describes some frequently used mysqlimport options.

-- Columns =Column name of the data table corresponding to the first column,Column name of the data table corresponding to the second column,...

Define the name of the database table corresponding to each column in the imported File

-- Fields-terminated-by =

What is the end of the data field? The parameter is a string. The default value is\ T

-- Fields-enclosed-by =

The symbol used to expand the data field. The default value is null. It can be double quotation marks or parentheses.

-- Fields-optionally-enclosed-by =

What symbols can be used to enclose a data field, because only some of the data is enclosed by these symbols.

-- Fields-escaped-by =

Escape Character. The parameter is a character. The default value is\

-- Lines-terminated-by =

What is the end of a Data row? The parameter is a string,WindowsDefault Value:\ R \ n

-- User = user_name-u user_name

-- Password [= password]-p [password]

-- Host = host_name-h hostname

--Port = port_num,-P port_num

User name, password,MysqlServerIpAndUsed for connectionTCP/IPPort number. The default value isMysqlDefault port3306

-- Ignore-lines = n

Ignore data files beforeNLine because many data files have Headers

-- Delete-D

Delete the original data in the table before inserting the data in the file

-- Local-L

Specify to read data files from the client computer; otherwise, read data from the server computer.

-- Lock-tables -L

Lock all tables before processing text files for writing,Ensure that all tables are synchronized on the server

-- Protocol = {TCP | SOCKET | PIPE | MEMORY}

Connection protocol used

-- Force -F

Ignore errors. For example, if the table of a text file does not exist, continue to process other files.

Not used-- ForceIf the table does not existMysqlimportExit

-- Compress-C

Compress all information sent between the client and the server (if both support compression)

-- Silent,-S

Silence mode,Output only when an error occurs

-- Socket = path,-S path

When connectedLocalhostSocket file used(Default host)

-- Verbose,-V

Lengthy mode. Prints detailed information about program operations.

-- Version,-V

Display version information and exit.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.