Вопрос по mysql – таблица mysqldump без сброса первичного ключа

33

У меня одна таблица распределена по двум серверам под управлением MySql 4. Мне нужно объединить их в один сервер для нашей тестовой среды.

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

Поскольку они находятся в производственной среде, я не могу их каким-либо образом изменить на существующих серверах.

Проблема в том, что первичный ключ - это уникальное поле с автоматическим приращением, поэтому есть пересечения.

Я пытался выяснить, как использовать команду mysqldump для игнорирования определенных полей, но --disable-keys просто изменяет таблицу, а не полностью избавляется от ключей.

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

Помогите

Ваш Ответ

9   ответов
24

каким будет значение столбца auto_increment, просто загрузите первый файл, переименуйте таблицу, затем заново создайте таблицу и загрузите второй файл. наконец, используйте

INSERT newly_created_table_name (all, columns, except, the, auto_increment, column)
       SELECT all, columns, except, the, auto_increment, column
         FROM renamed_table_name
В качестве альтернативы, если вы используете phpmyadmin или такой инструмент, как mysql workbench, вы можете сделать дамп mysql и исключить определенные столбцы из результатов. Shawn
выглядит многообещающе, я начну пробовать это. Zee Spencer
Нет, не будет. Изменяет первичный ключ. longneck
У меня есть поля внешнего ключа, которые ссылаются на первичные ключи. Поддерживает ли это решение такую ссылочную целостность? Это не похоже на это. aamiri
0

Это одна из причин, почему автоинкрементные ключи являются PITA. Одно из решений - не удалять данные, а добавлять к ним.

CREATE VIEW myView AS
SELECT id*10+$x, name, email FROM users

(где $ x - это единственная цифра, уникально идентифицирующая исходную базу данных), либо создайте представление исходной базы данных (что, как вы намекаете, это невозможно), либо используйте процедуру извлечения, подобную описанной Autocracy, либо загрузите данные в промежуточные таблицы на тестовая коробка.

В качестве альтернативы, не создавайте таблицу в тестовой системе - вместо этого поместите в отдельные таблицы для данных src, а затем создайте представление, которое извлекает их обоих:

CREATE VIEW users AS
(SELECT * FROM users_on_a) UNION (SELECT ,* FROM users_on_b)

С

Я не понимаю, как помогает создание представления, поскольку mysqldump не выводит данные DATA вида, а только оператор CREATE VIEW, так что вы не идете дальше. Если вы просто используете представление, чтобы затем создать временную таблицу, то представление является избыточным. Если затем вы собираетесь использовать select .. в outfile, то снова, представление будет избыточным. Что мне не хватает? Tom Auger
3
SELECT null as fake_pk, `col_2`, `col_3`, `col_4` INTO OUTFILE 'your_file'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table;

LOAD DATA INFILE 'your_file' INTO TABLE your_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

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

2

Используйтеmysqldump обычно--opts -c. Например, ваш первичный ключ - «id». Отредактируйте выходные файлы и добавьте строку «dummy_id» в структуру вашей таблицы того же типа, что и «id» (но, конечно, не первичный ключ). Затем изменитеINSERT оператор и заменить 'id' на 'dummy_id'. После импорта удалите столбец dummy_id.

5

запустив что-то вроде

sed -e "s/([0-9]*,/(/gi" export.sql > expor2.sql 

на свалке, чтобы избавиться от первичных ключей, а затем

sed -e "s/VALUES/(col1,col2,...etc.) VALUES/gi" LinxImport2.sql > LinxImport3.sql

для всех столбцов, кроме первичного ключа. Конечно, вы должны быть осторожны, что([0-9]*, не заменяет ничего, что вы действительно хотите.

Надеюсь, это кому-нибудь поможет.

Мне пришлось удалитьi флаг из регулярного выражения, но в остальном это работает как шарм! Благодарность joshwhatk
тебе не нужно второе утверждение при использованииmysqldump --complete-insert .... Blauhirn
13

я посмотрел этот вопрос, нашел ответ @ pumpkinthehead и понял, что все, что нам нужно сделать, это найти + заменить первичный ключ в каждой строке на NULL, чтобы mysql использовал вместо этого значение по умолчанию auto_increment.

(your complete mysqldump command) | sed -e "s/([0-9]*,/(NULL,/gi" > my_dump_with_no_primary_keys.sql

Первоначальный вывод:

INSERT INTO `core_config_data` VALUES
    (2735,'default',0,'productupdates/configuration/sender_email_identity','general'),
    (2736,'default',0,'productupdates/configuration/unsubscribe','1'),

Трансформированный вывод:

INSERT INTO `core_config_data` VALUES
    (NULL,'default',0,'productupdates/configuration/sender_email_identity','general'),
    (NULL,'default',0,'productupdates/configuration/unsubscribe','1'),

Примечание: это все еще взломать; Например, произойдет сбой, если ваш столбец автоинкремента не является первым столбцом, но решит мою проблему в 99% случаев.

11

а затем запустить mysqldump для этого представления.

Так, если в вашей таблице "пользователи" есть столбцы: идентификатор, имя, адрес электронной почты

> CREATE VIEW myView AS
  SELECT name, email FROM users

Редакция: ах, я вижу, я не уверен, есть ли другой способ.

Не в MySQL 4 :(. Знаю, грустно: (. Zee Spencer
Таблицы находятся в диапазоне миллионов записей. Zee Spencer
В зависимости от размера таблицы вы можете создать временную копию (без PK) вместо создания представления. balpha♦
FYI Попытка экспортировать представление в phpmyadmin приведет к отсутствию строк. fionbio
6
Clone Твой стол Удалить столбец в таблице клонов Дамп таблицы клонов без структуры (но с опцией -c для получения полных вставок) Импорт, где вы хотите
0

которое я использовал, является простой экспорт SQL данных, которые я экспортирую, с последующим удалением первичного ключа из операторов вставки с помощью редактора поиска и замены RegEx. Лично я использую Sublime Text, но я уверен, что TextMate, Notepad ++ и т. Д. Могут делать то же самое.

Затем я просто запускаю запрос, в который необходимо вставить данные, скопировав запрос в окно запроса HeidiSQL или PHPMyAdmin. Если естьМНОГ данных Я сохраняю запрос вставки в файл SQL и вместо этого использую импорт файла. Копирование и вставка большого количества текста часто приводит к зависанию Chrome.

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

Похожие вопросы