MySQL data import Export method and tool introduction (3-exporting data)

mysql| data
MySQL data import Export method and tool introduction (3-exporting data)
Methods of exporting data: Methods of exporting data

Translation statement:
This article is part of a book from Sam's Teach yourself MySQL in and by Mark Maslakowski
English original version ownership of the original author, the Chinese part of the translation has a slight additions and deletions, the original book is too clear where there is a deletion, not clear where there is an increase, if there is improper translation or incorrect place, please correct me.

Translator: David Euler,scu. de_euler-david@www.yahoo.com.cn
Time: 2004/04/24 in Sichuan



You can see that MySQL has a lot of ways to import data, but these are only half of what's being transferred. The other is typically exporting data from the MySQL database. There are many reasons why we need to export the data. One important reason is to back up the database. The cost of data is often expensive and needs to be handled with care. Backing up frequently can help prevent the loss of valuable data, and another reason is that you might want to export the data to share. In this growing world of information technology, shared data is becoming more common.

For example, Macmillan USA maintains a large database of books that will be published. The database is shared among many bookstores so that they know which books will be published soon. Hospitals are increasingly moving to paperless records so that they can be followed by you at any time. The world is getting smaller, and information is being shared more and more. There are many ways to export data, which are similar to the imported data. Because, after all, these are just a way of perspective. The data that is exported from the database is the data that is imported from the other end. Here we do not discuss the various methods of exporting data from other databases, and you will learn how to use MySQL to implement data export.

Use mysqldump:

(The mysqldump command is in the mysql/bin/directory)
Mysqldump tools in many ways are similar to the opposite effect of tool mysqlimport. They have some of the same options. But mysqldump can do more things. It can load the entire database into a separate text file. This file contains all the SQL commands needed to rebuild your database. This command gets all the schemas (schemas, followed by explanations) and converts them to the DDL syntax (create statement, which is the database definition statement), gets all the data, and creates the INSERT statement from the data. This tool reverses all the designs in your database. Because all things are included in a text file. This text file can be used in a simple batch process and a suitable SQL statement to guide back to MySQL. The tool was incredibly simple and fast. There's no place to be a pain in the neck.

Therefore, if you are loading the contents of the entire database meet_a_geek into a file, you can use the following command:
Bin/mysqldump–p Meet_a_geek > Meetageek_dump_file.txt

This statement also allows you to specify a table for dump (Backup/export/load?). If you just want to export the entire contents of the table orders in database Meet_a_geek to a file, you can use the following command:
Bin/mysqldump–p Meet_a_geek Orders >meetageek_orders.txt

This is very flexible, you can even use the WHERE clause to select the records you want to export to the file. To achieve this, you can use a command similar to the following:
Bin/mysqldump–p–where= "order_id > Meet_a_geek" Orders > Special_dump.txt

The Mysqldump tool has a number of options, some of which are listed below:
Option/option action/action performed
--add-drop-table
This option will precede each table with the drop table if exists statement, which guarantees that the MySQL database will be returned without error, because each time it is returned, the table is first checked for existence and deleted.
--add-locks
This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents other users from working on the table when these records are imported again into the database

-C Or-complete_insert
This option causes the mysqldump command to add (field) to each generated INSERT statement

Name. This option is useful when exporting data to another database.
--delayed-insert Add delay option to insert command
-F or-flush-logs Use this option to flush the MySQL server log before performing the export.
-F Or-force Use this option to continue exporting even if errors occur
--full This option adds additional information to the statement in the CREATE table
-L or-lock-tables using this option, the server will lock the table when the table is exported.
-T Or-no-create-info
This option causes the mysqldump command to not create a creating table statement, which is handy when you need only data without the need for DDL (database definition statements).

-D or-no-data This option causes the mysqldump command to not create INSERT statements.
You can use this option when you need only DDL statements.
--opt This option opens all options that will increase the speed of file exports and create a file that can be imported faster.
-Q or-quick This option allows MySQL to not read the entire exported content into memory and then perform the export, but write to the guide file when read.
-T Path or-tab = Path This option will create two files, one file contains DDL statements or table creation statements, and another file contains data. The DDL file is named Table_name.sql, and the data file is named Table_name.txt. The path name is the directory where the two files are stored. The directory must already exist and the user of the command has privileges to the file.

-W "where Clause" Or-where = "where Clause"
As mentioned earlier, you can use this option to filter what will be placed in the
Export the data for the file.

Suppose you need to create a file for the account that you want to use in a form, the manager wants to see all orders for this year (2004), they are not interested in DDL, and require a comma-delimited file because it's easy to import into Excel. To complete this character, you can use the following sentence:
Bin/mysqldump–p–where "order_date >= ' 2000-01-01 '"
–tab =/home/mark–no-create-info–fields-terminated-by=, Meet_a_geek Orders
This will get the results you want.

Schema: Schema
The set of statements, expressed in data definition language, that completely describe the structure of a data base.
A set of statements expressed in a data definition language that fully describes the structure of the database.



SELECT into outfile:
If you think the mysqldump tool is not cool enough to use SELECT into OutFile, MySQL also provides a command that has the opposite effect to the load DATA infile command, which is the SELECT INTO OutFile command. There are many similarities between the two commands. First, they have almost the same options. Now you need to complete the previous function with Mysqldump, you can follow the steps below:

1. Ensure that the MYSQLD process (service) is already running
2. Cd/usr/local/mysql
3. Bin/mysqladmin Ping;//If this sentence does not pass, you can use this: mysqladmin-u root-p ping
Mysqladmin Ping is used to detect the status of Mysqld, the is Alive description is running, and an error may require a username and password.
4. Start the MySQL listener program.
5. bin/mysql–p meet_a_geek;//Enter the MySQL command line, and open the database Meet_a_geek, you need to enter a password
6. At the command line, enter the command:
SELECT * into outfile '/home/mark/orders.txt '
FIELDS
Terminated by = ', '
From Orders
WHERE order_date >= ' 2000-01-01 '

After you press RETURN (enter), the file is created. This sentence is like a regular SELECT statement that redirects the output of the screen to the file. This means that you can use joins to implement advanced queries for multiple tables. This feature can also be used as a report generator.
For example, you can combine the methods discussed in this chapter to produce a very interesting query, and try this:

To create a text file named Report_g.rpt in the MySQL directory, add the following line:
Use Meet_a_geek;
INSERT into Customers (customer_id, last_name, first_name)
VALUES (NULL, "Kinnard", "Vicky");
INSERT into Customers (customer_id, last_name, first_name)
VALUES (NULL, "Kinnard", "Steven");
INSERT into Customers (customer_id, last_name, first_name)
VALUES (NULL, "Brown", "Sam");
SELECT last_name into outfile '/home/mark/report.rpt '
From Customers WHERE customer_id > 1;
Then confirm that the MySQL process is running and that you are in the MySQL directory, enter the following command:
Bin/mysql < report_g.rpt Check the file you named as output, and this file will contain the last name of all the customers you entered in the Customers table. As you can see, you'll be able to use the import/export (Import/export) method you learned today to help you get a report.