Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the load statement in mysql to enter data in batches, the content of the article is of high quality, so the editor shares it for you to do a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

How to input data in batch by using load statement in mysql

1. Basic grammar

Syntax: the LOADDATA [local] INFILE'file_name.txt' [REPLACE | IGNORE] INTOTABLEtbl_nameLOADDATAINFILE statement is read into a table at a high speed from a piece of article. If you specify the LOCAL keyword, read the file from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL3.22.6 or later.)

For security reasons, when reading the article located on the server, the file must be in the database directory or be readable by everyone. In addition, in order to use LOADDATAINFILE for files on the server, you must have file permissions on the server host. See chapter 7, database security.

The REPLACE and IGNORE keywords control duplicate processing of existing unique key records. If you specify REPLACE, the new row replaces the existing row with the same unique key value. If you specify IGNORE, skip the input of duplicate lines of existing lines with a unique key. If you do not specify any of the options, an error occurs when a duplicate key is found, and the rest of the article is ignored.

If you use the LOCAL keyword to load data from a local file, the server has no way to stop the file transfer during the operation, so the default behavior is as if IGNORE is specified.

2. The principle of searching documents

When looking for files on the server host, the server uses the following rules:

If an absolute pathname is given, the server uses that pathname.

If a relative pathname with one or more front parts is given, the server searches the file relative to the server's data directory.

If you give a file name without a front part, the server looks for the file in the database directory of the current database.

Note that these rules mean that a file like ". / myfile.txt" is read from the server's data directory, while a file given as "myfile.txt" is read from the database directory of the current database. Also note that the db1 file is read from the database directory instead of db2 for which of the following statements:

Mysql > USEdb1

Mysql > LOADDATAINFILE ". / data.txt" INTOTABLEdb2.my_table

How to input data in batch by using load statement in mysql

3. Syntax of FIELDS and LINES clauses

If you specify a FIELDS clause, each of its clauses (TERMINATEDBY, [OPTIONALLY] ENCLOSEDBY and ESCAPEDBY) is also optional, except that you must specify at least one of them.

If you do not specify a FIELDS clause, the default value is the same as if you write this:

FIELDSTERMINATEDBY'\ tENCLOSEDBYEY escape EDBY'\\'

If you do not specify a LINES clause, the default value is the same as if you write this:

LINESTERMINATEDBY'\ n'

In other words, the default value causes the LOADDATAINFILE to behave as follows when reading input:

Look for line boundaries at newline characters

Break lines into fields at the locator

Do not expect fields to be encapsulated by any quote characters

Some literal characters that are interpreted as field values by locators, newlines, or "\" that begin with "\"

LOADDATAINFILE can be used to read files obtained from external sources. For example, a file in dBASE format will have fields separated by commas and enclosed in double quotes. If the line in the file is terminated by a newline character, the command shown below describes the fields and line processing options that you will use to load the file:

Mysql > LOADDATAINFILE'data.txt'INTOTABLEtbl_name

FIELDSTERMINATEDBY','ENCLOSEDBY' "'

LINESTERMINATEDBY'\ n'

Any field or row processing option can specify an empty string (''). If not empty, the FIELDS [optional] ENCLOSEDBY and FIELDSESCAPEDBY values must be a single character. The FIELDSTERMINATEDBY and LINESTERMINATEDBY values can be more than one character. For example, write to a line terminated by a carriage return newline pair (CR+LF), or read a file that contains such a line, specifying a LINESTERMINATEDBY'\ r\ n 'clause.

The enclosing character of the FIELDS [OPTIONALLY] ENCLOSEDBY control field. For the output (SELECT...INTOOUTFILE), if you omit OPTIONALLY, all fields are surrounded by ENCLOSEDBY characters. An example of such output (using a comma as a field delimiter) is shown below:

"1", "astring", "100.20"

"2", "astringcontaininga,comma", "102.20"

"3", "astringcontaininga\" quote "," 102.20 "

"4", "astringcontaininga\", quoteandcomma "," 102.20 "

If you specify that the OPTIONALLY,ENCLOSEDBY character is only used to surround the CHAR and VARCHAR fields:

1, "astring", 100.20

2, "astringcontaininga,comma", 102.20

3, "astringcontaininga\" quote ", 102.20

4, "astringcontaininga\", quoteandcomma ", 102.20

Note that the occurrence of the ENCLOSEDBY character in a field value is escaped by using the ESCAPEDBY character as its prefix. Also note that if you specify an empty escape database value, it may produce output that cannot be read correctly by LOADDATAINFILE. For example, if the escape character is empty, the output shown above is shown below. Notice that the second field in the fourth line contains a comma followed by quotation marks, which (mistakenly) seems to terminate the field:

1, "astring", 100.20

2, "astringcontaininga,comma", 102.20

3, "astringcontaininga" quote ", 102.20

4, "astringcontaininga", "quoteandcomma", 102.20

FIELDSESCAPEDBY controls how special characters are written or read. If the FIELDSESCAPEDBY character is not empty, it is used to prefix the following characters on the output:

FIELDSESCAPEDBY character

FIELDS [optional] ENCLOSEDBY character

The first character of the FIELDSTERMINATEDBY and LINESTERMINATEDBY values

ASCII0 (actually write subsequent escape characters as ASCII'0', instead of a zero byte)

If the FIELDSESCAPEDBY character is empty, no character is escaped. It may not be a good idea to specify an empty escape character, especially if the field value in your data contains any of the characters in the table just given.

For input, if the FIELDSESCAPEDBY character is not empty, the occurrence of the character is stripped and subsequent characters are literally part of the field value. The exception is an escaped "0" or "N" (that is,\ 0 or\ N, if the escape character is "\"). These sequences are interpreted as ASCII0 (a zero byte) and NULL. See the following rules for NULL processing.

Summary

Loading data for a database is one of the important responsibilities of administrators, and because it is important, MySQL provides a wide range of methods. The main ones are listed in this section:

1. Use INSERT and REPLACE statements

2. Use INSERT/REPLACE... SELECT statement

3. Use the LOADDATAINFILER statement

4. Use the utility mysqlimport

How to use load statement in mysql to enter data in batches is shared here. I hope the above content can be of some help to everyone and can 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.