Как добавить в БД огромный массив?

Question

[Max Ba]

Ребят, есть некий массив в php, который очень здоровый, около 10000 ключей, внутри каждого по 10 параметров. Приходится разбивать массив на части и делать много подзапросов иначе не вставляет вообще ничего.

$a = array_chunk($a, 300, TRUE);
foreach($a as $k => $v){
	//query insert + php implode
}

А нельзя ли как-то добавить за 1 запрос?

Comments

[Max Ba]

Александр +: да не, мне нужно просто вставить в БД много много значений за 1 раз. Я слышал, что множественные запросы в цикле не приветствуются. Я сохранил свой запрос в файл 1.txt, чтобы посмотреть, что там получилось. В итоге он выглядит так:

INSERT INTO `tab1` (`pid`, `price`) VALUE (1,1000), (2,2000), ......, (100000, 3000)

Весит около 5 МБ.

//for test
$test = file_get_contents('1.txt');
$stmt = $db->query($test); //и ничего, пусто

[ThunderCat]

Макс Васильев: откройте любой дамп большой таблицы, все разбито на несколько инсертов, одним не делают, это может быть 1 транзакция и в ней МНОГО запросов.

[Max Ba]

ThunderCat: мне главное, чтоб был один запрос $query = "INSERT INTO `test` VALUE ".implode(",\n", $a); Попробую разбить на группы INSERTов, в такой же гигантский массив. Добавлю только группы.

Answer 1

[ThunderCat]

Одним инсертом скорее всего вставить не получится, как на стороне мускуля есть ограничения по буферу запроса так и со стороны пхп есть ограничения по таймауту, длинне передаваемого запроса и проч( много настроек короче.) Как написал Eugene Mosyukov есть 3 варианта, но если вам необходимо использовать только код - разбивайте на инсерты по 100 строк в цикле, это 100 запросов у вас выйдет, должно быстро вставиться, можно до 200-300 строк за раз попробовать, если есть возможность этот массив получать всегда то экспериментом только вывести количество можно.

Макс Васильев:

Я слышал, что множественные запросы в цикле не приветствуются.

да, это плохая практика, но она относится к задачам типа "в цикле делаем вычисления, и тут же в цикле делаем запрос". Создаются куча запросов к базе на пустом месте, там где можно сформировать 1 запрос и после цикла отправить все сразу 1 запросом. У вас другая ситуация - данных много, больше чем за раз обрабатывает буфер бд, цикл здесь для преодоления оверхеда по размеру, тут он будет в тему, ибо все равно не отправляет по 1 строке, а формирует большие запросы с разовым исполнением

Comments

[Max Ba]

Значит я верно сделал (код в стартпосте и в комментарии к нему)
Я просто подумал, можно как-то COMMIT-ы вставить или BEGIN. Видел такие штуки.

[ThunderCat]

Макс Васильев: это называется транзакция, ее смысл в том, что при неудачном добавлении/изменении таблицы, при любой ошибке база вернется в состояние аналогичное начальному( rollback), что гарантирует консистенси базы в целом, но не гарантирует внесение изменений.

[Max Ba]

Понятно. Жаль... Попробовал разбить на инсерты, но в рамках одной переменной, всё без толку. Значит буфер БД всё портит.

Эксперимент. Неудачный.

$a = array();
for($i=1;$i<=100000;$i++){
	$a[] = "(".$i.",".rand(1000,9999).")";
}
$a = array_chunk($a, 100, TRUE);

$query = "";
foreach($a as $k => $v){
	$query .= "INSERT INTO `test` VALUE ".implode(", ", $v).";\n";
}

$db->query($query);

Что ж, буду делать в цикле запросы. Не так страшно, думаю.

Answer 2

[Eugene Mosyukov]

Вариант 1.
1. Сгенерировать один большой SQL-запрос.
2. Выполнить запрос.

Вариант 2.
1. Сгенерировать SQL-файл.
2. Загрузить файл с помощью консоли и команды mysql.

Вариант 3.
1. Сгенерировать csv-файл.
2. Загрузить csv через LOAD DATA INFILE.

Comments

[Max Ba]

Меня интересует как раз вариант 1. В коменте выше, я написал немного кода.

[Eugene Mosyukov]

Макс Васильев: Попробуйте каждые 1000 вставляемых строк дублировать INSERT:

INSERT INTO `tab1` (`pid`, `price`) VALUES
(1, 1000), (2, 1000),  ...  (1000, 1000);

INSERT INTO `tab1` (`pid`, `price`) VALUES
(1001, 1000), (1002, 1000),  ...  (2000, 1000);

[Max Ba]

Eugene Mosyukov: так а если у меня 100.000 записей, одним запросом вообще никак? Именно так я и делаю :)

[Eugene Mosyukov]

Макс Васильев: за один INSERT нельзя превысить количество данных, которое определено в max_allowed_packet (это в mysql). Посмотрите какое у вас значение установлено, и либо увеличьте его, либо вставляйте за раз еще меньше данных.