如何在 MySQL 转储中删除这些注释？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1916392/
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 can I get rid of these comments in a MySQL dump?
提问by etheros
I'm trying to create a simple structure only dump of my database. Using mysqldumpgives me a result like:
我正在尝试创建一个简单的结构，仅转储我的数据库。使用mysqldump给了我一个结果：
/*!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 */;
DROP TABLE IF EXISTS `foo`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
No matter what I try, I just can't seem to get rid of those comments.
无论我尝试什么，我似乎都无法摆脱这些评论。
I'm currently using: mysqldump -p -d --add-drop-table --skip-tz-utc --skip-set-charset -h 127.0.0.1 -u foo bar --result-file=dumpfile.sql
我目前正在使用： mysqldump -p -d --add-drop-table --skip-tz-utc --skip-set-charset -h 127.0.0.1 -u foo bar --result-file=dumpfile.sql
Edit:I do however wish to retain other comments, such as -- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)
编辑：但是我确实希望保留其他评论，例如-- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)
回答by O. Jones
WHOA! These aren't really comments even though they look that way. They are conditional-execution tokens.
哇！即使它们看起来那样，这些也不是真正的评论。它们是条件执行令牌。
Take this line:
走这条线：
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
If the version of mySQL is 4.00.14 or higher, then the MySQL server will run this statement.
如果 mySQL 的版本是 4.00.14或更高，那么 MySQL 服务器将运行此语句。
This magic comment syntax is documented in the Comment Syntaxsection of the manual.
这个神奇的注释语法记录在手册的注释语法部分。
You probably don't want to get rid of this stuff.
你可能不想摆脱这些东西。
回答by Tom Hennigan
I know this is an ancient question, but here is an answer at least. I also couldn't find a flag in mysqldump to remove the conditional comments, or indeed a better option to set a minimum mysql version for these comments to appear. If you just want to nuke them all, you can do so using grep or sed (sed leaves blank lines, grep does not):
我知道这是一个古老的问题，但至少这里有一个答案。我也无法在 mysqldump 中找到一个标志来删除条件注释，或者确实是一个更好的选择来设置最小 mysql 版本以显示这些注释。如果您只是想将它们全部删除，您可以使用 grep 或 sed（sed 留下空白行，grep 不这样做）：
mysqldump ... | grep -v '^\/\*![0-9]\{5\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-9]\{5\}.*\/;$//g'
To answer my own wish of conditionally removing comments dependent on mysql version, use one of these (removes any comments for anything < mysql5):
要满足我自己的有条件删除依赖于 mysql 版本的评论的愿望，请使用以下其中之一（删除任何 <mysql5 的任何评论）：
mysqldump ... | grep -v '^\/\*![0-4][0-9]\{4\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-4][0-9]\{4\}.*\/;$//g'
回答by Mahesh Velaga
Try --skip-comments?
试试--skip-comments？
Thanks
谢谢
Edit:
编辑：
I see .. Try this
我明白了..试试这个
--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset
Play around to remove some of the options till you get the desired result, basically this is same as --compactwithout --skip-comments
尝试删除一些选项，直到获得所需的结果，基本上这与--compact没有--skip-comments
--skip-commentsremoves the comments relating to version and stuff ..
--skip-comments删除与版本和内容相关的评论..
回答by Corey Ballou
回答by Ike Walker
Technically the lines you are trying to get rid of are not comments. They temporarily modify some variables at the beginning, and then reset them to the previous value at the end.
从技术上讲，您试图摆脱的行不是评论。他们在开始时临时修改了一些变量，然后在最后将它们重置为以前的值。
They're not very useful (but they're also harmless) in your case, since you're using --no-data, but I thought it worth mentioning that the lines do serve a purpose, and are not just comments.
在您的情况下，它们不是很有用（但它们也无害），因为您使用的是 --no-data，但我认为值得一提的是，这些行确实有用途，而不仅仅是评论。
回答by Gustavo
Those are not comments, the execution of that part of the scripts depends on the version of your mysql.
这些不是注释，该部分脚本的执行取决于您的 mysql 版本。
You can delete "the comment part", like
您可以删除“评论部分”，例如
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
to
到
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0
making the script more "comfortable" for reading.
使脚本更“舒适”阅读。
If you try to run a "comfortable" script in a version newer than the specified in the "comment", you will get an error.
如果您尝试在比“评论”中指定的版本更新的版本中运行“舒适”脚本，您将收到错误消息。
回答by jwg2s
If you've stumbled up on this answer trying to include your structure.sql file in git/github, you can strip out auto-increment with the following code right after you rake db:structure:dump
如果您偶然发现了这个答案，试图将您的 structure.sql 文件包含在 git/github 中，您可以在 rake db:structure:dump 后立即使用以下代码去除自动增量
# Remove beginning auto increments to prevent merge conflicts
filename = 'db/structure.sql'
File.atomic_write(filename) do |output|
File.open(filename, 'rb').each do |input|
output.write(input.gsub(/\s+AUTO_INCREMENT=\d+\s+/, ' '))
end
end
回答by MindStalker
Probably running a regex on it to remove lines that contain 40014 or 40111 etc.
可能在其上运行正则表达式以删除包含 40014 或 40111 等的行。
回答by Mark Byers
Since you are on Windows, if no-one finds a better solution then you could use a Python script instead:
由于您使用的是 Windows，如果没有人找到更好的解决方案，那么您可以改用 Python 脚本：
import re, sys
sql = sys.stdin.read()
regex = re.compile(r'/\*![^\n]* \*/;\n', re.M)
print regex.sub('', sql)
Usage from command line:
命令行中的用法：
python program.py < your.sql > output.sql
It removes all lines like this:
它删除所有这样的行：
/*!....... */;
回答by Daniel
It's really important to keep the conditional-execution comments. But if you absolutely knowthat the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this:
保留条件执行注释非常重要。但是，如果您绝对知道将加载转储的 MySQL 版本大于或等于创建它的版本，则可以删除“注释”部分：
sed -r s'#/\*![0-9]{5} ?([^*]*)\*/##'g
It will convert lines such as
它将转换诸如
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
to
到
SET SQL_MODE=@OLD_SQL_MODE ;
Because this line mustrun on any MySQL >= 4.1.1
因为这一行必须在任何 MySQL >= 4.1.1 上运行
Note that this will not remove multi-line conditional-execution comments, such as when dumping a trigger.
请注意，这不会删除多行条件执行注释，例如在转储触发器时。
Since it's impossible to predict the future, it's better to store the dump with the comments on, and only remove them when you want to visualize it.
由于无法预测未来，因此最好将带有评论的转储存储起来，并且仅在您想要对其进行可视化时才将其删除。
mysqldump ... > dump.sql
cat dump.sql | sed -E s'#/\*![0-9]{5} ?([^*]*)\*/##'g > dump.no-comments.sql
