如何在MySQL转储中删除这些注释?

I'm trying to create a simple structure only dump of my database. Using mysqldump gives 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-tzutc—skip-set- set-charset - h127.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转储10.13分发版5.1.41，用于Win32 (ia32)

14 个解决方案

#1

128

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 Syntax section of the manual.

这个神奇的注释语法在手册的注释语法一节中进行了说明。

You probably don't want to get rid of this stuff.

你可能不想摆脱这些东西。

#2

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'

#3

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 --compact without --skip-comments

试着删除一些选项，直到得到想要的结果，基本上这和——不带跳投注释的紧凑式是一样的

--skip-comments removes the comments relating to version and stuff ..

-skip-comments删除与版本等相关的注释。

#4

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.

它们在您的例子中并不是很有用(但它们也是无害的)，因为您正在使用——无数据，但是我认为值得一提的是，这些行确实有目的，而且不仅仅是注释。

#5

Have you tried the shortcut option --compact?

你试过快捷方式——紧凑吗?

Information here.

这里的信息。

#6

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 */

来

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.

如果您试图在比“注释”中指定的更新的版本中运行“舒适”脚本，您将会得到一个错误。

#7

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:

它删除了所有这样的线条:

/*!....... */;

#8

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

如果你发现了这个答案，试着把你的结构包括进去。在git/github上的sql文件中，您可以在耙完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

#9

Another option, if you're using vi/vim and need a quick-and-dirty way to preserve the statement contents but nuke the parenthetical comment:

另一种选择是，如果你使用vi/vim，并且需要一种快速而肮脏的方式来保存语句内容，但是用nuke这个插入语注释:

: %s/\/\*!\d\+\s//g
: %s/\*\///g

(Only safe on mysqldumps, since it nukes the ending */comment mark, but human-readable comments, at least as of 5.5, use line-comment syntax --).

(仅在mysqldumps中是安全的，因为它使用了结尾的*/注释标记，但是人类可读的注释，至少在5.5中使用了行注释语法—)。

#10

I made this script to normalize the dump, including removing conditional comments: https://github.com/luissquall/dbdump.

我使用这个脚本对转储进行规范化，包括删除条件注释:https://github.com/luissquall/dbdump。

You just have to:

你只需要:

npm install -g @luissquall/dbdump

# Redirect output to a file
dbdump -u user -p -d database > struct.sql

#11

-1

Probably running a regex on it to remove lines that contain 40014 or 40111 etc.

可能在上面运行一个regex来删除包含40014或40111等内容的行。

#12

-1

Not a direct answer - but I have ditched straight mysqldump entirely for mk-parallel-dump - it's faster (spawns multiple processes) and depending on what you are going to do with the dump output, more flexible as it effectively encapsulates mysqldump and 'select into outfile' syntax together.

这不是一个直接的答案——但我已经完全抛弃了直接的mysqldump，转而使用mk-parallel-dump——它更快(生成多个进程)，而且取决于您将如何处理转储输出，它更灵活，因为它有效地封装了mysqldump和“select into outfile”语法。

#13

-1

As @Ollie and a few others pointed out, these are are conditional-execution tokens written in comment style but served a purpose. Without them, you may run into issues of recreating tables with heavily enforced foreign key constraint. For instance, table A has FK for table B and thus table A cannot be created until table B do and so on so forth. Without disabling the key checks, you may never be able to recreate them depending how your table order is fined.

正如@Ollie和其他一些人指出的，这些都是用注释风格编写的条件执行令牌，但有一定的用途。如果没有它们，您可能会遇到重新创建具有强强制外键约束的表的问题。例如，表A为表B提供了FK，因此表A在表B为表B时才能创建，以此类推。如果不禁用密钥检查，您可能永远无法重新创建它们，这取决于您的表订单是如何被罚款的。

#14

-2

I dont know if it is what are you looking for, i simply wanted to get rid of all the mysql comments stuff to be able to use a syntax highlighter, i used a simple regex and replace all with the following "/\*![0-9]{5}|\*/" and voila! nice colors in the code ;)

我不知道这是不是你要找的，我只是想去掉所有mysql注释，以便使用语法高亮显示，我使用了一个简单的regex，并用下面的“/\*!”[0 - 9]{ 5 } | \ * /”瞧!代码中的漂亮颜色;)

#1

128