在语句边界上分割一个大的sql文件？
我需要将大型 SQL 表从 Oracle 数据库导入到 mySQL 数据库。我使用 RazorSQL“转储”了 Oracle 表，这样我现在就有了一个大型 (2.5 GB) .sql 文件，其中包含许多 INSERT 语句。
该文件太大，我想将其拆分，以便分块导入。分割这样一个文件的聪明方法是什么？
split命令可以工作，除非某些字符串值包含换行符，因此split在语句中分割的风险很高。使用正则表达式在“;\n”（语句结束）上进行分割的 Perl 快速工具可以工作，但在数据中找到这样的字符序列的可能性仍然很小。
当然，我总是可以编写一个解析器来理解字符串引用。我想知道是否有更聪明的东西？
类似的问题也被问过。例如：
但唯一的分割解决方案是对某些 Windows 软件的建议。 Windows 不适合我。我运行 Unix，即 Mac OS X。
为了提供一些背景知识，我想要这样做的原因是在将文件导入 mySQL 时出现错误。定位错误是很痛苦的，因为它非常耗时，而且对于大多数文本编辑器来说，打开如此大的文本文件进行编辑充其量也不是微不足道的。我的一些 SQL 工具甚至不报告文件中错误的位置，这强烈建议采用二分法通过分割次数来归零错误。自动化这一点将会很有用。
谢谢你的任何想法。
I need to import a large SQL table from an Oracle database to a mySQL database. I "dumped" the Oracle table using RazorSQL so that I now have a large (2.5 GB) .sql file, with many INSERT statements.
This file is too big and I want to split it so that I import it in chunks. What would be a clever way to split such a file?
using the
splitcommand would work except some string values contains newlines characters so that there is a high risk ofsplitsplitting mid-statement.a Perl quickie using a regexp to split on ";\n" (end of statement) would work, but there is still the remote possibility that such a character sequence could be found in the data.
Of course I can always write a parser to understand string quoting. I am wondering though whether there is anything smarter?
Similar questions have been asked. eg:
How do I split the output from mysqldump into smaller files?
But the only splitting solution has been a suggestion for some Windows software. Windows is not an option for me. I run Unix, namely Mac OS X.
To give some background, the reason I want to do that is that I have an error when importing the file into mySQL. Localizing the error is painful if only because it's time consuming, and because opening such large text files for editing is non trivial at best with most text editors. Some of my SQL tools don't even report the location of the error in the file, which would strongly suggest a dichotomy approach to zeroing on to the error by splitting number of times. Automatizing this would be useful.
Thanks for any idea.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不完全是您问题的答案，但也许是您问题的解决方案：
安装 Navicat (MySQL GUI)。它有一个 oracle=>mysql 迁移工具，可能会帮助您，因为您遇到的错误很可能来自语法差异。手动将一种 SQL 方言转换为另一种 SQL 方言将是一项艰巨的任务。
Not exactly an answer to your question, but maybe a solution to your problem:
Install Navicat (MySQL GUI). It has an oracle=>mysql migration tool, that might help you, because, most likely, errors you get come from syntax differences. It will be a hard task to convert one SQL dialect to another by hand.