{"id":371,"date":"2016-03-31T20:30:30","date_gmt":"2016-03-31T11:30:30","guid":{"rendered":"http:\/\/www.onepage.co.kr\/wordpress\/?p=371"},"modified":"2016-04-01T00:20:33","modified_gmt":"2016-03-31T15:20:33","slug":"mssql-to-mysql","status":"publish","type":"post","link":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/2016\/03\/31\/mssql-to-mysql\/","title":{"rendered":"mssql to mysql conversion"},"content":{"rendered":"

mssql to mysql<\/p>\n

http:\/\/www.mysqltutorial.org\/mysql-data-types.aspx<\/a><\/p>\n

1.\u00a0 isnull ==> ifnull — \ucffc\ub9ac\uc5d0\uc11c\ub9cc \ubcc0\uacbd\ud574\uc57c.<\/p>\n

    \n
  1. index.asp 224 chk_DAU_Count > “0”\u00a0 \u00a0 \ub370\uc774\ud0c0 \ud0c0\uc785 \ubbf8\uc2a4\ub9e4\uce58 –> csng<\/li>\n
  2. getdate() ==> now()<\/li>\n
  3. top 3 ==> limit 3 or limit 0, 3<\/li>\n
  4. dbo.\u00a0 ==> \uacf5\ubc31<\/li>\n
  5. datediff(hh,ubb_indt,getdate())\u00a0 ==> TIMESTAMPDIFF(HOUR, ubb_indt, now())<\/li>\n
  6. convert(varchar(10),regdate,120)\u00a0 ==> date_format(regdate,’%Y-%m-%d’)<\/li>\n
  7. convert(varchar(10),regdate,112)\u00a0 ==> date_format(regdate,’%Y%m%d’)<\/li>\n
  8. view\uc5d0\uc11c –> end if ;\u00a0 \uc138\ubbf8\ucf5c\ub860 \uc8fc\uc758<\/li>\n
  9. db connection \ub2e4\uc218 –> \ubaa8\ub450 \ucc3e\uc544 \ub300\uccb4 utf \ud3ec\ud568.<\/li>\n
  10. with(nolock) ==> \uacf5\ubc31 , (nolock) ==>\uacf5\ubc31<\/li>\n
  11. nCount from db return == > \ud0c0\uc785 \uce90\uc2a4\ud305 csng<\/li>\n
  12. \ud604\uc7ac \ubd80\ubaa8\uacbd\ub85c \uc0ac\uc6a9 \uc0c1\ud0dc\uc784 — > \uc218\uc815 \ubcc0\uacbd \ud544\uc694<\/li>\n
  13. mysql ltrim, rtrim \uc9c0\uc6d0\ub428<\/li>\n
  14. exec sp_xx ==> call sp_xx\u00a0 \ud504\ub85c\uc2dc\uc838 \ud638\ucd9c<\/li>\n
  15. mysql \ubdf0\uc0dd\uc131\uc2dc from \uc808 \ub4a4 \uc11c\ube0c\ucffc\ub9ac\ub294 \uc0ac\uc6a9 \ubd88\uac00\ub2a5\ud558\ub2e4<\/li>\n
  16. TIME_TO_SEC(TIMEDIFF(‘2007-01-09 10:24:46′,’2007-01-09 10:23:46’))<\/li>\n
  17. not in(select * frm table \u00a0 limt)\u00a0 ==> This version of MySQL doesn’t yet support ‘LIMIT & IN\/ALL\/ANY\/SOME subquery’<\/li>\n
  18. select charindex(‘st’,’myteststring’) \u00a0 0 \u00a0 ==> SELECT INSTR(‘myteststring’,’st’);\u00a0 1\u00a0 \uc778\ub371\uc2a4 \uc8fc\uc758<\/li>\n<\/ol>\n

    mssql to mysql<\/p>\n

      \n
    1. default ‘getdate()’ –> ‘000-000-000’\u00a0 ==> \ub2e4\uc2dc \uc5b4\ud50c\uc5d0\uc11c now()\u00a0 \ucd94\uac00<\/li>\n
    2. Visual Studio 2010 Tools for Office Runtime — mssql -> \uc5d1\uc140 -> mysql \ub85c convert<\/li>\n
    3. mysql for excel<\/li>\n<\/ol>\n

      mssql to mysql\u00a0 : exists<\/p>\n

      IF EXISTS (SELECT 1 FROM Table WHERE FieldValue=”)<\/p>\n

      BEGIN<\/p>\n

      SELECT TableID FROM Table WHERE FieldValue=”<\/p>\n

      END<\/p>\n

      ELSE<\/p>\n

      BEGIN<\/p>\n

      INSERT INTO TABLE(FieldValue) VALUES(”)<\/p>\n

      SELECT SCOPE_IDENTITY() AS TableID<\/p>\n

      END<\/p>\n

      — rewritten for MySQL<\/p>\n

      IF (SELECT 1 = 1 FROM Table WHERE FieldValue=”) THEN<\/p>\n

      BEGIN<\/p>\n

      SELECT TableID FROM Table WHERE FieldValue=”;<\/p>\n

      END;<\/p>\n

      ELSE<\/p>\n

      BEGIN<\/p>\n

      INSERT INTO Table (FieldValue) VALUES(”);<\/p>\n

      SELECT LAST_INSERT_ID() AS TableID;<\/p>\n

      END;<\/p>\n

      END IF;<\/p>\n

      myql procedure<\/p>\n

      http:\/\/code.tutsplus.com\/articles\/an-introduction-to-stored-procedures-in-mysql-5–net-17843<\/a><\/p>\n

       <\/p>\n

      DELIMITER \/\/<\/p>\n

      CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))<\/p>\n

      BEGIN<\/p>\n

      SELECT *<\/p>\n

      FROM offices<\/p>\n

      WHERE country = countryName;<\/p>\n

      END \/\/<\/p>\n

      DELIMITER ;<\/p>\n

      CALL GetOfficeByCountry(‘USA’)<\/p>\n","protected":false},"excerpt":{"rendered":"

      mssql to mysql http:\/\/www.mysqltutorial.org\/mysql-data-types.aspx 1.\u00a0 isnull ==> ifnull — \ucffc\ub9ac\uc5d0\uc11c\ub9cc \ubcc0\uacbd\ud574\uc57c. index.asp 224 chk_DAU_Count > “0”\u00a0 \u00a0 \ub370\uc774\ud0c0 \ud0c0\uc785 \ubbf8\uc2a4\ub9e4\uce58 –> csng getdate() ==> now() top 3 ==> limit 3 or limit 0, 3 dbo.\u00a0 ==> \uacf5\ubc31 datediff(hh,ubb_indt,getdate())\u00a0 ==> TIMESTAMPDIFF(HOUR, ubb_indt, now()) convert(varchar(10),regdate,120)\u00a0 ==> date_format(regdate,’%Y-%m-%d’) convert(varchar(10),regdate,112)\u00a0 ==> date_format(regdate,’%Y%m%d’) view\uc5d0\uc11c –> end if ;\u00a0 \uc138\ubbf8\ucf5c\ub860 \uc8fc\uc758 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,5],"tags":[],"class_list":["post-371","post","type-post","status-publish","format-standard","hentry","category-mariadb-mysql","category-mssql"],"_links":{"self":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/371","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=371"}],"version-history":[{"count":5,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/371\/revisions"}],"predecessor-version":[{"id":379,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/371\/revisions\/379"}],"wp:attachment":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}