如何将 SQL Server 数据库导出到 MySQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3917081/
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 SQL Server database to MySQL?
提问by matkins
I'm trying to convert from a SQL Server database backup file (.bak) to MySQL. This questionand answers have been very useful, and I have successfully imported the database, but am now stuck on exporting to MySQL.
我正在尝试从 SQL Server 数据库备份文件 ( .bak) 转换为 MySQL。这个问题和答案非常有用，我已经成功导入了数据库，但现在卡在导出到 MySQL 上。
The MySQL Migration Toolkitwas suggested, but seems to have been replaced by the MySQL Workbench. Is it possible to use the MySQL Workbench to migrate from SQL Server in the same way that the migration tool worked?
建议使用MySQL Migration Toolkit，但似乎已被MySQL Workbench取代。是否可以使用 MySQL Workbench 以与迁移工具相同的方式从 SQL Server 迁移？
Or is the Migration Toolkit still available somewhere?
或者迁移工具包是否仍然可用？
回答by Javier Cadiz
You can use MySQL Workbenchwhich provides a way to quickly migrate data and applications from Microsoft SQL Server to MySQL employing less time and effort.
您可以使用MySQL Workbench，它提供了一种以更少的时间和精力将数据和应用程序从 Microsoft SQL Server 快速迁移到 MySQL 的方法。
This tool has a lot of cool features like:
这个工具有很多很酷的功能，比如：
- Database migrations- enables migrations from Microsoft SQL Server, Sybase ASE and PostgreSQL.
- Migration project management- allows migrations to be configured, copied, edited, executed and scheduled.
- 数据库迁移- 支持从 Microsoft SQL Server、Sybase ASE 和 PostgreSQL 迁移。
- 迁移项目管理- 允许配置、复制、编辑、执行和计划迁移。
回答by Man_k
I use sqlyogto migrate from mssql to mysql. I tried Migration toolkit and workbenchbut liked sqlyog for its SJA. I could schedule the import process and could do incremental import using WHERE clause.
我使用sqlyog从 mssql 迁移到 mysql。我尝试了迁移工具包和工作台，但喜欢 sqlyog 的SJA。我可以安排导入过程，也可以使用 WHERE 子句进行增量导入。
回答by David d C e Freitas
PhpMyAdmin has a Import wizard that lets you import a MSSQL file type too.
PhpMyAdmin 有一个导入向导，可让您导入 MSSQL 文件类型。
See http://dev.mysql.com/doc/refman/5.1/en/sql-mode.htmlfor the types of DB scripts it supports.
有关它支持的数据库脚本类型，请参阅http://dev.mysql.com/doc/refman/5.1/en/sql-mode.html。
回答by Professor Falken
As mentioned above, if your data contains tab characters, commas, or newlines in your data then it's going to be very hard to export and import it with CSV. Values will overflow out of the fields and you will get errors. This problem is even worse if any of your long fields contain multi-line text with newline characters in them.
如上所述，如果您的数据中包含制表符、逗号或换行符，那么将很难用 CSV 导出和导入它。值将从字段中溢出，您将收到错误消息。如果您的任何长字段包含带有换行符的多行文本，这个问题就会更糟。
My method in these cases is to use the BCP command-line utility to export the data from SQL server, then use LOAD DATA INFILE .. INTO TABLE command in MySQL to read the data file back in. BCP is one of the oldest SQL Server command line utilities (dating back to the birth of SQL server - v6.5) but it is still around and still one of the easiest and most reliable ways to get data out.
在这些情况下，我的方法是使用 BCP 命令行实用程序从 SQL Server 导出数据，然后在 MySQL 中使用 LOAD DATA INFILE .. INTO TABLE 命令将数据文件读回。 BCP 是最古老的 SQL Server 之一命令行实用程序（可追溯到 SQL 服务器的诞生 - v6.5），但它仍然存在并且仍然是获取数据的最简单和最可靠的方法之一。
To use this technique you need to create each destination table with the same or equivalent schema in MySQL. I do that by right clicking the Database in SQL enterprise manager, then Tasks->Generate Scripts... and create a SQL script for all the tables. You must then convert the script to MySQL compatible SQL by hand (definitely the worst part of the job) and finally run the CREATE TABLE commands on the MySQL database so you have matching tables to the SQL server versions column-wise, empty and ready for data.
要使用此技术，您需要在 MySQL 中创建具有相同或等效架构的每个目标表。我通过右键单击 SQL 企业管理器中的数据库，然后单击任务-> 生成脚本... 并为所有表创建一个 SQL 脚本来做到这一点。然后，您必须手动将脚本转换为与 MySQL 兼容的 SQL（这绝对是工作中最糟糕的部分），最后在 MySQL 数据库上运行 CREATE TABLE 命令，以便您将表与 SQL 服务器版本逐列匹配，为空并准备好数据。
Then, export the data from the MS-SQL side as follows.
然后，从 MS-SQL 端导出数据，如下所示。
bcp DatabaseName..TableName out TableName.dat -q -c -T -S ServerName -r LOAD DATA INFILE '/tmp/TableName.dat' INTO TABLE TableName FIELDS TERMINATED BY '!\t!' LINES TERMINATED BY 'jdbc:jtds:sqlserver://"sql_server_ip_address":1433/<db_name>;Instance=<sqlserver_instanceName>;user=sa;password=PASSWORD;namedPipe=true;charset=utf-8;domain=
';
-t !\t!
(If you're using SQL Server Express, use a -S value like so: -S "ComputerName\SQLExpress")
（如果您使用的是 SQL Server Express，请使用 -S 值，如下所示：-S "ComputerName\SQLExpress"）
That will create a file named TableName.dat, with fields delimited by ![tab]! and rows delimited by \0 NUL characters.
这将创建一个名为 TableName.dat 的文件，其中的字段以 ![tab]! 和由 \0 NUL 字符分隔的行。
Now copy the .dat files into /tmp on the MySQL server and load on the MySQL side like so:
现在将 .dat 文件复制到 MySQL 服务器上的 /tmp 并像这样加载到 MySQL 端：
##代码##Don't forget that the tables (TableName in this example) must be created already on the MySQL side.
不要忘记表（在本例中为 TableName）必须已经在 MySQL 端创建。
This procedure is painfully manual when it comes to converting the SQL schema over, however it works for even the most difficult of data and because it uses flat files you never need to persuade SQL Server to talk to MySQL, or vice versa.
在转换 SQL 模式时，此过程是非常痛苦的手动操作，但它甚至适用于最困难的数据，并且因为它使用平面文件，您永远不需要说服 SQL Server 与 MySQL 对话，反之亦然。
回答by HimalayanCoder
if you have a MSSQL compatible SQL dump you can convert it to MySQL queries one by one using this online tool
如果您有 MSSQL 兼容的 SQL 转储，您可以使用此在线工具将其一一转换为 MySQL 查询
Hope it saved your time
希望它节省了您的时间
回答by Kevin
You can do this easily by using Data Loadertool. I have already done this before using this tool and found it good.
您可以使用Data Loader工具轻松完成此操作。我在使用这个工具之前已经这样做了，发现它很好。
回答by Martin
It looks like you correct: The Migration Toolkit is due to be integrated with MySQL Workbench - but I do not think this has been completed yet. See the End-of-life announcement for MySQL GUI Tools (which included the Migration Toolkit):
看起来您是对的：迁移工具包将与 MySQL Workbench 集成 - 但我认为这尚未完成。请参阅 MySQL GUI 工具（包括迁移工具包）的生命周期终止公告：
http://www.mysql.com/support/eol-notice.html
http://www.mysql.com/support/eol-notice.html
MySQL maintain archives of the MySQL GUI Tools packages:
MySQL 维护 MySQL GUI 工具包的档案：
回答by rajesh.sampangiramiaih
I used the below connection string on the Advanced tab of MySQL Migration Tool Kit to connect to SQL Server 2008 instance:
我在 MySQL Migration Tool Kit 的 Advanced 选项卡上使用以下连接字符串连接到 SQL Server 2008 实例：
##代码##Usually the parameter has "systemName\instanceName". But in the above, do not add"systemName\" (use only InstanceName).
通常参数有“systemName\instanceName”。但是在上面，不要添加“systemName\”（只使用InstanceName）。
To check what the instanceName should be, go to services.msc and check the DisplayName of the MSSQL instance. It shows similar to MSSQL$instanceName.
要检查 instanceName 应该是什么，请转到 services.msc 并检查 MSSQL 实例的 DisplayName。它显示类似于 MSSQL$instanceName。
Hope this help in MSSQL connectivity from mysql migration toolKit.
希望这有助于 mysql 迁移工具包的 MSSQL 连接。
回答by Matias P.
MySQL Migration Toolkit (1.1.10) still available for download from here:
MySQL 迁移工具包 (1.1.10) 仍可从此处下载：
http://downloads.mysql.com/archives.php?p=MySQLDeveloperSuite&v=1.1.10
http://downloads.mysql.com/archives.php?p=MySQLDeveloperSuite&v=1.1.10
回答by Marco C.
downloads are no more available on the official website (http://dev.mysql.com/downloads/gui-tools/5.0.html) instead, take a look here: http://download.softagency.net/MySQL/Downloads/MySQLGUITools/
官方网站（http://dev.mysql.com/downloads/gui-tools/5.0.html）上不再提供下载，请看这里：http: //download.softagency.net/MySQL/Downloads /MySQLGUI工具/
