How to import large amounts of data in MySQL

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. "/r/n ");

For ($ k = 0; $ k

{

$ Msg = mysql_fetch_row ($ result );

For ($ I = 0; $ I <$ numfields; $ I ++)

{

$ Msg [$ I] = str_replace ("/r/n", "& php2000mysqlreturn &", $ msg [$ I]);

$ Msg [$ I] = str_replace ("/n", "& php2000mysqlreturn &", $ msg [$ I]);

Fputs ($ handle, $ msg [$ I]. "/r/n ");

}

Fputs ($ handle, "------- php2000 dump data program V1.0 for MySQL --------/r/n ");

}

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 &", "/r/n", chop ($ message [$ I]);

$ Value. = "'". addslashes ($ tmp )."',";

}

$ Tmp = str_replace ("& php2000mysqlreturn &", "/r/n", 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!