MySQL data import and export methods and tools (1)
Translation statement:
This article is from part of Sams Teach Yourself MySQL in 21 Days.
The copyright of the original English text is owned by the original author. some Chinese translations are slightly added and deleted; while the original book is too clear about the content, but not clearly about the content; if any translation is incorrect or incorrect, correct it.
David Euler, SCU. de_euler-david@www.yahoo.com.cn
Time: 2004/04/24
1). mysqlimport syntax introduction:
Mysqlimport is located in the mysql/bin directory and is a very effective tool for mysql to load (or import) data. This is a command line tool. There are two parameters and a large number of options to choose from. This tool imports a text file into your specified database and table. Finally, we want to import data from the customers.txt file to the Custermers table in the Meet_A_Geek database:
Mysqlimport Meet_A_Geek Customers.txt
Note: customers.txt is the text file for data import, while Meet_A_Geek is the database to be operated, and the table name in the database is Customers, the data format of the text file must be the same as that of the record in the MERS table. Otherwise, the mysqlimport command will fail.
The table name is the file string before the first period (.) of the imported file. Another example is as follows:
Mysqlimport Meet_A_Geek Cus.to.mers.txt
Then we will import the content in the file to the Cus table in the database Meet_A_Geek. In the above example, only two parameters are used and no more options are used. The following describes the options of mysqlimport.
2). common mysqlimport options:
Option
-D or -- delete all information in the data table before the new data is imported into the data table.
-F or -- force: no matter whether an error occurs or not, mysqlimport will force data insertion to continue.
-I or -- ignore mysqlimport skips or ignores rows with the same unique keyword. data in the import file will be ignored.
-L or-lock-tables: the table is locked before data is inserted. This prevents you from affecting your queries and updates when updating the database.
-R or-replace is opposite to-I. This option replaces records with the same unique keywords in the table.
-- Fields-enclosed-by = char
Specifies the title of a data record in a text file. in many cases, the data is enclosed by double quotation marks. By default, data is not enclosed by characters.
-- Fields-terminated-by = char
Specifies the delimiter between values of each data. in a file separated by periods, the delimiter is a period. You can use this option to specify the delimiter between data. The default delimiter is a Tab)
-- Lines-terminated-by = str
This option specifies the delimiter string or character between the row and row in a text file. By default, mysqlimport uses newline as the line separator.
You can choose to use a string to replace a single character: a new line or a carriage return.
Common options of the mysqlimport command include-v display version and-p prompt for password.
3). example: to import a file with a comma as the delimiter, the row record format is as follows:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Our task is to import the data in this file to the table Orders in the database Meet_A_Geek. We use this command:
Bin/mysqlimport-prolactin-fields-enclosed-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt
This command may seem uncomfortable, but it is very simple when you are familiar with it.
The first part is bin/mysqlimport, which tells the operating system that the command you want to run is mysqlimport in the mysql/bin directory. option p requires a password, this requires you to enter the password before changing the database, which makes the operation safer. We use the r option because we want to replace the record with a unique keyword that already exists in the file record with the data in the file. The data in our form is not up-to-date and needs to be updated using the data in the file. Therefore, we use the r option to replace the existing records in the database. L option is used to lock the table when we insert data, which prevents users from querying or changing the table when we update the table.
--------------------------------------------------------------------------------
How to import and export large amounts of data from MySQL
Http://www.cx66.com/cxgzs/tips/00773.htm
You must have used the database import and export function in phpmyadmin, which is very convenient. However, in practical application, I found the following problems:
1. the size of the database exceeds a certain size. for example, if the size of 6 MB is used for export, the database can be correctly saved to the local hard disk, but the import will not work! The reason is: the size of temporary files/uploaded files is limited to 2 MB in PHP. INI, while phpmyadmin uses the upload method, resulting in a failure.
2. when importing the. SQL file exported to the hard disk back, the import may fail due to some single quotation marks, which can only be imported using applications such as mysql.
My database has exceeded 10 MB, so this problem must be solved. My ideas:
Export: use phpmyadmin to save the database/table structure, use scripts to read the database content, and save it to a file!
Import: use phpmyadmin to restore the database/table structure, use scripts to read files, and save them to the database!
The export program is as follows: the call method is ***. php? Table = tablename
This simple program currently saves a table at a time !! Data of one field for each behavior !!
If ($ table = "") exit ();
Mysql_connect ("localhost", "name", "password ");
Mysql_select_db ("database ");
$ Result = mysql_query ("select * from $ table ");
If (mysql_num_rows ($ result) <= 0) exit ();
Echo "starts to convert data to text...
";
$ Handle = fopen ("export table.txt", "w ");
$ Numfields = mysql_num_fields ($ result );
Fputs ($ handle, $ numfields ."");
For ($ k = 0; $ k
{
$ Msg = mysql_fetch_row ($ result );
For ($ I = 0; $ I <$ numfields; $ I ++)
{
$ Msg [$ I] = str_replace ("", "& php2000mysqlreturn &", $ msg [$ I]);
$ Msg [$ I] = str_replace ("", "& php2000mysqlreturn &", $ msg [$ I]);
Fputs ($ handle, $ msg [$ I]. "");
}
Fputs ($ handle, "------- php2000 dump data program V1.0 for MySQL --------");
}
Fclose ($ handle );
Echo "OK ";
?>
The imported program is as follows: the usage is the same as above!
If ($ table = "") exit ();
Mysql_connect ("localhost", "name", "password ");
Mysql_select_db ("database ");
$ Message = file ("cmdtable.txt ");
Echo $ numfields = chop ($ message [0]);
For ($ k = 1; $ k
{
$ Value = "";
For ($ I = $ k; $ I <($ k + $ numfields-1); $ I ++)
{
$ Tmp = str_replace ("& php2000mysqlreturn &", "", chop ($ message [$ I]);
$ Value. = "". addslashes ($ tmp ).",";
}
$ Tmp = str_replace ("& php2000mysqlreturn &", "", chop ($ message [$ k + $ numfields-1]);
$ Value. = "". $ tmp ."";
$ Query = "insert into $ table values (". $ value .")";
Echo mysql_error ();
Mysql_query ($ query );
Echo $ k ."";
}
Echo "OK ";
?>
Usage and possible problems!
1. there may be problems with the file () function during import (my 10 M data is normal). you can change it to fopen () and then read a row again !!
2. ftp is required for import and export. after export, ftp is used to transfer the data to the local machine. ftp is used to transfer the data to the server before import!
----------------------------
Examples of commonly used export and import commands in mysql databases
Www. yiz. name
======================================
Several common use cases:
1. export the entire database
Mysqldump-u username-p database name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL
2. export a table
Mysqldump-u user name-p database name table name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL
3. export a database structure
Mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data -- add-drop-table adds a drop tab before each create statement. Why? Tid = 3071 page = e MySQL data import and export methods and tools (1) translation statement: This article is from the part of Sams Teach Yourself MySQL in 21 Days... |
