Я передаю большой набор данных в таблицу MySQL через PHP, используя команды вставки, и мне интересно, можно ли вставлять примерно 1000 строк за раз с помощью запроса, кроме добавления каждого значения в конец строки длиной в милю и затем выполняя его. Я использую фреймворк CodeIgniter, поэтому его функции также доступны мне.
Как вставить несколько строк из массива с помощью фреймворка CodeIgniter?
Ответы (12)
Сборка одного оператора INSERT
с несколькими строками намного быстрее в MySQL, чем один оператор INSERT
на строку.
Тем не менее, похоже, что вы можете столкнуться с проблемами обработки строк в PHP, что на самом деле является проблемой алгоритма, а не языка. По сути, при работе с большими строками вы хотите минимизировать ненужное копирование. В первую очередь это означает, что вы хотите избежать конкатенации. Самый быстрый и эффективный с точки зрения памяти способ построить большую строку, например, для вставки сотен строк за одну, - это воспользоваться функцией implode()
и назначением массива.
$sql = array();
foreach( $data as $row ) {
$sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));
Преимущество этого подхода состоит в том, что вы не копируете и не повторно копируете оператор SQL, который вы до сих пор собрали, с каждой конкатенацией; вместо этого PHP делает это один раз в операторе implode()
. Это большая победа.
Если у вас есть много столбцов, которые нужно собрать, и один или несколько очень длинные, вы также можете построить внутренний цикл, чтобы сделать то же самое, и использовать implode()
для присвоения предложения значений внешнему массиву.
.implode(',', $sql)
для чего нужны две кавычки и запятые?
- person Dasa; 11.10.2011
INSERT
требует запятой между каждым предложением, но не после последнего. Это самый элегантный способ сделать это.
- person staticsan; 12.10.2011
VALUE
s, но не в конце проблемы. Спасибо!
- person msanford; 31.07.2012
max_allowed_packet
переменную mysql, если вставка слишком большая, произойдет ошибка
- person Marius.C; 21.01.2014
mysql_real_escape_string
на mysqli_real_escape_string
и mysql_query
на mysqli_query
, поскольку я использую MySQLi, и они устарели с PHP5. Большое спасибо!
- person wordman; 24.01.2014
max_allowed_packet
.
- person staticsan; 19.08.2016
mysql_*
был удален из PHP, поэтому обязательно используйте mysqli_*
интерфейс.
- person Rick James; 03.11.2018
CodeIgniter теперь поддерживает множественную / пакетную вставку.
$data = array(
array(
'title' => 'My title' ,
'name' => 'My Name' ,
'date' => 'My date'
),
array(
'title' => 'Another title' ,
'name' => 'Another Name' ,
'date' => 'Another date'
)
);
$this->db->insert_batch('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
Вы можете подготовить запрос для вставки одной строки с помощью класса mysqli_stmt, а затем выполнить итерацию по массиву данных. Что-то вроде:
$stmt = $db->stmt_init();
$stmt->prepare("INSERT INTO mytbl (fld1, fld2, fld3, fld4) VALUES(?, ?, ?, ?)");
foreach($myarray as $row)
{
$stmt->bind_param('idsb', $row['fld1'], $row['fld2'], $row['fld3'], $row['fld4']);
$stmt->execute();
}
$stmt->close();
Где «idsb» - это типы связываемых данных (int, double, string, blob).
mysqli в PHP 5 - это объект с некоторыми хорошими функциями, которые позволят вам ускорить время вставки для ответа выше:
$mysqli->autocommit(FALSE);
$mysqli->multi_query($sqlCombined);
$mysqli->autocommit(TRUE);
Отключение автоматической фиксации при вставке большого количества строк значительно ускоряет вставку, поэтому отключите его, затем выполните, как указано выше, или просто создайте строку (sqlCombined), состоящую из множества операторов вставки, разделенных точками с запятой, и с несколькими запросами они будут обрабатываться нормально.
Вы всегда можете использовать mysql LOAD DATA
:
LOAD DATA LOCAL INFILE '/full/path/to/file/foo.csv' INTO TABLE `footable` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
выполнять массовую вставку, а не использовать кучу операторов INSERT
.
LOCAL
.
- person Kyle; 13.12.2017
Что ж, вы не хотите выполнять 1000 вызовов запросов, но это нормально:
$stmt= array( 'array of statements' );
$query= 'INSERT INTO yourtable (col1,col2,col3) VALUES ';
foreach( $stmt AS $k => $v ) {
$query.= '(' .$v. ')'; // NOTE: you'll have to change to suit
if ( $k !== sizeof($stmt)-1 ) $query.= ', ';
}
$r= mysql_query($query);
В зависимости от вашего источника данных заполнение массива может быть таким же простым, как открытие файла и выгрузка содержимого в массив через file()
.
Вы можете сделать это несколькими способами в codeigniter, например.
Первый по циклу
foreach($myarray as $row)
{
$data = array("first"=>$row->first,"second"=>$row->sec);
$this->db->insert('table_name',$data);
}
Второй - путем вставки пакета
$data = array(
array(
'first' => $myarray[0]['first'] ,
'second' => $myarray[0]['sec'],
),
array(
'first' => $myarray[1]['first'] ,
'second' => $myarray[1]['sec'],
),
);
$this->db->insert_batch('table_name', $data);
Третий способ - передача нескольких значений
$sql = array();
foreach( $myarray as $row ) {
$sql[] = '("'.mysql_real_escape_string($row['first']).'", '.$row['sec'].')';
}
mysql_query('INSERT INTO table (first, second) VALUES '.implode(',', $sql));
Хотя уже поздно отвечать на этот вопрос. Вот мой ответ на то же самое.
Если вы используете CodeIgniter, вы можете использовать встроенные методы, определенные в классе query_builder.
$ this- ›db-› insert_batch ()
Создает строку вставки на основе предоставленных вами данных и выполняет запрос. Вы можете передать в функцию массив или объект. Вот пример использования массива:
$data = array(
array(
'title' => 'My title',
'name' => 'My Name',
'date' => 'My date'
),
array(
'title' => 'Another title',
'name' => 'Another Name',
'date' => 'Another date'
)
);
$this->db->insert_batch('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
Первый параметр будет содержать имя таблицы, второй - ассоциативный массив значений.
Дополнительную информацию о query_builder можно найти здесь
Я создал класс, который выполняет многострочность и используется следующим образом:
$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;
/**
* Create a PDOMultiLine Insert object.
*
* @param PDO $pdo The PDO connection
* @param type $tableName The table name
* @param type $fieldsAsArray An array of the fields being inserted
* @param type $bigInsertCount How many rows to collect before performing an insert.
*/
function __construct(PDO $pdo, $tableName, $fieldsAsArray, $bigInsertCount = 100) {
$this->_numberOfFields = count($fieldsAsArray);
$insertIntoPortion = "REPLACE 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;
}
}
Используйте пакет вставки в codeigniter, чтобы вставить несколько строк данных.
$this->db->insert_batch('tabname',$data_array); // $data_array holds the value to be inserted
Мне пришлось ВСТАВИТЬ более 14000 строк в таблицу, и я обнаружил, что эта строка для строки с подготовленными операторами Mysqli заняла более десяти минут, в то время как распаковка аргументов со строковыми параметрами для тех же подготовленных операторов Mysqli сделала это менее чем за 10 секунд. Мои данные были очень повторяющимися, поскольку они были кратными идентификаторам и одному постоянному целому числу.
10-минутный код:
$num = 1000;
$ent = 4;
$value = ['id' => 1,
'id' => 2,
'id' => 3,
'id' => 4,
'id' => 5,
'id' => 6,
'id' => 7,
'id' => 8,
'id' => 9,
'id' => 10,
'id' => 11,
'id' => 12,
'id' => 13,
'id' => 14];
$cnt = 0;
$query = "INSERT INTO table (col1, col2) VALUES (?,?)";
$stmt = $this->db->prepare($query);
$stmt->bind_param('ii', $arg_one,$arg_two);
foreach ($value as $k => $val) {
for ($i=0; $i < $num; $i++) {
$arg_one = $k;
$arg_two = $ent;
if($stmt->execute()) {
$cnt++;
}
}
}
10-секундный код:
$ent = 4;
$num = 1000;
$value = ['id' => 1,
'id' => 2,
'id' => 3,
'id' => 4,
'id' => 5,
'id' => 6,
'id' => 7,
'id' => 8,
'id' => 9,
'id' => 10,
'id' => 11,
'id' => 12,
'id' => 13,
'id' => 14];
$newdat = [];
foreach ($value as $k => $val) {
for ($i=0; $i < $num; $i++) {
$newdat[] = $val;
$newdat[] = $ent;
}
}
// create string of data types
$cnt = count($newdat);
$param = str_repeat('i',$cnt);
// create string of question marks
$rec = (count($newdat) == 0) ? 0 : $cnt / 2 - 1;
$id_q = str_repeat('(?,?),', $rec) . '(?,?)';
// insert
$query = "INSERT INTO table (col1, col2) VALUES $id_q";
$stmt = $db->prepare($query);
$stmt->bind_param($param, ...$newdat);
$stmt->execute();
Я создал эту простую функцию, которую вы, ребята, можете легко использовать. Вам нужно будет передать имя таблицы ($tbl)
, поле таблицы ($insertFieldsArr)
вместо ваших данных вставки, массива данных ($arr)
.
insert_batch('table',array('field1','field2'),$dataArray);
function insert_batch($tbl,$insertFieldsArr,$arr){ $sql = array();
foreach( $arr as $row ) {
$strVals='';
$cnt=0;
foreach($insertFieldsArr as $key=>$val){
if(is_array($row)){
$strVals.="'".mysql_real_escape_string($row[$cnt]).'\',';
}
else{
$strVals.="'".mysql_real_escape_string($row).'\',';
}
$cnt++;
}
$strVals=rtrim($strVals,',');
$sql[] = '('.$strVals.')';
}
$fields=implode(',',$insertFieldsArr);
mysql_query('INSERT INTO `'.$tbl.'` ('.$fields.') VALUES '.implode(',', $sql));
}