看代码:
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=....条件得不到满足)。