sqlserver openrowset mysql_sqlserver连接mysql

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