mysql to sql server_将 MySQL 数据库转换 (MySQLToSQL) - SQL Server | Microsoft Docs

转换 MySQL 数据库 (MySQLToSQL)Converting MySQL Databases (MySQLToSQL)

01/19/2017

本文内容

连接到 MySQL、连接到 SQL ServerSQL Server 或 SQL Azure，并设置项目和数据映射选项后，可以将 MySQL 数据库对象转换为 SQL ServerSQL Server 或 Azure SQL 数据库对象。After you have connected to MySQL, connected to SQL ServerSQL Server or SQL Azure, and set project and data mapping options, you can convert MySQL database objects to SQL ServerSQL Server or Azure SQL Database objects.

转换过程The Conversion Process

转换数据库对象将获取 MySQL 中的对象定义，然后将其转换为类似 SQL ServerSQL Server 或 SQL Azure 的对象，然后将此信息加载到 SSMA 元数据。Converting database objects takes the object definitions from MySQL, converts them to similar SQL ServerSQL Server or SQL Azure objects, and then loads this information into the SSMA metadata. 它不会将信息加载到的实例中 SQL ServerSQL Server 。It does not load the information into the instance of SQL ServerSQL Server. 然后，可以使用 SQL ServerSQL Server 或 SQL Azure 元数据资源管理器查看对象及其属性。You can then view the objects and their properties by using the SQL ServerSQL Server or SQL Azure Metadata Explorer.

在转换过程中，SSMA 会将输出消息打印到 "输出" 窗格，并将错误消息打印到 "错误列表" 窗格。During the conversion, SSMA prints output messages to the Output pane and error messages to the Error List pane. 使用输出和错误信息来确定是否必须修改 MySQL 数据库或转换过程以获取所需的转换结果。Use the output and error information to determine whether you have to modify your MySQL databases or your conversion process to obtain the desired conversion results.

设置转换选项Setting Conversion Options

转换对象之前，请在 "项目设置" 对话框中查看项目转换选项。Before converting objects, review the project conversion options in the Project Settings dialog box. 使用此对话框，可以设置 SSMA 转换表和索引的方式。By using this dialog box, you can set how SSMA converts tables and indexes.

转换结果Conversion Results

下表显示了转换哪些 MySQL 对象以及生成的 SQL ServerSQL Server 对象：The following table shows which MySQL objects are converted, and the resulting SQL ServerSQL Server objects:

MySQL 对象MySQL Objects

生成的 SQL Server 对象Resulting SQL Server Objects

具有依赖对象(如索引)的表Tables with dependent objects such as indexes

SSMA 创建具有依赖对象的表。SSMA creates tables with dependent objects. 表将与所有索引和约束一起转换。Table is converted with all indexes and constraints. 索引将转换为单独 SQL ServerSQL Server 的对象。Indexes are converted into separate SQL ServerSQL Server objects.

空间数据类型映射只能在表节点级执行。Spatial data type mapping can be performed only at table node level.

有关表转换设置的详细信息，请参阅转换设置For more information on the Table Conversion settings, see Conversion Settings

函数Functions

如果该函数可以直接转换为 Transact-sql，则 SSMA 将创建一个函数。If the function can be directly converted to Transact-SQL, SSMA creates a function. 在某些情况下，该函数必须转换为存储过程。In some cases, the function must be converted to a stored procedure. 可以通过在项目设置中使用函数转换来完成此操作。This can be done by using Function Conversion in Project Settings. 在这种情况下，SSMA 创建存储过程和调用存储过程的函数。In this case, SSMA creates a stored procedure and a function that calls the stored procedure.

提供的选项：Choices Given:

根据项目设置进行转换Convert according to project settings

转换为函数Convert to function

转换为存储过程Convert to stored procedure

有关函数转换设置的详细信息，请参阅转换设置For more information on Function Conversion settings, see Conversion Settings

过程Procedures

如果可以将该过程直接转换为 Transact-sql，SSMA 会创建一个存储过程。If the procedure can be directly converted to Transact-SQL, SSMA creates a stored procedure. 在某些情况下，必须在自治事务中调用存储过程。In some cases a stored procedure must be called in an autonomous transaction. 在这种情况下，SSMA 创建两个存储过程：一个用于实现过程，另一个用于调用实现存储过程。In this case, SSMA creates two stored procedures: one that implements the procedure, and another that is used for calling the implementing stored procedure.

数据库转换Database Conversion

作为 MySQL 对象的数据库不是由 SSMA for MySQL 直接转换而成。Databases as MySQL objects are not directly converted by SSMA for MySQL. MySQL 数据库处理起来更像架构名称，并且在转换过程中所有物理参数都将丢失。MySQL databases are treated more like a schema names and all the physical parameters are lost during conversion. SSMA for MySQL 使用映射 mysql SQL Server 数据库 (MySQLToSQL)将对象从 MySQL 数据库映射到适当的 SQL Server 数据库/架构对。SSMA for MySQL uses Mapping MySQL Databases to SQL Server Schemas (MySQLToSQL) to map objects from MySQL database to appropriate SQL Server database/schema pair.

触发器转换Trigger Conversion

SSMA 基于以下规则创建触发器：SSMA creates triggers based on the following rules:

在触发器转换为 INSTEAD of T-sql 触发器之前BEFORE triggers are converted into INSTEAD OF T-SQL triggers

触发器在 T-sql 触发器转换为之后，在 T-sql 触发器中，每行都有或不包含迭代。AFTER triggers are converted into AFTER T-SQL triggers with or without iterations per rows.

视图转换View Conversion

SSMA 创建具有依赖对象的视图SSMA creates views with dependent objects

语句转换Statement Conversion

