We are in the postMySQL Data Export - brucexia - Blogspot ()In this article, we talk about exporting MySQL data, and this article explains importing MySQL data.
Importing MySQL data includes importing using the LOAD DATA INFILE command and importing using the mysqlimport command.
Importing Text Files with LOAD DATA INFILE
MySQL allows data to be exported to and imported from external files.MySQL provides a number of tools for importing data, including the LOAD DATA statement, the source command, and the mysql command.The LOAD DATA INFILE statement is used to read rows from a text file at high speed into a table. The file name must be a text string.
The basic format of the LOAD DATA statement is as follows:
LOAD DATA INFILE '' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
In the LOAD DATA statement, the keyword INFILE followed by the filename file for the source of the imported data; tablename indicates the name of the data table to be imported; [OPTIONS] for the optional parameter options, OPTIONS part of the syntax, including the FIELDS and LINES clauses, and its possible values are:
FIELDS TERMINATED BY 'value': set the separator character between fields, can be single or multiple characters, default is "\t".
FIELDS [OPTIONALLY] ENCLOSED BY 'value': sets the enclosing character of the field to a single character only. If OPTIONALLY is used, only character data fields such as CHAR and VERCHAR are enclosed.
FIELDS ESCAPED BY 'value': control how to write or read special characters, can only be a single character, i.e. set the escape character, the default value is "\".
LINES STARTING BY 'value': set the character at the beginning of each line of data, can be single or multiple characters, by default no character is used.
LINES TERMINATED BY 'value': set the character at the end of each line of data, can be a single or multiple characters, the default value is "\n".
The IGNORE number LINES option indicates that the number of lines at the beginning of the file is ignored. number indicates the number of lines to ignore. Executing the LOAD DATA statement requires FILE privileges.
[Example 11.19] Use the LOAD DATA statement to import data from the D:\ file into the person table of the test_db database with the following SQL statement:
LOAD DATA INFILE 'D:\' INTO TABLE test_db.person;
Before importing the data, delete all the data in the person table, i.e., log in to MySQL and use the DELETE statement to delete the data in the person table:
mysql> USE test_db; Database changed; mysql> DELETE FROM person; Query OK, 10 rows affected (0.00 sec)
To import data from a file, the SQL statement is as follows:
mysql> LOAD DATA INFILE 'D:\' INTO TABLE test_db.person; Query OK, 10 rows affected (0.00 sec) Records: 10 Deleted: 0 Skipped: 0 Warnings: 0 mysql> SELECT * FROM person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Mary | 24 | Musician | | 4 | Willam | 20 | sports man | | 5 | Laura | 25 | NULL | | 6 | Evans | 27 | secretary | | 7 | Dale | 22 | cook | | 8 | Edison | 28 | singer | | 9 | Harry | 21 | magician | | 10 | Harriet | 19 | pianist | +----+---------+----+-------------+
As you can see, the data in the file is imported into the person table after the statement is executed successfully.
[Example 11.20] Use the LOAD DATA statement to import the data in the D:\ file into the person table in the test_db database, using the FIELDS option and the LINES option, requiring that the fields be spaced by ",", that all field values be enclosed in double quotes, and defining the escape character to be "\'", each row ends with "\r\n", the SQL statement is as follows:
LOAD DATA INFILE 'D:\' INTO TABLE test_db.person FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\'' LINES TERMINATED BY '\r\n';
Before importing the data, use the DELETE statement to delete all the data in the person table as follows:
mysql> DELETE FROM person; Query OK, 10 rows affected (0.00 sec) Importing data from the file is performed as follows: mysql> LOAD DATA INFILE 'D:\' INTO TABLE test_db .person -> FIELDS -> TERMINATED BY ',' -> ENCLOSED BY '\"' -> ESCAPED BY '\'' -> LINES -> TERMINATED BY '\r\n'; Query OK, 10 rows affected (0.00 sec) Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
After the statement executes successfully, use the SELECT statement to view the records in the person table, and the results are the same as in [Example 11.19].
Importing a Text File Using the mysqlimport Command
The mysqlimport command can be used to import text files without logging into the MySQL client. mysqlimport provides many of the same features as the LOAD DATA INFILE statement, and most of the options correspond directly to the LOAD DATA INFILE clause. To use the mysqlimport command, you need to specify the desired options, the name of the database to be imported, and the path and name of the datafile to be imported. the basic syntax of the mysqlimport command is formatted as follows:
mysqlimport -u root-p dbname [OPTIONS]
dbname is the name of the database where the imported table is located. Note that the mysqlimport command does not specify the name of the table in the imported database. The name of the data table is determined by the name of the import file, that is, the file name is used as the table name, and the table must exist before importing the data. [OPTIONS] is an optional parameter option, and its common values are:
--fields-terminated-by= 'value': set the separator character between fields, can be single or multiple characters, default is "\t".
--fields-enclosed-by= 'value': sets the enclosing character of the field.
--fields-optionally-enclosed-by= 'value': set the enclosing character of the field to a single character only, including character data fields such as CHAR and VERCHAR.
--fields-escaped-by= 'value': control how to write or read special characters, can only be a single character, i.e. set the escape character, default value is "\".
--lines-terminated-by= 'value': set the character at the end of each line, can be single or multiple characters, default value is "\n".
--ignore-lines=n: Ignore the first n lines of the data file.
[Example 11.21] Use the mysqlimport command to import the contents of a file in the D-disk directory into the test_db database, use "," to separate fields, use double quotes to indent the values of character-type fields, and define the escape character as "? " The escape character is defined as "?", and each row of records ends with "\r\n", the SQL statement is as follows:
C:\ >mysqlimport -u root -p test_db D:\ --fields-terminated-by=, --fields-optionally-enclosed-by=\"--fields-escaped-by=?--lines-terminated-by=\r\n
The above statement should be entered in a single line, after the successful execution of the statement, the data will be imported into the database test_db.
In addition to the few options described earlier, the mysqlimport command supports many other options, such as:
--columns=column_list, -c column_list: Use comma-separated column names as their values. The order of the column names indicates how the datafile columns and table columns are matched.
--compress, -C: Compress all messages sent between client and server (if both support compression).
-d, --delete: clear the table before importing the text file.
--force, -f: Ignore errors. For example, when a table does not exist for a particular text file, continue processing the other files. When --force is not used, mysqlimport exits if the table does not exist.
--host=host_name, -h host_name: Import data into the MySQL server on the given host. The default host is localhost.
--ignore, -i: See the description of the --replace option.
--ignore-lines=n: Ignore the first n lines of the data file.
--local, -L: Read the input file from the local client.
--lock-tables, -l: Lock all tables for writing before processing text files. This ensures that all tables remain synchronized on the server.
--password[=password], -p[password]: the password to use when connecting to the server. If the short option form (-p) is used, there can be no space between the option and the password. If there is no password value following the --password or -p option on the command line, you are prompted for a password.
--port=port_num, -P port_num: TCP/IP port number used for connection.
--protocol={TCP | SOCKET | PIPE | MEMORY}: connection protocol used.
The --replace and --ignore options control the processing of input rows that copy existing rows with unique key values. If --replace is specified, the new line replaces an existing line with the same unique key value; if --ignore is specified, the input line that copies an existing unique key value is skipped and no replacement occurs; if neither option is specified, an error occurs when a copied key value is found and the remainder of the text file is ignored.
--silent, -s: silent mode. Outputs a message only when an error occurs.
--user=user_name, -u user_name: The username used by MySQL when connecting to the server.
--verbose, -v: verbose mode. Prints out detailed information about the program's operations.
--version, -V: Display version information and exit.
