php - 自定义 sql 导出导致导入时出现重复键错误-6ren

php - 自定义 sql 导出导致导入时出现重复键错误

转载作者：行者123 更新时间：2023-11-29 18:57:35

我使用自己的 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 中 unique indexes allow multiple null values存在于索引值中:

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

但是，空字符串与null不同，会触发重复键错误。

此外，您的代码会将所有值转换为字符串，无论真正的底层值是什么。如果目标 MySQL 服务器启用了 strict_all_tables sql 模式，这可能会导致问题，因为字符串不是数字字段的有效数据。

为了处理 null 问题，我会检查该值是否为 null 并以不同的方式处理它:

output = [];
foreach($arr as $val) {
    if $val===NULL {
        $output[] = 'NULL';
    }
    else {
        $output[] = "'".$val."'";
    }
}

return implode(',', $output);

您可以根据数据的类型向上述循环添加进一步的检查，并相应地修改输出。

关于php - 自定义 sql 导出导致导入时出现重复键错误，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44064505/

文章推荐： javascript - React Native 组件渲染两次但 renderItem 不更新值？

文章推荐： android - 不兼容类型 : View cannot be converted to RadioButton

文章推荐： mysql - MySQL 事件调度程序是全局的吗？

sql - SQL、PL-SQL 和 T-SQL 之间有什么区别？
SQL、PL-SQL 和 T-SQL 之间有什么区别？谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？最佳答案 SQL 是一种对集合进行操作的查询语言。它或多或少是标准化的，几乎所有关
sql - T-SQL、SQL Server 和 SQL 有什么区别
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。我对 SQL 的了解足以完成我的
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任
sql - 在字符串中查找第二组数字(SQL/PL-SQL)
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
sql - 服务器端 sql 与客户端 sql
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
sql - sql 如何计算 sql 存储过程中的附加表？
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。有什么方
sql - 嵌入式 SQL 与动态 SQL
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。我自己做了一些数据库工作
sql - SQL Server SQL 语句可以有多少个字符？
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
sql-server - SQL 行到列 sql
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
sql - SQL Server SQL 语句中的动态日期
预先感谢您对此提供的任何帮助。假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
sql - (SQL Server) SQL 不允许在检查过程是否存在后创建过程
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
sql - 动态 SQL 是否比 SQL Server 中的静态 SQL 性能更高？
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
sql - exec sp_executesql @sql 和 exec (@sql) SQL Server
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
sql - 动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？最佳答案 sp_executes
c# - SQL > Linq to Sql，SQL 查询有效，Linq to SQL 返回空数据集
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
sql-server - "> sql.txt && sql -h-1 -i sql.txt && del sql.txt"命令是什么意思？
我正在调试一些构建成功运行的 SQL 命令的代码。然而，在查询结束时，查询结果似乎被写入了一个文本文件。完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
sql - 如何从 MS SQL 数据库(Microsoft SQL Server)中的其他 .sql 文件运行 .sql 文件？
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
sql - 如何在查询中使用 SQL 变量(SQL Server)？
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
sql - 如何将两个列表转换为邻接矩阵 SQL Server T-SQL？
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

php - 自定义 sql 导出导致导入时出现重复键错误