|
Introduction to MySQL data import and export methods and tools (1)
Translation statement:
The content of this article comes from Sams Teach Yourself MySQL in 21 Part of the content of the book Days, by Mark Maslakowski
The copyright of the original English text belongs to the original author. Some of the Chinese translations have slight additions and deletions; places where the original book was too clear have been deleted, and places where the original book was not clear have been added; If there are any inappropriate or incorrect translations, please correct them.
Translator: David Euler,SCU. de_euler-david@www.yahoo.com.cn
Time: 2004/04/24 at Sichuan University
1) Introduction to the syntax of .mysqlimport:
Mysqlimport is located in the mysql/bin directory and is a very effective tool for loading (or importing) data in mysql. 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 the database and tables you specify. For example, we want to import data from the file Customers.txt into the table Customers in the database Meet_A_Geek:
mysqlimport Meet_A_Geek Customers.txt
Note: Here Customers.txt is the text file we want to import data from, and Meet_A_Geek is the database we want to operate. The table name in the database is Customers. The data format of the text file here must be consistent with the record format in the Customers table, otherwise the mysqlimport command will cause an error.
The name of the table is the file string preceding the first period (.) of the imported file. Another example:
mysqlimport Meet_A_Geek Cus.to.mers.txt
Then we will put the contents of the file Import into the Cus table in the database Meet_A_Geek. In the above examples, only two parameters are used, and no more options are used. The options of mysqlimport are introduced below.
2) Introduction to common options of .mysqlimport:
Options Function
-d or --delete Delete all information in the data table before importing new data into the data table
-f or --force No matter whether an error is encountered, mysqlimport will force to continue inserting data
-i or --ignore mysqlimport skips or ignores rows with the same unique keyword, and the data in the imported file will be ignored.
-l or -lock-tables locks the table before data is inserted, thus preventing user queries and updates from being affected when you update the database.
-r or -replace This option has the opposite effect to the -i option; this option will replace records with the same unique key in the table.
--fields-enclosed- by= char
By default data is not enclosed in characters.
--fields-terminated- by=char
Specifies the separator between the values of each data. In a period-delimited file, the separator is a period. You can use this option to specify the delimiter between data. The default delimiter is Tab
--lines-terminated- by=str
This option specifies the string or character that separates data between lines in the text file. By default, mysqlimport uses newline as the line separator.
You can choose to replace a single character with a string: a newline or a carriage return.
Commonly used options of the mysqlimport command include -v to display the version (version), -p to prompt for a password (password), etc.
3). Example: Import a comma-delimited file. The record format of the lines in the file is as follows:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Our task is to import the data in this file into the table Orders in the database Meet_A_Geek. We use this command:
bin/mysqlimport –prl –fields-enclosed-by=" –fields-terminated-by =, Meet_A_Geek Orders.txt
This command may look awkward, but once you get familiar with it, it is very simple.
The first part, bin/mysqlimport, tells the operating system that the command you want to run is mysql. For mysqlimport in the /bin directory, option p requires a password, which requires you to enter a password before changing the database. The operation will be safer. We use the r option because we want to associate the unique keywords in the table with the file. Records with duplicate unique keywords are replaced with data in the file. The data in our form is not the latest and needs to be updated with the data in the file, so we use the r option to replace the existing records in the database l. The function of the option is to lock the table when we insert data, thus preventing users from querying or changing the table when we update the table.
-------------------------------------------------- ----------------------------------
Program implementation method for importing and exporting large amounts of data from MySQL
http ://www.cx66.com/cxgzs/tips/00773.htm
Everyone must have used the database import and export functions in phpmyadmin, which is very convenient. However, in practical applications, I found the following problems:
1. The database exceeds a certain size, such as 6M. At this time, exporting is generally no problem and can be correctly saved to the local hard disk, but importing does not work! The reason is: Generally, the size limit of temporary files/uploaded files is set to 2M in PHP.INI, but phpmyadmin used the upload method, causing failure.
2. When importing .SQL files exported to the hard disk, they often fail due to certain single quote problems, causing the import to fail and can only be imported using applications such as mysql.
My database has exceeded 10M, so this problem must be solved. My idea:
Export: Use phpmyadmin to save the database/table structure, use a script to read the database content and save it to a file!
Import: Use phpmyadmin to restore the database/table structure, use a script to read the file, and then save it to the library!
The export program is as follows: the calling method is ****.php?table=tablename
This simple program currently saves one table at a time! ! Each line contains data for one field! !
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 "Start converting data to text...
";
$handle = fopen("$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("$table .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 when importing (there is no problem with my 10M data). You can change it to fopen() and read one line at a time. ! !
2. Import and export require ftp operation, that is, after exporting, use ftp to transfer the data to the local machine, and when importing, use ftp to transfer the data to the server first! ----------------------
Several use cases of common export and import commands for mysql database
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_apps_wcnc > wcnc.sql
2. Export a table
mysqldump -u user name -p database name table name> exported file name
mysqldump -u wcnc -p smgp_apps_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 Add a drop tab before each create statement
http://www.bkjia.com/PHPjc/508509.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/508509.htmlTechArticleExcerpted from http://qfxy.org.ru/read.php?tid=3071page=e MySQL data import and export Introduction to methods and tools (1) Translation statement: The content of this article comes from part of the book Sams Teach Yourself MySQL in 21 Days...
|
