How to convert MSSQL to MySQL

Method One:

1. Guide Table structure

A way to generate a create script using MySQL. Find the script that generated the export, and modify it in MySQL syntax to create the table's column structure in the MySQL database or whatever.

2. Guide Table data

To export a text file using bcp on the MSSQL side:

BCP "SELECT * from Dbname.dbo.tablename;" Queryout tablename.txt-c-slocalhostdb2005-usa

Where "" is the SQL statement to be exported,-c specifies to use T for field-delimited, record-delimited using N,-s specifies the database server and instance,-u specifies the username, and-p specifies the password.

Use Mysqlimport to import text files to corresponding tables on the MySQL side

Mysqlimport-uroot-p Databasename/home/test/tablename.txt

Where-u Specifies the user name,-p specifies the password, databasename the database name, and the table name is the same as the file name.

Method Two:

MYSQL migration Toolkit should be available.

1, the MySQL Migration Toolkit installed on the machine running with MS SQL Server;

2, this machine also needs to install the Java environment and JDBC driver:

Java environment to download the Sun site, JDBC driver to the MySQL Web site download (mysql-connector-java-5.0.5.zip), and set the path of the environment variable;

3. If you have a Chinese character in your original database, you must explicitly set several places on the character set:

A. Source Database Select Database system for MS SQL:

Here you need to write the JDBC connection string manually: Click on the Advanced button below the interface and a box appears, prompting for "Connection String:", enter the following format here:

jdbc:jtds:sqlserver://ip:port/yourdb_name;user=user;password=passwd;charset=gb2312 (or GBK);d omain=

Where, uppercase IP, port, Yourdb_name,user, passwd are to use the actual MS SQL Server server IP and port, database name, database user name, password fill;

B. Target Database also needs to manually edit the JDBC connection string:

Click on the Advanced button below the interface and a box appears, prompting for "Connection String:"

Enter the following format here:

Jdbc:mysql://ip:3306/?user=user&password=passwd&useserverprepstmts=false&characterencoding=gbk

Where, uppercase IP, user, passwd are to use the actual MySQL server IP, database username, password fill

C. Choose SQL Server schema and tables

D. Object Mapping

Modify "Migration of type MSSQL Table":

Click "Set Parameter", select "User defined", set "charset=gb2312 (or GBK), collation=gb2312 (or GBK) _chinese_ci"

E. There is no other option, until the end

F. After the transplant, use graphics tools (MySQL query browser, SQLYOG, etc.) or join with the MySQL Client (command line) to see Chinese.

On the client (the command line), first execute the

Mysql> Set names gb2312 (or GBK);

4, because MS SQL Server and MySQL have some differences in syntax format and data type definition, the porting process is not very simple and may need to be repeated several times to correct syntax formatting and data type definitions. MySQL Migration Toolkit allows users to manually modify the generated MySQL SQL statements, in which the "Manual Editing" section