1. 项目缘起为什么要在Sql Server里操作Mysql最近在做一个老系统的数据迁移项目遇到了一个挺典型的场景核心业务数据还在Sql Server 2008 R2上跑着但一部分新上线的外围系统数据已经用Mysql 8.0来存了。业务部门提了个需求希望能在Sql Server的报表里直接关联查询这两边的数据生成一份统一的业务视图。这要是放几年前我可能第一反应就是写个ETL作业定时把Mysql数据同步到Sql Server里。但这次需求对实时性有点要求而且同步机制本身也是个运维负担。所以能不能让Sql Server“直接”去读Mysql的数据呢就像访问本地表一样。答案是肯定的这就是“链接服务器”的功能。简单说你可以在Sql Server里配置一个指向Mysql的“链接”然后通过这个链接去执行查询、更新甚至连接操作。对于很多从Sql Server单库环境转向多数据库混合架构的团队来说这是个非常实用的技能。网上一搜相关的问题也特别多比如“sql server 连接 mysql”、“odbc 驱动”这些关键词热度一直很高说明这确实是个普遍需求。但说实话我第一次配置的时候也踩了不少坑从驱动版本不对到权限问题再到查询语法报错每一步都可能让你卡半天。尤其是对于刚接触数据库间互操作的朋友那些专业术语像ODBC、OPENQUERY听起来就头大。这篇内容我就把自己趟过的路结合2023年12月这个时间点最新的驱动和常见问题重新梳理一遍目标是让你能跟着步骤一次配通避开我当年掉进去的那些“坑”。2. 核心原理与选型ODBC驱动与链接服务器在开始动手之前我们得先搞清楚Sql Server是怎么“认识”Mysql的。它们俩不是一个厂家的产品语言协议不通不能直接对话。这就需要一个“翻译官”也就是数据库驱动。在这里我们用的是ODBC驱动。ODBC你可以理解为一个标准的数据库访问接口。微软的Sql Server提供了“链接服务器”功能这个功能可以透过ODBC接口去连接各种支持ODBC的数据库Mysql就在其中。所以整个链路是这样的你的Sql Server实例 - 链接服务器配置 - ODBC数据源 - Mysql的ODBC驱动 - 最终连接到Mysql数据库。这里就引出了第一个关键选择用哪个Mysql ODBC驱动目前主流的有两个MySQL Connector/ODBC 8.0这是官方最新版性能好支持Mysql 8.0的新特性如默认的caching_sha2_password认证插件。强烈推荐使用这个版本。MySQL Connector/ODBC 5.3老版本如果你的Mysql是非常旧的5.6或5.7且认证方式还是老的mysql_native_password可以考虑用它。但对于新安装的环境一律建议上8.0。为什么我强调要用8.0因为Mysql 8.0默认的认证插件变了。如果你用5.3的驱动去连8.0的数据库很大概率会报“Authentication plugin caching_sha2_password cannot be loaded”这类错误。虽然可以通过修改Mysql用户密码插件方式绕过去但这增加了不必要的复杂度和安全降级。直接用8.0驱动一劳永逸。选定了驱动我们再来理解“链接服务器”。在Sql Server中配置好一个链接服务器后你会给它起个名字比如MY_MYSQL_LINK。之后你就可以通过一些特殊的查询语法像OPENQUERY或四部分名称来通过这个“链接”访问远程的Mysql数据了。这比动不动就写个同步程序要轻量和灵活得多。3. 实战第一步安装与配置Mysql ODBC 8.0驱动道理讲清楚了我们开始动手。第一步就是在运行Sql Server的那台服务器上安装Mysql ODBC 8.0驱动。记住一定是Sql Server所在的机器不是你本地开发机。3.1 获取驱动安装包最稳妥的方式是去Oracle官网下载。直接搜索“MySQL Connector/ODBC”进入下载页面选择适合你操作系统位数通常Sql Server是64位就选64位MSI Installer的8.0版本。网盘资源可能版本陈旧或有风险不推荐。3.2 安装过程与注意事项运行下载的MSI安装文件。安装过程基本就是一路“Next”但有几个地方需要注意安装类型选择“Complete”完全安装。安装路径默认即可不建议修改。安装程序最后可能会问你是否要配置一个ODBC数据源这里可以先跳过我们后面用手动配置的方式更清晰。安装完成后你可以验证一下打开Windows的“ODBC 数据源管理程序”64位。有两种方式打开运行odbcad32.exe这是32位的管理器注意区分。更好的是直接在文件资源管理器地址栏输入C:\Windows\SysWOW64\odbcad32.exe打开32位管理器或者C:\Windows\System32\odbcad32.exe打开64位管理器。由于我们的Sql Server是64位重点看“系统DSN”或“用户DSN”选项卡里有没有出现“MySQL ODBC 8.0 Unicode Driver”或“MySQL ODBC 8.0 ANSI Driver”。通常我们使用Unicode版本以支持更广泛的字符集。注意这里有个巨坑Windows系统里有两个ODBC数据源管理器一个32位一个64位。如果你装的是64位驱动却在32位的管理器里找是找不到的。确保你用正确位数的管理器查看。对于64位Sql Server务必使用64位的ODBC数据源管理器进行操作。3.3 创建系统DSN光有驱动还不够我们需要创建一个具体的“数据源”告诉ODBC如何去连接我们目标Mysql数据库。在“ODBC 数据源管理器(64位)”中切换到“系统DSN”选项卡点击“添加”。在驱动列表里选择“MySQL ODBC 8.0 Unicode Driver”点击完成。会弹出配置窗口需要填写以下关键信息Data Source Name: 给你这个数据源起个名字比如MyMysqlDSN。这个名字非常重要后面在Sql Server里配置链接服务器时会用到。TCP/IP Server: 填写你的Mysql服务器IP地址和端口例如192.168.1.100:3306。User和Password: 填写有权限访问目标数据库的Mysql用户名和密码。Database: 填写你要默认连接的数据库名。这一步不是必须但建议填上可以避免后续一些麻烦。填好后可以点击“Test”按钮测试连接。如果成功会弹出连接成功的提示。这里如果失败最常见的问题就是网络不通、端口不对、或者用户名密码错误。请逐一排查。4. 在Sql Server中配置链接服务器ODBC数据源配好了翻译官就位了。现在我们要在Sql Server这边正式建立“链接服务器”。4.1 使用SSMS图形界面配置对于新手用SQL Server Management Studio的图形界面最直观。连接上你的Sql Server实例在“服务器对象” - “链接服务器”上右键选择“新建链接服务器”。“常规”页签链接服务器输入一个你喜欢的名字这是你在Sql Server里调用它时用的名字比如MYSQL_LINK。服务器类型选择“其他数据源”。访问接口从下拉框中选择“Microsoft OLE DB Provider for ODBC Drivers”。注意不是“ODBC Driver”本身而是这个OLE DB Provider。产品名称可以填MySQL。数据源这里就填上一步我们在ODBC里创建的系统DSN的名字也就是MyMysqlDSN。“安全性”页签重中之重这里配置如何登录到远程的Mysql。我推荐使用“使用此安全上下文建立连接”远程登录填入连接Mysql的用户名。使用密码填入对应用户的密码。 这样配置后所有通过Sql Server访问这个链接服务器的查询都会使用这个固定的Mysql账号。你也可以选择“模拟”或“不建立连接”等但固定账号的方式最简单问题最少。“服务器选项”页签保持默认即可但可以确认一下“RPC”和“RPC Out”是否设置为“True”这会影响一些存储过程的调用。点击“确定”保存。如果配置信息正确链接服务器就会创建成功。你可以在“链接服务器”下看到新创建的MYSQL_LINK。4.2 使用T-SQL脚本配置对于喜欢脚本化或者需要部署到多台环境的情况用T-SQL更高效。下面是一个示例脚本EXEC master.dbo.sp_addlinkedserver server NMYSQL_LINK, -- 链接服务器名称 srvproductNMySQL, providerNMSDASQL, datasrcNMyMysqlDSN; -- ODBC 系统DSN名称 EXEC master.dbo.sp_addlinkedsrvlogin rmtsrvname NMYSQL_LINK, useself NFalse, locallogin NULL, rmtuser Nyour_mysql_user, -- Mysql用户名 rmtpassword Nyour_mysql_password; -- Mysql密码执行这段脚本效果和图形化操作是一样的。sp_addlinkedserver创建链接sp_addlinkedsrvlogin配置登录映射。5. 连接测试与基础查询OPENQUERY的使用链接服务器创建好了怎么用呢最常用、也最不容易出错的方式是使用OPENQUERY函数。5.1 OPENQUERY 语法解析OPENQUERY函数允许你在链接服务器上直接执行一个查询使用目标数据库的SQL方言然后将结果作为一张表返回给Sql Server。它的基本语法是SELECT * FROM OPENQUERY([链接服务器名称], 你的原生查询语句);[链接服务器名称]就是你在上一步创建的比如MYSQL_LINK。你的原生查询语句这个查询语句是发给Mysql去执行的所以必须使用Mysql的SQL语法而不是T-SQL。5.2 实战查询示例假设我们的Mysql里有个数据库叫test_db里面有张表user。简单查询-- 查询Mysql中user表的所有数据 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT id, name, email FROM test_db.user);执行这个查询Sql Server会把OPENQUERY返回的结果集当作一张本地表来展示。带条件的查询-- 查询id大于10的用户 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM test_db.user WHERE id 10);注意WHERE id 10这个过滤条件是在Mysql端执行的这通常效率更高因为它只把过滤后的结果传回Sql Server。在Sql Server中做进一步处理-- 将OPENQUERY的结果作为子查询与本地表做关联 SELECT l.local_id, r.mysql_name FROM my_local_table l INNER JOIN ( SELECT id AS mysql_id, name AS mysql_name FROM OPENQUERY(MYSQL_LINK, SELECT id, name FROM test_db.user) ) r ON l.remote_user_id r.mysql_id;这里展示了更强大的用法把从Mysql查回来的数据当作一个派生表然后和Sql Server本地的表进行JOIN操作。这对于数据整合报表非常有用。5.3 一个关键的踩坑点查询语句的引号在OPENQUERY的第二个参数里你写的是一条完整的、给Mysql执行的SQL字符串。如果这条SQL里本身有单引号就会和包裹它的单引号冲突。 例如你想在Mysql端执行一个带字符串条件的查询-- 错误写法会导致语法错误 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM user WHERE name John);这里Mysql的查询是SELECT * FROM user WHERE name John但嵌入到OPENQUERY里外层的单引号在John这里就闭合了导致语句错误。 正确的写法是对内部字符串中的单引号进行转义用两个单引号-- 正确写法 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM test_db.user WHERE name John);这是使用OPENQUERY时非常常见的一个错误务必记住。6. 进阶操作四部分名称与数据更新除了OPENQUERY还有一种访问链接服务器的语法叫“四部分名称”格式为[链接服务器名].[数据库名].[架构名].[表名]。但由于Mysql没有“架构”这个概念通常写成[链接服务器名]...[表名]或者[链接服务器名].[数据库名]..[表名]。6.1 使用四部分名称查询-- 假设链接服务器配置了默认的数据库为test_db SELECT * FROM MYSQL_LINK...user; -- 或者明确指定数据库 SELECT * FROM MYSQL_LINK.test_db..user;这种写法看起来更简洁像访问本地链接表一样。但是我强烈不推荐在复杂查询中优先使用这种方式。原因在于当你使用四部分名称时Sql Server可能会尝试将整个查询包括WHERE条件、JOIN等进行“远程传递”但它的传递能力有限对于复杂的T-SQL语法比如某些函数、子查询可能无法正确转换为Mysql的语法导致查询失败或性能低下因为它可能把整张表数据拉到Sql Server内存里再做过滤。而OPENQUERY是明确地将一段“纯”Mysql SQL发送过去执行意图更清晰问题更少。6.2 实现数据更新UPDATE/DELETE/INSERT我们的标题里有“更新”二字那么如何通过链接服务器更新Mysql里的数据呢 同样使用OPENQUERY是最直接可靠的方式因为你可以编写完整的Mysql DML语句。-- 1. 更新数据 SELECT * FROM OPENQUERY(MYSQL_LINK, UPDATE test_db.user SET email new_emailexample.com WHERE id 1); -- OPENQUERY执行UPDATE/DELETE/INSERT本身不返回结果集但你可以用SELECT * FROM OPENQUERY执行一个SELECT来验证 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM test_db.user WHERE id 1); -- 2. 删除数据 SELECT * FROM OPENQUERY(MYSQL_LINK, DELETE FROM test_db.user WHERE id 10); -- 3. 插入数据 SELECT * FROM OPENQUERY(MYSQL_LINK, INSERT INTO test_db.user (name, email) VALUES (Tom, tomexample.com));注意OPENQUERY函数本身需要返回一个结果集。当你用它执行不返回结果的DML语句时在Sql Server的查询窗口里可能会看到一个空结果或者“命令已成功完成”的消息。为了验证操作是否成功通常我会紧接着执行一个SELECT查询。如果你想使用四部分名称进行更新在简单情况下也是可以的但同样有远程查询传递能力的限制UPDATE MYSQL_LINK.test_db..user SET email newexample.com WHERE id 2;这条语句可能会成功但前提是WHERE id 2这个谓词能被成功地“推”到Mysql端去执行。如果不行就可能变成全表更新再过滤极其危险。因此对于更新操作我的建议依然是优先使用OPENQUERY来精确控制发送到Mysql的SQL语句。7. 常见错误排查与性能优化建议配置和使用过程中难免会遇到问题。这里我总结几个最常见的错误和排查思路。7.1 连接失败类错误错误信息链接服务器“(null)”的 OLE DB 访问接口 “MSDASQL” 返回了消息 “[Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序”。排查这说明Sql Server找不到你配置的ODBC数据源。请确认在Sql Server所在的服务器上ODBC数据源系统DSN是否创建成功名字是否拼写正确创建的是64位的系统DSN吗用64位ODBC管理器查看。在Sql Server配置管理器里Sql Server服务SQL Server (MSSQLSERVER)使用的启动账户是否有权限读取这个系统DSN通常使用“本地系统账户”或“网络服务账户”问题不大如果用了自定义域账户可能需要检查权限。错误信息链接服务器“MYSQL_LINK”的 OLE DB 访问接口 “MSDASQL” 返回了消息 “[MySQL][ODBC 8.0(w) Driver]Access denied for user ‘xxx’’xxx’ (using password: YES)”。排查这是Mysql端的权限问题。请确认在链接服务器安全性配置里填写的用户名和密码是否正确。该Mysql用户是否允许从Sql Server所在服务器的IP地址进行连接Mysql的权限是userhost绑定的。可以在Mysql中执行SELECT host, user FROM mysql.user;查看。该用户是否对目标数据库有足够的SELECTUPDATE等权限7.2 查询执行类错误错误信息消息 7321级别 16状态 2第 1 行 准备对链接服务器“MYSQL_LINK”的 OLE DB 访问接口“MSDASQL”执行查询“...”时出错。排查这通常是OPENQUERY内部的SQL语句有语法错误或者访问了不存在的表/列。请仔细检查你写在OPENQUERY第二个参数里的Mysql SQL语句最好能先在Mysql客户端如Workbench里直接运行测试一下确保语法正确且能返回预期结果。错误信息消息 7416级别 16状态 1第 1 行 对 OLE DB 访问接口“MSDASQL”的架构和/或目录的使用无效。排查这在使用四部分名称时常见。尝试改用OPENQUERY语法。如果必须用四部分名称确保数据库名和表名都正确并且Mysql用户有权限访问。7.3 性能优化建议链接服务器的查询性能受网络和两边数据库性能影响很大以下几点可以帮助提升尽量将过滤操作下推到Mysql就像前面例子提到的在OPENQUERY内部写好WHERE子句让Mysql只返回最少量的数据而不是SELECT *全表拉取。只查询需要的列避免使用SELECT *明确列出需要的字段名。谨慎使用JOIN通过链接服务器做跨数据库的JOIN尤其是大表性能开销很大。如果可能考虑定期将Mysql中的维度表同步到Sql Server的临时表或缓存表中让JOIN在本地发生。考虑异步或定时对于实时性要求不高的报表可以建立Sql Server Agent作业定时通过链接服务器将Mysql数据抽取到Sql Server的某张表中后续查询都基于这张本地表进行。这能极大减轻对生产Mysql的即时查询压力。索引是关键确保Mysql端被频繁查询的字段上有合适的索引这对OPENQUERY内部查询的效率有决定性影响。8. 一个完整的实战案例同步用户状态最后我们用一个稍微复杂点的例子把前面的知识串起来。场景Mysql中有一张用户日志表user_log记录用户最后活跃时间。我们需要在Sql Server端每天凌晨更新本地用户表local_user的“是否活跃”状态假设7天内活跃为活跃。步骤1在Sql Server端准备目标表-- 假设本地已有这张表 -- CREATE TABLE local_user (user_id INT PRIMARY KEY, is_active BIT, last_check_date DATE);步骤2编写更新脚本我们使用OPENQUERY从Mysql获取最近7天有日志的用户ID列表然后更新本地表。-- 方法使用 OPENQUERY 获取Mysql数据然后与本地表JOIN更新 BEGIN TRANSACTION; BEGIN TRY -- 先获取需要更新的活跃用户ID列表存入临时表 SELECT mysql_user_id INTO #active_users FROM OPENQUERY(MYSQL_LINK, SELECT DISTINCT user_id AS mysql_user_id FROM test_db.user_log WHERE log_time DATE_SUB(NOW(), INTERVAL 7 DAY) ); -- 更新本地表标记在临时表中的用户为活跃 (is_active 1) UPDATE lu SET lu.is_active 1, lu.last_check_date CAST(GETDATE() AS DATE) FROM local_user lu INNER JOIN #active_users au ON lu.user_id au.mysql_user_id; -- 将不在临时表中的用户标记为非活跃 (is_active 0) UPDATE lu SET lu.is_active 0, lu.last_check_date CAST(GETDATE() AS DATE) FROM local_user lu LEFT JOIN #active_users au ON lu.user_id au.mysql_user_id WHERE au.mysql_user_id IS NULL; DROP TABLE #active_users; COMMIT TRANSACTION; PRINT 数据更新成功; END TRY BEGIN CATCH ROLLBACK TRANSACTION; DROP TABLE IF EXISTS #active_users; PRINT 更新失败: ERROR_MESSAGE(); END CATCH这个脚本展示了几个要点使用OPENQUERY执行Mysql原生查询利用Mysql的日期函数DATE_SUB和NOW()进行过滤效率最高。将远程查询结果存入Sql Server的临时表#active_users避免在复杂UPDATE的JOIN中反复调用OPENQUERY。使用了事务BEGIN TRANSACTION和错误处理TRY...CATCH确保数据更新的原子性要么全部成功要么全部回滚。最后清理临时表。你可以把这个脚本放到Sql Server代理作业里定时执行就实现了一个简单的、通过链接服务器进行的数据同步更新任务。整个过程从驱动安装、DSN配置、链接服务器建立到查询、更新以及错误处理基本上覆盖了日常使用的主要场景。最关键的是理解OPENQUERY的工作机制——它是一扇传送门门那边的世界Mysql有它自己的规则Mysql SQL语法你要过去办事就得遵守那边的规则。只要把握住这一点多实践几次Sql Server连接Mysql这个技能点就算稳稳拿下了。
1. 项目缘起为什么要在Sql Server里操作Mysql最近在做一个老系统的数据迁移项目遇到了一个挺典型的场景核心业务数据还在Sql Server 2008 R2上跑着但一部分新上线的外围系统数据已经用Mysql 8.0来存了。业务部门提了个需求希望能在Sql Server的报表里直接关联查询这两边的数据生成一份统一的业务视图。这要是放几年前我可能第一反应就是写个ETL作业定时把Mysql数据同步到Sql Server里。但这次需求对实时性有点要求而且同步机制本身也是个运维负担。所以能不能让Sql Server“直接”去读Mysql的数据呢就像访问本地表一样。答案是肯定的这就是“链接服务器”的功能。简单说你可以在Sql Server里配置一个指向Mysql的“链接”然后通过这个链接去执行查询、更新甚至连接操作。对于很多从Sql Server单库环境转向多数据库混合架构的团队来说这是个非常实用的技能。网上一搜相关的问题也特别多比如“sql server 连接 mysql”、“odbc 驱动”这些关键词热度一直很高说明这确实是个普遍需求。但说实话我第一次配置的时候也踩了不少坑从驱动版本不对到权限问题再到查询语法报错每一步都可能让你卡半天。尤其是对于刚接触数据库间互操作的朋友那些专业术语像ODBC、OPENQUERY听起来就头大。这篇内容我就把自己趟过的路结合2023年12月这个时间点最新的驱动和常见问题重新梳理一遍目标是让你能跟着步骤一次配通避开我当年掉进去的那些“坑”。2. 核心原理与选型ODBC驱动与链接服务器在开始动手之前我们得先搞清楚Sql Server是怎么“认识”Mysql的。它们俩不是一个厂家的产品语言协议不通不能直接对话。这就需要一个“翻译官”也就是数据库驱动。在这里我们用的是ODBC驱动。ODBC你可以理解为一个标准的数据库访问接口。微软的Sql Server提供了“链接服务器”功能这个功能可以透过ODBC接口去连接各种支持ODBC的数据库Mysql就在其中。所以整个链路是这样的你的Sql Server实例 - 链接服务器配置 - ODBC数据源 - Mysql的ODBC驱动 - 最终连接到Mysql数据库。这里就引出了第一个关键选择用哪个Mysql ODBC驱动目前主流的有两个MySQL Connector/ODBC 8.0这是官方最新版性能好支持Mysql 8.0的新特性如默认的caching_sha2_password认证插件。强烈推荐使用这个版本。MySQL Connector/ODBC 5.3老版本如果你的Mysql是非常旧的5.6或5.7且认证方式还是老的mysql_native_password可以考虑用它。但对于新安装的环境一律建议上8.0。为什么我强调要用8.0因为Mysql 8.0默认的认证插件变了。如果你用5.3的驱动去连8.0的数据库很大概率会报“Authentication plugin caching_sha2_password cannot be loaded”这类错误。虽然可以通过修改Mysql用户密码插件方式绕过去但这增加了不必要的复杂度和安全降级。直接用8.0驱动一劳永逸。选定了驱动我们再来理解“链接服务器”。在Sql Server中配置好一个链接服务器后你会给它起个名字比如MY_MYSQL_LINK。之后你就可以通过一些特殊的查询语法像OPENQUERY或四部分名称来通过这个“链接”访问远程的Mysql数据了。这比动不动就写个同步程序要轻量和灵活得多。3. 实战第一步安装与配置Mysql ODBC 8.0驱动道理讲清楚了我们开始动手。第一步就是在运行Sql Server的那台服务器上安装Mysql ODBC 8.0驱动。记住一定是Sql Server所在的机器不是你本地开发机。3.1 获取驱动安装包最稳妥的方式是去Oracle官网下载。直接搜索“MySQL Connector/ODBC”进入下载页面选择适合你操作系统位数通常Sql Server是64位就选64位MSI Installer的8.0版本。网盘资源可能版本陈旧或有风险不推荐。3.2 安装过程与注意事项运行下载的MSI安装文件。安装过程基本就是一路“Next”但有几个地方需要注意安装类型选择“Complete”完全安装。安装路径默认即可不建议修改。安装程序最后可能会问你是否要配置一个ODBC数据源这里可以先跳过我们后面用手动配置的方式更清晰。安装完成后你可以验证一下打开Windows的“ODBC 数据源管理程序”64位。有两种方式打开运行odbcad32.exe这是32位的管理器注意区分。更好的是直接在文件资源管理器地址栏输入C:\Windows\SysWOW64\odbcad32.exe打开32位管理器或者C:\Windows\System32\odbcad32.exe打开64位管理器。由于我们的Sql Server是64位重点看“系统DSN”或“用户DSN”选项卡里有没有出现“MySQL ODBC 8.0 Unicode Driver”或“MySQL ODBC 8.0 ANSI Driver”。通常我们使用Unicode版本以支持更广泛的字符集。注意这里有个巨坑Windows系统里有两个ODBC数据源管理器一个32位一个64位。如果你装的是64位驱动却在32位的管理器里找是找不到的。确保你用正确位数的管理器查看。对于64位Sql Server务必使用64位的ODBC数据源管理器进行操作。3.3 创建系统DSN光有驱动还不够我们需要创建一个具体的“数据源”告诉ODBC如何去连接我们目标Mysql数据库。在“ODBC 数据源管理器(64位)”中切换到“系统DSN”选项卡点击“添加”。在驱动列表里选择“MySQL ODBC 8.0 Unicode Driver”点击完成。会弹出配置窗口需要填写以下关键信息Data Source Name: 给你这个数据源起个名字比如MyMysqlDSN。这个名字非常重要后面在Sql Server里配置链接服务器时会用到。TCP/IP Server: 填写你的Mysql服务器IP地址和端口例如192.168.1.100:3306。User和Password: 填写有权限访问目标数据库的Mysql用户名和密码。Database: 填写你要默认连接的数据库名。这一步不是必须但建议填上可以避免后续一些麻烦。填好后可以点击“Test”按钮测试连接。如果成功会弹出连接成功的提示。这里如果失败最常见的问题就是网络不通、端口不对、或者用户名密码错误。请逐一排查。4. 在Sql Server中配置链接服务器ODBC数据源配好了翻译官就位了。现在我们要在Sql Server这边正式建立“链接服务器”。4.1 使用SSMS图形界面配置对于新手用SQL Server Management Studio的图形界面最直观。连接上你的Sql Server实例在“服务器对象” - “链接服务器”上右键选择“新建链接服务器”。“常规”页签链接服务器输入一个你喜欢的名字这是你在Sql Server里调用它时用的名字比如MYSQL_LINK。服务器类型选择“其他数据源”。访问接口从下拉框中选择“Microsoft OLE DB Provider for ODBC Drivers”。注意不是“ODBC Driver”本身而是这个OLE DB Provider。产品名称可以填MySQL。数据源这里就填上一步我们在ODBC里创建的系统DSN的名字也就是MyMysqlDSN。“安全性”页签重中之重这里配置如何登录到远程的Mysql。我推荐使用“使用此安全上下文建立连接”远程登录填入连接Mysql的用户名。使用密码填入对应用户的密码。 这样配置后所有通过Sql Server访问这个链接服务器的查询都会使用这个固定的Mysql账号。你也可以选择“模拟”或“不建立连接”等但固定账号的方式最简单问题最少。“服务器选项”页签保持默认即可但可以确认一下“RPC”和“RPC Out”是否设置为“True”这会影响一些存储过程的调用。点击“确定”保存。如果配置信息正确链接服务器就会创建成功。你可以在“链接服务器”下看到新创建的MYSQL_LINK。4.2 使用T-SQL脚本配置对于喜欢脚本化或者需要部署到多台环境的情况用T-SQL更高效。下面是一个示例脚本EXEC master.dbo.sp_addlinkedserver server NMYSQL_LINK, -- 链接服务器名称 srvproductNMySQL, providerNMSDASQL, datasrcNMyMysqlDSN; -- ODBC 系统DSN名称 EXEC master.dbo.sp_addlinkedsrvlogin rmtsrvname NMYSQL_LINK, useself NFalse, locallogin NULL, rmtuser Nyour_mysql_user, -- Mysql用户名 rmtpassword Nyour_mysql_password; -- Mysql密码执行这段脚本效果和图形化操作是一样的。sp_addlinkedserver创建链接sp_addlinkedsrvlogin配置登录映射。5. 连接测试与基础查询OPENQUERY的使用链接服务器创建好了怎么用呢最常用、也最不容易出错的方式是使用OPENQUERY函数。5.1 OPENQUERY 语法解析OPENQUERY函数允许你在链接服务器上直接执行一个查询使用目标数据库的SQL方言然后将结果作为一张表返回给Sql Server。它的基本语法是SELECT * FROM OPENQUERY([链接服务器名称], 你的原生查询语句);[链接服务器名称]就是你在上一步创建的比如MYSQL_LINK。你的原生查询语句这个查询语句是发给Mysql去执行的所以必须使用Mysql的SQL语法而不是T-SQL。5.2 实战查询示例假设我们的Mysql里有个数据库叫test_db里面有张表user。简单查询-- 查询Mysql中user表的所有数据 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT id, name, email FROM test_db.user);执行这个查询Sql Server会把OPENQUERY返回的结果集当作一张本地表来展示。带条件的查询-- 查询id大于10的用户 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM test_db.user WHERE id 10);注意WHERE id 10这个过滤条件是在Mysql端执行的这通常效率更高因为它只把过滤后的结果传回Sql Server。在Sql Server中做进一步处理-- 将OPENQUERY的结果作为子查询与本地表做关联 SELECT l.local_id, r.mysql_name FROM my_local_table l INNER JOIN ( SELECT id AS mysql_id, name AS mysql_name FROM OPENQUERY(MYSQL_LINK, SELECT id, name FROM test_db.user) ) r ON l.remote_user_id r.mysql_id;这里展示了更强大的用法把从Mysql查回来的数据当作一个派生表然后和Sql Server本地的表进行JOIN操作。这对于数据整合报表非常有用。5.3 一个关键的踩坑点查询语句的引号在OPENQUERY的第二个参数里你写的是一条完整的、给Mysql执行的SQL字符串。如果这条SQL里本身有单引号就会和包裹它的单引号冲突。 例如你想在Mysql端执行一个带字符串条件的查询-- 错误写法会导致语法错误 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM user WHERE name John);这里Mysql的查询是SELECT * FROM user WHERE name John但嵌入到OPENQUERY里外层的单引号在John这里就闭合了导致语句错误。 正确的写法是对内部字符串中的单引号进行转义用两个单引号-- 正确写法 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM test_db.user WHERE name John);这是使用OPENQUERY时非常常见的一个错误务必记住。6. 进阶操作四部分名称与数据更新除了OPENQUERY还有一种访问链接服务器的语法叫“四部分名称”格式为[链接服务器名].[数据库名].[架构名].[表名]。但由于Mysql没有“架构”这个概念通常写成[链接服务器名]...[表名]或者[链接服务器名].[数据库名]..[表名]。6.1 使用四部分名称查询-- 假设链接服务器配置了默认的数据库为test_db SELECT * FROM MYSQL_LINK...user; -- 或者明确指定数据库 SELECT * FROM MYSQL_LINK.test_db..user;这种写法看起来更简洁像访问本地链接表一样。但是我强烈不推荐在复杂查询中优先使用这种方式。原因在于当你使用四部分名称时Sql Server可能会尝试将整个查询包括WHERE条件、JOIN等进行“远程传递”但它的传递能力有限对于复杂的T-SQL语法比如某些函数、子查询可能无法正确转换为Mysql的语法导致查询失败或性能低下因为它可能把整张表数据拉到Sql Server内存里再做过滤。而OPENQUERY是明确地将一段“纯”Mysql SQL发送过去执行意图更清晰问题更少。6.2 实现数据更新UPDATE/DELETE/INSERT我们的标题里有“更新”二字那么如何通过链接服务器更新Mysql里的数据呢 同样使用OPENQUERY是最直接可靠的方式因为你可以编写完整的Mysql DML语句。-- 1. 更新数据 SELECT * FROM OPENQUERY(MYSQL_LINK, UPDATE test_db.user SET email new_emailexample.com WHERE id 1); -- OPENQUERY执行UPDATE/DELETE/INSERT本身不返回结果集但你可以用SELECT * FROM OPENQUERY执行一个SELECT来验证 SELECT * FROM OPENQUERY(MYSQL_LINK, SELECT * FROM test_db.user WHERE id 1); -- 2. 删除数据 SELECT * FROM OPENQUERY(MYSQL_LINK, DELETE FROM test_db.user WHERE id 10); -- 3. 插入数据 SELECT * FROM OPENQUERY(MYSQL_LINK, INSERT INTO test_db.user (name, email) VALUES (Tom, tomexample.com));注意OPENQUERY函数本身需要返回一个结果集。当你用它执行不返回结果的DML语句时在Sql Server的查询窗口里可能会看到一个空结果或者“命令已成功完成”的消息。为了验证操作是否成功通常我会紧接着执行一个SELECT查询。如果你想使用四部分名称进行更新在简单情况下也是可以的但同样有远程查询传递能力的限制UPDATE MYSQL_LINK.test_db..user SET email newexample.com WHERE id 2;这条语句可能会成功但前提是WHERE id 2这个谓词能被成功地“推”到Mysql端去执行。如果不行就可能变成全表更新再过滤极其危险。因此对于更新操作我的建议依然是优先使用OPENQUERY来精确控制发送到Mysql的SQL语句。7. 常见错误排查与性能优化建议配置和使用过程中难免会遇到问题。这里我总结几个最常见的错误和排查思路。7.1 连接失败类错误错误信息链接服务器“(null)”的 OLE DB 访问接口 “MSDASQL” 返回了消息 “[Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序”。排查这说明Sql Server找不到你配置的ODBC数据源。请确认在Sql Server所在的服务器上ODBC数据源系统DSN是否创建成功名字是否拼写正确创建的是64位的系统DSN吗用64位ODBC管理器查看。在Sql Server配置管理器里Sql Server服务SQL Server (MSSQLSERVER)使用的启动账户是否有权限读取这个系统DSN通常使用“本地系统账户”或“网络服务账户”问题不大如果用了自定义域账户可能需要检查权限。错误信息链接服务器“MYSQL_LINK”的 OLE DB 访问接口 “MSDASQL” 返回了消息 “[MySQL][ODBC 8.0(w) Driver]Access denied for user ‘xxx’’xxx’ (using password: YES)”。排查这是Mysql端的权限问题。请确认在链接服务器安全性配置里填写的用户名和密码是否正确。该Mysql用户是否允许从Sql Server所在服务器的IP地址进行连接Mysql的权限是userhost绑定的。可以在Mysql中执行SELECT host, user FROM mysql.user;查看。该用户是否对目标数据库有足够的SELECTUPDATE等权限7.2 查询执行类错误错误信息消息 7321级别 16状态 2第 1 行 准备对链接服务器“MYSQL_LINK”的 OLE DB 访问接口“MSDASQL”执行查询“...”时出错。排查这通常是OPENQUERY内部的SQL语句有语法错误或者访问了不存在的表/列。请仔细检查你写在OPENQUERY第二个参数里的Mysql SQL语句最好能先在Mysql客户端如Workbench里直接运行测试一下确保语法正确且能返回预期结果。错误信息消息 7416级别 16状态 1第 1 行 对 OLE DB 访问接口“MSDASQL”的架构和/或目录的使用无效。排查这在使用四部分名称时常见。尝试改用OPENQUERY语法。如果必须用四部分名称确保数据库名和表名都正确并且Mysql用户有权限访问。7.3 性能优化建议链接服务器的查询性能受网络和两边数据库性能影响很大以下几点可以帮助提升尽量将过滤操作下推到Mysql就像前面例子提到的在OPENQUERY内部写好WHERE子句让Mysql只返回最少量的数据而不是SELECT *全表拉取。只查询需要的列避免使用SELECT *明确列出需要的字段名。谨慎使用JOIN通过链接服务器做跨数据库的JOIN尤其是大表性能开销很大。如果可能考虑定期将Mysql中的维度表同步到Sql Server的临时表或缓存表中让JOIN在本地发生。考虑异步或定时对于实时性要求不高的报表可以建立Sql Server Agent作业定时通过链接服务器将Mysql数据抽取到Sql Server的某张表中后续查询都基于这张本地表进行。这能极大减轻对生产Mysql的即时查询压力。索引是关键确保Mysql端被频繁查询的字段上有合适的索引这对OPENQUERY内部查询的效率有决定性影响。8. 一个完整的实战案例同步用户状态最后我们用一个稍微复杂点的例子把前面的知识串起来。场景Mysql中有一张用户日志表user_log记录用户最后活跃时间。我们需要在Sql Server端每天凌晨更新本地用户表local_user的“是否活跃”状态假设7天内活跃为活跃。步骤1在Sql Server端准备目标表-- 假设本地已有这张表 -- CREATE TABLE local_user (user_id INT PRIMARY KEY, is_active BIT, last_check_date DATE);步骤2编写更新脚本我们使用OPENQUERY从Mysql获取最近7天有日志的用户ID列表然后更新本地表。-- 方法使用 OPENQUERY 获取Mysql数据然后与本地表JOIN更新 BEGIN TRANSACTION; BEGIN TRY -- 先获取需要更新的活跃用户ID列表存入临时表 SELECT mysql_user_id INTO #active_users FROM OPENQUERY(MYSQL_LINK, SELECT DISTINCT user_id AS mysql_user_id FROM test_db.user_log WHERE log_time DATE_SUB(NOW(), INTERVAL 7 DAY) ); -- 更新本地表标记在临时表中的用户为活跃 (is_active 1) UPDATE lu SET lu.is_active 1, lu.last_check_date CAST(GETDATE() AS DATE) FROM local_user lu INNER JOIN #active_users au ON lu.user_id au.mysql_user_id; -- 将不在临时表中的用户标记为非活跃 (is_active 0) UPDATE lu SET lu.is_active 0, lu.last_check_date CAST(GETDATE() AS DATE) FROM local_user lu LEFT JOIN #active_users au ON lu.user_id au.mysql_user_id WHERE au.mysql_user_id IS NULL; DROP TABLE #active_users; COMMIT TRANSACTION; PRINT 数据更新成功; END TRY BEGIN CATCH ROLLBACK TRANSACTION; DROP TABLE IF EXISTS #active_users; PRINT 更新失败: ERROR_MESSAGE(); END CATCH这个脚本展示了几个要点使用OPENQUERY执行Mysql原生查询利用Mysql的日期函数DATE_SUB和NOW()进行过滤效率最高。将远程查询结果存入Sql Server的临时表#active_users避免在复杂UPDATE的JOIN中反复调用OPENQUERY。使用了事务BEGIN TRANSACTION和错误处理TRY...CATCH确保数据更新的原子性要么全部成功要么全部回滚。最后清理临时表。你可以把这个脚本放到Sql Server代理作业里定时执行就实现了一个简单的、通过链接服务器进行的数据同步更新任务。整个过程从驱动安装、DSN配置、链接服务器建立到查询、更新以及错误处理基本上覆盖了日常使用的主要场景。最关键的是理解OPENQUERY的工作机制——它是一扇传送门门那边的世界Mysql有它自己的规则Mysql SQL语法你要过去办事就得遵守那边的规则。只要把握住这一点多实践几次Sql Server连接Mysql这个技能点就算稳稳拿下了。
![ExifToolGUI：免费强大的图像元数据批量编辑终极指南 [特殊字符]](http://pic.xiahunao.cn/yaotu/ExifToolGUI：免费强大的图像元数据批量编辑终极指南 [特殊字符])
![Postman便携版终极指南：打造你的免安装API开发环境[特殊字符]](http://pic.xiahunao.cn/yaotu/Postman便携版终极指南：打造你的免安装API开发环境[特殊字符])
