Перенести данные из файла в базу данных
Перенести данные из файла в базу данных
Здравствуйте. Нужен совет как правильно перенести данные из файла в базу данных при минимум запросов к БД.
Сейчас я получаю файл, в цикле получаю строки с данными и переношу их в базу данных. На каждую строку получается отдельный запрос.
Можно ли как-то сначала получить все данные из файла, потом вставить эти данные в таблиц одним запросом?
Сейчас я получаю файл, в цикле получаю строки с данными и переношу их в базу данных. На каждую строку получается отдельный запрос.
Можно ли как-то сначала получить все данные из файла, потом вставить эти данные в таблиц одним запросом?
- timlar
- Сообщения: 1382
- Зарегистрирован: 2009.09.19, 17:49
- Откуда: Украина, Днепропетровск
- Контактная информация:
Re: Перенести данные из файла в базу данных
Формировать запрос вида:
и выполнять одним запросом. Но без фанатизма. Не стоит инсертить сразу миллион записей, лучше разбить на несколько запросов, например по 100-500 значений.
Код: Выделить всё
INSERT INTO table_name (column_1, column_2, column_3) VALUES
('column 1 value', 'column 2 value', 'column 3 value'),
('column 1 value', 'column 2 value', 'column 3 value'),
. . .
('column 1 value', 'column 2 value', 'column 3 value');
Twitter: @timlar_ua
Re: Перенести данные из файла в базу данных
дело в том, что в файле бывает до тысячи записей. число записей постоянно увеличивается. нужно каждый день обновлять записи, добавляя новые из файла в базу.
Re: Перенести данные из файла в базу данных
Эм.. вариант, предложенный timlar, будет работать и при 10к и при 100к записей. Разве что время будет расти... Ну и для длинных запросов лучше разбивать их на части - к примеру по 50к символов.rem писал(а):дело в том, что в файле бывает до тысячи записей. число записей постоянно увеличивается. нужно каждый день обновлять записи, добавляя новые из файла в базу.
Каждый день - пользуйте крон.. если нет крона, можно и без него...
обновлять добавляя.. - INSERT ... ON DUPLICATE KEY UPDATE
либо заменять безусловно REPLACE INTO .... (),(),()...
p.s. Вот, если ID-шник в файле не задан...
...
Re: Перенести данные из файла в базу данных
Самый и быстрый и простой способ - импор в промежуточную таблицу и вставка/обновление не достающих, быстрее и проще ничего не придумать, всего 3 команды. Если база данных позволяет, то можно сделать импорт сразу в основную таблицу исключая дубликаты и обновляя изменившиеся, если конечно есть первичный ключ по которому можно идентифицировать записи.
Делать то, что советовал timlar не следует, этот метод устарел лет так 30 назад, работает крайне медленно.
Делать то, что советовал timlar не следует, этот метод устарел лет так 30 назад, работает крайне медленно.
Re: Перенести данные из файла в базу данных
Первый стандарт SQL-86 был одобрен ISO в 87 году.. как бэ меньше 30 лет назадTM123 писал(а):Делать то, что советовал timlar не следует, этот метод устарел лет так 30 назад, работает крайне медленно.
И по сути - что именно такого устаревшего посоветовал timlar?.. Если я правильно понял, Ваш метод как раз будет использовать его (или аналогичный) запрос для вставки-импорта в промежуточную таблицу? Или есть более передовые технологии SQL, которые он (да и я тоже) упустил из виду?..
...
Re: Перенести данные из файла в базу данных
Рассмотрим что предложил господин timlar
1. Открыть файл
2. сформировать строку insert into tablename (...fields...) values
3. прочесть файл построчно или сразу в переменную и дальше с ней работать построчно не суть
4. итерировать строки добавляя к ранее сформированной строке (...values...),
5. при достижении итератора 100-500 записей отправить сформированный insert на исполнение в базу
6. продолжаем до конца файла
7. закрыть файл
Это так выглядит то что предложил timlar, куча ненужного кода на PHP или говоря по простому, так делают люди оставшиеся в своем развитии на уровне однопользовательских БД типа dbase, paradox и т.д. или же люди читавшие учебники и "умные книги" от авторов оставшихся на указанном уровне развития.
То что предложил я
1. Выполнить SQL команду import
В общем то это весь алгоритм!!! и эта команда выполняется SQL сервером и выполняется максимально быстро.
Да могут быть дублирующие записи, в этом случае если позволяет база данных указать в алгоритме timlar в начале команды ключ вставлять только не существующие в противном случае придется его дорабатывать, делая запросы к базе данных на предмет существования записи в таблице перед тем как выполнить п. 4.
Мой алгоритм
1. import into tmp_table
2. insert into gen_table () select * from tmp_table where pkey not in (select pkey from gen_table)
Это опять весь алгоритм.
Если в файле записи могут меняться, в этом случае если позволяет база данных указать в алгоритме timlar в начале команды ключ имеющиеся обновлять/заменять существующие в противном случае придется его дорабатывать, обработкой варианта нужная запись есть, сделать update, количество update будет равно количесву измененных записей и все прелести формата insert into () values (), (), () будут потеряны, потому что все равно придется выполнить более одной команды.
Мой алгоритм преобразуется
1. import into tmp_table
2. insert into gen_table () select * from tmp_table where pkey not in (select pkey from gen_table)
3. update gen_table set ... where pkey in (select pkey from tmp_table)
п.3 можно оптимизировать для сокращения количества обновляемых записей с учетом особенностей данных хранящихся в файле, либо если позволяет база данных доработать п.2 на то чтобы существующие записи обновить/заменить.
В результате что мы имеем, в алгоритме timlar минимум 7 шагов с кучей кода, максимум 9 шагов с мегаколичеством запросов к базе равным 1+(количество записей в файле)+(количество измененных записей). В моем алгоритме минимум 1 строка на PHP выполняющая 1 запрос к базе, максимум 1 строка на PHP и 3 запроса к базе или в случае кривых баз без поддержки скриптов 3 PHP 3 SQL. О том что надо открыть соединение с базой и закрыть его я не пишу, т.к. оно будет и там и там.
Внимание вопрос, что сделать проще и в чем состоит одинаковость алгоритмов?
Могу дать совет, не придирайтесь к цифрам, а учитесь мыслить наборами, чтобы научиться писать эффективные команды на SQL и выносите максимум работы с БД на SQL и используйте Active Record только для выборки данных, она работает корректно с точки зрения эффективности только в этом случае, при манипулировании данными она крайне не эффективна и ни в коем случае не используйте постобработку данных на PHP, даже те же курсоры на стороне БД работают в десятки раз быстрее чем циклы в PHP.
1. Открыть файл
2. сформировать строку insert into tablename (...fields...) values
3. прочесть файл построчно или сразу в переменную и дальше с ней работать построчно не суть
4. итерировать строки добавляя к ранее сформированной строке (...values...),
5. при достижении итератора 100-500 записей отправить сформированный insert на исполнение в базу
6. продолжаем до конца файла
7. закрыть файл
Это так выглядит то что предложил timlar, куча ненужного кода на PHP или говоря по простому, так делают люди оставшиеся в своем развитии на уровне однопользовательских БД типа dbase, paradox и т.д. или же люди читавшие учебники и "умные книги" от авторов оставшихся на указанном уровне развития.
То что предложил я
1. Выполнить SQL команду import
В общем то это весь алгоритм!!! и эта команда выполняется SQL сервером и выполняется максимально быстро.
Да могут быть дублирующие записи, в этом случае если позволяет база данных указать в алгоритме timlar в начале команды ключ вставлять только не существующие в противном случае придется его дорабатывать, делая запросы к базе данных на предмет существования записи в таблице перед тем как выполнить п. 4.
Мой алгоритм
1. import into tmp_table
2. insert into gen_table () select * from tmp_table where pkey not in (select pkey from gen_table)
Это опять весь алгоритм.
Если в файле записи могут меняться, в этом случае если позволяет база данных указать в алгоритме timlar в начале команды ключ имеющиеся обновлять/заменять существующие в противном случае придется его дорабатывать, обработкой варианта нужная запись есть, сделать update, количество update будет равно количесву измененных записей и все прелести формата insert into () values (), (), () будут потеряны, потому что все равно придется выполнить более одной команды.
Мой алгоритм преобразуется
1. import into tmp_table
2. insert into gen_table () select * from tmp_table where pkey not in (select pkey from gen_table)
3. update gen_table set ... where pkey in (select pkey from tmp_table)
п.3 можно оптимизировать для сокращения количества обновляемых записей с учетом особенностей данных хранящихся в файле, либо если позволяет база данных доработать п.2 на то чтобы существующие записи обновить/заменить.
В результате что мы имеем, в алгоритме timlar минимум 7 шагов с кучей кода, максимум 9 шагов с мегаколичеством запросов к базе равным 1+(количество записей в файле)+(количество измененных записей). В моем алгоритме минимум 1 строка на PHP выполняющая 1 запрос к базе, максимум 1 строка на PHP и 3 запроса к базе или в случае кривых баз без поддержки скриптов 3 PHP 3 SQL. О том что надо открыть соединение с базой и закрыть его я не пишу, т.к. оно будет и там и там.
Внимание вопрос, что сделать проще и в чем состоит одинаковость алгоритмов?
Могу дать совет, не придирайтесь к цифрам, а учитесь мыслить наборами, чтобы научиться писать эффективные команды на SQL и выносите максимум работы с БД на SQL и используйте Active Record только для выборки данных, она работает корректно с точки зрения эффективности только в этом случае, при манипулировании данными она крайне не эффективна и ни в коем случае не используйте постобработку данных на PHP, даже те же курсоры на стороне БД работают в десятки раз быстрее чем циклы в PHP.
Re: Перенести данные из файла в базу данных
Где вы вязли такую команду? В доках mysql я ее не встречал. Знаю консольную утилиту mysqlimport, но она работает с командой LOAD DATA, которая запрещена на многих хостингах по соображениям безопасности.TM123 писал(а):1. Выполнить SQL команду import
Это не весь алгоритм. Даже если существует команда import, целевой файл должен быть корректно сформирован. Итак, ваш алгоритм:TM123 писал(а): То что предложил я
1. Выполнить SQL команду import
1. Открыть файл.
2. Прочесть файл построчно или сразу в переменную и дальше с ней работать.
3. Закрыть/очистить файл.
4. Сформировать нужный для базы формат.
5. Открыть файл (если он новый) и записать данные.
6. Закрыть файл.
Так делают только те, которые, как вы выразились, остановились в своем развитии. Делается сразу попытка вставки. Потом проверяется кол-во затронутых строк. Если кол-во равно 0, значит вставки не было и нужно делать обновление. И того, максимум два запроса на одной итерации.TM123 писал(а):Да могут быть дублирующие записи, в этом случае если позволяет база данных указать в алгоритме timlar в начале команды ключ вставлять только не существующие в противном случае придется его дорабатывать, делая запросы к базе данных на предмет существования записи в таблице перед тем как выполнить п. 4.
Ваш алгоритм неверен, т.к. рассматривает всю строку, как нечто атомарное. Что вы будите делать, если в строке изменяется лишь одно поле, причем это арифметическое поле? Например, count = count + 1? Вам нужно будет всю логику расчета проводить над таблицей tmp_table.TM123 писал(а):Мой алгоритм преобразуется
1. import into tmp_table
2. insert into gen_table () select * from tmp_table where pkey not in (select pkey from gen_table)
3. update gen_table set ... where pkey in (select pkey from tmp_table)
С учетом того, что вы обязаны формировать верный исходный файл, контролировать целостность таблицы tmp_table, особенно в случаях, когда меняются только отдельные поля, ваш код по объему сравним с кодом timlar, а при определенных условиях даже проигрывает ему.TM123 писал(а):В результате что мы имеем, в алгоритме timlar минимум 7 шагов с кучей кода, максимум 9 шагов с мегаколичеством запросов к базе равным 1+(количество записей в файле)+(количество измененных записей). В моем алгоритме минимум 1 строка на PHP выполняющая 1 запрос к базе, максимум 1 строка на PHP и 3 запроса к базе или в случае кривых баз без поддержки скриптов 3 PHP 3 SQL. О том что надо открыть соединение с базой и закрыть его я не пишу, т.к. оно будет и там и там.
Внимание вопрос, что сделать проще и в чем состоит одинаковость алгоритмов?
ЗЫ. Не опускайтесь до уровня оскорблений и наездов на авторов иных точек зрения.
Re: Перенести данные из файла в базу данных
1. Приведен общий алгоритм без подробностей, все что вы написали придирки по мелочи
2. В разных базах данных команда импорта называется по разному и я в общем не собирался устраивать лекбез, назвал ее import что совершенно должно быть понятно
3. Читайте лучше доку по MySQL http://www.mysql.ru/docs/mysql-man-4.0- ... #load-data даже в 4-ой версии, ну а точнее хотя бы иногда читайте доки к новым версиям продуктов которые используете!!!
4. Если у провайдера есть проблемы с функционалом - в топку такого провайдера и его соображения. Соображения безопасности устанавливаете вы для своей БД, а не провайдер который не может правильно настроить изоляцию прав доступа.
5. Данные предоставляемые источником должны быть корректны и достоверны, если файл сформирован неверно и содержит повреждения формата, то это уже проблемы источника, в любом случае крайне не дальновидно пытаться исправить некорректный файл и тем самым получить некорректные данные вместо того, чтобы получить корректный файл.
2. В разных базах данных команда импорта называется по разному и я в общем не собирался устраивать лекбез, назвал ее import что совершенно должно быть понятно
3. Читайте лучше доку по MySQL http://www.mysql.ru/docs/mysql-man-4.0- ... #load-data даже в 4-ой версии, ну а точнее хотя бы иногда читайте доки к новым версиям продуктов которые используете!!!
4. Если у провайдера есть проблемы с функционалом - в топку такого провайдера и его соображения. Соображения безопасности устанавливаете вы для своей БД, а не провайдер который не может правильно настроить изоляцию прав доступа.
5. Данные предоставляемые источником должны быть корректны и достоверны, если файл сформирован неверно и содержит повреждения формата, то это уже проблемы источника, в любом случае крайне не дальновидно пытаться исправить некорректный файл и тем самым получить некорректные данные вместо того, чтобы получить корректный файл.
Re: Перенести данные из файла в базу данных
Вы хотя бы иногда читайте, что вам пишут собеседники. Именно по этому я и уточнил у вас, что вы имеете ввиду под словом import. В mysql команда LOAD INFILE крайне опасная. И не в правах тут дело, а в самом содержимом файла.TM123 писал(а):1. Приведен общий алгоритм без подробностей, все что вы написали придирки по мелочи
2. В разных базах данных команда импорта называется по разному и я в общем не собирался устраивать лекбез, назвал ее import что совершенно должно быть понятно
3. Читайте лучше доку по MySQL http://www.mysql.ru/docs/mysql-man-4.0- ... #load-data даже в 4-ой версии, ну а точнее хотя бы иногда читайте доки к новым версиям продуктов которые используете!!!
Это не относится к случаю, когда разработчик приложения получает файл из вне, например, с другого сайта или другого приложения. А на самом деле в большинстве случаев так оно и бывает. Откуда разработчику стороннего сервиса знать, в каком формате вы собираетесь использовать его данные? Он, к примеру, передает их в xml. Ваш выход из ситуации? Будите сразу импортировать подобный файл в базу? Бред получите. Поэтому в 99% случаев это ваша проблема - сформировать файл, понятный вашей бд.TM123 писал(а):5. Данные предоставляемые источником должны быть корректны и достоверны, если файл сформирован неверно и содержит повреждения формата, то это уже проблемы источника, в любом случае крайне не дальновидно пытаться исправить некорректный файл и тем самым получить некорректные данные вместо того, чтобы получить корректный файл.
Re: Перенести данные из файла в базу данных
1. Вы говорили об отсутствие такой команды, она есть.
2. В чем опасность данной команды?
3. Импортят сразу данные из внешних источников в боевые таблицы только очень смелые люди, более хитрожопые типа меня используют для этого временные буферные таблицы, на которых проводятся всяческие проверки полученных данных перед перенос на бой.
4. Стоит определиться какие именно данные получаются и на какой основе, если эти данные получаются постоянно, то читай мое сообщение выше - проблемы надежности и достоверности источника. Если они получаются единоразово или время от времени, то это следует делать в ручном режиме, опять таки по описанному мной алгоритму
2. В чем опасность данной команды?
3. Импортят сразу данные из внешних источников в боевые таблицы только очень смелые люди, более хитрожопые типа меня используют для этого временные буферные таблицы, на которых проводятся всяческие проверки полученных данных перед перенос на бой.
4. Стоит определиться какие именно данные получаются и на какой основе, если эти данные получаются постоянно, то читай мое сообщение выше - проблемы надежности и достоверности источника. Если они получаются единоразово или время от времени, то это следует делать в ручном режиме, опять таки по описанному мной алгоритму
Re: Перенести данные из файла в базу данных
Покажите мне цитату, где сказано, что команда LOAD DATA отсутствует в бд MySQL. Если вы до сих пор не поняли, я просил вас указать аналог команды import для бд MySQL, чтобы не было недопонимания об одном и том же ли мы говорим. Не путайте теплое с мягким.TM123 писал(а):1. Вы говорили об отсутствие такой команды, она есть.
Опасность в содержимом файла. Файл должен быть строго определенного формата. Иначе попытка вставки закончится неудачей. Это с точки зрения пользователя. Если с точки зрения системы, то база данных должна иметь доступ на чтение, чтобы вы могли воспользоваться командой LOAD DATA. А это значит, что сервер может прочитать не только этот файл...TM123 писал(а):2. В чем опасность данной команды?
Есть временная таблицаTM123 писал(а):3. Импортят сразу данные из внешних источников в боевые таблицы только очень смелые люди, более хитрожопые типа меня используют для этого временные буферные таблицы, на которых проводятся всяческие проверки полученных данных перед перенос на бой.
Код: Выделить всё
tmp_table(
`id` int(10) unsigned NOT NULL DEFAULT '0',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ip` text NOT NULL,
`name` text CHARACTER NOT NULL,
`count` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY (`name`)
)
Код: Выделить всё
691898;1326458923;91.205.208.37;"Bow";1;"";
688956;1325751979;95.29.43.252;"Reflex";1;"";
688770;1325700830;178.122.109.22;"";1;"";
686839;1325357851;93.125.76.8;"frozenochek";1;"";
Re: Перенести данные из файла в базу данных
1. Да файл может не загрузиться и в чем опасность? - это не опасность, это нефиг совать на вход что не попадя, это тоже самое что возмущаться почему у вас прямоугольный брусок не влезает в круглое отверстие.
2. Не умение правильно настраивать права доступа не говорит что функцией не надо пользоваться, а "умных" провайдеров в топку
3. Временные таблицы для того и существуют, чтобы полностью соответствовать структуре данных, а не искать на свою голову приключения в виде меньшего количества полей в таблице по сравнению с исходным файлом. Ну и до кучи нормальные люди для временных таблиц используют поля типа текст с NULL значениями по умолчанию, чтобы все было прочтено как есть, а уж потом начинают заниматься валидацией конкретных полей, хотя из принципа можно тоже поискать себе секса на свою голову с соответствием типов полей при импорте.
4. Я не собираюсь сдавать тут экзамены, мне это нафиг не надо
А вообще хотите искать на свою голову приключения, пожалуйста ищите и используйте то что вам хочется, пока вы работаете со всякими проектами в которых стоимость их данных практически равна 0 в реальном денежном выражении - это нормально будет прокатывать. Я же вам не запрещаю и мне вообще полностью фиолетово что и как вы делаете.
2. Не умение правильно настраивать права доступа не говорит что функцией не надо пользоваться, а "умных" провайдеров в топку
3. Временные таблицы для того и существуют, чтобы полностью соответствовать структуре данных, а не искать на свою голову приключения в виде меньшего количества полей в таблице по сравнению с исходным файлом. Ну и до кучи нормальные люди для временных таблиц используют поля типа текст с NULL значениями по умолчанию, чтобы все было прочтено как есть, а уж потом начинают заниматься валидацией конкретных полей, хотя из принципа можно тоже поискать себе секса на свою голову с соответствием типов полей при импорте.
4. Я не собираюсь сдавать тут экзамены, мне это нафиг не надо
А вообще хотите искать на свою голову приключения, пожалуйста ищите и используйте то что вам хочется, пока вы работаете со всякими проектами в которых стоимость их данных практически равна 0 в реальном денежном выражении - это нормально будет прокатывать. Я же вам не запрещаю и мне вообще полностью фиолетово что и как вы делаете.
Re: Перенести данные из файла в базу данных
Что и следовало ожидать . Аргументы закончились, спустились до банальных наездов.
А проблема то в простом не соответствии данных, который предоставляет сторонний сервис с той структурой бд, которая существует на вашем проекте. И ведь проблема даже не в том, что поля не равны, а в том, что mysql требует в качестве разделителей знак табуляции, а не точку с запятой... Конечно, вы можете подстраивать свое приложение к тем данным, что предоставляет сторонний сервис. Но вот что будет, если сервис, за который вы не отвечаете, изменит формат данных? Или ваш заказчик решит переключиться на другой сервис? Будите пол приложения перелопачивать? Вот то-то и оно. Если вы не можете контролировать формат входных данных, сделайте так, чтобы ваше приложение зависело от них как можно меньше.
А если потом еще и обновлять, именно обновлять, а не как вы предлагаете, полностью заменять существующие данные, то итерация каждой из 20 тыс. строк над массивом, из к примеру, 18 тыс элементов, затянется на веки вечные .
А проблема то в простом не соответствии данных, который предоставляет сторонний сервис с той структурой бд, которая существует на вашем проекте. И ведь проблема даже не в том, что поля не равны, а в том, что mysql требует в качестве разделителей знак табуляции, а не точку с запятой... Конечно, вы можете подстраивать свое приложение к тем данным, что предоставляет сторонний сервис. Но вот что будет, если сервис, за который вы не отвечаете, изменит формат данных? Или ваш заказчик решит переключиться на другой сервис? Будите пол приложения перелопачивать? Вот то-то и оно. Если вы не можете контролировать формат входных данных, сделайте так, чтобы ваше приложение зависело от них как можно меньше.
Т.е. вы согласны, если вам с завода пришел прямоугольный брусок, вы не будите его сразу совать в круглое отверстие, как предлагали выше, а сперва обработаете его соответствующим инструментом .TM123 писал(а):1. Да файл может не загрузиться и в чем опасность? - это не опасность, это нефиг совать на вход что не попадя, это тоже самое что возмущаться почему у вас прямоугольный брусок не влезает в круглое отверстие.
Вы тут нас, темных, упрекали в том, что мол мы делаем кучу ненужных запросов к бд. Позвольте у вас поинтересоваться, как вы собрались валидировать данные во временной таблице, не отправляя серверу кучу запросов валидации? 20 тыс строк, в каждой строке 20 столбцов. Вы собираетесь отправить серверу 400 тыс. запросов, чтобы валидировать каждое поле?TM123 писал(а):а уж потом начинают заниматься валидацией конкретных полей
А если потом еще и обновлять, именно обновлять, а не как вы предлагаете, полностью заменять существующие данные, то итерация каждой из 20 тыс. строк над массивом, из к примеру, 18 тыс элементов, затянется на веки вечные .
Re: Перенести данные из файла в базу данных
Обычно чем меньше уровень знаний, тем больше упорства в собственной глупости - это раз уж начинаются упреки в дешевых наездах.
1. Вы читали документацию как я вам советовал, для тех кто на бронепоезде рекомендую обратить внимание на параметры
FIELDS TERMINATED BY и LINES TERMINATED BY
2. Мне очень интересно, а как при изменении формата исходных данных вам поможет отстаиваемая схема? ваша мегапрограмма что-то там на приводит на восстанавливает к общему знаменателю и получит извините за выражение полную херню? или вы предполагаете разработку искусственного интеллекта? Ну PHP вам в помощь
3. Для тех кто не умеет писать не на чем кроме PHP. Я отправлю столько запросов, сколько полей в базе данных или менее, если уместно будет свести проверку сразу нескольких полей в один запрос, а вы сделаете именно то о чем говорите - пару млн запросов или вы думаете что я буду сидеть и руками писать эти пару млн запросов? Лично я слишком ленив для этого, а вам PHP вам в помощь
4. Есть ли разница между обновлением и удаление старых + вставка новых, для меня нет хотя для вас да, кстати базы данных работают по второму принципу, берется запись полностью, в ней меняются нужные поля и вся запись выталкивается обратно без разбору, какие там поля поменялись, просто потому что это работает быстрее и по экономии процессора и по экономии дискового времени, а вы наверное думаете что база данных ищет на диске положение каждого поля каждой обновляемой записи и изменяет ровно эти 2 байта типа smallint и никак ничего иначе. Рекомендую почитать - описание принципов устройства компьютера - раздел работа дисковой подсистемы и низкоуровневое программирование на уровне процессора - C или Assembler (если он еще жив).
1. Вы читали документацию как я вам советовал, для тех кто на бронепоезде рекомендую обратить внимание на параметры
FIELDS TERMINATED BY и LINES TERMINATED BY
2. Мне очень интересно, а как при изменении формата исходных данных вам поможет отстаиваемая схема? ваша мегапрограмма что-то там на приводит на восстанавливает к общему знаменателю и получит извините за выражение полную херню? или вы предполагаете разработку искусственного интеллекта? Ну PHP вам в помощь
3. Для тех кто не умеет писать не на чем кроме PHP. Я отправлю столько запросов, сколько полей в базе данных или менее, если уместно будет свести проверку сразу нескольких полей в один запрос, а вы сделаете именно то о чем говорите - пару млн запросов или вы думаете что я буду сидеть и руками писать эти пару млн запросов? Лично я слишком ленив для этого, а вам PHP вам в помощь
4. Есть ли разница между обновлением и удаление старых + вставка новых, для меня нет хотя для вас да, кстати базы данных работают по второму принципу, берется запись полностью, в ней меняются нужные поля и вся запись выталкивается обратно без разбору, какие там поля поменялись, просто потому что это работает быстрее и по экономии процессора и по экономии дискового времени, а вы наверное думаете что база данных ищет на диске положение каждого поля каждой обновляемой записи и изменяет ровно эти 2 байта типа smallint и никак ничего иначе. Рекомендую почитать - описание принципов устройства компьютера - раздел работа дисковой подсистемы и низкоуровневое программирование на уровне процессора - C или Assembler (если он еще жив).
Re: Перенести данные из файла в базу данных
Топикстартер, напиши пожалуйста используемый формат файла, тогда сразу станет ясно какой способ лучше использовать.
Yii Jabber Conference: yii@conference.jabber.ru
- timlar
- Сообщения: 1382
- Зарегистрирован: 2009.09.19, 17:49
- Откуда: Украина, Днепропетровск
- Контактная информация:
Re: Перенести данные из файла в базу данных
Ох и развели тут срач из ничего Я описал самый простой, дедовский способ. Я не говорил о том, что это единственный способ, что он самый правильный, лучший, быстрый и т.д. Если захотелось рассказать о еще одном способе, то это можно сделать спокойно, без упреков остальных участников дискуссии и без перехода на личности. Думаю, здесь не место обсуждать какой из методов лучше и почему. Это тема отдельного разговора. Можно создать отдельную тему и там уже (в спокойной обстановке) прийти к решению, каким методом пользоваться лучше всего, описав его достоинства и недостатки. Давайте уважать друг друга.
Twitter: @timlar_ua
Re: Перенести данные из файла в базу данных
Да очень просто, мне придется изменить лишь пару строк в модуле, который парсит файл. База то всегда ожидает данные в строго определенном формате. И выхлоп модуля по прежнему будет в нужном формате. А вот вам придется переделывать всю структуру временной таблицы, переделывать запросы на вставку во временную таблицу, переделывать запросу на валидацию данных.TM123 писал(а):2. Мне очень интересно, а как при изменении формата исходных данных вам поможет отстаиваемая схема? ваша мегапрограмма что-то там на приводит на восстанавливает к общему знаменателю и получит извините за выражение полную херню? или вы предполагаете разработку искусственного интеллекта? Ну PHP вам в помощь
Допустим 20 полей в строке. 20 запросов в базу на валидацию? Расскажите подробнее, чем вам поможет валидация поля, если вам нужна валидация данных? Вам нужно проверить каждое из 20 полей в каждой из 20 тыс. строк.TM123 писал(а):3. Для тех кто не умеет писать не на чем кроме PHP. Я отправлю столько запросов, сколько полей в базе данных или менее, если уместно будет свести проверку сразу нескольких полей в один запрос
А кто сказал, что валидацию полей я буду проводить через запросы в базу? Я средствами пхп во время парсинга исходного файла проверю все данные . Это в разы быстрее и проще, чем отправлять кучу запросов валидации в базу.TM123 писал(а):а вы сделаете именно то о чем говорите - пару млн запросов или вы думаете что я буду сидеть и руками писать эти пару млн запросов?
Как будет выглядеть ваш алгоритм, если требуется обновление данных, к примеру, count = count из базы + count из файла? Итак, вы запихиваете весь файл во временную таблицу, проводите кучу запросов на валидацию. Как потом сложить измененые поля? Вам нужно из боевой таблице извлечь все строки, которые появились в файле. Допустим, файл содержит 400 тыс строк. Допустим, вы не удаляете временную таблицу между вызовами скрипта (чтобы была возможность найти новые строки в файле). Вы определили, что кол-во новых строк 20 тыс. Теперь вы вынуждены сделать запрос на извлечение 20 тыс строк из боевой таблицы. Боевая таблица возвращает вам 18 тыс строк. Это говорит о том, что 2 тыс. строк не изменились. Но вы то не знаете, какие именно из 20 тыс строк не изменились. Вы вынуждены итерировать каждую из 20 тыс строк над массивом из 18 тыс элементов, чтобы отсеять лишние 2 тыс строки. Во время итерации вы можете сразу же и обновлять поле count. Теперь вам нужна вторая временная таблица, первая то занята для контроля с какой позиции считывать файл. Но данные во второй временной таблице уже не соответствуют боевой. Вам нужно полностью очистить боевую таблицу перед вставкой. Как поведет себя приложение в момент, когда боевая таблица будет очищена, а данные еще не будут полностью вставлены? Можно использовать транзакции. Но тогда возникает вопрос, на сколько будет критично для клиента ожидание пока заполниться таблица?TM123 писал(а):4. Есть ли разница между обновлением и удаление старых + вставка новых, для меня нет
Есть алгоритм без использования промежуточных запросов для обновления поля count. Но там код еще более ужасен.
Что же делаю я. Парсинг файла, сразу во время парсинга проверка средствами пхп корректности данных. Выполняется в разы быстрее, чем заливка всего файла в бд и валидация данных через запросы. Для поиска новых данных можно попросту сохранять предыдущий файл и потом сверять текущий с предыдущим. Снова выполняется в разы быстрее, чем попытка поиска новых данных средствами бд (еще вопрос, какой путь вы будите использовать, поиск новых строк или сразу пихать все данные в таблицу каждый раз валидируя старые). Я то всегда буду валидировать лишь новые данные. А потом просто используя подготовленные запросы я буду делать попытку вставки новой строки и контролировать число задетых строк. Если число равно 1, значит вставка удалась, если 0, значит данные уже есть и нужно просто выполнить UPDATE над полем count. Да, возможно запросов будет больше, чем у вас (хотя далеко не факт), но вот общее время работы скрипта в разы быстрее, понятность и простота кода намного выше, контроль целостности бд также намного выше.
Re: Перенести данные из файла в базу данных
Полностью поддерживаю. Способ предложенный TM123 подходит в очень ограниченных случаях, когда вся строка атомарна, не нужно проводить операций замены, а только операции вставки и кода строк не очень много. Тогда использование LOAD DATA предпочтительнее. В остальных случаях нужно искать другие способы.timlar писал(а):Ох и развели тут срач из ничего Я описал самый простой, дедовский способ. Я не говорил о том, что это единственный способ, что он самый правильный, лучший, быстрый и т.д. Если захотелось рассказать о еще одном способе, то это можно сделать спокойно, без упреков остальных участников дискуссии и без перехода на личности. Думаю, здесь не место обсуждать какой из методов лучше и почему. Это тема отдельного разговора. Можно создать отдельную тему и там уже (в спокойной обстановке) прийти к решению, каким методом пользоваться лучше всего, описав его достоинства и недостатки. Давайте уважать друг друга.
Re: Перенести данные из файла в базу данных
Ну вот.. ) а так всё начиналось.. Пришёл timlar и всё испортил. (ну или попытался?)
Предлагаю подвести итоги "срача" : (краткое содержание предыдущих серий)
((личности пока предлагаю оставить, дабы можно было отследить ход мыслей.. некоторые части обсуждения опущены по тем или иным причинам))
В ответ на вопрос rem-а про перенос данных из некоторого файла в базу
timlar предложил конкретный "дедовский" (по его же словам) вариант, который решает озвученную задачу;
TM123 предложил, лучший (поначалу абстрактный, правда..) способ (import который чуть позже был расшифрован в LOAD DATA INFILE не без помощи Jampire), при этом содержащий дофига (не озвученных изначально - те же самые привилегии на FILE/возможность использования LOCAL) "если". И в общем случае (формат файла не подходит под требуемый для функции), данные на вход опять же нужно "готовить" в скрипте (дёргать их из XML, из Excel.. и экранировать те же самые _TERMINATED_BY.. возможно, преобразовать спецсимволы или вырезать тэги).. При использовании этого способа, опять же, имеются ограничения на размер файла ( max_allowed_packet - не факт, что mysql на том же сервере, что и скрипт)
Вариант TM123, кстати, предусматривает валидацию полей средствами БД (хотелось бы подробностей.. с примерами.. чуть поинтереснее, чем проверку на >0 или длину символов в строке)
Кстати, некоторые плюшки, которые позволяют ускорить выполнение (обоих способов) применительно к mysql можно вычитать тут. Там же мелькает информация о том, что LOAD DATA ~ раз в 20 быстрее INSERT-а. Кроме того в некоторых ситуациях вариант с insert-ом может и не прокатить.
Итого (если я всё правильно понял):
Предлагаю подвести итоги "срача" : (краткое содержание предыдущих серий)
((личности пока предлагаю оставить, дабы можно было отследить ход мыслей.. некоторые части обсуждения опущены по тем или иным причинам))
В ответ на вопрос rem-а про перенос данных из некоторого файла в базу
timlar предложил конкретный "дедовский" (по его же словам) вариант, который решает озвученную задачу;
TM123 предложил, лучший (поначалу абстрактный, правда..) способ (import который чуть позже был расшифрован в LOAD DATA INFILE не без помощи Jampire), при этом содержащий дофига (не озвученных изначально - те же самые привилегии на FILE/возможность использования LOCAL) "если". И в общем случае (формат файла не подходит под требуемый для функции), данные на вход опять же нужно "готовить" в скрипте (дёргать их из XML, из Excel.. и экранировать те же самые _TERMINATED_BY.. возможно, преобразовать спецсимволы или вырезать тэги).. При использовании этого способа, опять же, имеются ограничения на размер файла ( max_allowed_packet - не факт, что mysql на том же сервере, что и скрипт)
Вариант TM123, кстати, предусматривает валидацию полей средствами БД (хотелось бы подробностей.. с примерами.. чуть поинтереснее, чем проверку на >0 или длину символов в строке)
Кстати, некоторые плюшки, которые позволяют ускорить выполнение (обоих способов) применительно к mysql можно вычитать тут. Там же мелькает информация о том, что LOAD DATA ~ раз в 20 быстрее INSERT-а. Кроме того в некоторых ситуациях вариант с insert-ом может и не прокатить.
Итого (если я всё правильно понял):
Тюнинг mysql *и других БД (пока) в рамки данного срача обсуждения не вошёл.Разбираем входные данные, при необходимости экранируем/валидируем/фильтруем
- если есть возможность - готовим файл для Load Data Infile (для других БД смотреть функцию import-а)
или
- готовим insert|replace (возможность есть всегда?)
* держим в уме ограничение по длине
В обоих случаях можно использовать
- вставку в новую/промежуточную таблицу (к примеру, в memory ?)
- временное отключение индексов
- lock (?)
...