Mssql server database to MySQL

Thoughts:
The insert into statement of MySQL is constructed using mssql, saved to a text file, and then imported into the MySQL database.

The method is as follows:

1. create a mysql table synchronously based on the ms SQL table structure. You can use the mssql client tool to generate a table creation statement for mssql. The statements include the section COLLATE Chinese_PRC_CI_AS and CONSTRAINT [DF_xxx_xxx, it is useless for mysql. Use the ue's search replacement function to delete it. Replace "CONSTRAINT [DF_xxx_xxx]" with a regular expression to match the CONSTRAINT. *?]
2. the simplified create table... the statement is rewritten to a table creation statement suitable for MySQL, mainly because of the corresponding modification of the field type (there is no nvarchar or other n-headers in mysql) replace square brackets with backquotes, and delete the definition of the mssql identifier column.
3. Execute create table in MySQL to create a table.
4. Return to mssql and perform a string replacement for each text field. Replace the single quotation marks with the mysql-style escape character ('). The statement is roughly as follows:
Update you_table_name set [field_name_1] = replace ([field_name_1], ''', ''')
In mssql, the single quotation mark escape is two single quotation marks, so it looks awkward. By the way, it forces the MS's brain escape conventions.
5. Change the non-complex fields in the mssql table to the complex type. Of course, you can do the same without changing the fields, but the following steps will be painful.
6. We need to construct a series of insert statements used to insert mssql records into the MySQL table,
7. We need to concatenate a string in mssql to construct the query statement.

The code is as follows:	Copy code
Select top 100 'set into 'you _ table_name' ('field _ name_1 ', 'Field _ name_2 ',...) values (''' + [field_name_1] + ''', ''' + convert (varchar (10), isnull ([field_name_2], 0 )) + ''',...); 'From you_mssql_table

From this statement, we can see that if it is a numeric field, like field_name_1, it can be directly connected using the mssql concatenation operator (+), but if it is a numeric or date field, for example, 'Field _ name_2 'requires convert to be converted, and the date type requires a third parameter, such as convert (varchar (10), time, 120.
We can first construct an insert statement suitable for MySQL for the first 100 rows, and randomly copy several rows to MySQL to test whether the insert statement can be inserted normally.
8. after confirmation, export the data from mssql, export the preceding query to an access database file, and then export the text file from access (you can also directly export the text from mssql ), generally, text files exported by mssql are ansi encoded. You can use tools such as editplus to open and save them as UTF-8 encoded files. It is not recommended to use ue Conversion. Lessons learned indicate that ue conversion encoding is prone to errors.
9. Log on to the mysql command line client. Add the -- default-character-set = utf8 parameter. Otherwise, a garbled string may be imported.
> Mysql-uroot-p -- default-character-set = utf8
10. Enter the corresponding database use you_database
11. Run source X: pathtoyoursqlfile. SQL.
It should be completed soon. During the import, pay attention to the error message. Usually not.
12. Now the conversion is complete.

If you are using a php program for conversion, pay attention to the following differences in the data types and usage of mysql and mssql:

1. MySQL supports enum, set, and SQL server.

2 MySQL does not support nchar, nvarchar, and ntext

3. The incremental statement of MySQL is AUTO_INCREMENT, while that of mssql is identity)

4 msms by default, the default value of the statement for creating a table everywhere is (0), but it is not allowed to contain square brackets in the MySQL database.

5 MySQL needs to specify the storage type for the table

6. The mssql identifier is [] and [type], indicating that it is different from the keyword, but MySQL is ', that is, the symbol on the left of key 1.

7. mssql supports the getdate () method to obtain the current time and date. However, MySQL supports date and time types. The current date is cur_date (), and the current complete time is the now () function.

8 mssql does not support replace into statements, but in the latest sql20008, it also supports merge syntax

9 MySQL supports insert into table1 set t1 = '', t2 ='', but mssql does not support this write.

10 MySQL supports insert into tabl1 values)

11 mssql does not support limit statements. Unfortunately, it can only replace limt 0, N, row_number () over () with the top function to replace limit N, M

12 when creating a table, MySQL must specify a storage engine type for each table, while mssql only supports one storage engine.

13 MySQL does not support the datetime type whose default value is the current time (mssql is easy to implement). The timestamp type is used in the MySQL database.

14. Check whether the table exists in mssql and delete it again. You need:

The code is as follows:	Copy code
If exists (select * from dbo. sysobjects where id = object_id (N 'UC _ newpm ') and OBJECTPROPERTY (id, N 'isusertable') = 1)

However, in MySQL, you only need to drop table if exists cdb_forums;

15 MySQL supports unsigned integers, so it can store up to twice the maximum number of unsigned mssql statements.

16 MySQL does not support the varchar (max) type that is very convenient in mssql. This type can be used in both general data storage and blob data storage in mssql.

17 to create a non-clustered index in MySQL, you only need to specify it as the key when creating the table. For example, KEY displayorder (fid, displayorder) must be:

The code is as follows:	Copy code
Create unique nonclustered index index_uc_protectedmembers_username_appid on dbo. uc_protectedmembers (Username asc, appid asc)

18 The MySQL text field type cannot have default values

The total field length of a table in 19MySQL cannot exceed 65XXX.

20 The obvious difference is that the installation of MySQL database is very simple, and the file size is only 110 M (non-installation version). Compared with Microsoft, the installation progress is simply .....

There are several good 21 MySQL management tools, MySQL_front, and the official suite, but they are not easy to use SSMS, which is a major disadvantage of MySQL.

22MySQL stored procedures only appear in the latest version, and the stability and performance may be inferior to mssql.
 

23 with the same load pressure, MySQL consumes less CPU and memory, while mssql does consume a lot of resources.
 

24php connects to MySQL and mssql in similar ways. You only need to replace the MySQL function with mssql.
 

The 25MySQL database supports the date, time, and year types. Only date and time are supported by mssql to 2008.

Next I will introduce some conversion tools.

MSSQL2MYSQL
Microsoft DTS
SQLyog
Access Export
Text Import/Export

Microsoft DTS should be the built-in import and export tool of MSSQL. MSSQL is good in terms of import and export ACCESS and text, but ODBC is not found in the import and export tool of MSSQL2005, dizzy enough, isn't it? It seems that you still need to check the documentation.

Access Export is the Access Export tool. When exporting data, select ODBC. Of course, you must configure the ODBC parameters of MYSQL first.

SQLyog is a MYSQL management tool. It is not tested because it is too lazy to download it.

Text Import/Export: I can Import Text files in phpMyAdmin, which may be suitable for a small amount of data. It is not very convenient for a large number of tools.

You can use Baidu search to convert it directly.