Find a good way to convert from SQLServer to Mysql database

Find a good way to convert from SQLServer to Mysql database

Find a good way to convert from SQLServer to Mysql database

At the beginning of the year, I had been working on the migration of a website MSSQL2000-> MySQL5. Because different program systems were used, the main problem was data migration. Because the structure of the two System databases is very different, it is not convenient to use the ODBC data source function imported into MYSQL in SQL SERVER (this is not recommended, and the field type and other differences will lead to death ~), Therefore, in WINDOWS, you can write a PHP script to read data from SQLSERVER, and then insert the data into MYSQL according to the requirements of the new system. This is flexible and convenient. The actual process mainly involves the following problems:

1. database connection, mainly connecting to SQL SERVER. There are three main methods:
1.1 Use mssql _ series functions in PHP. This is similar to using mysql _ series functions, but you need to open related extensions (extension = php_mssql.dll) in php. ini ).
1.2 using ODBC connection, because a specific database is abstracted, there is no way to use the data table field name => array key name feature. It is not very convenient for specific applications. The code format is as follows:

$ Conn = odbc_connect ("datasource", "username", "password ");
$ SQL = "select * from news ";
$ Cur = odbc_exec ($ conn, $ SQL );
While (odbc_fetch_row ($ cur )){
$ Field1 = odbc_result ($ cur, 1 );
$ Field2 = odbc_result ($ cur, 2 );
// Do something
}
1.3 using PDO-PHP5 to add the data object abstraction layer as an officially launched data access interface has many advantages, such as supporting parameter binding to prevent SQL injection; for different databases to load different drivers, the program code is consistent, easy to transplant, and so on, I believe it should be the trend of the times. However, because PHP5's new object-oriented feature is used, PHP5 support is required. 5.1 can be used directly, 5 requires PECL installation, and PHP needs to be modified. ini, add: extension = php_pdo_mysql.dll and extension = php_pdo_mssql.dll. The actual code is as follows:

Try {
$ DBH = new PDO ("mssql: dbname = XXX; host = localhost ",
"Root", "password"); // Connect to DB
} Catch (PDOException $ e ){
Print "Error! : ". $ E-> getMessage (); // Error Message
Die ();
}
$ Stmt = $ DBH-> prepare ("SELECT * FROM news"); // Stmt Handle $ stmt
If ($ stmt-> execute ()){
While ($ row = $ stmt-> fetch ()){
// Do something
}
}
$ Stmt2 = $ mssql-> prepare ("insert into news
(Title, author) VALUES (: title,: author )");
$ Stmt2-> bindParam (': title', $ title );
$ Stmt2-> bindParam (': author', $ author );
$ Stmt2-> execute ();
$ DBH = null; // Close Connection. Here, we want to remind you that MSSQL does not have the LIMIT syntax in MYSQL.

2. The TEXT field is truncated.
The above three methods have been tried to connect to the database, because after MSSQL is connected, the data in the SELECT statement is always only 4 K in length, which is due to the limitation of the connection method, I finally checked the information to know that it was php. problems with the configuration of these two sentences in ini:
; Valid range 0-2147483647. Default = 4096.
Mssql. textlimit = 4096

; Valid range 0-2147483647. Default = 4096.
Mssql. textsize = 4096
You can change 4096 to-1 (which indicates no limit), or use mssql_query ("set textsize 65536.

3. there are two problems with different field types in the two databases. One is that the maximum length of a field is supported to avoid being truncated during database insertion, another problem is the date format. I prefer UNIX timestamps. When connecting to MSSQL, you can use "select unix_timestamp (created) from news" to convert DATETIME to MYSQL timestamps in MSSQL. However, during the migration, all fields in the table need to be extracted at one time. The method above does not have the simple "select * from news", and all fields need to be listed. You can SELECT a string directly. For example, in MSSQL, the string is 2006 12:01, and the obtained string is "January 01" (somewhat strange, I don't know why Chinese is generated ). The following function can be used to convert a timestamp:

Function ConvertTime ($ timestring ){
If ($ timestring = null ){
Return 0;
}
$ Time = explode ("", $ timestring );
$ Year = $ time [0];
Switch ($ time [1]) {
Case "January": $ month = "1"; break;
Case "February": $ month = "2"; break;
Case "March": $ month = "3"; break;
Case "April": $ month = "4"; break;
Case "May": $ month = "5"; break;
Case "June": $ month = "6"; break;
Case "July": $ month = "7"; break;
Case "August": $ month = "8"; break;
Case "September": $ month = "9"; break;
Case "October": $ month = "10"; break;
Case "November": $ month = "11"; break;
Case "December": $ month = "12"; break;
Default: break;
}
$ Day = $ time [2];
$ H = 0;
$ M = 0;
$ S = 0;
If (! Empty ($ time [3]) {
$ Time2 = explode (":", $ time [3]);
$ H = $ time2 [0];
$ I = $ time2 [1];
}
// Return date ("Y-m-d H: I: s", mktime ($ h, $ I, $ s, $ month, $ day, $ year ));
Return mktime ($ h, $ I, $ s, $ month, $ day, $ year );
You can use php.exe abc. php to execute the script in the last step. This method has no timeout and is suitable for migrating large volumes of data.
This is basically the content and I hope it will help you.

Update (06/05/05): For timestamp conversion, add
Mssql. datetimeconvert = Off
Then we can get a format similar to without Chinese characters.