How to import and export a large number of data programs in MySQL

MySQL databaseOfImport and ExportI believe that many people have been involved,Large amount of dataIt is difficult to import and export data to and from the MySQL database. Next we will explain how to import and export a large number of data programs from the MySQL database for your reference.

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 hard disk of the local machine, but cannot be imported! 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 !! Reference content is as follows:

<?

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 <mysql_num_rows ($ result); $ 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! Reference content is as follows:

<?

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 <count ($ message); $ k + = ($ numfields + 1 ))

{

$ 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. During the import, the file () function may be faulty. I have no problem with 10 MB of data.) You can change it to fopen () and then never read a row !!

2. ftp is required for both 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!

I have already introduced a lot about the import and export of MySQL databases. The above section describes how to import and export a large amount of data from MySQL databases, which further deepens the knowledge, I hope everyone can make some gains.