导入非常大的 SQL 文件 (MySQL) 时的单次提交
我正在尝试将大型 SQL 文件 (> 2.5 Gb) 还原到 Windows 上的 MySQL 数据库中.
I'm trying to restore large SQL files (> 2.5 Gb) into a MySQL database on Windows.
我无法编辑这些文件以在文件开头添加 SET autocommit=0; 之类的文本(这是缩短导入时间所必需的).
I'm not able to edit these files to add text like SET autocommit=0; at the beginning of the file (which is necessary to improve the import time).
我也无法使用 source，因为它会输出到屏幕(非常慢)，即使文件中有任何错误，执行也会继续.例如:
I'm also not able to use source, as this outputs to the screen (which is very slow) and the execution continues even if there are any errors in the file. E.g.:
mysql> CREATE DATABASE IF NOT EXISTS dbname;
mysql> USE dbname;
mysql> SET autocommit=0;
mysql> source file.sql;
mysql> COMMIT;
是否可以在导入仅适用于当前会话的 SQL 文件之前和之后运行任意命令?我已经尝试了以下两种方法，但都不能在 Windows 上运行(在这两种情况下都忽略了第二个操作):
Is it possible to run arbitrary commands before and after importing an SQL file that apply only to the current session? I've tried both of the following, and neither work on Windows (in both cases the second operation is ignored):
mysql -u username -p -e "SET autocommit=0;" dbname < file.sql
或者，
mysql -u username -p < initial_commands.sql < file.sql
如果可能的话，我不想每次执行此操作时都更改全局 autocommit 设置，然后必须记住将其更改回来(我也不确定如果没有最后的 COMMIT;).
If possible I don't want to change the global autocommit setting each time I do this, and then have to remember to change it back (also I'm not sure that this will work without the final COMMIT;).
也许有一种方法可以使用 BEGIN ... COMMIT; 而不是关闭自动提交?
Perhaps there is a way to use BEGIN ... COMMIT; instead of turning off autocommit?
我会很高兴收到必须做这种事情的人的任何建议！
I'd be happy with any suggestions from people who have to do this kind of thing!
推荐答案
(echo set autocommit=0; && type file.sql && echo. && echo commit;) | mysql -u username -p passwd
使用管道代替输入重定向，echo 用于命令，echo. 用于换行
Just use pipes instead of input redirection, use echo for the commands, and echo. for newlines
相关文章