2019-04-02 14:47
浏览 83


Very often i need to prevent querys to get executed when a value like a email already exist.

Until now i searched for the value like that:

$checkemailexist = $X['db']->prepare("SELECT uid FROM userdata WHERE uid = :uid LIMIT 1");



The problem on a big database with many rows, a string search even on a varchar can take a lot of performance and time.

So i made the uid column unique and tried something like that:

    $insertuser = $X['dbh']->prepare("
    INSERT INTO user (uid) VALUES (:uid) 

    ':uid'=> $mail

} catch (PDOException $e) {
        header("Location: ...");

Its working fine, but could the performance even be worse ?

图片转代码服务由CSDN问答提供 功能建议

我经常需要阻止查询在电子邮件等值存在时执行。 \ n


  $ checkemailexist = $ X ['db']  - > prepare(“SELECT uid FROM userdata  WHERE uid =:uid LIMIT 1“); 
 $ checkemailexist-> execute(array(
':uid'=> $ uid 
if(空($ checkemailexist)){\  n INSERT QUERY .. 


大型数据库中存在多行的问题, 即使在varchar上进行字符串搜索也会占用大量的性能和时间。


   try {
 $ insertuser = $ X ['dbh']  - > prepare(“
 INSERT INTO user(uid)VALUES(:uid)
 $ insertuser->  execute(array(
':uid'=> $ mail 
} catch(PDOException $ e){
 exit()  ;  


  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongzhuo1498 2019-04-02 14:56

    After making uid column an [unique] index, you made all your queries faster. Both queries, either SELECT or INSERT will have to check the index, and it will take them both the same time to perform.

    Adding an index to the column used for search for is the real answer to your question. As to whether to use a select query or to catch an exception during insert is a matter of taste.

    However, your second example is rather wrong. You shouldn't handle every PDOException the same way but only a specific exception related to this very case, as it's shown in my PDO tutorial.

    The best way would be to keep the unique index but add a keyword IGNORE to the query and then check the number of affected rows

    $insertuser = $X['dbh']->prepare("INSERT IGNORE INTO user (uid) VALUES (:uid)");
    $insertuser->execute(['uid'=> $mail]));
    if (!$insertuser->numRows()) {
        header("Location: ...");

    adding IGNORE would suppress the unique index error, and you will be able to check whether such a value already exists by simply checking the number of affected rows

    解决 无用
    打赏 举报

相关推荐 更多相似问题