将 MS SQL 存储过程移植到 MySQL
我有大约 140 - 150 个存储过程，需要从 MS SQL 2005 迁移到 MySQL 5.1
在过去八小时内成功移植其中 0 个之后，我决定顺道过来询问是否有人有任何经验、技巧或了解一些util 应用程序可以提供帮助。
MySQL 管理员不是很有帮助，会显示令人沮丧的“MySQL 错误号 1064 您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册以获取正确的语法”。鉴于 SP 的平均长度为 +100 行，简单地猜测问题是什么是不可行的。
例如。这就是我得到的确切错误：
Script line: 1 You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at line 5
然后在第 5 行我所得到的是：
declare PropID float;
i have about 140 - 150 stored procedures that I need to migrate from MS SQL 2005 to MySQL 5.1
After succesfully porting 0 of them in the last eight hours I decied to drop by and ask if anyone has any experience, or tips, or knows some util app that can help.
MySQL Administrator is not very helpfull, with a frustrating "MySQL Error number 1064 You have an error in your SQL Syntax; check the manual that corresponds to your MySQL server version for the right syntax". Given that an average SP is +100 lines long, simply GUESSING what the problem is is not an option.
Eg. This is the exact error that I get :
Script line: 1 You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at line 5
Then at line 5 all I have is:
declare PropID float;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Gartner Group 报告称，两种相似语言之间的手动转换速度约为每天 150 行。根据他们的说法，您的进度仅比平均水平低一点点 :-} 更糟糕的是，它预测您需要 150 个工作日的努力。
这就是人们构建自动迁移工具的原因，它可以以常规且值得信赖的方式应用转换规则。如果你幸运的话，有人在你之前就已经做到了这一点。人们可能想要执行的“从-到”转换对的数量是巨大的，比人们已完成的集合要大得多，所以通常你运气不好。
接下来有人会告诉我们“使用正则表达式”。这可能行不通，因为你必须找到编程语言结构，它不是“常规的”而是“上下文无关的”。
获得执行此操作的工具的一种方法是使用程序转换。这些是用一个代码片段替换另一个代码片段的编程操作。它们通常需要完整的语言解析器，以便操作可以像编译器一样对程序元素进行操作，而不是对文本字符串进行操作。
编写程序转换的一种好方法是使用语言的表面语法作为模式，本质上是“如果您看到这个，则将其替换为那个”。
将它们视为增强处理“上下文无关”问题的“正则表达式”。
一个可以接受这种表面语法程序转换的工具，
是我们的 DMS 软件再工程工具包。
编写可信的转换代码需要付出一些努力。您有大约 15,000 行代码需要转换。当要转换的代码量如此之少时，在对转换进行编码与简单地硬着头皮完成工作之间进行权衡是很困难的。
Gartner Group reports that manual conversion between two similar langauges occur at the rate of about 150 lines per day. According to them, your rate of progress is just a little bit lower than average :-} Worse, it predicts you have 150 man-days of effort in front of you.
This is the reason that people build automated migration tools, that can apply conversion rules in a regular and trustworthy way. If you are lucky, somebody has done this before you. The number of pairs of from-to conversions people might want to do is huge, much larger than the set people have done, so typically you're out of luck.
The next thing somebody will tell is "use regular expressions". That probably won't work, because you have to find programmiong language constructs, which are not "regular" but rather "context free".
One way to get a tool to do this is use program transformations. These are programmed actions that replace one code fragment by another. They typically require a full language parser so that actions can operate on program elements like compilers do, rather than on text strings.
A nice way to write program transformations uses the surface syntax of the language as patterns, essentially of "if you see this, replace it by that".
Think of these as "regular expressions" beefed up to handle "context free" issues.
A tool that can accept such surface-syntax program transformations,
is our DMS Software Reengineering Toolkit.
It takes some effort to code trustworthy transformations. You have some 15,000 lines of code to convert. The tradeoff between coding the transformations vs. simply biting the bullet and doing the work is tough when the amount of code to transform is this small.
您可以尝试 MySQL 查询浏览器而不是 MySQL 管理员：
You might try MySQL Query Browser instead of MySQL administrator:
您正在从一个功能强大的工具移植到功能较弱的工具。如果旧程序使用 MySQL 不支持的功能，则移植几乎不可能。
可能有帮助的一件事是大多数 SQL Server 过程都实现某种业务逻辑。好消息是，通常只使用其中的一小部分。过滤掉未使用的过程可以为您节省大量工作。
坏消息是业务程序在移植后必须进行测试；即使是最好的程序员也无法用不同的语言重写它们并确保它们能够工作。这意味着大量的测试工作，以及移植程序投入生产后的大量支持工作。
You're porting from a powerful tool to a less powerful tool. If the old procedures use features that MySQL does not support, this can make porting practically impossible.
One thing that might help is that most SQL Server procedures implement business logic of some sort. The good news is that often only a small part of them is used. Filtering out unused procedures can save you a lot of work.
The bad news is that business procedures have to be tested after porting; not even the best programmer can rewrite them in a different language and be sure that they work. This means a lot of testing effort, and a lot of support work after the ported procedures have gone into production.