くわこのpermission denied.

WEBエンジニアの僕がぶつかった技術的な問題や発見

【MySQL】値があればUPDATE文、無ければINSERT文に分岐させる処理【DUPLICATE KEY】

WEBサービスを作っていると、「これに関する行があるなら上書き、無いなら新規登録したい!」という時がありますが、MySQL歴の短い僕はその方法が分からず、毎回

特定の行が存在するかチェック➡あればUPDATE、無ければINSERT

という面倒な処理を書いていましたが、どうやら(もちろん)そんなことはする必要がなかったみたいです笑

基本的に僕みたいな初心者が欲しいと思ったようなことは、探せば便利な関数が既にあるか、サンプルコードが落ちてますね。Googleさんありがとう。

注意しなければならないのは、指定するカラムの中にPrimaryキーまたはUniqueキーがなければいけません。

mysql> insert into table_name (id, name ) values ('1000', 'MasK') on duplicate key update id='1000', name='hoge';

これで指定したテーブルの中に同じ行が無かった場合は先に指定した('1000', 'MasK')がINSERT、既に有った場合は後から指定したid='1000', name='hoge'にUPDATEされます。

参考サイト

oracle - レコードがなければINSERT,あればUPDATEをするSQL - Qiita [キータ]

ユニーク制約などの違反時に指定の値で上書きする(INSERT ... ON DUPLICATE KEY UPDATE文) - データの追加と削除 - MySQLの使い方

 

ただ、この方法が分かったところでCodeIgniter上でのやり方が分からないと僕的にはあまり意味が有りません。(まあMySQL文をそのまま書く方法も有るけれど。)

ということで、これを使える方法を模索...

いろいろ検索したのですが、CodeIgniterというフレームワーク自体が日本では下火あまりシェアが無いので、日本語の文献が少ないです。

で、結局英語のサイトからそれっぽいの見つけました。

MySQL Active Record - on duplicate key insert | CodeIgniter Forums

以下英語読むのダルい人向き。

/system/database/drivers/mysql/mysql_driver.php に

function _duplicate_insert($table, $values)
{
    $updatestr = array();
    $keystr    = array();
    $valstr    = array();
    
    foreach($values as $key => $val)
    {
        $updatestr = $key." = ".$val;
        $keystr
    = $key;
        $valstr[]    = $val;
    }
    
    $sql  = "INSERT INTO ".$this->_escape_table($table)." (".implode(', ',$keystr).") ";
    $sql .= "VALUES (".implode(', ',$valstr).") ";
    $sql .= "ON DUPLICATE KEY UPDATE ".implode(', ',$updatestr);
    
    return $sql;

を貼付けて、

system/database/DB_active_rec.php に

function on_duplicate($table = '', $set = NULL )
{
    if ( ! is_null($set))
    {
        $this->set($set);
    }

    if (count($this->ar_set) == 0)
    {
            if ($this->db_debug)
        {
                return $this->display_error('db_must_use_set');
        }
            return FALSE;
    }

    if ($table == '')
    {
        if ( ! isset($this->ar_from[0]))
        {
            if ($this->db_debug)
            {
                return $this->display_error('db_must_set_table');
            }
            return FALSE;
        }
        
        $table = $this->ar_from[0];
    }
    
        
    $sql = $this->_duplicate_insert($this->_protect_identifiers($this->dbprefix.$table), $this->ar_set );
        
    $this->_reset_write();
    return $this->query($sql);
}

 で、実際に使いたいところで

$data = array(
     'id'    => 10,
     'title' => $title,
     'name'  => $name,
     'date'  => $date
     );

$this->db->on_duplicate('mytable', $data);

みたいな感じに$this->db->on_duplicate();にテーブル名と配列を渡せばいいみたいです。

明日実際にやってみよう。