A program implementation method for importing large amounts of data from MySQL

mysql| Program | Data you must have used the phpMyAdmin inside the database import, export function, very convenient. But in practical applications, I found the following questions:

1, the database exceeds a certain size, such as 6M at this time using the export generally no problem, you can correctly save to the local hard disk, but the import is not! The reason is: General PHP. INI set temporary file/upload file size limit of 2M, and phpMyAdmin use the way to upload, resulting in failure.

2, exported to the hard disk. SQL file in the lead back, often because of some single quotes problems caused by failure, resulting in the import failure, can only use MySQL and other applications imported.

My database is over 10M, so this problem must be addressed. My train of thought:

Export: Save database/table structure with phpMyAdmin, read database content with script and save to file!

Import: Restore the database/table structure with phpMyAdmin, read the file with the script, and save it to the library!



The export process is as follows: Call method ****.php?table=tablename

This simple program saves a table at once!! Each behavior a field of data!!


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 begins converting data to text ...

";

$handle = fopen ("$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 programs are as follows: Use the same top!


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&&", "\ 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";

?>



How to use and possible problems!

1, Import time the file () function may have a problem (my 10M data does not appear to be a problem), you can change to fopen () and then read a line!!

2, import, export all need to use FTP operation, that is, after the export, with FTP data to the local machine, the first transfer data to the server with FTP!