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

Editor to share with you MySQL data import and export of pure data ways, I believe that most people do not know much, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

MySQL generally uses load data file, mysqlimport, select into outfile, > / > > redirection to import and export pure data. This paper mainly introduces the methods of load data file and select into outfile.

1. MySQL imports and exports data:

1 、 load data file

Brief introduction:

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT... INTO OUTFILE.

Official reference manual: http://dev.mysql.com/doc/refman/5.6/en/load-data.html

Syntax format:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[PARTITION (partition_name,...)]

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}]

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number {LINES | ROWS}]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]

Load data file example:

1) Import the file directly

LOAD DATA INFILE 'test_data.txt' INTO TABLE dbtest.t1

2) set column delimiter and row separator

LOAD DATA LOCAL INFILE 'test_data.txt' INTO TABLE T1

FIELDS TERMINATED BY','

OPTIONALLY ENCLOSED BY'"

LINES TERMINATED BY'\ n'

3) Import to a specific column

LOAD DATA LOCAL INFILE 'test_data.txt' INTO TABLE T1 (col1,col2,col3)

Parameter description:

1) local parameters

If LOCAL is specified and is considered to be related to the connected client, the file is read by the client on the client host and sent to the server. The file is given a full path name to specify the exact location. If given a relative path name, this name is understood relative to the directory in which the client was started.

If LOCAL is not specified, the file must be on the server host and be read directly by the server.

When locating files on the server host, the server uses the following rules:

If an absolute path name is given, the server uses this path name.

If a relative path name with one or more boot components is given, the server searches for files relative to the server data directory.

If a file name is given without a boot component, the server looks for the file in the database directory of the default database.

Note that these rules mean that a file named. / myfile.txt is read from the server data directory, and the same file named myfile.txt is read from the database directory of the default database.

Load data using an absolute path from the client: LOAD DATA LOCAL INFILE'/ import/test_data.txt' INTO TABLE dbtest.t1

Using the relative path load data from the server, the following LOAD DATA statement reads the file test_data.txt from the dbtest database directory, because db1 is the current database. Even if the statement explicitly loads the file into a table in the db2 database, it will be read from the dbtest directory.

USE dbtest

LOAD DATA INFILE 'test_data.txt' INTO TABLE dbtest.t1

To sum up: if you specify the local keyword, it indicates that the file is read from the client host. If local is not specified, the file must be located on the server.

2) IGNORE number LINES parameters

The IGNORE number LINES option can be used to ignore lines at the beginning of the file. You can use IGNORE 1 LINES to skip a starting header row that contains column names:

LOAD DATA INFILE'/ tmp/test.txt' INTO TABLE test IGNORE 1 LINES

3) parameters of REPLACE and IGNORE

If you specify REPLACE, the input row replaces the original row (a row with the same value for a primary or unique index as the original row).

If you specify IGNORE, the input line that copies the original line to the unique key value is skipped.

If you do not specify either of these options, the performance depends on whether the LOCAL keyword is specified. Without LOCAL, when duplicate key values occur, an error occurs and the remaining text files are ignored. When using LOCAL, the default operation is the same as when IGNORE is specified; this is because there is no way for the server to abort the transfer of files during the run.

4) FIELDS parameters

The splitting format of the file field is specified, and the syntax of the column delimiter parameter is

[{FIELDS | COLUMNS}]

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

The delimiter of the terminated by description field, which by default is the tab character (\ t)

Enclosed by describes the enclosed characters of fields. If you omit the word OPTIONALLY, all fields are included in the ENCLOSED BY string. If you specify OPTINALLY, ENCLOSED BY characters are only used to contain values in columns with string data types (such as CHAR, BINARY, TEXT, or ENUM).

The escape character of the escaped by description. The default is the backslash (backslash:\)

If you do not specify the FIELDS clause, the default value is the value assumed when you write the following statement:

FIELDS TERMINATED BY't 'ENCLOSED BY' 'ESCAPED BY'\'

When you read the input value, the default value causes LOAD DATA INFILE to run as follows:

Find the boundary of the line at the new line.

No row prefixes are skipped.

Decompose the row into fields at the tab.

You do not want the field to be contained in any quote characters.

When tabs, new lines, or'\ 'precede'\ 'appear, it is understood as a text character that is part of the field value.

Note: if you have already generated a text file on the Windows system, you may have to use LINES TERMINATED BY'\ r\ n' to read the file correctly, because Windows programs usually use two characters as a line Terminator. Some programs, when writing files, may use\ r as the line Terminator. To read such a file, use LINES TERMINATED BY'\ r'. To write FIELDS ESCAPED BY'\', you must specify two backslashes for the value to be read to use as a single backslash.

6) LINES parameters

The delimiter specified for each record defaults to'\ n', which is the newline delimiter, and its syntax is:

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

If you do not specify the LINES clause, the default value is the value assumed when you write the following statement:

LINES TERMINATED BY'\ n 'STARTING BY''

If all the lines you want to read contain a common prefix that you want to ignore, you can use 'prefix_string' to skip the prefix (and the characters before the prefix). If a line does not include a prefix, the entire line is skipped. Note: prefix_string appears in the middle of the line.

ALTER TABLE...DISABLE KEYS

