在实际的数据库应用中，经常会遇到在不同数据库之间连接查询数据或其他一些数据间进行操作的情况。那如果在一种数据库下连接并操作另一种数据库下的数据呢？下面简要介绍在SQL Server数据库下操作MySQL数据库中数据的步骤和方法。

一、相关驱动安装
1、安装MySQL驱动
    想要在SQL Server中操作MySQL，首先要在SQL Server所在的服务器上安装MySQL的驱动。MySQL的驱动安装包在MySQL的官网上可以下载到，我下载到的安装包名为：mysql-connector-odbc-5.1.5-winx32，适用于32位的Windows服务器（下载地址：http://download.csdn.net/detail/qq405165798/1660084）。双击安装

包，直接下一步即可安装成功。安装成功后，你可以在控制面板——管理工具——数据源（ODBC）——驱动程序选项下找到你刚安装的驱动了；当然也可以在运行中输入“odbcad32.exe”打开“ODBC数据源管理器”，点击“驱动程序”选项，可看到“MySQL ODBC 5.1 Driver”已安装成功，如下图所示：

2、建立ODBC数据源
    安装好MySQL的数据源驱动后就可以在SQL Server所在的服务器上建立指向MySQL服务器的ODBC数据源了。在“ODBC数据源管理器”中选择“系统 DSN”选项卡，点击“添加”按钮，如下图所示：

选择“MySQL ODBC 5.1 Driver”，点击“完成”，如下图所示：

    在弹出的配置框中填写数据源名、MySQL服务器IP、端口、用户名和密码，点击“Test”按钮，测试下连接是否正确，如所填参数正确，最后再点击“ok”按钮，ODBC数据源创建成功，如下图所示：

三、建立链接服务器
    建立链接服务器有两种方式，可以通过SQL Server Management Studio中的创建链接服务器向导建立，也可以直接使用SQL语句建立。

1、使用向导建立链接服务器
    在SQL Server Management Studio中连接上SQL Server，然后在“服务器对象”->“链接服务器”上点击右键，选择“新建连接服务器(N)…”。在“链接服务器”框中填写自定义的链接服务器名称，“服务器类型”选择“其他数据源”，“访问接口”选择”Microsoft OLE DB Provider for ODBC Drivers”，“产品名称”跟链接服务器名称填写成一样的，“数据源”填写刚才创建的ODBC数据源，点击“确定”，链接服务器创建成功。

    其实，也可以不建立ODBC数据源，直接建立链接服务器的，只要把“数据源”留空，而在“访问接口字符串”中填写链接字符串

“Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.102;User=root;Password=rootPassword;”即可，如下图所示：

2、使用SQL语句创建链接服务器
可以使用以下SQL语句创建链接服务器，其中使用了ODBC数据源：

EXEC master.dbo.sp_addlinkedserver
@server = 'MyTest', --链接服务器名
@srvproduct = 'mysqlserver', --产品名称
@provider = 'MSDASQL', --访问接口
@datasrc = 'Testmysql' --数据源名称，这里使用了ODBC数据源

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname='MyTest',

@useself='False',

@locallogin=NULL,

@rmtuser=NULL,

@rmtpassword=NULL
GO

也可以使用以下SQL语句创建链接服务器，其中没有使用ODBC数据源，使用的是访问接口字符串：

EXEC master.dbo.sp_addlinkedserver

@server = 'MyTest', --链接服务器名

@srvproduct = 'mysqlserver', --产品名称

@provider = 'MSDASQL', --访问接口

@provstr = 'Driver={MySQL ODBC 5.1 Driver};Server=192.168.0.102;User=root;Password=rootPassword;' --访问接口字符串

 

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname='MyTest',--链接服务器名称

@useself='False',

@locallogin=NULL,

@rmtuser=NULL,--登录远程服务器用户名

@rmtpassword=NULL--登录远程服务器密码

GO

注：这种不使用ODBC数据源创建链接服务器的方式在数据处理中相对比较灵活，一般比较常用。

四、通过SQL Server操作MySQL进行数据查询等操作测试
MySQL数据库test 中的数据表UserInfo的结构如下：

操作该表的方法如下：

1、查询MySQL中的数据
select * from openquery(MyTest,'select * from test.UserInfo');

