Summary of database SQL Server to MySQL migration methods

Recently took over an old project database with SQL Server to MySQL migration. So there were some surveys and summaries before the migration. Here are some of the SQL Server to MySQL migration methods.

1. Migrating with SQLyog

Specific ways to participate: http://www.cnblogs.com/gaizai/p/3237907.html

Advantages: The migration method is very simple, flexible, when migrating, can be modified field, such as in SQL Server is originally datetime, and then migrated to MySQL when you can configure into timestamp;

The success rate is very high;

Cons: Migration is slow! This is the biggest drawback of this method, if the data volume of the table reaches hundreds of thousands of rows, or even millions of rows, you will find that the migration is really slow. Significantly slower than other migration methods.

2. Use the script export feature of PowerDesigner and SQL Server to migrate

The first step: The method first uses PowerDesigner, the SQL Server database, reverse engineering, get the E-r diagram, and then generate MySQL table statement. Complete the migration of the database structure;

Of course, the migration of the table structure, not using PowerDesigner is also possible. For example, I export the table structure to a statement, and then manually modify, and then run in MySQL, is the same;

Part II: Then use the SQL Server tool, SSMS, to export the data from the tables in the SQL Server database into an INSERT statement, export a file for each table, and then do some processing of the file,

Then import into the MySQL database.

For more details on how to handle SQL Server exported SQL files, see: http://www.cnblogs.com/digdeep/p/4822499.html

3. Migrating using Oracle MySQL Server's official Workbeach tool

MySQL official has two SQL Server migration to MySQL instruction manual, can refer to:

http://www.mysql.com/why-mysql/white-papers/guide-to-workbench-migration-wizard/
http://www.mysql.com/why-mysql/white-papers/guide-to-migrating-from-sql-server-to-mysql/

The above connection can be downloaded to PDF files, instructions for installation files, and can be migrated using the Workbeach tool. Because Workbeach has 30 days of use, so there is absolutely no need to worry;

When Workbench connects to SQL Server, the user needs to have the view any database permission. Otherwise, workbench cannot access the metadata for the table structure of SQL Server and cannot be migrated.

There are also methods for migrating using Navicat, and the methods are similar to SQLyog.

4. Comparison of three methods

Method one is suitable for the migration method of small data volume, if the data volume is large, the migration time is short, the basic is not suitable;

Method Two is a good method, the disadvantage is that SQL Server exported insert script in the DateTime field and decimal need to write their own code to convert, a little bit troublesome;

The third method should be the best choice, relatively simple, fast, and there is no need for their own field processing.

Therefore, for a large amount of data, you should choose method three or method two.

5. Some issues to be aware of migration

Some of the issues that SQL Server needs to be aware of when migrating to MySQL are the connection http://www.mysql.com/why-mysql/white-papers/ Guide-to-migrating-from-sql-server-to-mysql/is documented in the documentation. Here are some specific considerations:

1) Unlike unique indexes, SQL Server's unique indexed fields can only allow a null value, while MySQL, which has always allowed multiple null values for the fields corresponding to the unique index in Oracle;

2) ifnull and isnull, SQL Server uses ifnull, while MySQL uses isnull:

  <id= "Updatemodelaccuracybyid"  parameterclass= "Java.util.Map"  >      update Model_model       set Accuracy_num=ifnull (accuracy_num,0) + 1, accuracy_total= Ifnull (accuracy_total,0) + #accuracyTotal:D ecimal#       where id= #id: binint# and Status=1  </  update>

3) All paging SQL needs to be rewritten:

Paging to SQL Server (using row_number () over (...)) ：

  <SelectID= "Getmodelchoiselistbyuserid"Parameterclass= "Java.util.Map"ResultClass= "Net.xxx.xxx.dataobject.model.ModelChoiseVo">SELECT * FROM (select Row_number () up (order by c.choise_time Desc) as rowID, c.id as id,m.gid as Gid,m.me        mber_id as memberid,mb.g_member_nickname as MemberName, mb.g_member_mobile as membermobile,c.user_id as UserId, ... m.order_num as ordernum,m.model_satisfaction as modelsatisfaction,m.height as height, M.professi  Onal_type Professionaltype from Model_model m inner joins Model_choise C on m.id=c.model_id inner joins members MB On mb.g_memberid=m.member_id where M.status=1<Dynamic>        <Isnotnullprepend= "and" Property= "UserId">c.user_id = #userId: bigint#</Isnotnull>        <Isnotnullprepend= "and" Property= "status">c.status = #status: integer#</Isnotnull>    </Dynamic>) TT where ROWID&gt;= #minRow: integer# and rowID&lt;= #maxRow: integer#</Select>

Rewrite with MySQL:

  <SelectID= "Getmodelchoiselistbyuserid"Parameterclass= "Java.util.Map"ResultClass= "Net.xxx.xxx.dataobject.model.ModelChoiseVo">Select C.id as id,m.gid as gid,m.member_id as memberid,mb.g_member_nickname as MemberName, MB.G_MEMB Er_mobile as membermobile,c.user_id as UserId, m.id as modelid,c.status as status, C.pay_amount as Payamount , c.accuracy as accuracy, ... m.order_num as ordernum,m.model_satisfaction as Modelsatisfaction,m.height As height, M.professional_type professionaltype from Model_model m inner join model_choise C on M.id=c.model _ID INNER JOIN members MB on mb.g_memberid=m.member_id where M.status=1<Dynamic>        <Isnotnullprepend= "and" Property= "UserId">c.user_id = #userId: bigint#</Isnotnull>        <Isnotnullprepend= "and" Property= "status">c.status = #status: integer#</Isnotnull>    </Dynamic>ORDER BY c.choise_time desc limit #minRow: integer#, #maxRow: integer#</Select>

4) The migration of stored procedures is the most troublesome:

The syntax of a stored procedure differs greatly.

There are some notes in http://www.mysql.com/why-mysql/white-papers/guide-to-migrating-from-sql-server-to-mysql/'s documentation.

Here are some reference articles for MySQL stored procedures:

Http://www.cnblogs.com/digdeep/p/4818660.html

Http://www.cnblogs.com/digdeep/p/4814020.html

Summary of database SQL Server to MySQL migration methods