ALTER TABLE...ENABLE KEYS

2 、 select into outfile

Brief introduction:

Official reference manual: http://dev.mysql.com/doc/refman/5.6/en/select-into.html

Syntax format:

SELECT... INTO var_list # # selects column values and stores them into variables.

SELECT... INTO OUTFILE # # writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.

SELECT... INTO DUMPFILE # # writes a single row to a file without any formatting.

Select into outfile example:

1) SELECT... INTO OUTFILE 'file_name'

2) SELECT, a dint, bjorn, an INTO OUTFILE'/ tmp/result.txt'

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

LINES TERMINATED BY'\ n'

FROM test_table

3) select * from T1 into outfile'/ tools/databak/dbtest_t1.txt'

Fields TERMINATED BY','

Lines TERMINATED BY'\ n'

Parameter description: (refer to load data parameter)

3. Mysqlimport (not recommended)

Mysqlimport is an operating system command, which has the same function as load data. The specific usage is as follows:

Syntax format:

Mysqlimport-- defaults-file=''-- default-character-set=utf8-- columns=id,name-- delete-- fields-terminated-by=''-- fields-enclosed-by=''-- fields-optionally-enclosed-by=''-- fields-escaped-by=''-- force-h-I-- ignore-lines-- lines-terminated-by=''-L-P-S-u dbname

Examples of use:

Mysqlimport-L-uroot dbtest / tools/databak/t1.txt-fields-terminated-by=','-lines-terminated-by='\ n'

Parameter description:

Mysqlimport-help

-- print-defaults Print the program argument list and exit.

-- no-defaults Don't read default options from any option file

Except for login file.

Defaults-file=# Only read default options from the given file #.

-- defaults-extra-file=# Read this file after the global files are read.

-- defaults-group-suffix=#

Also read groups with concat (group, suffix)

-- login-path=# Read this path from the login file.

-- bind-address=name IP address to bind to.

-- character-sets-dir=name

Directory for character set files.

-- default-character-set=name

Set the default character set.

-c,-- columns=name Use only these columns to import the data to. Give the

Column names in a comma separated list. This is same as

Giving columns to LOAD DATA INFILE.

-C.-- compress Use compression in server/client protocol.

-#-- debug [= name] Output debug log. Often this is'd is a kind of filename.

-- debug-check Check memory and open file usage at exit.

-- debug-info Print some debug info at exit.

-- default-auth=name Default authentication client-side plugin to use.

-d,-- delete First delete all rows from table.

-- enable-cleartext-plugin

Enable/disable the clear text authentication plugin.

-- fields-terminated-by=name

Fields in the input file are terminated by the given

String.

-- fields-enclosed-by=name

Fields in the import file are enclosed by the given

Character.

-- fields-optionally-enclosed-by=name

Fields in the input file are optionally enclosed by the

Given character.

-- fields-escaped-by=name

Fields in the input file are escaped by the given

Character.

-f,-- force Continue even if we get an SQL error.

-?-- help Displays this help and exits.

-h,-- host=name Connect to host.

-I,-ignore If duplicate unique key was found, keep old row.

-- ignore-lines=# Ignore first n lines of data infile.

-- lines-terminated-by=name

Lines in the input file are terminated by the given

String.

-L.-- local Read all files through the client.

-l,-- lock-tables Lock all tables for write (this disables threads).

-- low-priority Use LOW_PRIORITY when updating the table.

-p,-- password [= name]

Password to use when connecting to server. If password is

Not given it's asked from the tty.

-- plugin-dir=name Directory for client-side plugins.

-P,-- port=# Port number to use for connection or 0 for default to, in

Order of preference, my.cnf, $MYSQL_TCP_PORT

/ etc/services, built-in default (3306).

-- protocol=name The protocol to use for connection (tcp, socket, pipe

Memory).

-r,-- replace If duplicate unique key was found, replace old row.

-- secure-auth Refuse client connecting to server if it uses old

(pre-4.1.1) protocol.

(Defaults to on; use-skip-secure-auth to disable.)

-s,-- silent Be more silent.

-S,-- socket=name The socket file to use for connection.

-ssl Enable SSL for connection (automatically enabled with

Other flags).

-- ssl-ca=name CA file in PEM format (check OpenSSL docs, implies

Ssl).

Ssl-capath=name CA directory (check OpenSSL docs, implies-- ssl).

Ssl-cert=name X509 cert in PEM format (implies-- ssl).

Ssl-cipher=name SSL cipher to use (implies-- ssl).

Ssl-key=name X509 key in PEM format (implies-- ssl).

Ssl-crl=name Certificate revocation list (implies-- ssl).

Ssl-crlpath=name Certificate revocation list path (implies-- ssl).

-- ssl-verify-server-cert

Verify server's "Common Name" in its cert against

Hostname used when connecting. This option is disabled by

Default.

-- ssl-mode=name SSL connection mode.

-- use-threads=# Load files in parallel. The argument is the number of

Threads to use for loading data.

-u,-- user=name User for login if not current user.

-v,-- verbose Print info about the various stages.

V,-- version Output version information and exit.

The above is all the contents of this article entitled "what are the ways for MySQL data to import and export pure data?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.