ODBC实现跨数据库同步数据

1. odbc介绍

ODBC(Open Database Connectivity，开放数据库互连)是微软公司开放服务结构(WOSA，Windows Open Services Architecture)中有关数据库的一个组成部分，它建立了一组规范，并提供了一组对数据库访问的标准API（应用程序编程接口）。这些API利用SQL来完成其大部分任务。ODBC本身也提供了对SQL语言的支持，用户可以直接将SQL语句送给ODBC。一组数据的位置，可以使用 ODBC驱动程序访问该位置。

一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS，不直接与DBMS打交道，所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说，不论是FoxPro、Access还是Oracle数据库，均可用ODBC API进行访问。由此可见，ODBC的最大优点是能以统一的方式处理所有的数据库。

2. 配置odbc

在sql server服务器上面配置odbc

2.1. 打开windows图标，搜索odbc（选64位），如图

注意：要选择"系统DSN"

2.2. 安装mysqlodbc数据源，安装文件：mysql-odbc.zip

默认安装mysql-connector-odbc-8.0.30-winx64.msi，再安装vc_redist.x64.exe

2.3. 创建mysql远程访问的账号和密码

grant select,insert,update,delete on test_mysql.* to ‘test’@‘192.168.0.113’ identified by ‘123abc’;
说明：给ip为192.168.0.113的设备，创建账号test，密码123456，访问数据库test_mysql，访问权限为，select,insert,update,delete

2.4. 选择"系统DNS"，点击"添加"按钮，选择mysqlodbc数据源，如图

• 说明：
  a. Data source Name：表示数据源名称，供sql server调用
  b. TCP/IP Server：设置访问mysql服务器的IP和端口
  c. User：设置远程访问mysql的账号
  d. Password：设置远程访问mysql的密码
  e. Database：设置远程访问mysql的数据库
  f. 设置好后，点击Test按钮，验证是否设置成功

3. 创建链接服务器

3.1. 打开Microsoft SQL Server Management Studio，连接上服务器，选择：服务器对象-》链接服务器，点击右键，如图

3.2. 设置"常规"页面

• 说明：
  a. 链接服务器：自己命名
  b. 提供程序：一定要选图片上面的
  c. 产品名称：自己命名
  d. 数据源：一定要写之前设置的odbc数据源
  e. 其他的可以不用填

3.3. 设置"安全性"页面

• 说明：
  a. 选择"使用此安全上下文建立链接"
  b. 远程登录：mysql远程访问的账号
  c. 使用密码：mysql远程访问的密码

4. 数据同步

openquery官网介绍

• 说明

a. 远程mysql数据库中有一张m_account_usr表，里面有以下字段：M_Id，M_No，M_Name，M_RegTime
b. SQL Server有一张s_account_usr表，里面有以下字段：S_Id，S_No，S_Name，S_RegTime
c. SQL Serverl里面s_account_usr同步到mysql的m_account_usr表
d. 链接数据库名称：QMS

4.1. 查询

4.1.1. 查询mysql数据表m_account_usr

SELECT * FROM OPENQUERY (QMS, 'SELECT * FROM m_account_usr;');

4.1.2. 查询SQL Server表s_account_usr

SELECT * FROM s_account_usr;

4.2. 新增

SQL Server数据新增到mysql数据库

INSERT OPENQUERY (QMS, 'SELECT * FROM m_account_usr;') (M_No，M_Name) select S_No，S_Name FROM s_account_usr where S_No not in (select M_No from openquery([QMS], 'select M_No from m_account_usr;'));
写入数据时增加判断，是否已经写入

4.3. 更新

UPDATE OPENQUERY (QMS, 'SELECT * FROM m_account_usr;') SET M_Name=S_Name from s_account_usr where M_No=S_No and M_Name!=S_Name;
判断数据有改变才更新

4.4. 删除

DELETE OPENQUERY (QMS, 'SELECT * FROM m_account_usr WHERE M_Name=''xxx''');
. 删除

下一篇文章结合项目，实际运用：SQL server定时新增和更新到mysql数据库