Array multi insert uname and email check
I have an array I want to check whether the username and Email values exists in database, if it does not exist inser开发者_如何学Pythont the username and email values in the database. Using Multi insert since there are around 80000 inserts to be preformed.
$arr1 = Array
(
[0] => Array ( [Username] => uname1, [Email] => email1 )
[1] => Array ( [Username] => uname2, [Email] => email2 )
[2] => Array ( [Username] => uname3, [Email] => email3 )
)
A SQL statement with 80000 INSERT
s will function slower than 80000 separate INSERT
s, because the monolithic, multi-INSERT
SQL statement-string won't need to be built. For overwriting existing rows, you will need a primary key to ensure that duplicate records are overwritten: http://dev.mysql.com/doc/refman/5.1/en/replace.html.
foreach ($arr1 as $user){
$sql = "REPLACE INTO `users` SET `Username`='" . mysql_escape_string($user["Username"]) . "', `Email`='" . mysql_escape_string($user["Email"]) . "'";
mysql_query($sql);
}
My code assumes that Username
is the primary key. If not, add to the SET
clause to include the primary key. If Username
is your primary key, consider using an integer-based one as primary keys based on strings are slow.
The simplest way would be to create a unique index on username+email. Then you can just do an INSERT IGNORE. Although you probably do not want to bulk insert 80000 records using PHP unless you also adjust the timeout parameters. It would be better to break that up into bulk inserts of say 500 at a time. Then it will scale well.
Using INSERT IGNORE with the unique index will just insert records that don't already have a match in the index. Do NOT do single insert and/or lookups. While that will scale, it will be slow. Chunk your data, it will scale and perform well.
精彩评论