Migrating-MySQL-to-SQL-Server-2014-and-Azure-SQL-DB.pdf

(1044 KB) Pobierz
Guide to Migrating from MySQL to SQL Server 2014 and
Azure SQL DB
SQL Server Technical Article
Writers:
Alexander Pavlov (DB Best Technologies), Yuri Rusakov (DB Best Technologies),
Valentin Panarin (DB Best Technologies), Natalia Vilenskaia (DB Best Technologies)
Technical Reviewer:
Dmitry Balin (DB Best Technologies)
Published:
November 2014
Applies to:
SQL Server 2014
Summary:
In this migration guide you will learn the differences between the MySQL and SQL
Server 2014 database platforms, and the steps necessary to convert a MySQL database to
SQL Server.
Created by: DB Best Technologies LLC
2535 152
nd
Ave NE, Redmond, WA 98052
Tel.: +1-855-855-3600
E-mail: info@dbbest.com
Web:
www.dbbest.com
Copyright
This is a preliminary document and may be changed substantially prior to final commercial
release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation
on the issues discussed as of the date of publication. Because Microsoft must respond to
changing market conditions, it should not be interpreted to be a commitment on the part of
Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the
date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES,
EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the
rights under copyright, no part of this document may be reproduced, stored in or introduced into
a retrieval system, or transmitted in any form or by any means (electronic, mechanical,
photocopying, recording, or otherwise), or for any purpose, without the express written
permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual
property rights covering subject matter in this document. Except as expressly provided in any
written license agreement from Microsoft, the furnishing of this document does not give you any
license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-
mail addresses, logos, people, places and events depicted herein are fictitious, and no
association with any real company, organization, product, domain name, email address, logo,
person, place or event is intended or should be inferred.
© 2014 Microsoft Corporation. All rights reserved.
Microsoft. SQL Server, and Visual C++ are registered trademarks of Microsoft Corporation in
the United States and other countries.
The names of actual companies and products mentioned herein may be the trademarks of their
respective owners.
Contents
Introduction ................................................................................................................................ 6
MySQL to SQL Server 2014 Migration ....................................................................................... 7
Main Migration Steps .............................................................................................................. 7
Converting Database Objects ................................................................................................. 7
Migrating MySQL Data Types .................................................................................................... 8
Type Mapping......................................................................................................................... 8
Data Type Migration Issues.......................................................................................................12
Numeric Data Types ..............................................................................................................12
Date and Time Types ............................................................................................................15
String Types ..........................................................................................................................18
ENUM and SET Data Types ..................................................................................................21
Other Types...........................................................................................................................25
Implicit Data Type Conversion ...............................................................................................25
Data Type Default Values ......................................................................................................26
MySQL Migration Issues ...........................................................................................................28
Operators ..............................................................................................................................28
Comparison Operators ..........................................................................................................28
Bit Operators .........................................................................................................................31
Assignment Operators ...........................................................................................................32
Variables ...............................................................................................................................32
Utility Statements ..................................................................................................................35
Data Definition Statements........................................................................................................36
IF NOT EXISTS, IF EXISTS, OR REPLACE Clauses ............................................................36
Temporary Tables .................................................................................................................43
SCHEMA Keyword in DATABASES Statements ...................................................................47
CHARACTER SET and COLLATE Clauses in DDL Statements ............................................47
CREATE INDEX Statement ...................................................................................................48
CREATE TABLE Statement ..................................................................................................51
ALTER TABLE Statement .....................................................................................................57
RENAME DATABASE Statement ..........................................................................................62
RENAME TABLE Statement..................................................................................................64
CREATE VIEW, ALTER VIEW, and DROP VIEW Statements ..............................................66
CREATE EVENT, ALTER EVENT, DROP EVENT Statements .............................................68
CREATE/ALTER/DROP PROCEDURE/FUNCTION Statements...........................................68
Data Manipulation Statements ..................................................................................................74
LIMIT Clause .........................................................................................................................74
DELETE Statement ...............................................................................................................76
UPDATE Statement...............................................................................................................77
INSERT Statement ................................................................................................................78
REPLACE Statement ............................................................................................................82
SELECT Statement ...............................................................................................................83
SELECT…INTO and LOAD DATA INFILE Statements ..........................................................84
GROUP BY, HAVING, and ORDER BY Clauses ...................................................................85
JOINs ....................................................................................................................................88
Subqueries ............................................................................................................................91
Prepared Statements.............................................................................................................92
DO Command .......................................................................................................................92
HANDLERs ...........................................................................................................................93
MODIFIERs ...........................................................................................................................94
Transactional and Locking Statements .....................................................................................95
BEGIN TRANSACTION Statements ......................................................................................95
END TRANSACTION Statements .........................................................................................97
Named Transaction SAVEPOINT Statements .......................................................................97
SET AUTOCOMMIT Statements ...........................................................................................99
LOCK TABLES and UNLOCK TABLES Statements ............................................................101
SET TRANSACTION ISOLATION LEVEL Statement ..........................................................101
XA Transaction Statements .................................................................................................101
Database Administration Statements ......................................................................................103
Account Management Statements .......................................................................................103
Table Maintenance Statements ...........................................................................................103
SET Statement ....................................................................................................................103
SHOW Statement ................................................................................................................106
Other Administrative Statements .........................................................................................106
Stored Procedures and Functions (Routines) ..........................................................................107
CALL Statements ................................................................................................................107
Compound Statements Block ..............................................................................................108
Local Variables ....................................................................................................................110
Conditions and Handlers .....................................................................................................112
Cursors................................................................................................................................113
Flow Control Constructs ......................................................................................................116
Routines ..............................................................................................................................120
Triggers...................................................................................................................................121
SQL Mode (SQL_MODE System Variable) .............................................................................125
Data Migration.........................................................................................................................126
Migration Steps ...................................................................................................................126
Validating Migration Results ....................................................................................................131
Migrating MySQL System Functions .......................................................................................132
Equivalent Functions ...........................................................................................................132
Nonsupported Functions .....................................................................................................132
Emulated Functions .............................................................................................................132
Conclusion ..............................................................................................................................141

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin