Geek Answers Handbook

How to build a large MySQL INSERT query in PHP without memory loss

I have a code that looks something like this:

$data = file_get_contents($tempFile); // perhaps 30MB of file data, now in PHP memory
$hash = md5($data);
$query = "INSERT INTO some_table
          SET BlobData = '" . mysql_real_escape_string($data) . "',
          BlobHash = '$hash'
          ";
mysql_query($query);

I know that this is not very effective, since each of them. operators reallocate a larger block of memory, and a line of 30 MB will be copied several times.

Is there anything more effective than the following solution?

$data = file_get_contents($tempFile); // perhaps 30MB of file data, now in PHP memory
$hash = md5($data);
$query = "INSERT INTO some_table SET BlobData = '%s', BlobHash = '$hash'";
mysql_query(sprintf($query, mysql_real_escape_string($data)));
+5
source share
5 answers

You have two questions:

# 1, you can calculate the MD5 hash in several ways:

  • Do the way you do it and load PHP as a string and use PHP md5()
  • Use php md5_file()
  • Starting with PHP 5.1+, you can use the PHP threading API with any of md5or md5_file, so as not to load memory completely.
  • exec() md5sum
  • MySQL md5()

, . , , md5 exec , PHP md5_file . , , .

# 2, mysql_real_escape_string , blob , (!) INSERT. , / DB 3x 1x 2x PHP.

PHP5 . , , , , blob blob . PHP docs mysqli_stmt::send_long_data : INSERT - blob, .

, API , md5_file, exec system md5, INSERT, , !

+7

PDO , PDO:: PARAM_LOB. . №2 , , .

http://us2.php.net/manual/en/pdo.lobs.php

+3

?

ob_start();
echo 'INSERT INTO some_table SET BlobData = \'', mysql_real_escape_string( $data ), '\', BlobHash = \'', $hash, '\'';
mysql_query( ob_get_clean() );

, , - mysqli MDB2, . mysql_real_escape_string .

0

, , MySQL. , , .

0

, , CONCAT .

( ):

    $id = 1337;
$h = fopen("path/to/file.ext", "r");
while (!feof($h)) 
    {
    $buffer = fread($h, 4096);
    $sql = "UPDATE table SET my_field = CONCAT(my_field, '" . mysql_real_escape_string($buffer) . "') WHERE Id = " . $id;
    mysql_query($sql);
    }

This method will be slower, but you only need 4 KB of your memory.

-1
source

More articles:


All Articles