How to migrate a database from SQL Server to MySQL

1. migrate database schema.

First, use the reverse engineering function of Sybase powerdesigner to reverse the physical model of the SQL Server database. The specific operation is to select "file" in powerdesigner, "reverse engine", then select database, and select DBMS as SQL Server,

Then select the data source, that is, the SQL Server database server to be connected to, and then select the database name to reverse. For example, select "wss_content_80 ":

Click OK to generate the physical model diagram:

Click Change current DBMS under the "Database" menu to modify the current DBMS to MySQL 5.0,

Click OK to generate the MySQL physical model, and then click Generate database under the "Database" menu to generate the database script file.

Next, manually modify the content of the generated script.

Replace all DBO. values with null values.

Delete A statement such as create user.

If some characters are keywords in MySQL, you must use the "'" (match on the left of the number 1 on the keyboard) to match the box.

Add the storage engine required by MySQL, for example, after each table creation statement:

Engine = InnoDB Character Set utf8 collate utf8_general_ci;

Run the generated script in MySQL once to create a database.

Ii. Data Migration content

The data content can only be done by generating an insert statement.

First, use the "generate script" function of SSMs (right-click the database and select the "task" and "generate script" option) to generate an insert script for data in the SQL Server database.

First, select the table for data migration. All Tables are selected here:

Click Next and save the script to the new query window:

Click the advanced option. In the advanced options window, select the Data Type of the script to be written as data only:

Then, click "OK" and then click "Next" to generate the insert script file.

Modify the generated script file, which includes the following modifications:

Remove [] using the batch replacement method, which is consistent with SQL Server and not used in MySQL.

Remove DBO by batch replacement.

Some words are keywords in MySQL, so they must be caused.

For datetime type data, you need to manually modify it. By default, SQL Server generates such a statement, which cannot be parsed in MySQL:

Cast (0x00009eef00000000 as datetime)

Add one for each row. This indicates that an insert statement ends. This Semicolon is not required in SQL Server, but is required in MySQL. An easy way is to use an advanced text editor (such as NotePad ++) and replace \ r \ n with \ r \ n.