Use mysqlimport to import formatted files to MySQL tables

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.