How does mssql Convert to MySQL?

Method 1:

1. Table Structure

Use MySQL to generate the create script. Find the script to be exported and modify the Column Structure of the table created in the MySQL database according to the MySQL syntax.

2. Export table data

Use bcp on MSSQL to export text files:

Bcp "Select * FROM dbname. dbo. tablename;" queryout tablename.txt-c-Slocalhost \ db2005-Usa

"" Indicates the SQL statement to be exported.-c specifies that \ t is used to separate fields, \ n is used to separate records, and-S specifies the database server and instance, -U specifies the user name and-P specifies the password.

Use mysqlimport on MySQL to import text files to the corresponding table

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

-U indicates the user name,-p indicates the password, and databasename indicates the database name. The table name is the same as the file name.

Method 2:

MYSQL Migration Toolkit should be usable.

1. Install MySQL Migration Toolkit on a machine running ms SQL server;

2. You also need to install the java environment and jdbc driver on this machine:

Java environment: Go to the sunnetwork, download the JDBC driver to the mysqlnetwork (mysql-connector-java-5.0.5.zip), and set the environment variables of the path;

3. If your original database contains Chinese characters, you must explicitly set several character sets:

A. Select Database System as ms SQL for Source Database:

You need to manually write the jdbc Connection String here: Click the Advanced key at the bottom of the interface, and a box is displayed, prompting you to enter "Connection String:". Enter the following format here:

Jdbc: jtds: sqlserver: // IP: PORT/YOURDB_NAME; user = USER; password = PASSWD; charset = gb2312 (or gbk); domain =

The uppercase IP address, PORT, YOURDB_NAME, USER, and PASSWD must be filled in with the ip address and PORT of the actual ms SQL Server, database name, database username, and password;

B. You also need to manually edit the jdbc connection string for Target Database:

Click the Advanced key at the bottom of the interface and a box is displayed, prompting you to enter "Connection String :"

Enter the following format:

Jdbc: mysql: // IP: 3306 /? User = USER & password = PASSWD & useserverprep0000ts = false & characterEncoding = gbk

The uppercase IP address, USER, and PASSWD must be the ip address, database username, and password of the actual MySQL server.

C. Select the schema and table of SQL server.

D. Object Mapping

Modify "Migration of type MSSQL Table ":

Click "Set Parameter", select "User defined", and Set "charset = gb2312 (or gbk), collation = gb2312 (or gbk) _ chinese_ci"

E. There are no other options until the end

F. After transplantation, You can see Chinese characters by using a graphical tool (MySQL query browser, SQLyog, etc.) or using a mysql client (command line.

On the client (command line), run

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, modify the syntax format and data type definition. MySQL Migration toolkit allows you to manually modify the generated MySQL SQL statements, in the "Manual Editing" section