1.需要遠端連接配接MYSQL的伺服器上安裝mysql-connector-odbc-5.1.8-winx64 (根據自己OS和mysql版本下載下傳相應版本)
2.就可以通過sqlserver查詢分析器執行相關語句操作mysql
3.操作語句DML
SELECT * FROM OPENROWSET(
'MSDASQL',
'DRIVER={MySQL ODBC 5.1 Driver};
SERVER=192.168.2.8;
PORT=3306;DATABASE=suzhou;
USER=root;PASSWORD=password;
STMT=set names gb2312;
OPTION=2049',
'select * from t1;'
)
go
insert OPENROWSET(
'MSDASQL',
'DRIVER={MySQL ODBC 5.1 Driver};
SERVER=192.168.2.8;
PORT=3306;DATABASE=suzhou;
USER=root;PASSWORD=password;
STMT=set names gb2312;
OPTION=2049',
'select * from t1;'
)
(sid,sname,sex)
values(108,'楊文','男')
go
--或
insert OPENROWSET(
'MSDASQL',
'DRIVER={MySQL ODBC 5.1 Driver};
SERVER=192.168.2.8;
PORT=3306;DATABASE=suzhou;
USER=root;PASSWORD=password;
STMT=set names gb2312;
OPTION=2049',
'select * from t1;'
)
values(108,'楊文','男')
go
--或
insert into OPENROWSET(
'MSDASQL',
'DRIVER={MySQL ODBC 5.1 Driver};
SERVER=192.168.2.8;
PORT=3306;DATABASE=suzhou;
USER=root;PASSWORD=password;
STMT=set names gb2312;
OPTION=2049',
'select * from t1;'
)
select * from t1
go
delete from
OPENROWSET(
'MSDASQL',
'DRIVER={MySQL ODBC 5.1 Driver};
SERVER=192.168.2.8;
PORT=3306;DATABASE=suzhou;
USER=root;PASSWORD=password;
STMT=set names gb2312;
OPTION=2049',
'select * from t1;'
)
where sid=0
go
update OPENROWSET( 'MSDASQL', 'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=192.168.2.8; PORT=3306;DATABASE=suzhou; USER=root;PASSWORD=password; STMT=set names gb2312; OPTION=2049', 'select * from t1;' ) set sname='楊文' where sid=107 go