반응형
PDO 준비 단일 쿼리에 여러 행을 삽입합니다
현재 MySQL 에서이 유형의 SQL을 사용하여 단일 쿼리에 여러 행의 값을 삽입하고 있습니다.
INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...
PDO에 대한 독서에서 사용 준비된 문은 정적 쿼리보다 더 나은 보안을 제공해야합니다.
따라서 준비된 문을 사용하여 "하나의 쿼리를 사용하여 여러 행의 값 삽입"을 생성 할 수 있는지 알고 싶습니다.
그렇다면 어떻게 구현할 수 있습니까?
PDO 준비된 명령문으로 다중 값 삽입
하나의 execute 문에 여러 값을 삽입합니다. 이 페이지 에 따르면 일반 인서트보다 빠르기 때문입니다.
$datafields = array('fielda', 'fieldb', ... );
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
더 많은 데이터 값이 있거나 데이터를 채우는 루프가있을 수 있습니다.
준비된 삽입물을 사용하면 삽입 할 필드와?를 생성 할 필드 수를 알아야합니다. 자리 표시자가 매개 변수를 바인딩합니다.
insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....
이것이 기본적으로 insert 문의 모습입니다.
이제 코드 :
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}
$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
implode(',', $question_marks);
$stmt = $pdo->prepare ($sql);
try {
$stmt->execute($insert_values);
} catch (PDOException $e){
echo $e->getMessage();
}
$pdo->commit();
테스트에서는 여러 개의 인서트와 단일 값으로 정기적으로 준비된 인서트를 사용할 때 1 초의 차이 만있었습니다.
발라 타스 씨와 같은 대답, 약간 더 명확합니다 ...
최신 버전의 MySQL 및 PHP PDO 는 다중 행 INSERT문을 지원 합니다.
SQL 개요
3 열 테이블을 가정하면 SQL은 다음과 같이 보입니다 INSERT.
INSERT INTO tbl_name
(colA, colB, colC)
VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) [,...]
ON DUPLICATE KEY UPDATE다중 행 삽입으로도 예상대로 작동합니다. 이것을 추가하십시오 :
ON DUPLICATE KEY UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC)
PHP 개요
귀하의 PHP 코드는 보통 따를 $pdo->prepare($qry)와 $stmt->execute($params)PDO 전화를.
$params에 전달할 모든 값 의 1 차원 배열입니다 INSERT.
위 예제에서 9 개의 요소를 포함해야합니다. PDO는 3의 모든 세트를 단일 값 행으로 사용합니다. (각 3 열의 3 행 삽입 = 9 요소 배열)
이행
아래 코드는 효율성이 아니라 명확성을 위해 작성되었습니다. array_*()원하는 경우 데이터를 더 잘 매핑하거나 살펴 보는 더 나은 방법을 위해 PHP 함수를 사용하십시오. 트랜잭션을 사용할 수 있는지 여부는 MySQL 테이블 유형에 따라 다릅니다.
가정 :
$tblName-INSERT 할 테이블의 문자열 이름$colNames-테이블의 열 이름의 1 차원 배열이 열 이름은 유효한 MySQL 열 식별자 여야합니다. 그렇지 않으면 백틱 (``)으로 이스케이프 처리$dataVals-각 요소가 INSERT 할 값 행의 1 차원 배열 인 다중 차원 배열
샘플 코드
// setup data values for PDO
// memory warning: this is creating a copy all of $dataVals
$dataToInsert = array();
foreach ($dataVals as $row => $data) {
foreach($data as $val) {
$dataToInsert[] = $val;
}
}
// (optional) setup the ON DUPLICATE column names
$updateCols = array();
foreach ($colNames as $curCol) {
$updateCols[] = $curCol . " = VALUES($curCol)";
}
$onDup = implode(', ', $updateCols);
// setup the placeholders - a fancy way to make the long "(?, ?, ?)..." string
$rowPlaces = '(' . implode(', ', array_fill(0, count($colNames), '?')) . ')';
$allPlaces = implode(', ', array_fill(0, count($dataVals), $rowPlaces));
$sql = "INSERT INTO $tblName (" . implode(', ', $colNames) .
") VALUES " . $allPlaces . " ON DUPLICATE KEY UPDATE $onDup";
// and then the PHP PDO boilerplate
$stmt = $pdo->prepare ($sql);
try {
$stmt->execute($dataToInsert);
} catch (PDOException $e){
echo $e->getMessage();
}
$pdo->commit();
가치있는 것에 대해, 많은 사용자가 선택된 답변이했던 것처럼 단일 문자열 쿼리로 작성하는 대신 INSERT 문을 반복하는 것이 좋습니다. 두 개의 필드와 매우 기본적인 insert 문으로 간단한 테스트를 실행하기로 결정했습니다.
<?php
require('conn.php');
$fname = 'J';
$lname = 'M';
$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');
for($i = 1; $i <= 10; $i++ ) {
$stmt->bindParam(':fname', $fname);
$stmt->bindParam(':lname', $lname);
$stmt->execute();
$fname .= 'O';
$lname .= 'A';
}
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Completed in ". $time ." seconds <hr>";
$fname2 = 'J';
$lname2 = 'M';
$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";
$stmt2 = $db->prepare($qry);
$values = array();
for($j = 1; $j<=10; $j++) {
$values2 = array($fname2, $lname2);
$values = array_merge($values,$values2);
$fname2 .= 'O';
$lname2 .= 'A';
}
$stmt2->execute($values);
$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;
echo "Completed in ". $time2 ." seconds <hr>";
?>
전체 쿼리 자체가 밀리 초 이하인 반면 후자 (단일 문자열) 쿼리는 일관되게 8 배 이상 빠릅니다. 이것이 더 많은 열에서 수천 행의 가져 오기를 반영하도록 구축 된 경우 그 차이는 엄청날 수 있습니다.
Herbert Balagtas의 대답은 $ data 배열이 작을 때 잘 작동합니다. 더 큰 $ data 배열에서는 array_merge 함수가 엄청나게 느려집니다. $ data 배열을 만드는 테스트 파일에는 28 열이 있으며 약 80,000 줄입니다. 마지막 스크립트는 41 초가 걸렸 습니다.
사용 array_push 것은 () 대신 array_merge의 $ INSERT_VALUES를 만드는 ()는 결과 100 배 속도까지 의 실행 시간 0.41s .
문제가있는 array_merge () :
$insert_values = array();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}
array_merge ()의 필요성을 없애기 위해 대신 다음 두 배열을 만들 수 있습니다.
//Note that these fields are empty, but the field count should match the fields in $datafields.
$data[] = array('','','','',... n );
//getting rid of array_merge()
array_push($insert_values, $value1, $value2, $value3 ... n );
이러한 배열은 다음과 같이 사용할 수 있습니다.
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
$pdo->beginTransaction();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
}
$sql = "INSERT INTO table (" . implode(",", array_keys($datafield) ) . ") VALUES " . implode(',', $question_marks);
$stmt = $pdo->prepare ($sql);
try {
$stmt->execute($insert_values);
} catch (PDOException $e){
echo $e->getMessage();
}
$pdo->commit();
두 가지 가능한 접근 방식 :
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:v1_1, :v1_2, :v1_3),
(:v2_1, :v2_2, :v2_3),
(:v2_1, :v2_2, :v2_3)');
$stmt->bindValue(':v1_1', $data[0][0]);
$stmt->bindValue(':v1_2', $data[0][1]);
$stmt->bindValue(':v1_3', $data[0][2]);
// etc...
$stmt->execute();
또는:
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
$stmt->bindValue(':a', $item[0]);
$stmt->bindValue(':b', $item[1]);
$stmt->bindValue(':c', $item[2]);
$stmt->execute();
}
모든 행의 데이터가 단일 배열에 있으면 두 번째 솔루션을 사용합니다.
준비된 문장을 사용하는 방식이 아닙니다.
하나의 준비된 명령문을 다른 매개 변수로 여러 번 실행할 수 있으므로 쿼리 당 하나의 행을 삽입하는 것이 좋습니다. 실제로 효율적이고 안전하며 편안한 방식으로 많은 수의 행을 삽입 할 수 있으므로 가장 큰 장점 중 하나입니다.
따라서 적어도 고정 된 수의 행에 대해 제안하는 구성표를 구현하는 것이 가능할 수도 있지만 이것이 실제로 원하는 것이 아니라는 것이 거의 보장됩니다.
더 짧은 대답 : 열 순서로 정렬 된 데이터 배열을 평평하게하십시오.
//$array = array( '1','2','3','4','5', '1','2','3','4','5');
$arCount = count($array);
$rCount = ($arCount ? $arCount - 1 : 0);
$criteria = sprintf("(?,?,?,?,?)%s", str_repeat(",(?,?,?,?,?)", $rCount));
$sql = "INSERT INTO table(c1,c2,c3,c4,c5) VALUES$criteria";
1,000 개 정도의 레코드를 삽입 할 때 필요한 모든 값이 필요할 때 레코드를 삽입하기 위해 모든 레코드를 반복 할 필요는 없습니다.
여기 내 간단한 접근법이 있습니다.
$values = array();
foreach($workouts_id as $value){
$_value = "(".$value.",".$plan_id.")";
array_push($values,$_value);
}
$values_ = implode(",",$values);
$sql = "INSERT INTO plan_days(id,name) VALUES" . $values_."";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
다음은 퍼지 옵션으로 여러 개의 삽입 작업을 수행 한 클래스입니다.
<?php
/**
* $pdo->beginTransaction();
* $pmi = new PDOMultiLineInserter($pdo, "foo", array("a","b","c","e"), 10);
* $pmi->insertRow($data);
* ....
* $pmi->insertRow($data);
* $pmi->purgeRemainingInserts();
* $pdo->commit();
*
*/
class PDOMultiLineInserter {
private $_purgeAtCount;
private $_bigInsertQuery, $_singleInsertQuery;
private $_currentlyInsertingRows = array();
private $_currentlyInsertingCount = 0;
private $_numberOfFields;
private $_error;
private $_insertCount = 0;
function __construct(\PDO $pdo, $tableName, $fieldsAsArray, $bigInsertCount = 100) {
$this->_numberOfFields = count($fieldsAsArray);
$insertIntoPortion = "INSERT INTO `$tableName` (`".implode("`,`", $fieldsAsArray)."`) VALUES";
$questionMarks = " (?".str_repeat(",?", $this->_numberOfFields - 1).")";
$this->_purgeAtCount = $bigInsertCount;
$this->_bigInsertQuery = $pdo->prepare($insertIntoPortion.$questionMarks.str_repeat(", ".$questionMarks, $bigInsertCount - 1));
$this->_singleInsertQuery = $pdo->prepare($insertIntoPortion.$questionMarks);
}
function insertRow($rowData) {
// @todo Compare speed
// $this->_currentlyInsertingRows = array_merge($this->_currentlyInsertingRows, $rowData);
foreach($rowData as $v) array_push($this->_currentlyInsertingRows, $v);
//
if (++$this->_currentlyInsertingCount == $this->_purgeAtCount) {
if ($this->_bigInsertQuery->execute($this->_currentlyInsertingRows) === FALSE) {
$this->_error = "Failed to perform a multi-insert (after {$this->_insertCount} inserts), the following errors occurred:".implode('<br/>', $this->_bigInsertQuery->errorInfo());
return false;
}
$this->_insertCount++;
$this->_currentlyInsertingCount = 0;
$this->_currentlyInsertingRows = array();
}
return true;
}
function purgeRemainingInserts() {
while ($this->_currentlyInsertingCount > 0) {
$singleInsertData = array();
// @todo Compare speed - http://www.evardsson.com/blog/2010/02/05/comparing-php-array_shift-to-array_pop/
// for ($i = 0; $i < $this->_numberOfFields; $i++) $singleInsertData[] = array_pop($this->_currentlyInsertingRows); array_reverse($singleInsertData);
for ($i = 0; $i < $this->_numberOfFields; $i++) array_unshift($singleInsertData, array_pop($this->_currentlyInsertingRows));
if ($this->_singleInsertQuery->execute($singleInsertData) === FALSE) {
$this->_error = "Failed to perform a small-insert (whilst purging the remaining rows; the following errors occurred:".implode('<br/>', $this->_singleInsertQuery->errorInfo());
return false;
}
$this->_currentlyInsertingCount--;
}
}
public function getError() {
return $this->_error;
}
}
이것이 내가 한 방법입니다.
먼저 사용할 열 이름을 정의하거나 비워 두십시오. pdo는 테이블의 모든 열을 사용한다고 가정합니다.이 경우 테이블에 나타나는 순서대로 행 값을 알려야합니다. .
$cols = 'name', 'middleName', 'eMail';
$table = 'people';
이제 2 차원 배열이 이미 준비되었다고 가정합니다. 이를 반복하고 다음과 같이 행 값으로 문자열을 구성하십시오.
foreach ( $people as $person ) {
if(! $rowVals ) {
$rows = '(' . "'$name'" . ',' . "'$middleName'" . ',' . "'$eMail'" . ')';
} else { $rowVals = '(' . "'$name'" . ',' . "'$middleName'" . ',' . "'$eMail'" . ')';
}
이제 방금 행한 것은 $ rows가 이미 정의되어 있는지 확인하고 그렇지 않은 경우이를 작성하고 행 값과 필요한 SQL 구문을 저장하여 유효한 명령문이되도록하는 것입니다. 문자열은 큰 따옴표와 작은 따옴표 안에 들어가야하므로 즉시 인식됩니다.
이제 남은 것은 명령문을 준비하고 실행하는 것입니다.
$stmt = $db->prepare ( "INSERT INTO $table $cols VALUES $rowVals" );
$stmt->execute ();
지금까지 최대 2000 행으로 테스트되었으며 실행 시간이 늦었습니다. 더 많은 테스트를 수행하고 더 기여할 것이 있으면 여기로 돌아올 것입니다.
문안 인사.
아직 제안되지 않았기 때문에 LOAD DATA INFILE이 여전히 인덱싱을 비활성화하고 모든 데이터를 삽입 한 다음 인덱스를 다시 활성화하여 데이터를로드하는 가장 빠른 방법이라고 확신합니다. 모두 단일 요청으로.
데이터를 csv로 저장하는 것은 fputcsv를 명심해야합니다. MyISAM이 가장 빠르지 만 여전히 InnoDB에서 큰 성능을 얻습니다. 다른 단점도 있지만 100 행 이하로 많은 데이터를 삽입하는 경우이 경로를 사용합니다.
오래된 질문에 대한 모든 기여는 저에게 큰 도움이되었지만 여기 내 DbContext수업 에서 작동하는 솔루션이 있습니다 . 이 $rows매개 변수는 단순히 행 또는 모델을 나타내는 연관 배열의 배열입니다 field name => insert value.
모델을 사용하는 패턴을 사용하는 경우 모델 데이터를 배열로 전달할 때 적합 ToRowArray합니다 (예 : 모델 클래스 내의 메소드).
참고 :이 방법으로 전달 된 인수가 사용자에게 노출되거나 검증되고 위생 처리 된 삽입 값 이외의 사용자 입력에 의존하지 않도록해야합니다.
$tableName인수 및 열 이름이 호출 로직에 의해 정의되어야한다; 예를 들어User모델을 사용자 테이블에 매핑 할 수 있는데,이 테이블에는 열 목록이 모델의 멤버 필드에 매핑되어 있습니다.
public function InsertRange($tableName, $rows)
{
// Get column list
$columnList = array_keys($rows[0]);
$numColumns = count($columnList);
$columnListString = implode(",", $columnList);
// Generate pdo param placeholders
$placeHolders = array();
foreach($rows as $row)
{
$temp = array();
for($i = 0; $i < count($row); $i++)
$temp[] = "?";
$placeHolders[] = "(" . implode(",", $temp) . ")";
}
$placeHolders = implode(",", $placeHolders);
// Construct the query
$sql = "insert into $tableName ($columnListString) values $placeHolders";
$stmt = $this->pdo->prepare($sql);
$j = 1;
foreach($rows as $row)
{
for($i = 0; $i < $numColumns; $i++)
{
$stmt->bindParam($j, $row[$columnList[$i]]);
$j++;
}
}
$stmt->execute();
}
이 함수를 사용하여 단일 쿼리에 여러 행을 삽입 할 수 있습니다.
function insertMultiple($query,$rows) {
if (count($rows)>0) {
$args = array_fill(0, count($rows[0]), '?');
$params = array();
foreach($rows as $row)
{
$values[] = "(".implode(',', $args).")";
foreach($row as $value)
{
$params[] = $value;
}
}
$query = $query." VALUES ".implode(',', $values);
$stmt = $PDO->prepare($query);
$stmt->execute($params);
}
}
$ row 는 값으로 구성된 배열 입니다. 귀하의 경우에는
insertMultiple("INSERT INTO tbl (`key1`,`key2`)",array(array('r1v1','r1v2'),array('r2v1','r2v2')));
이는 단일 쿼리로 여러 행을 삽입하는 동안 준비된 명령문 을 사용하는 이점이 있습니다 . 보안!
이것은 나를 위해 일했다
$sql = 'INSERT INTO table(pk_pk1,pk_pk2,date,pk_3) VALUES ';
$qPart = array_fill(0, count($array), "(?, ?,UTC_TIMESTAMP(),?)");
$sql .= implode(",", $qPart);
$stmt = DB::prepare('base', $sql);
$i = 1;
foreach ($array as $value)
{
$stmt->bindValue($i++, $value);
$stmt->bindValue($i++, $pk_pk1);
$stmt->bindValue($i++, $pk_pk2);
$stmt->bindValue($i++, $pk_pk3);
}
$stmt->execute();
여기 내 해결책이 있습니다 : auraphp / Aura.Sql 라이브러리를 기반으로 한 https://github.com/sasha-ch/Aura.Sql .
사용 예 :
$q = "insert into t2(id,name) values (?,?), ... on duplicate key update name=name";
$bind_values = [ [[1,'str1'],[2,'str2']] ];
$pdo->perform($q, $bind_values);
버그 리포트를 환영합니다.
모든 독일 우편 번호를 빈 테이블에 삽입하는 실제 사례 (나중에 도시 이름을 추가하기 위해) :
// obtain column template
$stmt = $db->prepare('SHOW COLUMNS FROM towns');
$stmt->execute();
$columns = array_fill_keys(array_values($stmt->fetchAll(PDO::FETCH_COLUMN)), null);
// multiple INSERT
$postcode = '01000';// smallest german postcode
while ($postcode <= 99999) {// highest german postcode
$values = array();
while ($postcode <= 99999) {
// reset row
$row = $columns;
// now fill our row with data
$row['postcode'] = sprintf('%05d', $postcode);
// build INSERT array
foreach ($row as $value) {
$values[] = $value;
}
$postcode++;
// avoid memory kill
if (!($postcode % 10000)) {
break;
}
}
// build query
$count_columns = count($columns);
$placeholder = ',(' . substr(str_repeat(',?', $count_columns), 1) . ')';//,(?,?,?)
$placeholder_group = substr(str_repeat($placeholder, count($values) / $count_columns), 1);//(?,?,?),(?,?,?)...
$into_columns = implode(',', array_keys($columns));//col1,col2,col3
// this part is optional:
$on_duplicate = array();
foreach ($columns as $column => $row) {
$on_duplicate[] = $column;
$on_duplicate[] = $column;
}
$on_duplicate = ' ON DUPLICATE KEY UPDATE' . vsprintf(substr(str_repeat(', %s = VALUES(%s)', $count_columns), 1), $on_duplicate);
// execute query
$stmt = $db->prepare('INSERT INTO towns (' . $into_columns . ') VALUES' . $placeholder_group . $on_duplicate);//INSERT INTO towns (col1,col2,col3) VALUES(?,?,?),(?,?,?)... {ON DUPLICATE...}
$stmt->execute($values);
}
보시다시피 완전히 유연합니다. 열의 양을 확인하거나 열의 위치를 확인할 필요가 없습니다. 삽입 데이터 만 설정하면됩니다.
$row['postcode'] = sprintf('%05d', $postcode);
array_merge와 같은 무거운 배열 함수없이 작동하는 쿼리 문자열 생성자 중 일부를 자랑스럽게 생각합니다. 특히 vsprintf ()는 좋은 발견이었습니다.
마지막으로 메모리 제한을 초과하지 않도록 2x while ()을 추가해야했습니다. 이것은 메모리 제한에 달려 있지만 문제를 피하기위한 좋은 일반적인 솔루션입니다 (10 개의 쿼리를 갖는 것이 여전히 10.000보다 훨씬 낫습니다).
test.php
<?php
require_once('Database.php');
$obj = new Database();
$table = "test";
$rows = array(
array(
'name' => 'balasubramani',
'status' => 1
),
array(
'name' => 'balakumar',
'status' => 1
),
array(
'name' => 'mani',
'status' => 1
)
);
var_dump($obj->insertMultiple($table,$rows));
?>
Database.php
<?php
class Database
{
/* Initializing Database Information */
var $host = 'localhost';
var $user = 'root';
var $pass = '';
var $database = "database";
var $dbh;
/* Connecting Datbase */
public function __construct(){
try {
$this->dbh = new PDO('mysql:host='.$this->host.';dbname='.$this->database.'', $this->user, $this->pass);
//print "Connected Successfully";
}
catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
}
/* Insert Multiple Rows in a table */
public function insertMultiple($table,$rows){
$this->dbh->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($rows as $d){
$question_marks[] = '(' . $this->placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
$datafields = array_keys($d);
}
$sql = "INSERT INTO $table (" . implode(",", $datafields ) . ") VALUES " . implode(',', $question_marks);
$stmt = $this->dbh->prepare ($sql);
try {
$stmt->execute($insert_values);
} catch (PDOException $e){
echo $e->getMessage();
}
return $this->dbh->commit();
}
/* placeholders for prepared statements like (?,?,?) */
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
}
?>
나는 똑같은 문제를 겪었고 이것이 내가 스스로 성취하는 방법이며, 그것을 위해 나 자신을 위해 기능을 만들었다.
예:
INSERT INTO 국가 (국가, 도시) VALUES (독일, 베를린), (프랑스, 파리);
$arr1 = Array("Germany", "Berlin");
$arr2 = Array("France", "France");
insertMultipleData("countries", Array($arr1, $arr2));
// Inserting multiple data to the Database.
public function insertMultipleData($table, $multi_params){
try{
$db = $this->connect();
$beforeParams = "";
$paramsStr = "";
$valuesStr = "";
for ($i=0; $i < count($multi_params); $i++) {
foreach ($multi_params[$i] as $j => $value) {
if ($i == 0) {
$beforeParams .= " " . $j . ",";
}
$paramsStr .= " :" . $j . "_" . $i .",";
}
$paramsStr = substr_replace($paramsStr, "", -1);
$valuesStr .= "(" . $paramsStr . "),";
$paramsStr = "";
}
$beforeParams = substr_replace($beforeParams, "", -1);
$valuesStr = substr_replace($valuesStr, "", -1);
$sql = "INSERT INTO " . $table . " (" . $beforeParams . ") VALUES " . $valuesStr . ";";
$stmt = $db->prepare($sql);
for ($i=0; $i < count($multi_params); $i++) {
foreach ($multi_params[$i] as $j => &$value) {
$stmt->bindParam(":" . $j . "_" . $i, $value);
}
}
$this->close($db);
$stmt->execute();
return true;
}catch(PDOException $e){
return false;
}
return false;
}
// Making connection to the Database
public function connect(){
$host = Constants::DB_HOST;
$dbname = Constants::DB_NAME;
$user = Constants::DB_USER;
$pass = Constants::DB_PASS;
$mysql_connect_str = 'mysql:host='. $host . ';dbname=' .$dbname;
$dbConnection = new PDO($mysql_connect_str, $user, $pass);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbConnection;
}
// Closing the connection
public function close($db){
$db = null;
}
경우 insertMultipleData ($ 테이블 $ multi_params)가 TRUE를 반환 , 데이터가 데이터베이스에 삽입되었습니다.
내 실험을 기반으로 단일 트랜잭션에서 여러 값 행을 가진 mysql insert 문이 가장 빠릅니다.
그러나 데이터가 너무 많은 경우 mysql의 max_allowed_packet설정은 여러 값 행으로 단일 트랜잭션 삽입을 제한 할 수 있습니다. 따라서 mysql max_allowed_packet크기 보다 큰 데이터가 있으면 다음 함수가 실패 합니다.
singleTransactionInsertWithRollbacksingleTransactionInsertWithPlaceholderssingleTransactionInsert
거대한 데이터 삽입 시나리오에서 가장 성공적인 transactionSpeed방법 은 방법이지만 위에서 언급 한 방법보다 더 많은 시간을 소비합니다. 따라서이 문제를 처리하기 위해 데이터를 더 작은 청크로 분할하고 단일 트랜잭션 삽입을 여러 번 호출하거나 transactionSpeed메소드 를 사용하여 실행 속도를 포기할 수 있습니다.
여기 내 연구가 있습니다
<?php
class SpeedTestClass
{
private $data;
private $pdo;
public function __construct()
{
$this->data = [];
$this->pdo = new \PDO('mysql:dbname=test_data', 'admin', 'admin');
if (!$this->pdo) {
die('Failed to connect to database');
}
}
public function createData()
{
$prefix = 'test';
$postfix = 'unicourt.com';
$salutations = ['Mr.', 'Ms.', 'Dr.', 'Mrs.'];
$csv[] = ['Salutation', 'First Name', 'Last Name', 'Email Address'];
for ($i = 0; $i < 100000; ++$i) {
$csv[] = [
$salutations[$i % \count($salutations)],
$prefix.$i,
$prefix.$i,
$prefix.$i.'@'.$postfix,
];
}
$this->data = $csv;
}
public function truncateTable()
{
$this->pdo->query('TRUNCATE TABLE `name`');
}
public function transactionSpeed()
{
$timer1 = microtime(true);
$this->pdo->beginTransaction();
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES (:first_name, :last_name)';
$sth = $this->pdo->prepare($sql);
foreach (\array_slice($this->data, 1) as $values) {
$sth->execute([
':first_name' => $values[1],
':last_name' => $values[2],
]);
}
// $timer2 = microtime(true);
// echo 'Prepare Time: '.($timer2 - $timer1).PHP_EOL;
// $timer3 = microtime(true);
if (!$this->pdo->commit()) {
echo "Commit failed\n";
}
$timer4 = microtime(true);
// echo 'Commit Time: '.($timer4 - $timer3).PHP_EOL;
return $timer4 - $timer1;
}
public function autoCommitSpeed()
{
$timer1 = microtime(true);
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES (:first_name, :last_name)';
$sth = $this->pdo->prepare($sql);
foreach (\array_slice($this->data, 1) as $values) {
$sth->execute([
':first_name' => $values[1],
':last_name' => $values[2],
]);
}
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function noBindAutoCommitSpeed()
{
$timer1 = microtime(true);
foreach (\array_slice($this->data, 1) as $values) {
$sth = $this->pdo->prepare("INSERT INTO `name` (`first_name`, `last_name`) VALUES ('{$values[1]}', '{$values[2]}')");
$sth->execute();
}
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function singleTransactionInsert()
{
$timer1 = microtime(true);
foreach (\array_slice($this->data, 1) as $values) {
$arr[] = "('{$values[1]}', '{$values[2]}')";
}
$sth = $this->pdo->prepare('INSERT INTO `name` (`first_name`, `last_name`) VALUES '.implode(', ', $arr));
$sth->execute();
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function singleTransactionInsertWithPlaceholders()
{
$placeholders = [];
$timer1 = microtime(true);
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES ';
foreach (\array_slice($this->data, 1) as $values) {
$placeholders[] = '(?, ?)';
$arr[] = $values[1];
$arr[] = $values[2];
}
$sql .= implode(', ', $placeholders);
$sth = $this->pdo->prepare($sql);
$sth->execute($arr);
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function singleTransactionInsertWithRollback()
{
$placeholders = [];
$timer1 = microtime(true);
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES ';
foreach (\array_slice($this->data, 1) as $values) {
$placeholders[] = '(?, ?)';
$arr[] = $values[1];
$arr[] = $values[2];
}
$sql .= implode(', ', $placeholders);
$this->pdo->beginTransaction();
$sth = $this->pdo->prepare($sql);
$sth->execute($arr);
$this->pdo->commit();
$timer2 = microtime(true);
return $timer2 - $timer1;
}
}
$s = new SpeedTestClass();
$s->createData();
$s->truncateTable();
echo "Time Spent for singleTransactionInsertWithRollback: {$s->singleTransactionInsertWithRollback()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for single Transaction Insert: {$s->singleTransactionInsert()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for single Transaction Insert With Placeholders: {$s->singleTransactionInsertWithPlaceholders()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for transaction: {$s->transactionSpeed()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for AutoCommit: {$s->noBindAutoCommitSpeed()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for autocommit with bind: {$s->autoCommitSpeed()}".PHP_EOL;
$s->truncateTable();
두 개의 열만 포함 된 테이블에 대한 100,000 개의 항목에 대한 결과는 다음과 같습니다.
$ php data.php
Time Spent for singleTransactionInsertWithRollback: 0.75147604942322
Time Spent for single Transaction Insert: 0.67445182800293
Time Spent for single Transaction Insert With Placeholders: 0.71131205558777
Time Spent for transaction: 8.0056409835815
Time Spent for AutoCommit: 35.4979159832
Time Spent for autocommit with bind: 33.303519010544
준비된 쿼리를 생성하기 위해 여기에 제공된 대부분의 솔루션은 필요 이상으로 복잡합니다. PHP의 내장 함수를 사용하면 상당한 오버 헤드없이 SQL 문을 쉽게 만들 수 있습니다.
$records각 레코드 자체가 인덱스 배열 (의 형식 field => value) 인 레코드 배열이 주어지면 다음 함수는 단일 준비된 명령문 만 사용하여 $tablePDO 연결 에서 지정된 테이블에 레코드를 삽입합니다 $connection. 다음에 대한 호출에서 인수 압축 풀기 사용으로 인해 PHP 5.6+ 솔루션입니다 array_push.
private function import(PDO $connection, $table, array $records)
{
$fields = array_keys($records[0]);
$placeHolders = substr(str_repeat(',?', count($fields)), 1);
$values = [];
foreach ($records as $record) {
array_push($values, ...array_values($record));
}
$query = 'INSERT INTO ' . $table . ' (';
$query .= implode(',', $fields);
$query .= ') VALUES (';
$query .= implode('),(', array_fill(0, count($records), $placeHolders));
$query .= ')';
$statement = $connection->prepare($query);
$statement->execute($values);
}
배열 조합은보다 빠르 array_push므로 다음과 같습니다.
$cumulativeArray += $rowArray;
참고 URL : https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query
반응형
'IT story' 카테고리의 다른 글
| 텍스트 영역에서 줄 바꿈을 어떻게 제거합니까? (0) | 2020.06.25 |
|---|---|
| Typescript에서 문자열이 숫자인지 확인하는 방법 (0) | 2020.06.25 |
| 크롬 개발자 도구에서 사람이 읽을 수있는 자바 스크립트 (0) | 2020.06.25 |
| HTML5 모드에서 AngularJS 애플리케이션을 URL 재 작성을 위해 IIS를 구성하려면 어떻게해야합니까? (0) | 2020.06.25 |
| 신경망에서 학습, 검증 및 테스트 세트의 차이점은 무엇입니까? (0) | 2020.06.25 |