WinServer系统上通过SqlServer创建DBlink远程操作MySQL数据库

工具：SqlServer2008,Mysql5.7.22,mysql-connector-odbc-8.0.28-winx64

    SqlServer服务器IP：172.31.2.27
    MySql服务器IP：123.57.41.176（阿里云服务器）

1、在SQLserver机器上安装MySQL ODBC

官方下载odbc驱动 https://dev.mysql.com/downloads/connector/odbc/

官方说明:建议将MySQL Connector / ODBC 8.0+与Mysql server 8.0,5.7,5.6和5.5一起使用。

我们可以下载64位odbc-8.0.28或8.0.29。

下载完成后，到SqlServer服务器172.31.2.27上安装驱动。可以采用自定义安装到D盘下。
2、创建ODBC数据源

打开控制面板->管理工具->数据源(ODBC)->系统DNS->点击右侧添加按钮，选择MySQL ODBC 8.0 Unicode Driver驱动程序，点击完成。

注：这里要说明一下“MySQL ODBC 8.0 ANSI Driver”和“MySQL ODBC 8.0 Unicode Driver”的区别：

①MySQL ODBC 8.0 ANSI Driver 只针对有限的字符集的范围；

②MySQL ODBC 8.0 Unicode Driver提供了更多字符集的支持，也就是提供了多语言的支持。

数据源名称：可自定义（后面创建链接服务器时会用到）

TCP/IP Server:Mysql数据库所在服务器IP地址：123.57.41.176
端口号：默认3306（该端口确保在阿里服务器控制台开启访问权限，非常重要，如果未开启会报10060错误）

 Uesr:Mysql数据库账号；Password:数据库登录密码；Database:数据库名称

 

 3、SqlServer服务器172.31.2.27上创建链接服务器

   1）登录SqlSerer数据库，打开服务器对象，右键链接服务器。点击新建链接服务器：

 2）在常规属性窗口中，输入链接服务器名称（DBLink名称），访问接口选择MySql默认接口名称即可。产品名称输入MySQL（自定义）。数据源：选择控制面板中创建的ODBC数据源名称。

3）点击安全性属性窗口，选择使用此安全上下文建立连接：输入MySQL登录账号和密码：

 点击确定，结束。

如果连接不成功或报错，请检查1）MySQL所在服务器（123.57.41.176）防火墙是否关闭。2）MySQL数据库允许远程服务器连接（root账号授权）

 Mysql>GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY '数据库密码';
 Mysql>flush privileges;
 用户root对应的host为%表示可以允许任何远程服务器连接。

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)

4、对MySql数据库操作 
    1)查询数据

select * from OPENQUERY(ZHIQILIB,
'SELECT EMP_CODE,EMP_NAME,EMP_DEPT_CODE,EMP_DEPT_NAME,BASE_PAY FROM ZHIQI.TB_SALARY WHERE EMP_CODE="0788"')

     ​​​​​2) 插入数据
 

INSERT INTO OPENQUERY(ZHIQILIB,'SELECT SALARY_YEAR,SALARY_MONTH,EMP_CODE,EMP_NAME,EMP_DEPT_CODE,EMP_DEPT_NAME,
BASE_PAY,POST_PAY,JI_NENG_PAY,GONG_LING,ACADEMIC_TITLE,PERFORMANCE,CHAN_LIANG_JIANG,ZHI_LIANG_JIANG,GANG_WEI_JIN_TIE,
YE_BAN_JIN_TIE,JIA_BAN_JIANG,QI_TA_JIANG,TONG_XUN_BU_TIE,JIAO_TONG_BU_TIE,BU_TIE,QI_TA_BU_FA,QI_TA_FU_LI,SHI_JIA_KOU_KUAN,
BIN_JIA_KOU_KUAN,YANG_LAO,YI_LIAO,SHI_YE,GONG_JI_JIN,QI_TA_KOU_KUAN,DAI_KOU_HE_JI,QTDK,YING_FA_HE_JI,BEFORE_TAX,EMP_TAX,AFTER_TAX FROM ZHIQI.TB_SALARY WHERE 1=0') 

Select 
SalaryYear, --SALARY_YEAR  
SalaryMonth, --SALARY_MONTH 
EmpCode,--EMP_CODE
EmpName,--EMP_NAME
EmpDeptCode,--EMP_DEPT_CODE
EmpDeptName,--EMP_DEPT_NAME
BasePay,--BASE_PAY
PostPay,--POST_PAY
JiNengPay,--JI_NENG_PAY
GongLing,--GONG_LING 
AcademicTitle, --ACADEMIC_TITLE
Performance,--PERFORMANCE
ChanLiangJiang,--CHAN_LIANG_JIANG
ZhiLiangJiang,--ZHI_LIANG_JIANG
GangWeiJinTie,--GANG_WEI_JIN_TIE
YeBanJinTie, --YE_BAN_JIN_TIE
JiaBanJiang,--JIA_BAN_JIANG
QiTaJiang,--QI_TA_JIANG
TongXunBuTie,--TONG_XUN_BU_TIE
JiaoTongBuTie,--JIAO_TONG_BU_TIE
BuTie,--BU_TIE
QiTaBuFa,--QI_TA_BU_FA
QiTaFuLi,--QI_TA_FU_LI
ShiJiaKouKuan,--SHI_JIA_KOU_KUAN
BinJiaKouKuan,--BIN_JIA_KOU_KUAN
YangLao,--YANG_LAO
YiLiao,--YI_LIAO
ShiYe,--SHI_YE
GongJiJin,--GONG_JI_JIN
QiTaKouKuan,--QI_TA_KOU_KUAN
DaiKouHeJi,--DAI_KOU_HE_JI
qtdk,--QTDK
YingFaHeJi,--YING_FA_HE_JI
BeforeTax,--BEFORE_TAX
EmpTax,--EMP_TAX
AfterTax  --AFTER_TAX
FROM HRXZ_View
WHERE HRXZ.SalaryYear=dbo.getLastYear(GETDATE()) and HRXZ.SalaryMonth=dbo.getLastMonth(getdate()) 

 3）更新数据

update openquery(ZHIQILIB,'select * from ZHIQI.TB_SALARY')
set name='Jone' where id=1;

 4）删除数据

delete from openquery(ZHIQILIB,'select * from ZHIQI.TB_SALARY')
where id=1;