Transact-SQL SQL Server 新增、修改、刪除、查詢 MySQL Windows Community Edition 資料 (OLE DB Provider for ODBC: MSDASQL)

本文將介紹如何以 SQL Server 指令對 MySQL Windows Community Edition (官方免費版) 資料進行新增、修改、刪除、查詢等作業 (OLE DB Provider for ODBC: MSDASQL)。

測試環境

(此處將 SQL Server Express、MySQL 安裝於同一台電腦)
Windows XP Professional SP3、SQL Server 2008 Express、MySQL 5.5 Windows Community Edition (下載 MySQL)


MySQL 部分

於 test 資料庫中，執行下列指令建立測試資料表：。

create table member (sn int, name varchar(50));

OLE DB、ODBC 設定

1) 點選 開始 --> 設定 --> 控制台 --> 系統管理工具 --> 資料來源 (ODBC)

2) 出現 "ODBC 資料來源管理員" 視窗，點選 "系統資料來源名稱" 頁籤 --> 新增 。

3) 出現 "建立新資料來源" 視窗，選擇 "MySQL ODBC 5.1 Driver" 後，按下 "完成" 。

5) 出現 "MySQL Connector/ODBC Data Source Configuration" 視窗，輸入資料後，點選 "OK"。
(以下為筆者的設定)
Data Source Name：msqlODBC
TCP/IP Server：127.0.0.1     (若欲連接至遠端資料庫伺服器，於此處輸入該伺服器 IP 位址。)
Port：3306
User：root
Password：您的 root 登入密碼
Database：test

6) 點選 "確定" 以關閉 "ODBC 資料來源管理員" 視窗。


SQL Server 部分

1) 執行以下指令以建立 "連接的伺服器"

-- 建立遠端伺服器的連接設定 MySQL_ODBC
exec sp_addlinkedserver @server     = N'MySQL_ODBC', 
      @srvproduct = N'', 
      @provider   = N'MSDASQL', 
      @datasrc    = N'msqlODBC';
      

exec sp_addlinkedsrvlogin @rmtsrvname = 'MySQL_ODBC', @useself = 'FALSE', @rmtuser = 'root', @rmtpassword = 'root 帳號使用之密碼' ;

-- 建立 SQL Server 伺服器與遠端伺服器的登入對應 
exec sp_addlinkedsrvlogin @rmtsrvname  = N'MySQL_ODBC', 
        @locallogin  = N'sa', 
        @useself     = N'False', 
        @rmtuser     = N'root',
        @rmtpassword = N'root 帳號使用之密碼';
  
-- 當上述對應關係不存在時，此二伺服器間不建立其他連接。
exec sp_droplinkedsrvlogin @rmtsrvname = N'MySQL_ODBC', @locallogin = NULL ;
go 

2) 新增

insert openquery (MySQL_ODBC, 'select sn, name from member')   values (1, 'Tom Smith') ;
insert openquery (MySQL_ODBC, 'select sn, name from member')   values (2, 'Mary Smith') ;

3) 查詢

select * from openquery(MySQL_ODBC,'select * from member where name like ''%Mary%'' ');

4) 修改

update openquery (MySQL_ODBC, 'select name from member where sn = 1') 
   SET name = 'JJJ';

-- 檢查結果
select * from openquery(MySQL_ODBC,'select * from member order by sn');

5) 刪除

delete openquery (MySQL_ODBC, 'select * from member where sn = 1');

-- 檢查結果
select * from openquery(MySQL_ODBC,'select * from member order by sn');