As a result of work, we need to import all the data stored in the sqlserver database to the mysql database, collect relevant information online, and find two methods. now we will talk about their views separately. The first method is to install mysqlODBC and use sqlserver's export function to select a mysql data source for direct data export. this method is simple, but for the actual use of SQL Server
To import all the data stored in the SQL server database to the mysql database, collect related information on the Internet, and find two methods. now let's talk about them separately.
The first method is to install mysql ODBC and use the export function of SQL server to select the mysql data source for direct data export. this method is simple, but there are many drawbacks for practical applications, the most important manifestation is the data type problem. first, ntext, image, and other data types in the SQL server database cannot be directly written into the mysql database. it is said that only a slight change is required, unfortunately, this Cainiao did not think about how to change it. Secondly, because I set the time in the mysql database design to the int type (the timestamp is saved), after the data is imported, conflicts will occur. Again, the field type of the mysql data table generated by this method is not suitable, so this method is not recommended.
The second method is to use php or asp scripts to import data. this method requires programming, but it is flexible and difficult to operate. everything is under your control, this method is briefly introduced.
The precondition is that your mysql environment has been set up. First, you have created the target database and then generated all the table structures using SQL statements. now everything is ready, and only data is missing.
You can use the following php script to import data from the user table of mydb database in SQL server to mydb database in mysql.
$ Cnx = odbc_connect ('web', 'admin', '000000'); // 'web' is the data source name of mydb in sqlserver, and 'admin' is the user name for accessing mydb, '123' is the password used to access mydb.
$ Cur = odbc_exec ($ cnx, 'select * from user'); // open the user table of mydb database in SQL server
$ Num_row = 0;
$ Conn = mysql_pconnect ("localhost", "root", "123456"); // Connect to mysql
@ Mysql_select_db ('mydb', $ conn) or
Die ("unable to connect to the database, please contact the administrator! "); // Open mysql's mydb database
While (odbc_fetch_row ($ cur) // extracts data from the user table in the mydb database of SQL server one by one. if you select the data, you can add conditions in the preceding select statement.
{
$ Num_row ++;
$ Field1 = odbc_result ($ cur, 1); // here the parameter I (, 3 ..) it refers to the I-th field in the record set. you can select it selectively. fieldi gets the value of the corresponding field, and then you can perform operations on fieldi.
$ Field2 = odbc_result ($ cur, 2 );
$ Field3 = odbc_result ($ cur, 3 );
$ Field4 = odbc_result ($ cur, 4 );
$ Field5 = odbc_result ($ cur, 5 );
$ Field6 = odbc_result ($ cur, 6 );
$ Field5 = timetoint ($ field5); // converts datetime fields in SQL server to the int type I need.
$ Querystring = "insert into user
(Id, name, username, password, recdate)
Values ('$ field1', '$ field2', '$ field3', '$ field4', '$ field5 ')";
Mysql_query ($ querystring, $ conn );
}
Function timetoint ($ str ){
$ Arr1 = split ("", $ str );
$ Datestr = $ arr1 [0];
$ Timestr = $ arr1 [1];
$ Arr_date = split ("-", $ datestr );
$ Arr_time = split (":", $ timestr );
$ Year = $ arr_date [0];
$ Month = $ arr_date [1];
$ Day = $ arr_date [2];
$ Hour = $ arr_time [0];
$ Minute = $ arr_time [1];
$ Second = $ arr_time [2];
$ Time_int = mktime ($ hour, $ minute, $ second, $ month, $ day, $ year );
Return $ time_int;
}
?>
Save the script as SQL. php and execute it on the server to import the data in the user table of mydb database in SQL server to the user table of mydb database in mysql. The operations on other tables are the same as those in other tables.
The following introduces how to import data from the mydb database in SQL server to the mydb database in mysql.
<%
Set conn = server. createobject ("adodb. connection ")
Conn. open 'web', 'admin', '200' // 'web' is the data source name of mydb in sqlserver, and 'admin' is the user name for accessing mydb, '123' is the password used to access mydb.
Set rs = server. createobject ("adodb. recordset ")
SQL = "select ID, name, username, password, datediff (s, '2017-01-01 00:00:00 ', recdate)-8*1970, reid, filename, fileContentType, filevalue from senddate "// This SQL statement converts the datetime type recdate field to the int type of unix timestamp.
Rs. open SQL, conn, 1, 3
Set conn1 = server. createobject ("adodb. connection ")
Conn1.open "myoa", "root", "q1-d6 = 7? "
I = 1
Do while not rs. eof
Field1 = rs (0)
Field2 = rs (1)
Field3 = rs (2)
Field4 = rs (3)
Field5 = rs (4)
Sql1 = "insert into user (ID, name, username, password, recdate)
Values ("& field1 &", '"& field2 &"', '"& field3 &"', '"& field4 &"', "& field5 &")"
Conn1.execute sql1
Rs. movenext
I = I + 1
Loop
Rs. close
Set rs = nothing
Conn. close
Set conn = nothing
Conn1.close
Set conn1 = nothing
%>
The above two methods use php scripts and asp scripts to import data from the user table from SQL server to mysql. in this case, I use two methods to avoid ntext, for image data transmission, change the ntext field to nvarchar (4000). because of the actual situation, the data length of this field in the original data cannot exceed 4000 characters, therefore, there is no data truncation. Another method is to extract the image data and write it to the file, save it as a file, and save the file path to the database. for details, see:
Function makeattach (fileContentType, filevalue, I)
Select case fileContentType
Case "application/msword"
Ext = "doc"
Case "application/vnd. ms-excel"
Ext = "exl"
Case "application/vnd. ms-powerpoint"
Ext = "pps"
Case "application/x-rar-compressed"
Ext = "rar"
Case "application/x-zip-compressed"
Ext = "zip"
Case "image/gif"
Ext = "gif"
Case "image/pjpeg"
Ext = "jpg"
Case "text/plain"
Ext = "txt"
Case else
Ext = "x"
End select
If ext <> "x" then
Set fso = server. createobject ("FileSystemObject ")
FName = "attech" & I & "." & ext
Dir = "d: attach"
If fso. FileExists (Dir & fName) Then fso. deletefile Dir & fName
If fName <> "" and not fso. FileExists (Dir & fName) Then
Set strm1 = Server. CreateObject ("ADODB. Stream ")
Strm1.Open
Strm1.Type = 1' Binary
Strm1.Write filevalue
Strm1.SaveToFile Dir & fName, 2
Set strm1 = Nothing
End if
Makeattach = fName
End if
End function
This function has three input parameters: the first is the contentType of the file, the second is the binary value of the file, and the third is a variable that can distinguish the file name, first, determine the suffix of the stored file based on contentType, then save the binary value to the file with the specified file name, and return the file name as an output parameter, write the returned parameters to the mysql database and save them.
