导入错误的 MySQL 转储时如何跳过行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7622253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to skip row when importing bad MySQL dump
提问by Almad
Given bad mysqldumpthat causes error on import:
鉴于不好mysqldump导致导入错误：
namtar backups # mysql -p < 2010-12-01.sql
Enter password:
ERROR 1062 (23000) at line 8020: Duplicate entry 'l?he?' for key 'wrd_txt'
Is there an easy way to tell import to just skip given row and continue?
有没有一种简单的方法可以告诉导入跳过给定的行并继续？
(Yes, I know I can manually edit the file or parse output, but it's not very convinient)
（是的，我知道我可以手动编辑文件或解析输出，但这不是很方便）
回答by jmlsteele
If you can make the dump again you could add --insert-ignoreto the command-line when dumping.
如果您可以再次进行转储，则可以在转储时添加--insert-ignore到命令行。
Or you can try using the mysqlimportcommand with --force,which will continue even if it encounters MySQL Errors.
或者您可以尝试使用带有的mysqlimport命令--force，即使遇到 MySQL 错误，它也会继续。
回答by Patrick Klingemann
mysql -f -p < 2010-12-01.sql
mysql -f -p < 2010-12-01.sql
the -f(force) being the operative option here, worked for me.
在-f（力）是这里的操作选项，为我工作。
回答by Ben
Following the advice from jmlsteele's answer and comment, here's how to turn the inserts into INSERT IGNOREon the fly.
按照 jmlsteele 的回答和评论中的建议，以下是如何将插入INSERT IGNORE内容即时转换成。
If you're importing from an sqlfile:
如果您从sql文件导入：
sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" < 2010-12-01.sql | mysql -p
If you're importing from a gzfile, just pipe the output from gunzip into sed instead of using the file input:
如果您从gz文件导入，只需将 gunzip 的输出通过管道传输到 sed 而不是使用文件输入：
gunzip < 2010-12-01.sql.gz | sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" | mysql -p
回答by PromInc
The other options certainly are viable options, but another solution would be to simply edit the .sqlfile obtained from the mysqldump.
其他选项当然是可行的选项，但另一种解决方案是简单地编辑.sql从mysqldump.
Change:
改变：
INSERT INTO table_name ...
TO
到
INSERT IGNORE INTO table_name ...
回答by Robert Saylor
Great tip. I did it a little different but same result.
很棒的提示。我做了一些不同但相同的结果。
perl -pi -e 's/INSERT INTO/INSERT IGNORE INTO/g' filename.sql
回答by zzapper
Just a thought did you delete the MySQL directives at the top of dump? (I unintentionally did when I restarted a restore after deleting all the records/tables I'd already inserted with a sed command). These directives tell MySQL ,among other things, not to do unique tests, foreign key tests etc)
只是想您是否删除了转储顶部的 MySQL 指令？（当我在删除所有已经用 sed 命令插入的记录/表后重新启动恢复时，我无意中做了）。这些指令告诉 MySQL，除其他外，不要进行唯一性测试、外键测试等）
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
