PHP よく使う？ヘルパー関数（MYSQL）

簡単なsql文の生成と、バルクインサート、アップデートが出来ます。

こんなテーブルがあったとして…

CREATE TABLE `member` (
  `member_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `member_name` varchar(255) NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL COMMENT '0:無効, 1:有効',
  `created_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

こう使う

// addWhere
$sql = "SELECT * FROM member";
$binds = [];
\Helper\MysqlHelper::addWhere($sql, $binds, [
    "member_name = ?" => ["renton"]
    , "status IN (:in)" => [0, 1]
]);
echo $sql; // SELECT * FROM member WHERE member_name = ? AND status IN (?,?)
print_r($binds); // ["renton", 0, 1]


// getBulkInsert
list($sql, $binds) = \Helper\MysqlHelper::getBulkInsert("member", [
    ["member_name" => "nagasawa", "status" => 1]
    , ["member_name" => "fuziki", "status" => 1]
]);
echo $sql; // INSERT INTO member (member_name, status) VALUES (?, ?), (?, ?);
print_r($binds); // ["nagasawa", 1, 'fuziki', 1]


// getBulkUpdate
$sets = [
    ["member_name" => "yamane", "status" => 1]
    , ["member_name" => "yamada", "status" => 1]
];
$wheres = [
    ["member_id" => 1]
    , ["member_id" => 2]
];
list($sql, $binds) = \Helper\MysqlHelper::getBulkUpdate("member", $sets, $wheres);
echo $sql; // UPDATE member SET member_name = CASE WHEN member_id = ? THEN ? WHEN member_id = ? THEN ? END, status = CASE WHEN member_id = ? THEN ? WHEN member_id = ? THEN ? END WHERE product_id IN (?, ?);
print_r($binds); // [1, 'yamane', 2, 'yamada', 1, 0, 2, 0, 1, 2]

MysqlHelper

# MysqlHelper.php

<?php
namespace Helper;

class MysqlHelper
{
    /**
     * WHERE区を追加
     *
     * @param string $sql
     * @param array $binds
     * @param array $conditions
     * @return void
     */
    public static function addWhere(&$sql, &$binds, $conditions)
    {
        $where = [];
        foreach ($conditions as $key => $vals) {
            $is_add = false;
            foreach ($vals as $val) {
                if ($val !== null) {
                    $is_add = true;
                    break;
                }
            }
            if (!$is_add) {
                continue;
            }
            if (strpos($key, ":in") !== false) {
                $placeholder = rtrim(str_repeat("?,", count($vals)), ",");
                $where[] = str_replace(":in", $placeholder, $key);
            } else {
                $where[] = $key;
            }
            $binds = array_merge($binds, $vals);
        }
        $sql .= ($where)? " WHERE " . join(" AND ", $where) : "";
    }

    /**
     * バルクインサート文を取得
     *
     * @param string $table_name
     * @param array $insert_list
     * @return array
     */
    public static function getBulkInsert($table_name, array $insert_list)
    {
        $binds = [];
        $columns = array_keys(array_shift($new_insert_list = $insert_list));
        $holder = "(". rtrim(str_repeat("?,", count($columns)), ",") .")";
        $holders = [];
        foreach ($insert_list as $row) {
            $holders[] = $holder;
            $binds = array_merge($binds, array_values($row));
        }
        $sql = "INSERT INTO {$table_name} (" . implode(",", $columns) . ") VALUES " . implode(",", $holders);
        return [$sql, $binds];
    }

    /**
     * バルクアップデート文を取得
     *
     * @param string $table_name
     * @param array $sets
     * @param array $wheres $setsの数と必ず同じにすること
     * @return array
     */
    public static function getBulkUpdate($table_name, array $sets, array $wheres)
    {
        $binds = [];

        $set_items = [];
        foreach ($sets as $key => $column_val) {
            foreach ($column_val as $column => $val) {
                $set_items[$column][] = $val;
            }
        }

        $where_items = [];
        foreach ($wheres as $key => $column_val) {
            foreach ($column_val as $column => $val) {
                $where_items[$column][] = $val;
            }
        }

        $set_holders = [];
        foreach ($set_items as $set_column => $set_vals) {
            $case_holders = [];
            foreach ($set_vals as $key => $set_val) {
                $where = $wheres[$key];

                $when_holders = [];
                foreach ($where as $column => $val) {
                    $when_holders[] = "{$column} = ?";
                    $binds[] = $val;
                }
                $case_holders[] = "WHEN ". implode(" AND ", $when_holders) . " THEN ?";
                $binds[] = $set_val;
            }
            $set_holders[] =  "{$set_column} = CASE ". implode(" ", $case_holders) . " END";
        }

        $where_holders = [];
        foreach ($where_items as $column => $vals) {
            $where_holders[] =  "{$column} IN (" . rtrim(str_repeat("?,", count($vals)), ",") . ")";
            foreach ($vals as $val) {
                $binds[] = $val;
            }
        }

        $sql = "UPDATE {$table_name} SET " . implode(",", $set_holders) . " WHERE " . implode(" AND ", $where_holders);
        return [$sql, $binds];
    }
}