MS SQL - 旧版 Web 应用程序中的 MySQL 迁移
我希望将旧版 Web 应用程序的数据库从 SQL Server 迁移到 MySQL。 我必须注意 MySQL 的哪些限制？ 在开始实际修改代码之前，哪些项目将成为综合清单的一部分？
I wish to migrate the database of a legacy web app from SQL Server to MySQL. What are the limitations of MySQL that I must look out for ? And what all items would be part of a comprehensive checklist before jumping into actually modifying the code ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我要检查的第一件事是数据类型 - 数据类型的确切定义因数据库而异。 我将创建一个映射列表，告诉我将每种数据类型映射到什么。 这将有助于构建新表。 我还会检查现在未使用的数据表或列。 迁移它们没有意义。 对函数、作业、sps 等执行同样的操作。现在是清理垃圾的时候了。
您如何通过 sps 或动态查询从数据库访问数据？ 通过在新的开发数据库上运行每个查询来检查每个查询，并确保它们仍然有效。 这两种 SQl 的工作方式也存在差异。 我没有使用过我的 sql，所以我不确定一些常见的故障点是什么。 当您执行此操作时，您可能想要对新查询进行计时并查看它们是否可以优化。 优化也因数据库而异，当您进行优化时，现在可能存在一些性能不佳的查询，您可以在迁移过程中修复这些查询。
用户定义的函数也需要考虑。 如果您正在这样做，请不要忘记这些。
不要忘记计划的作业，这些作业也需要在 myslq 中检查和重新创建。
您是否定期导入任何数据？ 所有导入都必须重写。
一切的关键是使用测试数据库并测试、测试、测试。 测试所有内容，尤其是季度或年度报告或您可能忘记的工作。
您想做的另一件事是通过版本控制的脚本来完成所有操作。 在可以在开发环境中按顺序运行所有脚本且不会出现任何故障之前，请勿转移到生产环境。
First thing I would check is the data types - the exact definition of datatypes varies from database to database. I would create a mapping list that tellme what to map each of the datatypes to. That will help in building the new tables. I would also check for data tables or columns that are not being used now. No point in migrating them. Do the same with functions, job, sps, etc. Now is the time to clean out the junk.
How are you accessing the data through sps or dynamic queries from the database? Check each query by running it aganst a new dev database and make sure they still work. Again there are differences between how the two flavors of SQl work. I've not used my sql so I'm not sure what some of the common failure points are. While you are at it you might want to time new queries and see if they can be optimized. Optimization also varies from database to database and while you are at it, there are probably some poorly performing queries right now that you can fix as part of the migration.
User defined functions will need to be looked at as well. Don't forget these if you are doing this.
Don't forget scheduled jobs, these will need to be checkd and recreated in myslq as well.
Are you importing any data ona regular schedule? All imports will have to be rewritten.
Key to everything is to use a test database and test, test, test. Test everything especially quarterly or annual reports or jobs that you might forget.
Another thing you want to do is do everything through scripts that are version controlled. Do not move to production until you can run all the scripts in order on dev with no failures.
我忘记了一件事，确保您正在运行迁移的开发数据库（sql server 数据库）在每次测试运行之前立即从生产环境中更新。 讨厌因为您正在针对过时的记录进行测试而导致产品失败。
One thing I forgot, make sure the dev database you are running the migration from (the sql server database) is updated from production immediately before each test run. Hate to have something fail on prod because you were testing against outdated records.
您的客户端代码几乎肯定是需要修改的最复杂的部分。 除非您的应用程序具有非常高质量的测试套件，否则您最终将不得不进行大量测试。 您不能依赖任何功能相同的东西，即使是您可能期望的东西。
是的，数据库本身的内容需要改变，但客户端代码是主要操作所在，它将需要大量的工作和严格的测试。
忘记迁移数据吧，这是您最不应该考虑的事情； 数据库模式可能可以轻松转换； 其他数据库对象（SP、视图等）可能会导致问题，但客户端代码才是问题的焦点。
几乎每个执行数据库查询的例程都需要更改，但绝对所有这些例程都需要进行测试。 这将是非常重要的。
我目前正在考虑将应用程序的主数据库从 MySQL 4.1 迁移到 MySQL 5，虽然差别不大，但它仍然是一项非常非常大的任务。
Your client code is almost certain to be the most complex part to modify. Unless your application has a very high quality test suite, you will end up having to do a lot of testing. You can't rely on anything working the same, even things which you might expect to.
Yes, things in the database itself will need to change, but the client code is where the main action is, it will need heaps of work and rigorous testing.
Forget migrating the data, that is the last thing which should be on your mind; the database schema can probably be converted without too much difficulty; other database objects (SPs, views etc) could cause issues, but the client code is where the focus of the problems will be.
Almost every routine which executes a database query will need to be changed, but absolutely all of them will need to be tested. This will be nontrivial.
I am currently looking at migrating our application's main database from MySQL 4.1 to 5, that is much less of a difference, but it will still be a very, very large task.