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

Editor to share with you how to use MySQL to achieve data export, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Use dump:

(the mysqldump command is located in the mysql/bin/ directory)

The mysqldump tool is similar in many ways to the counterproductive tool mysqlimport. They have some of the same options. But mysqldump can do more. It can load the entire database into a separate text file. This file contains all the SQL commands you need to rebuild your database. This command takes all the schemas (Schema, explained later) and converts them to DDL syntax (CREATE statements, that is, database definition statements), takes all the data, and creates INSERT statements from that data. This tool reverses all the designs in your database. Because everything is included in a text file. This text file can be imported back into MySQL with a simple batch and a suitable SQL statement. This tool is incredibly simple and fast. There will never be the slightest headache.

Therefore, if you want to load 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 the 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 so flexible that you can even use the WHERE clause to select the records you want to export to a file. To do this, use a command similar to the following:

Bin/mysqldump-p-where= "Order_ID > 2000" Meet_A_Geek Orders > Special_Dump.txt

The mysqldump tool has a large number of options, some of which are shown in the table below:

Option / Option role / Action Performed

-- add-drop-table

This option will precede each table with a DROP TABLE IF EXISTS statement, which ensures that there will be no error when importing back to the MySQL database, because each time it is imported, it will first check whether the table exists and then delete it.

-- add-locks

This option binds a LOCK TABLE and UNLOCK TABLE statement to the INSERT statement. This prevents other users from operating on the table when these records are re-imported into the database

-c or-complete_insert

This option causes the mysqldump command to add (field) to each that generates an INSERT statement

First name. This option is useful when exporting data to another database.

-- delayed-insert adds the DELAY option to the INSERT command

-F or-flush-logs uses this option to refresh the log of the MySQL server before performing the export.

-for-force uses this option to continue exporting even if an error occurs

-- full this option adds additional information to the CREATE TABLE statement

-l or-lock-tables uses this option, and the server will lock the table when it is exported.

-t or-no-create- info

This option prevents the mysqldump command from creating CREATE TABLE statements, which is convenient when you only need data and not DDL (database definition statements).

The-d or-no-data option causes the mysqldump command not to create INSERT statements.

You can use this option when you only need DDL statements.

-- opt this option opens all options that increase file export speed and create a file that can be imported faster.

The-Q or-quick option allows MySQL not to read the entire export into memory and then perform the export, but to write it to the import file when it is read.

The option-T path or-tab = path creates two files, one containing DDL statements or table creation statements, and the other containing data. The DDL file is named table_name.sql and the data file is named table_name.txt. The pathname is the directory where the two files are stored. The directory must already exist and the user of the command has privileges on 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 of the file.

Suppose you need to create a file for the account to be used in a form, and the manager wants to look at all the orders (Orders) for this year (2004), which are not interested in DDL and need to be comma-delimited because they can be easily imported 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: mode

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 don't think the mysqldump tool is cool, use SELECT INTO OUTFILE. MySQL also provides a command that is the opposite of the LOAD DATA INFILE command, which is the SELECT INTO OUTFILE command, and there are many similarities between the two commands. First of all, they have almost the same options. Now that you need to complete the previous functions done with mysqldump, you can follow these steps:

1. Make sure the mysqld process (service) is already running

2. Cd / usr/local/mysql

3. Bin/mysqladmin ping; / / if this sentence fails, you can use this: mysqladmin-u root-p ping

Mysqladmin ping is used to detect the status of mysqld, is alive indicates that it is running, and a user name and password may be required for errors.

4. Start the MySQL listener.

5. Bin/mysql-p Meet_A_Geek;// enters the mysql command line and opens the database Meet_A_Geek. You need to enter a password.

6. On the command line, enter the following command:

SELECT * INTO OUTFILE / home/mark/Orders.txt

FIELDS

TERMINATED BY =

FROM Orders

WHERE Order_Date > = 2000-01-01

After you press Return, the file is created. This sentence is like a regular SELECT statement, only redirecting the output of the screen to the file. This means that you can use JOIN 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. Try this:

Create a text file called Report_G.rpt in the mysql directory and 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 verify that the mysql process is running and that you enter the following command in the mysql directory:

Bin/mysql < Report_G.rpt checks the file you named as output, which will contain the last names of all customers you enter in the Customers table. As you can see, you can use the import / export (import/export) method you learned today to help get the report.

The above is all the contents of the article "how to use MySQL to export 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.