Instructions on how to convert mssql to mysql

A summary of the method of converting mssql to mysql, friends in need can refer to it.

Method 1:
1. Guide table structure

　Use MySQL to generate a create script. Find the script to be exported and modify it according to MySQL syntax to create the column structure of the table in MySQL database.

　2. Export table data

Use bcp to export text files on the MSSQL side:

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

Where "" is the sql statement to be exported, -c specifies the use of \t for field separation, use \n for record separation, -S specifies the database server and instance, -U Specify the user name, -P specifies the password.

Use mysqlimport on the MySQL side to import the text file into the corresponding table

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

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

Method 2:

MYSQL Migration Toolkit should be available.
1. Install the MySQL Migration Toolkit on a machine running MS SQL server;
2. This machine also needs to install the java environment and jdbc driver:
Go to the sun website to download the java environment and the jdbc driver. Download (mysql-connector-java-5.0.5.zip) from the MySQL website and set the path environment variable;
3. If there are Chinese characters in your original database, you must explicitly set several related Character set:
A. Source Database Select Database System as MS SQL:
You need to manually write the jdbc connection string here: click the Advanced button at the bottom of the interface, a box will appear, 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=

Among them, uppercase IP, PORT, YOURDB_NAME, USER, and PASSWD must be filled in with the actual MS SQL Server server ip and port, database name, database user name, and password;

B. Target Database also needs to be manually filled in Edit jdbc connection string:
Click the Advanced button at the bottom of the interface, and a box will appear, prompting you to enter "Connection String:"
Enter the following format here:

jdbc:mysql://IP: 3306/?user=USER&password=PASSWD&useServerPrepStmts=false&characterEncoding=gbk

Among them, the uppercase IP, USER, and PASSWD must be filled in with the actual MySQL server ip, database user name, and password

C. Select the schema and table of sql server
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 are no other options until the end
F. After transplantation, use graphical tools (MySQL query browser, SQLyog, etc.) or mysql client ( Command line) connection, you can see Chinese.
On the client (command line), first execute
mysql> set names gb2312(or gbk);
4. Since MS SQL server and MySQL have different syntax formats and data typesdefinition There are certain differences in other aspects, so the transplantation process is not very simple. It may need to be repeated several times to correct the syntax format and data type definition. The MySQL Migration toolkit allows users to manually modify the generated MySQL SQL statements. In the "Manual Editing" section

Method 3:
Microsoft released the latest dedicated conversion on the 13th Tool, the following is the address, supports mysql to 2005 or 2008
http://www.jb51.net/database/34633.html
Official download address
http://www.microsoft.com/downloads /details.aspx?FamilyID=c6f14640-da22-4604-aaaa-a45de4a0cd4a&displaylang=en

The above is the detailed content of Instructions on how to convert mssql to mysql. For more information, please follow other related articles on the PHP Chinese website!