Как обновить в таблице сотни тысяч строк? - MySQL

335
28 февраля 2017, 18:54

имеется такая таблица:

┌──────┬──────────────────────┬────┐
│ Path │ Name                 │ id │
├──────┼──────────────────────┼────┤
│ 0    │ D:\1.bat             │ 1  │
│ 0    │ D:\Nichrome.rar      │ 2  │
│ 0    │ C:\pagefile.sys      │ 3  │
│ 0    │ E:\Уроки вождения.avi│ 4  │
│ 0    │ D:\$RECYCLE.BIN      │ 5  │
└──────┴──────────────────────┴────┘

нужно второй столбец разбить на составляющие и привести к такому виду:

┌──────┬──────────────────────┬────┐
│ Path │ Name                 │ id │
├──────┼──────────────────────┼────┤
│ D:\  │ 1.bat                │ 1  │
│ D:\  │ Nichrome.rar         │ 2  │
│ C:\  │ pagefile.sys         │ 3  │
│ E:\  │ Уроки вождения.avi   │ 4  │
│ D:\  │ $RECYCLE.BIN         │ 5  │
└──────┴──────────────────────┴────┘

проблема заключается в том, что строк около 800 тысяч, а на пхп оператором UPDATE построчно исправлять, за 5 минут(больше в IIS не знаю где настроить, все настройки игнорит) получается только 32000 строки, не знаю как в 1 запрос внести сразу несколько сотен строк, как в случае с оператором INSERT, скрипт без запросов UPDATE к базе обходит все 800 тысяч строк всего за 40 сек. Может есть умы, которые смогут помочь? уже мысль всё это дело в CSV выгружать и лопатить через файл, думаю в разы быстрее получится

Answer 1

Не стоит выполнять UPDATE-запросы в цикле, более того, возможно стоит отказаться от выполнения такой задачи из PHP. Лучше всего выполнить такое преобразование при помощи чистого SQL при помощи одного UPDATE-запроса. Предположим, что вы имеете дело только с Windows-путями и разделителем у вас всегда выступает слеш. В этом случае задача сводится к поиску последнего слеша в подстроке Name и помещению всего что слева от него в Path, а все что справа от него в Name. Выполнить эту задачу можно при помощи следующего запроса:

UPDATE
  pathes
SET
  Path = SUBSTRING(Name, 1, LENGTH(Name) - LENGTH(SUBSTRING_INDEX(Name, '\\', -1))),
  Name = SUBSTRING_INDEX(Name, '\\', -1);

Если отказаться от PHP нет никакой возможности, возможно стоит выполнить этот запрос в несколько приемов, контролируя выполнено преобразование или нет при помощи конструкции WHERE и ограничивая количество обрабатываемых за раз строк при помощи LIMIT.

UPDATE
  pathes
SET
  Path = SUBSTRING(Name, 1, LENGTH(Name) - LENGTH(SUBSTRING_INDEX(Name, '\\', -1))),
  Name = SUBSTRING_INDEX(Name, '\\', -1)
WHERE
  Path <> '0'
LIMIT
  50000;

Это позволит не блокировать таблицу на слишком длительное время и последовательно преобразовать весь массив данных.

ПОСЛЕДНИЕ ВОПРОСЫ
Все вопросы
Подробнее о сайте tabernaculum.com.ua
ТОП-10
READ ALSO
Определение браузера способами php
PHP

Определение браузера способами php

Здрасьте всем, я мало что знаю в php, поэтому и спрашиваюЯ нашел вот эту функцию:

 628
Замена пробелов на перенос строки в файле средствами php
PHP

Замена пробелов на перенос строки в файле средствами php

Есть txt файл в котором есть некий текст:

 462
ffpmeg не вырезает видео
PHP

ffpmeg не вырезает видео

На локальном сервере работала эта команда:

 310
checkbox в yii 2.0
PHP

checkbox в yii 2.0

Подскажите, пожалуйста, как в представлении вывести в ActiveForm checkboxList, значение которые являются объектами модели переданными из контроллера?...

 634