2、向MySQL表中插入数据
insert into openquery(MyTest,'select * from test.UserInfo')
select 1,'UserName';

3、删除MySQL表中的数据
delete from openquery(MyTest,'select * from test.UserInfo');

4、修改MySQL表中的数据
update openquery(MyTest,'select * from test.UserInfo')
set UserName='jiajia' where UserID=1;

5、openquery参数中使用变量进行查询

示例A（不带变量）：

declare @sql varchar(1000)
declare @tab_name varchar(50)
set  @tab_name='Userinfo'

set @sql='select * from openquery(MyTest,'+''''+' SELECT * FROM test.'+@tab_name+' limit 10;'')'
exec(@sql);

 

示例B（带一个变量）：
declare @dt datetime , @begin_dt varchar(32) , @end_dt varchar(32)
declare @num1 int
declare @num2 int
select @dt= dateadd(dd , -1, getdate())

select  @begin_dt= convert(varchar(32) , convert(varchar(10),@dt,120)+' 00:00:00',121)
select @end_dt= convert(varchar(32),  convert(varchar(10),@dt,120)+' 23:59:59.997',121)

--select @begin_dt,@end_dt
declare @sql1 nvarchar(2000),@sql2 nvarchar(2000)
set @sql1='select buy_num from test.t_user_buy_log where log_dt between '''+@begin_dt+''' and '''+@end_dt+''''
set  @sql1='select @num1=isnull(sum(buy_num),0) from openquery(MyTest,'''+replace(@sql1,'''','"')+''')'
--select @sql1
exec sp_executesql @sql1,N'@num1 int output',@num1 output
--select @num1

set @sql2='select sell_num from test.t_user_sell_log where log_dt between '''+@begin_dt+''' and '''+@end_dt+''' and sell_type = 101 and user_id >= 10000'
set  @sql2='select @num2=isnull(sum(sell_num),0) from openquery(MyTest,'''+REPLACE(@sql2, '''', '"')+''')'
--select @sql2
exec sp_executesql @sql2,N'@num2 int output',@num2 output
--exec(@sql1)
--exec(@sql2)

select '用户购买数：', @num1 , '用户销售数：', @num2, '误差:', abs(@num1 - @num2)

 

示例C（带多个变量）：

declare @begin_dt varchar(32) , @end_dt varchar(32)
declare @user_id int ,@user_tel varchar(20),@buy_num int

select  @begin_dt= '2014-02-26'
select @end_dt= '2014-03-05'

declare @sql1 nvarchar(2000)

set @sql1='
SELECT  user_id,user_tel,SUM(buy_num)buy_num FROM t_user_buy_log WHERE log_dt BETWEEN  '''+@begin_dt+''' AND '''+@end_dt+'''
GROUP BY user_id,user_tel
ORDER BY SUM(buy_num) DESC
LIMIT  1'
set  @sql1='select @user_id=user_id,@user_tel=user_tel,@buy_num=buy_num from openquery(MyTest,'''+replace(@sql1,'''','"')+''')'
--select @sql1
exec sp_executesql @sql1,N'@user_id int output,@user_tel varchar(20) output,@buy_num int output',@user_id output,@user_tel output,@buy_num output

select @user_id,@user_tel,@buy_num,'购买数量最多用户'

 

示例D（返回多条记录，可以联合UNION ALL 做数据联合查询处理）：

declare @begin_dt varchar(32) , @end_dt varchar(32)

select  @begin_dt= '2014-02-26'
select @end_dt= '2014-03-05'

declare @sql1 nvarchar(2000)

--创建一个临时表

select top 0 * into #t_tmp from t_user_buy_log

 

set @sql1='
SELECT  user_id,user_tel,SUM(buy_num)buy_num FROM t_user_buy_log WHERE log_dt BETWEEN  '''+@begin_dt+''' AND '''+@end_dt+'''
GROUP BY user_id,user_tel
ORDER BY SUM(buy_num) DESC
LIMIT  3'
set  @sql1='select * from openquery(MyTest,'''+replace(@sql1,'''','"')+''')'
insert into #t_tmp(user_id,user_tel,buy_num)
exec sp_executesql @sql1

select user_id,user_tel,buy_num,'购买数量最多用户' from #t_tmp

 

本文主要参考：

http://blog.csdn.net/yongsheng0550/article/details/6598252