{"id":310863,"date":"2024-01-20T10:44:18","date_gmt":"2024-01-20T02:44:18","guid":{"rendered":"https:\/\/www.idc.net\/help\/310863\/"},"modified":"2024-01-20T10:44:18","modified_gmt":"2024-01-20T02:44:18","slug":"sql-server-2008%e9%80%9a%e8%bf%87linkserver%e8%ae%bf%e9%97%aemysql%e6%95%b0%e6%8d%ae%e5%ba%93","status":"publish","type":"post","link":"https:\/\/idc.net\/help\/310863\/","title":{"rendered":"SQL Server 2008\u901a\u8fc7LinkServer\u8bbf\u95eeMySQL\u6570\u636e\u5e93"},"content":{"rendered":"

\u600e\u6837\u8ba9SQL Server 2008<\/strong>\u6570\u636e\u5e93\u53ef\u4ee5\u8bbf\u95eeMySQL<\/strong>\u6570\u636e\u5e93\u7684\u6570\u636e\u5462\uff1f\u5176\u5b9e\u8fd9\u4e00\u5b9e\u73b0\u7684\u8fc7\u7a0b\u5e76\u4e0d\u96be\uff0c\u53ef\u4ee5\u901a\u8fc7\u8c03\u7528MySQL\u7684ODBC\u9a71\u52a8\uff0c\u5728SQL Server\u4e2d\u6dfb\u52a0LinkServer<\/strong>\u7684\u65b9\u5f0f\u5b9e\u73b0\u3002\u672c\u6587\u6211\u4eec\u5c31\u4ecb\u7ecd\u4e86\u8fd9\u4e00\u8bbf\u95ee\u8fc7\u7a0b\uff0c\u73b0\u5728\u6211\u4eec\u5c31\u5f00\u59cb\u4ecb\u7ecd\uff0c\u8ba9\u6211\u4eec\u6765\u4e00\u8d77\u5b66\u4e60\u5427\u3002<\/p>\n

\u5b9e\u73b0\u6b65\u9aa4\uff1a<\/strong><\/p>\n

1.\u4eceMySQL\u7f51\u7ad9\u4e0b\u8f7d\u6700\u65b0\u7684MySQL ODBC\u9a71\u52a8\uff1ahttp:\/\/www.mysql.com\/downloads\/connector\/odbc\/\uff0c\u76ee\u524d\u7684\u7248\u672c\u662f5.1.8<\/p>\n

2.\u5728SQL Server\u6240\u5728\u7684\u670d\u52a1\u5668\u5b89\u88c5<\/p>\n

3.\u5728SQL Server\u5efa\u7acbLinkserver\uff0c\u53ef\u901a\u8fc7\u5982\u4e0b\u811a\u672c\u5b9e\u73b0\uff1a<\/p>\n

 <\/p>\n

\n \n \n 
    \n  \n  \n  
  1. USE [master]  <\/span><\/span><\/li>\n  \n  \n  
  2.  <\/span><\/li>\n  \n  \n  
  3. GO  <\/span><\/li>\n  \n  \n  
  4.  <\/span><\/li>\n  \n  \n  
  5. EXEC master.dbo.sp_addlinkedserver   <\/span><\/li>\n  \n  \n  
  6.  <\/span><\/li>\n  \n  \n  
  7. @<\/span>server<\/span> = N<\/span>'ServerName'<\/span>,  <\/span><\/li>\n  \n  \n  
  8.  <\/span><\/li>\n  \n  \n  
  9. @<\/span>srvproduct<\/span> = N<\/span>'ServerName'<\/span>,  <\/span><\/li>\n  \n  \n  
  10.  <\/span><\/li>\n  \n  \n  
  11. @<\/span>provider<\/span> = N<\/span>'MSDASQL'<\/span>,  <\/span><\/li>\n  \n  \n  
  12.  <\/span><\/li>\n  \n  \n  
  13. @<\/span>provstr<\/span> = N<\/span>'Driver={MySQL ODBC 5.1 Driver};Server=ServerName;Database=DBName;User=Username;Password=pwd;Option=3;'<\/span>,  <\/span><\/li>\n  \n  \n  
  14.  <\/span><\/li>\n  \n  \n  
  15. @<\/span>catalog<\/span> = N<\/span>'DBName'<\/span> <\/span><\/li>\n \n \n <\/ol><\/pre>\n
     <\/p>\n
    \u4e4b\u540e\u53ef\u4ee5\u901a\u8fc7OPENQUERY\u7684\u65b9\u5f0f\u8c03\u7528MySQL\u7684\u6570\u636e\uff1a<\/p>\n
     <\/p>\n
    \n \n \n 
      \n  \n  \n  
    1. INSERT INTO OPENQUERY(Servername,'select * from DBName.tablename')  <\/span><\/span><\/li>\n  \n  \n  
    2.  <\/span><\/li>\n  \n  \n  
    3. SELECT '1'  <\/span><\/li>\n  \n  \n  
    4.  <\/span><\/li>\n  \n  \n  
    5. SELECT *  <\/span><\/li>\n  \n  \n  
    6.  <\/span><\/li>\n  \n  \n  
    7. FROM OPENQUERY(Servername,'select * from DBName.tablename')  <\/span><\/li>\n  \n  \n  
    8.  <\/span><\/li>\n  \n  \n  
    9. UPDATE OPENQUERY(Servername,'select * from DBName.tablename')  <\/span><\/li>\n  \n  \n  
    10.  <\/span><\/li>\n  \n  \n  
    11. SET <\/span>colname<\/span>= <\/span>'2'<\/span> <\/span><\/li>\n  \n  \n  
    12.  <\/span><\/li>\n  \n  \n  
    13. WHERE <\/span>colname<\/span>= <\/span>'1'<\/span> <\/span><\/li>\n  \n  \n  
    14.  <\/span><\/li>\n  \n  \n  
    15. SELECT *  <\/span><\/li>\n  \n  \n  
    16.  <\/span><\/li>\n  \n  \n  
    17. FROM OPENQUERY(Servername,'select * from DBName.tablename') <\/span><\/li>\n \n \n <\/ol><\/pre>\n
       <\/p>\n
      \u6309\u7167\u4e0a\u9762\u7ed9\u51fa\u7684\u6b65\u9aa4\u64cd\u4f5c\uff0c\u6211\u4eec\u5c31\u80fd\u591f\u8ba9SQL Server 2008\u6570\u636e\u5e93\u901a\u8fc7LinkServer\u6765\u8bbf\u95eeMySQL\u6570\u636e\u5e93\u7684\u6570\u636e\u4e86\u3002\u672c\u6587\u5c31\u4ecb\u7ecd\u5230\u8fd9\u91cc\uff0c\u8c22\u8c22\u5404\u4f4d\u7684\u6d4f\u89c8\uff01<\/p>\n
      \u3010\u7f16\u8f91\u63a8\u8350\u3011<\/p>\n
        \n
      1. SQL SERVER \u6570\u636e\u6316\u6398\u4e4b\u7406\u89e3\u5185\u5bb9\u7c7b\u578b<\/li>\n
      2. SQL SERVER\u6570\u636e\u6316\u6398\u4e4b\u7406\u89e3\u5217\u7684\u7528\u6cd5<\/li>\n
      3. SQL Server\u6570\u636e\u5e93\u4e3b\u952e\u53ca\u590d\u5408\u4e3b\u952e\u7684\u914d\u7f6e<\/li>\n
      4. SQL Server\u6570\u636e\u5e93\u5982\u4f55\u66f4\u6539SA\u5bc6\u7801\u548c\u9ed8\u8ba4\u7aef\u53e3\u53f7<\/li>\n
      5. \u8c08\u4e00\u8c08SQL Server\u6570\u636e\u5e93\u521b\u5efa\u5916\u952e\u7ea6\u675f\u65f6\u7684\u4e24\u4e2a\u5c0f\u5e94\u7528<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
        \u600e\u6837\u8ba9SQL Server 2008\u6570\u636e\u5e93\u53ef\u4ee5\u8bbf\u95eeMySQL\u6570\u636e\u5e93\u7684\u6570\u636e\u5462\uff1f\u5176\u5b9e\u8fd9\u4e00\u5b9e\u73b0\u7684\u8fc7\u7a0b\u5e76\u4e0d\u96be\uff0c\u53ef\u4ee5\u901a\u8fc7 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7038],"tags":[],"class_list":["post-310863","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/310863","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/comments?post=310863"}],"version-history":[{"count":0,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/310863\/revisions"}],"wp:attachment":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/media?parent=310863"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/categories?post=310863"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/tags?post=310863"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}