CodeIgniter 3 使用MYSQL INSERT ON DUPLICATE KEY UPDATE 的方法

在 MYSQL 有個 INSERT ON DUPLICATE KEY UPDATE 方法

如果要在 CodeIgniter 配合它DB 用法可以依照以下方式

建立 mysqli 擴充方法類別

application/libraries/database/drivers/mysqli/My_DB_mysqli_driver.php

<?php defined('BASEPATH') OR exit('No direct script access allowed'); class MY_DB_mysqli_driver extends CI_DB_mysqli_driver { final public function __construct($params) { parent::__construct($params); } /** * Insert_On_Duplicate_Key_Update_Batch * * Compiles batch insert strings and runs the queries * * @param string $table Table to insert into * @param array $set An associative array of insert values * @param bool $escape Whether to escape values and identifiers * @return int Number of rows inserted or FALSE on failure */ public function insert_on_duplicate_update_batch($table = '', $set = NULL, $escape = NULL) { if ($set !== NULL) { $this->set_insert_batch($set, '', $escape);
        }
        if (count($this->qb_set) === 0) {
            // No valid data array. Folds in cases where keys and values did not match up
            return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
        }
        if ($table === '') {
            if (!isset($this->qb_from[0])) {
                return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
            }
            $table = $this->qb_from[0];
        }
        // Batch this baby
        $affected_rows = 0;
        for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { $this->query($this->_insert_on_duplicate_key_update_batch($this->protect_identifiers($table, TRUE, $escape, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100)));
            $affected_rows += $this->affected_rows();
        }
        $this->_reset_write();
        return $affected_rows;
    }
    /**
     * Insert on duplicate key update batch statement
     *
     * Generates a platform-specific insert string from the supplied data
     *
     * @param   string  $table  Table name
     * @param   array   $keys   INSERT keys
     * @param   array   $values INSERT values
     * @return  string
     */
    private function _insert_on_duplicate_key_update_batch($table, $keys, $values)
    {
        foreach ($keys as $num => $key) {
            $update_fields[] = $key . '= VALUES(' . $key . ')';
        }
        return "INSERT INTO " . $table . " (" . implode(', ', $keys) . ") VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE " . implode(', ', $update_fields);
    }
}

擴展 loader

為了要載入剛的db 所以使用loader 讓他自動載入

建立 application/core/MY_Loader.php

<?php defined('BASEPATH') OR exit('No direct script access allowed'); class MY_Loader extends CI_Loader { /** * Database Loader * * @param mixed $params Database configuration options * @param bool $return Whether to return the database object * @param bool $query_builder Whether to enable Query Builder * (overrides the configuration setting) * * @return object|bool Database object if $return is set to TRUE, * FALSE on failure, CI_Loader instance in any other case */ public function database($params = '', $return = FALSE, $query_builder = NULL) { // Grab the super object $CI = & get_instance(); // Do we even need to load the database class? if ($return === FALSE && $query_builder === NULL && isset($CI->db) && is_object($CI->db) && !empty($CI->db->conn_id)) {
            return FALSE;
        }
        require_once(BASEPATH . 'database/DB.php');
        $DB = & DB($params, $query_builder);
        // ユーザードライバ読み込み
        $driver = config_item('subclass_prefix') . 'DB_' . $DB->dbdriver . '_driver';
        $driver_file = APPPATH . 'libraries/database/drivers/' . $DB->dbdriver . '/' . $driver . '.php';
        if (file_exists($driver_file)) {
            require_once($driver_file);
            $DB = new $driver(get_object_vars($DB));
            if (!empty($DB->subdriver)) {
                // ユーザーサブドライバ読み込み
                $driver = config_item('subclass_prefix') . 'DB_' . $DB->dbdriver . '_' . $DB->subdriver . '_driver';
                $driver_file = APPPATH . 'libraries/database/drivers/' . $DB->dbdriver . '/subdrivers/' . $driver . '.php';
                if (file_exists($driver_file)) {
                    require_once($driver_file);
                    $DB = new $driver(get_object_vars($DB));
                }
            }
            $DB->initialize();
        }
        if ($return === TRUE) {
            return $DB;
        }
        // Initialize the db variable. Needed to prevent
        // reference errors with some configurations
        $CI->db = '';
        // Load the DB class
        $CI->db = $DB;
        return $this;
    }
}

使用教學

$this->db->insert_on_duplicate_update_batch(‘tbl’, $data);

P.S 由於是 insert_on_duplicate_update_batch 所以要包含多筆資料的 $data 否則會出現錯誤

array_keys() expects parameter 1 to be array, integer given

 

Comments

comments