Вставьте несколько строк через массив PHP в MySQL
Я передаю большой набор данных в таблицу MySQL через PHP с помощью команд вставки, и мне интересно, возможно ли вставлять приблизительно 1000 строк за один раз с помощью запроса, отличного от добавления каждого значения в конец строки длиной в милю, а затем выполняя это. Я использую каркас CodeIgniter, поэтому его функции также доступны для меня.
15 ответов
Сборка одного 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() назначить предложение values внешнему массиву.
Многократная вставка / пакетная вставка теперь поддерживается 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 заявления.
Ну, вы не хотите выполнять 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(),
$query= array();
foreach( $your_data as $row ) {
$query[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $query));
Хотя уже поздно отвечать на этот вопрос. Вот мой ответ на тот же.
Если вы используете 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 здесь
Если кто-то ищет конкретно CodeIgniter 4:
$data[$array_1, $array_2, $array_3];
$db = db_connect();
if($db->table('sell')->insertBatch($data)){
echo "success - batch inserted.";
}
Вы можете сделать это несколькими способами в 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));
Я создал класс, который выполняет многострочный, который используется следующим образом:
$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));
}
use this in codeigniter for multiple data insertion
$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')