Пользовательский SQL-экспорт приводит к ошибке дубликата ключа при импорте
Я создал экспорт базы данных своей базы данных, используя свой собственный скрипт php.
Для экспорта структуры таблицы я использую SHOW CREATE TABLEЧтобы экспортировать содержимое моих таблиц, я выбираю все, что есть в таблице, и затем создаю из нее запрос, который выглядит следующим образом: INSERT INTO tablename VALUES (...), (..), etc.
Я сохраняю все в текстовый файл, сначала операторы создания, а затем все операторы вставки.
Когда я пытаюсь импортировать текстовый файл с помощью phpMyAdmin, я получаю сообщение об ошибке:
#1062 - Duplicate entry '1' for key 'name1012_parent_id'
Это оператор создания таблицы:
CREATE TABLE `pages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) unsigned NOT NULL DEFAULT '0',
`templates_id` int(11) unsigned NOT NULL DEFAULT '0',
`name` varchar(128) CHARACTER SET ascii NOT NULL,
`status` int(10) unsigned NOT NULL DEFAULT '1',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_users_id` int(10) unsigned NOT NULL DEFAULT '2',
`created` timestamp NOT NULL DEFAULT '2015-12-18 06:09:00',
`created_users_id` int(10) unsigned NOT NULL DEFAULT '2',
`published` datetime DEFAULT NULL,
`sort` int(11) NOT NULL DEFAULT '0',
`name1012` varchar(128) CHARACTER SET ascii DEFAULT NULL,
`status1012` int(10) unsigned NOT NULL DEFAULT '1',
`name1013` varchar(128) CHARACTER SET ascii DEFAULT NULL,
`status1013` int(10) unsigned NOT NULL DEFAULT '1',
`name1027` varchar(128) CHARACTER SET ascii DEFAULT NULL,
`status1027` int(10) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `name_parent_id` (`name`,`parent_id`),
UNIQUE KEY `name1012_parent_id` (`name1012`,`parent_id`),
UNIQUE KEY `name1013_parent_id` (`name1013`,`parent_id`),
UNIQUE KEY `name1027_parent_id` (`name1027`,`parent_id`),
KEY `parent_id` (`parent_id`),
KEY `templates_id` (`templates_id`),
KEY `modified` (`modified`),
KEY `created` (`created`),
KEY `status` (`status`),
KEY `published` (`published`)
) ENGINE=InnoDB AUTO_INCREMENT=1051 DEFAULT CHARSET=utf8;
И запросы вставки:
INSERT INTO `pages` (`id`,`parent_id`,`templates_id`,`name`,`status`,`modified`,`modified_users_id`,`created`,`created_users_id`,`published`,`sort`,`name1012`,`status1012`,`name1013`,`status1013`,`name1027`,`status1027`)
VALUES
("1","0","1","en","9","2017-03-06 11:49:05","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","de","1","fi","1","nl","1"),
("2","1","2","processwire","1035","2017-02-28 14:26:06","40","2017-02-28 14:25:36","2","2017-02-28 14:25:36","6","","1","","1","","1"),
("3","2","2","page","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("6","3","2","add","21","2017-02-28 14:26:15","40","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("7","1","2","trash","1039","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","7","","1","","1","","1"),
("8","3","2","list","1045","2017-02-28 14:26:18","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("9","3","2","sort","1047","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("10","3","2","edit","1045","2017-02-28 14:26:18","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","3","","1","","1","","1"),
("11","22","2","template","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("16","22","2","field","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("21","2","2","module","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("22","2","2","setup","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("23","2","2","login","1035","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","4","","1","","1","","1"),
("27","1","29","http404","1035","2017-02-28 14:25:36","41","2017-02-28 14:25:36","3","2017-02-28 14:25:36","5","","1","","1","","1"),
("28","2","2","access","13","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","3","","1","","1","","1"),
("29","28","2","users","29","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("30","28","2","roles","29","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("31","28","2","permissions","29","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("32","31","5","page-edit","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("34","31","5","page-delete","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","3","","1","","1","","1"),
("35","31","5","page-move","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","4","","1","","1","","1"),
("36","31","5","page-view","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("37","30","4","guest","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("38","30","4","superuser","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("40","29","3","guest","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("41","29","3","admin","1","2017-02-28 14:26:06","40","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("50","31","5","page-sort","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","5","","1","","1","","1"),
("51","31","5","page-template","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","6","","1","","1","","1"),
("52","31","5","user-admin","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","10","","1","","1","","1"),
("53","31","5","profile-edit","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","13","","1","","1","","1"),
("54","31","5","page-lock","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","8","","1","","1","","1"),
("300","3","2","search","1045","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","5","","1","","1","","1"),
("301","3","2","trash","1047","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","5","","1","","1","","1"),
("302","3","2","link","1041","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","6","","1","","1","","1"),
("303","3","2","image","1041","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","7","","1","","1","","1"),
("304","2","2","profile","1025","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","5","","1","","1","","1"),
("1000","1","26","search","1025","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","suche","1","haku","1","","1"),
("1001","1","29","about","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","uber","1","tietoja","1","","1"),
("1002","1001","29","child-page-example-1","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","unterseite-seite-beispiel-1","1","alasivu-sivu-esimerkki-1","1","","1"),
("1004","1001","29","child-page-example-2","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","unterseite-beispiel-2","1","alasivu-esimerkki-2","1","","1"),
("1005","1","34","site-map","1","2017-03-06 11:49:05","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","sitemap","1","sivukartta","1","","1"),
("1006","31","5","page-lister","1","2017-02-28 14:25:36","40","2017-02-28 14:25:36","40","2017-02-28 14:25:36","9","","1","","1","","1"),
("1007","3","2","lister","1","2017-02-28 14:25:36","40","2017-02-28 14:25:36","40","2017-02-28 14:25:36","8","","1","","1","","1"),
("1009","22","2","languages","16","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","","1","","1","","1"),
("1010","1009","43","default","16","2017-03-09 13:55:50","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","0","","1","","1","","1"),
("1011","22","2","language-translator","1040","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","3","","1","","1","","1"),
("1012","1009","43","de","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","1","","1","","1","","1"),
("1013","1009","43","fi","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","","1","","1","","1"),
("1015","3","2","recent-pages","1","2017-02-28 14:26:05","40","2017-02-28 14:26:05","40","2017-02-28 14:26:05","9","","0","","0","","1"),
("1016","31","5","page-edit-recent","1","2017-02-28 14:26:05","40","2017-02-28 14:26:05","40","2017-02-28 14:26:05","10","","1","","1","","1"),
("1017","22","2","logs","1","2017-02-28 14:26:12","40","2017-02-28 14:26:12","40","2017-02-28 14:26:12","4","","0","","0","","1"),
("1018","31","5","logs-view","1","2017-02-28 14:26:12","40","2017-02-28 14:26:12","40","2017-02-28 14:26:12","11","","1","","1","","1"),
("1019","31","5","logs-edit","1","2017-02-28 14:26:12","40","2017-02-28 14:26:12","40","2017-02-28 14:26:12","12","","1","","1","","1"),
("1022","31","5","page-edit-front","1","2017-03-01 13:01:39","41","2017-03-01 13:01:39","41","2017-03-01 13:01:39","13","","1","","1","","1"),
("1027","1009","43","nl","1","2017-03-09 12:57:23","41","2017-03-02 15:52:40","41","2017-03-02 15:52:40","3","","1","","1","","1"),
("1028","3","2","clone","1024","2017-03-02 16:04:07","41","2017-03-02 16:04:07","41","2017-03-02 16:04:07","10","","0","","0","","0"),
("1029","31","5","page-clone","1","2017-03-02 16:04:07","41","2017-03-02 16:04:07","41","2017-03-02 16:04:07","14","","1","","1","","1"),
("1030","31","5","page-clone-tree","1","2017-03-02 16:04:07","41","2017-03-02 16:04:07","41","2017-03-02 16:04:07","15","","1","","1","","1"),
("1046","22","2","jumplinks","1","2017-03-10 09:59:09","41","2017-03-10 09:59:09","41","2017-03-10 09:59:09","5","","0","","0","","0"),
("1047","31","5","jumplinks-admin","1","2017-03-10 09:59:09","41","2017-03-10 09:59:09","41","2017-03-10 09:59:09","16","","1","","1","","1"),
("1048","22","2","db-backups","1","2017-03-14 15:50:17","41","2017-03-14 15:50:17","41","2017-03-14 15:50:17","6","","0","","0","","0"),
("1049","31","5","db-backup","1","2017-03-14 15:50:17","41","2017-03-14 15:50:17","41","2017-03-14 15:50:17","17","","1","","1","","1"),
("1050","31","5","page-edit-protected","1","2017-05-18 10:42:59","41","2017-05-18 10:42:59","41","2017-05-18 10:42:59","18","","1","","1","","1");
Несомненно, есть оператор вставки с теми же значениями. Чего я не понимаю, так это почему. Я извлекаю все строки из исходной таблицы и помещаю их в текстовый файл. На мой взгляд, в исходной таблице также должны быть повторяющиеся записи, но это не должно быть возможным из-за уникального ключа.
То, как я пишу это в файл, выглядит так (упрощенно):
$tables = $this->getTables(); // returns a list of tables
$output = [
'tables' => '',
'inserts' => ''
];
foreach ($tables as => $table) {
$output['tables'][] = $this->getCreateStatement(); // uses SHOW CREATE TABLE `tableName`
}
foreach ($tables as => $table) {
foreach($this->getTableRows() as $row){
$output['inserts'][] = "INSERT INTO `$table` ($this->fieldsToSql(array_keys($row))) VALUES($this->valuesToSql($row))"}
}
$fileContent = implode(';', $output['table']) . ' ' . implode(';', $output['inserts']);
file_put_contents('test.sql', $fileContent);
Опять же, это НЕ реальный код, этот пример выше — упрощенная версия моего кода. Мой код в основном делает то же самое.
valuesToSql() Метод работает так:
$output = [];
foreach($arr as $val) {
$output[] = '".$val."';
}
return implode(',', $output);
Решение
Проблема в том, что ваш valuesToSql() метод переводит null значения в пустую строку. В MySQL уникальные индексы позволяют несколько null ценности присутствовать в значениях индекса:
Для всех механизмов индекс UNIQUE допускает несколько значений NULL для столбцов, которые могут содержать NULL. Если вы задаете значение префикса для столбца в индексе UNIQUE, значения столбца должны быть уникальными в пределах префикса.
Тем не менее, пустая строка не совпадает с null и вызывает ошибку двойного ключа.
Кроме того, ваш код переводит все значения в строки независимо от того, что является реальным базовым значением. Это может вызвать проблемы, если целевой сервер MySQL имеет strict_all_tables Режим SQL включен, так как строка не является допустимыми данными для числового поля.
Чтобы справиться с null вопрос, я бы проверил, если значение null и обращаться с этим по-другому:
output = [];
foreach($arr as $val) {
if $val===NULL {
$output[] = 'NULL';
}
else {
$output[] = "'".$val."'";
}
}
return implode(',', $output);
Вы можете добавить дополнительные проверки в вышеупомянутый цикл в зависимости от типа данных и соответственно изменить вывод.
Другие решения
Других решений пока нет …