SQL 拼接实现批量插入

运行环境 PHP

主体代码

public function batchInsert($table, $data, $keyName)
    {
        $keyStr = "";
        $paramStrOut = "";
        $params = [];
        $index = 0;
        foreach ($data as $item)
        {
            $paramStr = "";
            $paramStrOut .= "(";
            foreach ($item as $key => $value)
            {
                if (is_array($value) || $key == $keyName)
                {
                    continue;
                }
                if (!empty($keyStr) && !empty($paramStr))
                {
                    if ($index == 0)
                    {
                        $keyStr .= ", ";
                    }
                    $paramStr .= ", ";
                }
                if ($value === null)
                {
                    if ($index == 0)
                    {
                        $keyStr .= "`{$key}`";
                    }
                    $paramStr .= "null";
                }
                else if (strtoupper($value) == "NOW()")
                {
                    if ($index == 0)
                    {
                        $keyStr .= "`{$key}`";
                    }
                    $paramStr .= "NOW()";
                }
                else if(strpos(strtoupper($value), "DATE_FORMAT") !== false)
                {
                    if ($index == 0)
                    {
                        $keyStr .= "`{$key}`";
                    }
                    $paramStr .= "{$value}";
                }
                else if(strpos(strtoupper($value), "DATE_ADD") !== false)
                {
                    if ($index == 0)
                    {
                        $keyStr .= "`{$key}`";
                    }
                    $paramStr .= "{$value}";
                }
                else if(strtoupper($value) == "UNIX_TIMESTAMP()")
                {
                    if ($index == 0)
                    {
                        $keyStr .= "`{$key}`";
                    }
                    $paramStr .= "UNIX_TIMESTAMP()";
                }
                else
                {
                    if ($index == 0)
                    {
                        $keyStr .= "`{$key}`";
                    }
                    $paramStr .= "?";
                    $params[] = $this->format($value);
                }
            }
            $paramStrOut .= $paramStr."),";
            $index++;
        }
        $paramStrOut = substr($paramStrOut, 0, -1);
        $sql = "INSERT INTO {$table} ({$keyStr}) VALUES {$paramStrOut}";

        return Database::$Db->array_params_query($sql, $params);
    }

调用

return $this->batchInsert("数据库名", "批量数据（数组）", "主键");