如何将数据从 SQL Server 2005 导出到 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export data from SQL Server 2005 to MySQL
提问by Mat
I've been banging my head against SQL Server 2005trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated, so you can't deal with any row that has a string with a comma in it (or whatever you use as a delimiter) and I would still have to hand write all of the create table statements, as obviously CSV doesn't tell you anything about the data types.
我一直在反对SQL Server 2005试图获取大量数据。我得到了一个包含近 300 个表的数据库，我需要将其转换为 MySQL 数据库。我的第一个调用是使用 bcp 但不幸的是它不会产生有效的 CSV - 字符串没有被封装，所以你不能处理任何包含逗号的字符串的行（或者任何你用作分隔符的行）而且我仍然需要手写所有的创建表语句，因为显然 CSV 不会告诉您有关数据类型的任何信息。
What would be better is if there was some tool that could connect to both SQL Server and MySQL, then do a copy. You lose views, stored procedures, trigger, etc, but it isn't hard to copy a table that only uses base types from one DB to another... is it?
如果有一些工具可以同时连接到 SQL Server 和 MySQL，那就更好了，然后复制一份。您丢失了视图、存储过程、触发器等，但将仅使用基本类型的表从一个数据库复制到另一个数据库并不难……是吗？
Does anybody know of such a tool? I don't mind how many assumptions it makes or what simplifications occur, as long as it supports integer, float, datetime and string. I have to do a lot of pruning, normalising, etc. anyway so I don't care about keeping keys, relationships or anything like that, but I need the initial set of data in fast!
有人知道这样的工具吗？我不介意它做了多少假设或发生了什么简化，只要它支持整数、浮点数、日期时间和字符串。无论如何，我必须进行大量修剪、规范化等操作，因此我不在乎保留密钥、关系或类似内容，但我需要快速获取初始数据集！
回答by Nick Berardi
The best way that I have found is the MySQL Migration Toolkitprovided by MySQL. I have used it successfully for some large migration projects.
我发现的最好方法是MySQL提供的MySQL Migration Toolkit。我已经成功地将它用于一些大型迁移项目。
回答by Sean
SQL Server 2005 "Standard", "Developer" and "Enterprise" editions have SSIS, which replaced DTS from Sql server 2000. SSIS has a built in connection to it's own DB, and you can find a connection that someone else has written for MySQL. Hereis one example. Once you have your connections, you should be able to create an SSIS package that moves data between the two.
SQL Server 2005 "Standard", "Developer" 和 "Enterprise" 版本有SSIS，它取代了 Sql server 2000 中的 DTS。SSIS 有一个内置的连接到它自己的数据库，你可以找到其他人为 MySQL 编写的连接. 这是一个例子。建立连接后，您应该能够创建在两者之间移动数据的 SSIS 包。
I have not had to move data from SQlServer to MySQL, but I imagine that once the MySQl connection is installed, it works the same as moving data between two SQLServer DBs, which is pretty straight forward.
我不必将数据从 SQlServer 移动到 MySQL，但我想一旦安装了 MySQl 连接，它的工作方式与在两个 SQLServer 数据库之间移动数据的工作方式相同，这非常简单。
回答by Sean
Using MSSQL Management Studio i've transitioned tables with the MySQL OLE DB. Right click on your database and go to "Tasks->Export Data" from there you can specify a MsSQL OLE DB source, the MySQL OLE DB source and create the column mappings between the two data sources.
使用 MSSQL Management Studio，我已经使用 MySQL OLE DB 转换了表。右键单击您的数据库并转到“任务-> 导出数据”，您可以从那里指定 MsSQL OLE DB 源、MySQL OLE DB 源并创建两个数据源之间的列映射。
You'll most likely want to setup the database and tables in advance on the MySQL destination (the export will want to create the tables automatically, but this often results in failure). You can quickly create the tables in MySQL using the "Tasks->Generate Scripts" by right clicking on the database. Once your creation scripts are generated you'll need to step through and search/replace for keywords and types that exist in MSSQL to MYSQL.
您很可能希望在 MySQL 目标上预先设置数据库和表（导出将希望自动创建表，但这通常会导致失败）。您可以通过右键单击数据库，使用“任务->生成脚本”在 MySQL 中快速创建表。生成创建脚本后，您需要逐步搜索/替换 MSSQL 中存在的关键字和类型到 MYSQL。
Of course you could also backup the database like normal and find a utility which will restore the MSSQL backup on MYSQL. I'm not sure if one exists however.
当然，您也可以像往常一样备份数据库，并找到一个实用程序来恢复 MYSQL 上的 MSSQL 备份。但是，我不确定是否存在。
回答by pix0r
Rolling your own PHP solution will certainly work though I'm not sure if there is a good way to automatically duplicate the schema from one DB to the other (maybe this was your question).
滚动您自己的 PHP 解决方案肯定会起作用，但我不确定是否有一种好方法可以将模式从一个数据库自动复制到另一个数据库（也许这是您的问题）。
If you are just copying data, and/or you need custom code anyway to convert between modified schemas between the two DB's, I would recommend using PHP 5.2+ and the PDO libraries. You'll be able to connect using PDO ODBC (and use MSSQL drivers). I had a lot of problems getting large text fields and multi-byte characters from MSSQL into PHP using other libraries.
如果您只是复制数据，和/或无论如何您需要自定义代码在两个数据库之间的修改模式之间进行转换，我建议使用 PHP 5.2+ 和 PDO 库。您将能够使用 PDO ODBC（并使用 MSSQL 驱动程序）进行连接。我在使用其他库将大文本字段和多字节字符从 MSSQL 导入 PHP 时遇到了很多问题。
回答by Dillie-O
Another tool to try would be the SQLMaestro suite - http://www.sqlmaestro.comIt is a little tricky nailing down the precise tool, but they have a variety of tools, both free and for purchase that handle a wide variety of tasks for multiple database platforms. I'd suggest trying the Data Wizard tool first for MySQL, since I believe that will have the proper "import" tool you need.
另一个可以尝试的工具是 SQLMaestro 套件 - http://www.sqlmaestro.com确定精确的工具有点棘手，但他们有各种各样的工具，免费的和购买的，可以处理各种各样的任务适用于多个数据库平台。我建议首先为 MySQL 尝试 Data Wizard 工具，因为我相信它会有你需要的合适的“导入”工具。
