sqlserver跨数据库查询mysql数据

简介

我们知道同种类型数据库，本地数据库要想和远程数据库建立连接：

• oracle使用dblink
• sqlserver使用链接服务器
• mysql使用Federated引擎

大型项目尤其是高并发项目中，不推荐直接在本地数据库与远程数据库之间建立连接。但小型项目中，某些场景下使用数据库之间的连接，反而能让我们更方便的进行系统集成与数据查询。所以凡事无绝对，不同的应用场景考虑不同的技术。

本文我们主要讲述的是不同类型数据库跨库查询数据，如何在sqlserver中直接查询mysql数据。

不同类型数据库之间不能直接建立连接，在这里我们使用mysql-connector-odbc通过ODBC数据源来实现sqlserver跨数据库数据查询mysql数据。

环境说明：
sqlserver版本： Microsoft SQL Server 2008 R2 (RTM)
mysql版本： 5.7.31
mysql-connector-odbc-8.0.28-winx64.msi

详细步骤

第一步：安装mysql-connector-odbc-8.0.28-winx64.msi

下载地址：
https://dev.mysql.com/downloads/file/?id=509492

直接双击安装，接受协议，选择Custom，Change修改安装路径，如下图示：

mysql-connector-01.png

mysql-connector-02.png

如果安装过程中提示：
This application requires Visual Studio 2019 x64 Redistributable. Please install the Redistributable then run this installer again.

解决办法：安装Microsoft Visual C++ 2015-2022 Redistributable(x64)

官方下载地址：
https://learn.microsoft.com/zh-CN/cpp/windows/latest-supported-vc-redist?view=msvc-170
根据提示版本及操作系统类型下载：
vc_redist.x64.exe
vc_redist.x86.exe

下载安装vc_redist后，再继续安装mysql-connector-odbc即可。

第二步：配置ODBC数据源

打开电脑控制面板 -- 系统和安全 -- Windows工具，双击ODBC数据源(64bit)打开ODBC数据源管理程序。

切至系统DSN页签，点击添加按钮，选择刚安装的MySQL ODBC 8.0 Unicode Driver驱动程序，点击完成按钮，如下图示：

mysql-odbc-01.png

然后，填写数据源名称，数据库IP、端口、用户名、密码、数据库等信息，点击“Test”按钮，测试连接是否成功，如下图示：

mysql-odbc-02.png

如上所属，连接成功后，点击OK保存，ODBC数据源就配置完成了。

第三步：sqlserver创建链接服务器并测试连接查询

使用Microsoft SQL Server Management Studio打开本地sqlserver数据库，展开服务器对象 -- 链接服务器，点击右键新建链接服务器，如下图示：

sqlserver-odbc-02.png

填写链接服务器名称：MYSQL-LOCAL
选择访问接口：Microsoft OLE DB Provider for ODBC Drivers
产品名称：mysql-local
数据源：mysql-local

然后左上角选择安全性，点击“使用此安全上下文建立连接”，输入远程登录用户名和密码。点击确定即可。如下图示：

sqlserver-odbc-03.png

注意：严格控制远程用户权限。

如上配置完后，刷新链接服务器，MYSQL-LOCAL点击右键“测试连接”，测试链接服务器是否连接成功。如下图示：

sqlserver-odbc-04.png

展开链接服务器MYSQL-LOCAL -- 目录 -- 数据库，即可查看到有权限的表和视图。

然后，新建查询查询远程数据库数据：

-- 查询链接数据库数据
select * from openquery([MYSQL-LOCAL], 'select * from sys_user a ' );
 

执行结果如下图示：

sqlserver-odbc-05.png

也可以将以上查询创建为视图，这样就和操作本地表或视图一样，直接查询即可。

create view vw_user as 
select * from openquery([MYSQL-LOCAL], 'select * from sys_user a ' );

select * from vw_user ; 

扩展示例：使用链接服务器执行远程数据库表的增删查改（不推荐实际项目中使用，一般只用跨库查询）

-- 查询数据
SELECT * FROM OPENQUERY([MYSQL-LOCAL], 'select * from sys_user a ' );

-- 插入数据
INSERT OPENQUERY([MYSQL-LOCAL], 'SELECT user_id, user_name, nick_name FROM sys_user')
VALUES ('100', 'test', '测试');

-- 更新数据
UPDATE OPENQUERY([MYSQL-LOCAL], 'SELECT user_id, user_name, nick_name FROM sys_user WHERE user_id = 100')
SET nick_name = 'test2';

-- 删除数据
DELETE OPENQUERY([MYSQL-LOCAL], 'SELECT user_id FROM sys_user WHERE user_id = 100');

（完）