-每个 SQL 语句对象可能包含单个 MySQL 语句 (如 DDL、DML 和其他类型的语句) 或 BEGIN .。。结束块。- Each SQL Statement object may contain a single MySQL statement (like DDL, DML, and other types of statements) or BEGIN ... END block.

- 多语句转换： BEGIN .。。结束块转换SQL 语句还可以包含 BEGIN .。。END 块，如过程、函数或触发器定义中的一个。- MultiStatement Conversion:BEGIN ... END block conversionSQL Statement can also contain a BEGIN ... END block like one in procedure, function or trigger definition. 对于单个 MySQL 语句对象，应以相同的方式转换这些块。Those blocks should be converted the same way they are being converted for the single MySQL statement objects.

转换 MySQL 数据库对象Converting MySQL Database Objects

若要转换 MySQL 数据库对象，请首先选择要转换的对象，然后让 SSMA 执行转换。To convert MySQL database objects, you first select the objects that you want to convert, and then have SSMA perform the conversion. 若要在转换过程中查看输出消息，请在 "视图" 菜单上选择 "输出"。To view output messages during the conversion, on the View menu, select Output.

将 MySQL 对象转换为 SQL Server 或 SQL Azure 语法To convert MySQL objects to SQL Server or SQL Azure syntax

在 MySQL 元数据资源管理器中，展开 MySQL 服务器，然后展开 "数据库"。In MySQL Metadata Explorer, expand the MySQL server, and then expand Databases.

选择要转换的对象：Select objects to convert:

若要转换所有架构，请选中 "数据库" 旁边的复选框。To convert all schemas, select the check box next to Databases.

若要转换或省略数据库，请选中数据库名称旁边的复选框。To convert or omit a database, select the check box next to the Database name.

若要转换或省略对象的类别，请展开一个架构，然后选中或清除该类别旁边的复选框。To convert or omit a category of objects, expand a schema, and then select or clear the check box next to the category.

若要转换或省略单个对象，请展开 category 文件夹，然后选中或清除该对象旁边的复选框。To convert or omit individual objects, expand the category folder, and then select or clear the check box next to the object.

若要转换所有选定的对象，请右键单击 "数据库"，然后选择 "转换架构"。To convert all selected objects, right-click Databases and select Convert Schema.

您还可以通过右键单击对象或其父文件夹，然后选择 "转换架构"，来转换各个对象或对象类别。You can also convert individual objects or categories of objects by right-clicking the object or its parent folder, and then selecting Convert Schema.

查看转换问题Viewing Conversion Problems

某些 MySQL 对象可能不会转换。Some MySQL objects might not be converted. 您可以通过查看摘要转换报告来确定转换成功率。You can determine the conversion success rates by viewing the summary conversion report.

查看摘要报表To view a summary report

在 MySQL 元数据资源管理器中，选择 "数据库"。In MySQL Metadata Explorer, select Databases.

在右侧窗格中，选择 "报表" 选项卡。In the right pane, select the Report tab.

此报表显示已评估或转换的所有数据库对象的摘要评估报告。This report shows the summary assessment report for all database objects that have been assessed or converted. 您还可以查看单个对象的摘要报表：You can also view a summary report for individual objects:

若要查看单个架构的报表，请在 MySQL 元数据资源管理器中选择该数据库。To view the report for an individual schema, select the database in MySQL Metadata Explorer.

若要查看单个对象的报表，请在 MySQL 元数据资源管理器中选择该对象。To view the report for an individual object, select the object in MySQL Metadata Explorer. 具有转换问题的对象具有红色错误图标。Objects that have conversion problems have a red error icon.

对于失败转换的对象，可以查看导致转换失败的语法。For objects that failed conversion, you can view the syntax that resulted in the conversion failure.

查看各个转换问题To view individual conversion problems

在 MySQL 元数据资源管理器中，展开 "数据库"。In MySQL Metadata Explorer, expand Databases.

展开显示红色错误图标的数据库。Expand the database that shows a red error icon.

在数据库下，展开一个包含红色错误图标的文件夹。Under the database, expand a folder that has a red error icon.

选择包含红色错误图标的对象。Select the object that has a red error icon.

在右侧窗格中，单击 "报表" 选项卡。In the right pane, click the Report tab.

在 "报表" 选项卡的顶部是一个下拉列表。At the top of the Report tab is a drop-down list. 如果列表显示 "统计信息"，请将所选内容更改为 "源"。If the list shows Statistics, change the selection to Source.

SSMA 将显示源代码，并将多个按钮直接显示在代码上方。SSMA will display the source code and several buttons immediately above the code.

单击 "下一问题" 按钮。Click the Next Problem button. 这是一个红色的错误图标，其中有一个指向右侧的箭头。This is a red error icon with an arrow that points to the right.

SSMA 将突出显示在当前对象中找到的第一个有问题的源代码。SSMA will highlight the first problematic source code it finds in the current object.

对于无法转换的每个项，必须确定要对该对象执行哪些操作：For each item that could not be converted, you have to determine what you want to do with that object:

可以修改 MySQL 数据库中的对象，以删除或修改有问题的代码。You can modify the object in the MySQL database to remove or revise problematic code. 若要将更新的代码加载到 SSMA 中，必须更新元数据。To load the updated code into SSMA, you will have to update the metadata.

可以从迁移中排除对象。You can exclude the object from migration. 在 SQL ServerSQL Server 或 SQL Azure 元数据资源管理器和 MySQL 元数据资源管理器中，先清除项旁边的复选框，然后将对象加载到中 SQL ServerSQL Server 或 SQL Azure 并从 MySQL 迁移数据。In SQL ServerSQL Server or SQL Azure Metadata Explorer and MySQL Metadata Explorer, clear the check box next to the item before loading the objects into SQL ServerSQL Server or SQL Azure and migrating data from MySQL.

下一步Next Step

另请参阅See Also