如何在 MySQL 转储中消除这些注释？

我试图创建一个简单的结构只转储我的数据库。使用 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 */;

不管我怎么努力，我就是无法摆脱那些评论。

我现在使用的是: mysqldump -p -d --add-drop-table --skip-tz-utc --skip-set-charset -h 127.0.0.1 -u foo bar --result-file=dumpfile.sql

编辑: 但是我希望保留其他评论，比如 -- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)

56063 次浏览
小开

Have you tried the shortcut option --compact?

Information here.

小开

Try --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 ..

小开

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

小开

Since you are on Windows, if no-one finds a better solution then you could use a Python script instead:

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:

/*!....... */;
小开

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.

小开

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.

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.

小开

Those are not comments, the execution of that part of the scripts depends on the version of your 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.

小开

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 ... | 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):

mysqldump ... | grep -v '^\/\*![0-4][0-9]\{4\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-4][0-9]\{4\}.*\/;$//g'
小开

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

# 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
小开

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 ;)

小开

I made this script to normalize the dump, including removing conditional comments: 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
小开

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.

小开

Use --dump-date=FALSE

Does exactly what OP asks for. (not exactly, I see)

Source: mysqldump option summary

Edit: Just after a minute I realized, this is what me was looking for not the OP, but leaving here... in hope someone can use it: This date line which ruins source control, because it always a change...

小开

It's really important to keep the conditional-execution comments. But if you absolutely know that 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:

sed -r  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g

It will convert lines such as

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

to 

SET SQL_MODE=@OLD_SQL_MODE ;

Because this line must run on any 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} ?([^*]*)\*/#\1#'g > dump.no-comments.sql

提交答案