数据库迁移是如何完成的？
我记得在我之前的工作中，我需要进行数据迁移。在这种情况下，我需要迁移到一个新的系统，我要开发，所以它有一个不同的表模式。我认为第一，我应该知道：
一般来说，数据如何（使用相同的模式）迁移到不同的数据库引擎。例如。 MySQL-> MSSQL。就我而言，我的目标数据库是 MySQL，并且我
我想，在企业应用程序中，可能还有存储过程、触发器也需要导入。
如果表架构不同，我将如何执行此操作？在我之前的工作中，我所做的是将数据（在我的例子中，从 Access）导入到我的目标（MySQL）中，留下表结构。然后使用 SQL 选择数据并根据需要进行操作到最终目标表中。
就我而言，我没有旧数据库的文档，并且列的命名不正确，例如。它使用“field1”、“field2”等。我需要从应用程序代码中跟踪列的含义。还有更好的办法吗？或者有时，列在分隔数据中包含多个值，读取代码是唯一的方法吗？
i remember in my previous job, i needed to do data migration. in that case, i needed to migrate to a new system, i was to develop, so it has a different table schema. i think 1st, i should know:
in general, how is data migrated (with the same schema) to a different DB engine. eg. MySQL -> MSSQL. in my case, my destination DB was MySQL and i used MySQL Migration Toolkit
i am thinking, in an enterprise app, there may be stored procedures, triggers that also need to be imported.
if table schema is different, how will i then go abt doing this? in my prev job, what i did was import data (in my case, from Access) into my destination (MySQL) leaving table structures. then use SQL to select data and manipulate as required into final destination tables.
in my case, where i dont have documentation for the old db, and the columns was not named correctly, eg. it uses say 'field1', 'field2' etc. i needed to trace from the application code what the columns mean. any better way? or sometimes, columns contain multiple values in delimited data, is reading code the only way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我真的取决于，但从你的问题来看，我假设你想听听其他人在做什么。
这就是我在当前项目中所做的事情。
我必须从 Oracle 迁移到 Oracle，但迁移到完全不同的模式。
旧系统是 2 层（旧客户端、旧数据库），新系统是 3 层（新客户端、业务逻辑、新数据库）。新架构中有 600 多个表。
经过深思熟虑，我们放弃了用 SQL 从旧数据库迁移到新数据库的想法。我们决定，在我们的例子中，我会更容易去：
旧数据库 ->老客户->业务逻辑->新数据库在旧数据库中，许多数据以奇怪的方式存储，并且旧客户端
以复杂的方式破坏它。我们可以访问旧客户端的源代码，但它是一个非常大的系统。
我们编写了一个位于旧客户端和业务逻辑之上的迁移工具。
我们之前有一些 SQL，之后有一些 SQL，但大部分数据是通过
老客户端和业务逻辑。
缺点是速度很慢，在我们的例子中，完整的迁移需要 190 多个小时，但除此之外它运行良好。
更新
就存储过程和触发器而言：
即使我们在新旧系统（均为 Oracle）中使用相同的 DBMS，程序和
触发器是为新系统从头开始编写的。
I really depends, but from your question I assume you want to hear what other people do.
So here is what I do in my current project.
I have to migrate from Oracle to Oracle but to a completely different schema.
The old system was 2-tier (old client, old database) the new system is 3-tier (new client, business logic, new database). We have more than 600 tables in the new schema.
After much pondering we scraped the idea of doing a migration from old database to new database in SQL. We decided that in our case i would be much easier to go:
old database -> old client -> business logic -> new databaseIn the old database much of the data is stored in strange ways and the old client
mangles it in complex ways. We have access to the source code of the old client but it is a very large system.
We wrote a migration tool that sits above the old client and the business logic.
We have some SQL before and some SQL after that but the bulk of data is migrated via
old client and business logic.
The downside is that it is slow, a complete migration taking more than 190 hours in our case but otherwise it works well.
UPDATE
As far as stored procedures and triggers are concerned:
Even as we use the same DBMS in old and new system (both Oracle) the procedures and
triggers are written from scratch for the new system.
当我执行数据库迁移时，我使用该应用程序而不是通用工具来迁移数据库。该应用程序连接到两个数据库并将对象从一个数据库复制到另一个数据库。您不必担心架构或权限或诸如此类的问题，因为所有这些都在应用程序中处理，就像您首先设置应用程序时发生的情况一样。
当然，如果您的应用程序不支持这一点，这可能对您没有帮助。但如果您正在编写应用程序，我强烈建议您这样做。
When I've performed database migrations, I've used the application instead a general tool to migrate the database. The application connects to two databases and copies objects from one to the other. You don't have to worry about schema or permissions or whatnot since all that is handled in the application, just like what happens when you set up the application in the first place.
Of course, this may not help you if your application doesn't support this. But if you're writing an application, I strongly recommend doing it this way.
我推荐 wikipedia 文章，以获得良好的概述和主要商业工具的链接（以及一些非-商业的）。存储过程（以及类似的，例如用户定义的函数），如果丰富的话，将成为迁移中的“热点”，需要罕见且昂贵的人类技能——一旦你摆脱了“声明性”情绪对于主流 SQL 和程序代码，您不能指望自动化工具能够出色地完成工作（图灵定理表明，在足够普遍的情况下，它们实际上做不到；-）。因此，您需要工程师充分了解这两种引擎的程序陷阱——您要迁移的引擎，以及您要迁移到的引擎。你可以买它——这是顾问赚大钱的利基市场之一！-)
I recommend the wikipedia article for a good overview and links to the main commercial tools (and some non-commercial ones). Stored procedures (and kin, e.g. user-defined function), if abundant, are going to be the "hot spots" in the migration, requiring rare abd costly human skills -- as soon as you get away from the "declarative" mood of mainstream SQL, and into procedural code, you cannot expect automated tools to do a decent job (Turing's Theorem says that they actually can't, in a sufficiently general case;-). So, you need engineers with a good understanding of the procedural trappings of BOTH engines -- the one you're migrating from, the one you're migrating to. You can buy that -- it's one of the niches where consultants make REALLY good money!-)
如果您使用的是 MS SQL Server，则可以使用 SSMS 一次性编写架构和所有数据的脚本：SQL Server 2008：将数据脚本编写为插入。
如果您没有使用任何/许多非标准 SQL 构造，那么您也许可以手动编辑此脚本，而无需付出太多努力。
If you are using MS SQL Server, you can use SSMS to script out the schema and all data in one go: SQL Server 2008: Script Data as Inserts.
If you are not using any/many non-standard SQL constructs, then you might be able to manually edit this scipt without too much effort.