看代碼:
public static function saveTheater($theater, $country, $dbConn) {
$insertSql = sprintf('INSERT IGNORE INTO theaters(name, address, country, latitude, longitude, tel) VALUES (%s, %s, %s, %f, %f, %s)',
DBUtils::escape($theater->name, $dbConn),
DBUtils::escape($theater->address, $dbConn),
DBUtils::escape($country, $dbConn),
$theater->latitude, $theater->longitude,
DBUtils::escape($theater->tel, $dbConn));
DBUtils::execute($insertSql, $dbConn);
$theaterId = mysql_insert_id($dbConn);
if ($theaterId == 0) { //conflict, others saved it. we need get the theater by querying
$query = sprintf('SELECT id FROM theaters FORCE INDEX(theaters_I1) WHERE name=%s AND address=%s AND country=%s',
DBUtils::escape($theater->name, $dbConn),
DBUtils::escape($theater->address, $dbConn),
DBUtils::escape($country, $dbConn));
$resultSet = DBUtils::execute($query, $dbConn);
if ($row = mysql_fetch_assoc($resultSet)) {
$theaterId = $row['id'];
}
}
return $theaterId;
}
這段代碼從邏輯上講，無懈可擊--數(shù)據(jù)庫中沒有數(shù)據(jù)的話，插入, 然后獲取自增的id。如果這個(gè)值為0, 那么表示數(shù)據(jù)已經(jīng)存在數(shù)據(jù)庫中了，從而執(zhí)行一個(gè)Query操作,取得需要的id。如果從SQL的角度來看，可能執(zhí)行的兩條SQL是:
INSERT IGNORE INTO theaters(name, address, country, latitude, longitude, tel) VALUES ("Vue Cinemas - Cheshire Oaks", "Ellesmere Port, Coliseum Way, Chesire Oaks Outlet Village, South Wirral CH65 9HD, United Kingdom", "GB", 53.306521, -2.914062, "0871 224 0240")
SELECT id FROM theaters FORCE INDEX(theaters_I1) WHERE name="Vue Cinemas - Cheshire Oaks" AND address="Ellesmere Port, Coliseum Way, Chesire Oaks Outlet Village, South Wirral CH65 9HD, United Kingdom" AND country="GB"
看上去很完美，不是嗎?這里還有一個(gè)例外(可能有多個(gè),但這里指出一個(gè)), 這個(gè)例外與unique index有關(guān)。
如果數(shù)據(jù)庫中已經(jīng)存在了這樣一條記錄: country=GB, name=Vue Cinemas - Cheshire Oaks, address=Ellesmere Port, Coliseum Way, Chesire Oaks Outlet Village, South Wirral CH65 9HD, United Kingdom12321312321, 表(theaters)的unique index是(country, name, address(64)), 那么這個(gè)例外就出現(xiàn)了。
第一條SQL語句執(zhí)行不會(huì)成功, 因?yàn)槿绻迦氤晒?那么unique index的約束將被破壞--要插入的記錄和已有記錄在unique index語義下是相同的。有意思的是, 第二條SQL同樣找不到數(shù)據(jù)，因?yàn)閿?shù)據(jù)庫中沒有一條記錄它滿足條件(address=....條件得不到滿足)。