Wednesday, April 22, 2009

INSERT UPDATE larget many file data rows for InnoDB

INSERT UPDATE larget many file data rows for InnoDB
<?php
echo date('Y-m-d h:i:s') . '\n';

include('./common.php');

ini_set('max_execution_time', 0);

$conn = new mysqli($db_server, $db_user, $db_pass);

/* check connection */
if ($conn->connect_errno) {
printf("Connect failed: %s\n", $conn->connect_error);
exit();
}

/* change db to your db */
$conn->select_db($db_name['danny_testing']);

mb_internal_encoding("UTF-8");
$conn->query("SET NAMES 'utf8'");
$conn->query("SET CHARACTER SET 'utf8'");

// ### SET AUTOCOMMIT to 0 (for speeding up the process)
$conn->query("SET autocommit=0;");

$sql = "SELECT id, name FROM member";
$rs = $conn->query($sql);
while ($row = $rs->fetch_array()) {
$conn->query("UPDATE member SET name = '" . $row['name'] . "_new' WHERE id = " . $row['id']);

// ### COMMIT (save) the data every 10000 rows
if ($row % 10000 == 0) {
$conn->query('COMMIT;');
}
}

// ### COMMIT (save) the rest of rows.
$conn->query('COMMIT;');

fclose($handle);

echo 'Done!';

$conn->close();

echo date('Y-m-d h:i:s') . '\n';
?>

No comments: