1

ON DUPLICATE KEY UPDATE causes auto increment value to increase. I've a function that on every user login checks and updates user profile from social network. Obviously this means that with few users, ids are already in 1000000+

It looks like this:

public function update_usermeta($user_id,$user_profile,$access_token)
    {
        $user_profile->accessToken = $access_token;

        $sql = 'INSERT INTO users_meta
                            (user_id,meta_key,meta_value)
                     VALUES (:user_id,:meta_key,:meta_value)
                         ON DUPLICATE KEY
                     UPDATE meta_value = VALUES(meta_value)';

        foreach ($user_profile as $meta_key => $meta_value) {
            if ($meta_value == null OR $meta_value == "") {continue;}
            if ($meta_key == "identifier" OR $meta_key == "photoURL" OR $meta_key == "displayName" OR $meta_key == "email") {continue;}
            $params = array(
                ':meta_key'   => $meta_key,
                ':meta_value' => $meta_value,
                ':user_id'    => $user_id
            );
            $this->mysql_execute_query($sql,$params);
        }
    }

I found several topics on SO discussing this behaviour:

prevent autoincrement on MYSQL duplicate insert

Why does MySQL autoincrement increase on failed inserts?

But these topics are from 2009, quite outdate and talking about mysql v 5.1.

My question is this, is there new technique to avoid auto_increment on update? Obviously in my situation this default behaviour is not feasible. Any thoughts?

Community
  • 1
  • 1
  • There is no such behavior exist. i just checked. TRY without `VALUES` in `UPDATE` Value – Muhammad Haseeb Khan Feb 23 '14 at 17:00
  • @MuhammadHaseebKhan it does. What is your mysql version? 4.1? –  Feb 23 '14 at 17:04
  • @MuhammadHaseebKhan I'm on 5.5, and this behavior exists. –  Feb 23 '14 at 17:09
  • Easiest solution is to check with a regular `SELECT` if the entry exists and update it with an `UPDATE` query. Otherwise perform an `INSERT` query. Just one more query usually shouldn't be too bad for your performance. – Thorsten Feb 23 '14 at 18:49

0 Answers0