MySQL-数据库导入-恢复方法

MySQL-数据库导入-恢复方法 (MySQL - Database Import - Recovery Methods)

There are two simple ways in MySQL to load data into the MySQL database from a previously backed up file.

MySQL中有两种简单的方法可以将数据从先前备份的文件加载到MySQL数据库中。

使用LOAD DATA导入数据 (Importing Data with LOAD DATA)

MySQL provides a LOAD DATA statement that acts as a bulk data loader. Here is an example statement that reads a file dump.txt from your current directory and loads it into the table mytbl in the current database.

MySQL提供了一个LOAD DATA语句，可以用作批量数据加载器。 这是一个示例语句，该语句从当前目录读取文件dump.txt并将其加载到当前数据库的表mytbl中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

• If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using the looking into absolute pathname, which fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.

  如果没有LOCAL关键字，MySQL将使用对绝对路径名的查找在服务器主机上查找数据文件，该路径名从文件系统的根开始完全指定文件的位置。 MySQL从给定位置读取文件。

• By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.

  默认情况下， LOAD DATA假定数据文件包含以换行(换行符)结尾的行，并且行中的数据值由制表符分隔。

• To specify a file format explicitly, use a FIELDS clause to describe the characteristics of fields within a line, and a LINES clause to specify the line-ending sequence. The following LOAD DATA statement specifies that the datafile contains values separated by colons and lines terminated by carriage returns and new line character.

  要明确指定文件格式，请使用FIELDS子句描述一行中字段的特征，并使用LINES子句指定行尾序列。 以下LOAD DATA语句指定数据文件包含用冒号和以回车符和换行符结尾的行分隔的值。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
   -> FIELDS TERMINATED BY ':'
   -> LINES TERMINATED BY '\r\n';

• The LOAD DATA command assumes the columns in the datafile have the same order as the columns in the table. If that is not true, you can specify a list to indicate which table columns the datafile columns should be loaded into. Suppose your table has columns a, b, and c, but successive columns in the datafile correspond to columns b, c, and a.

  LOAD DATA命令假定数据文件中的列与表中的列具有相同的顺序。 如果不是这样，则可以指定一个列表以指示数据文件列应加载到哪些表列中。 假设您的表具有列a，b和c，但是数据文件中的连续列对应于列b，c和a。

You can load the file as shown in the following code block.

您可以按照以下代码块中所示加载文件。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
   -> INTO TABLE mytbl (b, c, a);

使用mysqlimport导入数据 (Importing Data with mysqlimport)

MySQL also includes a utility program named mysqlimport that acts as a wrapper around LOAD DATA, so that you can load the input files directly from the command line.

MySQL还包括一个名为mysqlimport的实用程序，它充当LOAD DATA的包装，因此您可以直接从命令行加载输入文件。

To load data from the dump.txt into mytbl, use the following command at the UNIX prompt.

要将数据从dump.txt加载到mytbl中 ，请在UNIX提示符下使用以下命令。

$ mysqlimport -u root -p --local database_name dump.txt
password *****

If you use mysqlimport, command-line options provide the format specifiers. The mysqlimport commands that correspond to the preceding two LOAD DATA statements looks as shown in the following code block.

如果使用mysqlimport ，则命令行选项提供格式说明符。 对应于前两个LOAD DATA语句的mysqlimport命令的外观如以下代码块所示。

$ mysqlimport -u root -p --local --fields-terminated-by = ":" \
   --lines-terminated-by = "\r\n"  database_name dump.txt
password *****

The order in which you specify the options doesn't matter for mysqlimport, except that they should all precede the database name.

指定选项的顺序对mysqlimport无关紧要，只是它们都应在数据库名称之前。

The mysqlimport statement uses the --columns option to specify the column order −

mysqlimport语句使用--columns选项指定列顺序-

$ mysqlimport -u root -p --local --columns=b,c,a \
   database_name dump.txt
password *****

处理引号和特殊字符 (Handling Quotes and Special Characters)

The FIELDS clause can specify other format options besides TERMINATED BY. By default, LOAD DATA assumes that values are unquoted and interprets the backslash (\) as an escape character for the special characters. To indicate the value quoting character explicitly, use the ENCLOSED BY command. MySQL will strip that character from the ends of data values during input processing. To change the default escape character, use ESCAPED BY.

FIELDS子句可以指定除TERMINATED BY之外的其他格式选项。 默认情况下，LOAD DATA假定值未加引号，并将反斜杠(\)解释为特殊字符的转义字符。 要明确指示值引号字符，请使用ENCLOSED BY命令。 MySQL将在输入处理期间从数据值的末尾去除该字符。 要更改默认的转义字符，请使用ESCAPED BY 。

When you specify ENCLOSED BY to indicate that quote characters should be stripped from data values, it is possible to include the quote character literally within data values by doubling it or by preceding it with the escape character.

当您指定ENCLOSED BY以指示应从数据值中删除引号字符时，可以通过将其加倍或在其前面加上转义字符来在数据值中实际包括引号字符。

For example, if the quote and escape characters are " and \, the input value "a""b\"c" will be interpreted as a"b"c.

例如，如果引号和转义字符为“和\，则输入值” a“” b \“ c”将解释为“ b” c 。

For mysqlimport, the corresponding command-line options for specifying quote and escape values are --fields-enclosed-by and --fields-escaped-by.

对于mysqlimport ，用于指定引号和转义值的相应命令行选项是--fields-enclosed-by和--fields-escaped-by 。

翻译自: https://www.tutorialspoint.com/mysql/mysql-database-import.htm