Блог

Мы создали приложение с MySQL в качестве базы данных. Каждую неделю мы экспортируем дамп данных из базы данных и удаляем все данные. Теперь мы хотим объединить все эти дампы вместе для некоторых задач анализа данных.

Проблема, с которой мы сталкиваемся, заключается в том, что поле «id» для всех таблиц автоматически увеличивается, поэтому во всех дампах данных оно начинается с 1, что приводит к дублированию идентификаторов в таблице. Я уверен, что должны быть лучшие способы сделать это, поскольку это должно быть довольно распространенной задачей в администрировании MySQL.

Какой был бы наилучший способ сделать это?

Если вы можете легко идентифицировать свои поля внешнего ключа (например, они принимают форму *_id), то вы можете использовать язык сценариев по вашему выбору для изменения первичного и внешнего ключей в файлах дампа, добавив «смещение пространства идентификаторов».

Допустим, у вас есть два файла дампа, и вы знаете, что диапазон их первичных ключей не превышает 1 000 000, вы увеличиваете первичный и внешний ключи во втором файле дампа на 1 000 000.

Это не совсем тривиально реализовать, поскольку вам придется определять положение полей внешнего ключа в операторах, а затем изменять значения в той же позиции столбца в другом месте инструкции.

Если ваши внешние ключи нелегко идентифицировать с помощью общего соглашения об именовании, тогда вы должны хранить отдельную информацию для каждой таблицы о том, как найти их позиции на основе положения столбца.

Удачи.

Лучшим способом было бы, если бы у вас была другая база данных, которая действует как хранилище данных, в которое вы копируете содержимое базы данных вашего приложения. После этого вы не усекаете все таблицы, вы просто используете DELETE ИЗ tablename — таким образом, ваши auto_increments не будут сброшены.

Это уродливое решение — экспортировать что-то, затем обрезать базу данных, а затем ожидать, что импорт будет выполнен должным образом. Даже если вы обойдете проблему столкновения автоматических приращений (есть оператор ON DUPLICATE KEY, который позволяет вам что-то делать, если не удается выполнить ограничение уникального ключа), ничто не гарантирует, что отношения между таблицами (внешние ключи) будут сохранены.

Это широкая тема, и предлагаемое решение быстрое и неприятное, некоторые другие люди, вероятно, предложат другие методы, но если вы делаете это для разгрузки базы данных, используемой вашим приложением — это плохой дизайн. Попробуйте поискать в Google поддержку разделов MySQL, если вы стремитесь к лучшей производительности при большем наборе данных.

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

В дальнейшем вы можете настроить дисциплину, в которой вы создаете дамп, а затем УДАЛЯЕТЕ строки, возраст которых превышает, скажем, один день. Таким образом, ваш идентификатор будет продолжать увеличиваться.

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