How to migrate SQL Server data to MySQL database and considerations _mssql

In project development, it is sometimes necessary to migrate SQL Server data to MySQL because the database used at the beginning of the project is SQL Server, and then the stored database is adjusted to MySQL. The following is a small series of day-to-day collation of a SQL Server database migration method.

One, SQL Server common data types and MySQL different places

Ii. issues to be noted in migrating SQL Server data to MySQL

1. Unlike a unique index, a field that is uniquely indexed by SQL Server can only allow a null value, and MySQL, which has a field that corresponds to a unique index in Oracle, allows multiple null values.

2, the syntax of stored procedures is very different, the migration of stored procedures is the most troublesome, need to be carefully modified.

3, the program part of the written SQL statements due to the different syntax will also be modified accordingly.

Common ways to migrate SQL Server data to MySQL

1, the use of sqlyog migration

Advantages

The migration method is simple, flexible, and can be modified when migrating, such as in SQL Server is the original datetime, and then migrated to MySQL you can be configured to timestamp, the success rate is very high;

Disadvantages

Migration is slow! This is the biggest drawback of the method, if the table data volume reached hundreds of thousands of lines, or even millions of lines, you will find that migration is really slow. are significantly slower than other migration methods.

2, using the PowerDesigner and SQL Server script export function to migrate

(1), this method first uses the PowerDesigner, to the SQL Server database, carries on the reverse engineering, obtains the E-R diagram, then produces the MySQL to build the table statement. Complete the migration of the database structure, of course, the migration of the table structure, not the use of PowerDesigner is also possible. For example, I export a table structure to a statement, and then manually modify, and then run in MySQL, is the same;

(2) and then use SQL Server's tool SSMS to export the data from the tables in the SQL Server database into an INSERT statement, each table corresponding to the export of a file, and then some processing of the file, and then imported into the MySQL database.

3, the use of Oracle MySQL Server official Workbeach tools for Migration

(1) When Workbench connects to SQL Server, the user needs to have permission to view any database. Otherwise, workbench cannot access the metadata of the table structure of SQL Server and thus cannot migrate.

(2), and the use of Navicat to migrate the method, and SQLyog is similar.

Above SQL Server database migration methods, I hope you like.