sqlserver连接mysql

最新推荐文章于 2025-03-18 20:22:59 发布

最新推荐文章于 2025-03-18 20:22:59 发布 · 143 阅读

文章标签：

#数据库

本文介绍如何使用SQL Server查询分析器执行跨库操作，包括通过安装mysql-connector-odbc实现与MySQL之间的连接，并演示了如何进行数据查询、插入、删除及更新等DML操作。

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