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. 数据同步
- 说明
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数据库