Mysql Data Import Export Method and Tool Description (3-Exporting Data) Export Data: Methods of Exporting Data
Translation statement: This article comes from Sam's TEACH YOURSELF MYSQL in 21 Days Book part of the book The unclear place is increasing; if there is a translation or incorrect place, please correct it.
Translator: David Euler, SCU. De_euler-david@yahoo.com.cn Time: 2004/04/24 Yuchuan You can see that Mysql has many ways to import data, however these is just half of data transfer. In addition, data is generally exported from the MySQL database. There are many reasons why we need to export data. An important reason is to back up the database. The cost of data is often expensive and needs to be careful. Frequently backups can help prevent valuable data loss; another reason is that you may want to export data to share. In the world that continuously grows this information technology, shared data is getting more common.
For example, Macmillan USA maintains a large database of books that will be published. This database is shared between many bookstores so they know which books will be published soon. The hospital is increasingly used to use a paper-free medical record, so that these medical records can follow you. The world has become less and smaller, and the information is also shared and more. There are many ways to export data, they are very similar to imported data. Because, after all, these are just a perspective. The data exported from the database is the data imported from the other end. Here we don't discuss other methods of other databases, you will learn how to use MySQL to implement data export.
Use mysqldump:
(MySQLDump Command is located in mysql / bin / directory) MySQLDUMP tool mysqlimport similar to the opposite effect. They have some same options. But mysqldump can do more things. It can load the entire database into a separate text file. This file contains the SQL commands you need to rebuild your database. This command acquires all modes (Schema, explained later) and converts it to DDL syntax (CREATE statement, ie, database definition statements), acquire all data, and create an Insert statement from these data. This tool reverses all your designs in your database. Because all things are included in a text file. This text file can be returned to MySQL with a simple batch and a suitable SQL statement. This tool is incredibly simple and fast. There will never have some points to make people distress.
Therefore, if you are on the content of the entire database meet_a_geek to 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 / loading?). If you just want to export the entire contents of the table ORDERS in the database meet_a_geek, 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 record you need to export to the file. To achieve this, you can use the following command: bin / mysqldump -p -where = "Order_id> 2000" meet_a_geek orders> special_dump.txtMysqldump tool has a lot of options, some options are as follows: Options / Options / Action Performed - add-drop-table will add a Drop Table if an exists statement in front of each table, so that it will not be wrong when the Mysql database is guided, because each time is returned, will first check first Whether the table exists, the presence is deleted - ADD-LOCKS this option bundles a Lock Table and UNLOCK TABLE statement in the INSERT statement. This prevents the operation of other users on the table when these records are again imported into the database.
-c or - complete_insert This option makes mySQLDUMP commands to generate an Insert statement plus (Field)
first name. This option is useful when you export the data. --Delayed-INSERT Add to DELAY Options in Insert Commands -f or -flush-logs Use this option before performing the export, will refresh the mysql server's log. -f or -force use this option, even if there is an error, still Continue to export -full This option also adds additional information to the CREATE TABLE statement - L or -Lock-Tables Use this option, the server will lock the table when exporting the table. -t or -no-create- info This option makes the mysqldump command not to create a CREATE TABLE statement. This option is convenient when you only need data without a DDL (database definition statement).
-d or-dia-data This option makes the mysqldump command does not create an Insert statement. This option can be used when you only need DDL statements. --Opt This option will open all options that will increase file export speed and create a file that can be imported faster. -q or -quick This option makes MySQL do not read the entire exported content into memory and execute, but is written in the file when you are 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 these two files are stored. The directory must already exist, and the user has the privilege of the file.
-w "where clause" or -where = "where clause" As mentioned earlier, you can use this option to filter the data that will be placed in the export file.
Assuming that you need to create a file for the account you want to use in a form, the manager wants to see all orders (ORDERS) this year (Orders), they do not interested DDL, and require files with comma separation, because this is easy Import into Excel. In order to complete this person, 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 result you want. Schema: Mode The Set of Statements, Expressed in Data Definition Language, That Completely Describe The Structure of A Data Base. A group of statement sets expressed in a data definition language, which completely describes the structure of the database.
Select Into Outfile: If you feel that the mysqldump tool is not cool enough, use SELECT INTO OUTFILE, MySQL also provides a command with the opposite effect with the load data infile command, which is the select into outfile command, which has a lot of similarities. Place. First, they have all the options almost the same. Now you need to complete the functionality completed with mysqldump in front, you can follow the steps below:
1. Make sure the mysqld process (service) is already running 2. CD / usr / local / mysql3. Bin / mysqladmin ping; // If this sentence is not available, you can use this: mysqladmin -u root -p ping mysqladmin ping for detection The status of MySQLD, IS Alive Description is running, and the error may be required. 4. Start the MySQL listener .5. Bin / mysql -p meet_a_geek; // Enter the mysql command line, and turn on the database meet_a_geek, you need to enter the password 6. In 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 rule's SELECT statement, just redirects the output of the desired screen to the file. This means that you can use Join to implement multi-table advanced queries. 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:
