看代碼:
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;
}
這段代碼從邏輯上講，無懈可擊--數據庫中沒有數據的話，插入, 然后獲取自增的id。如果這個值為0, 那么表示數據已經存在數據庫中了，從而執行一個Query操作,取得需要的id。如果從SQL的角度來看，可能執行的兩條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"
看上去很完美，不是嗎?這里還有一個例外(可能有多個,但這里指出一個), 這個例外與unique index有關。
如果數據庫中已經存在了這樣一條記錄: 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)), 那么這個例外就出現了。
第一條SQL語句執行不會成功, 因為如果插入成功,那么unique index的約束將被破壞--要插入的記錄和已有記錄在unique index語義下是相同的。有意思的是, 第二條SQL同樣找不到數據，因為數據庫中沒有一條記錄它滿足條件(address=....條件得不到滿足)。