mssql to mysql conversion

mssql to mysql

http://www.mysqltutorial.org/mysql-data-types.aspx

1.  isnull ==> ifnull — 쿼리에서만 변경해야.

1. index.asp 224 chk_DAU_Count > “0”    데이타 타입 미스매치 –> csng
2. getdate() ==> now()
3. top 3 ==> limit 3 or limit 0, 3
4. dbo.  ==> 공백
5. datediff(hh,ubb_indt,getdate())  ==> TIMESTAMPDIFF(HOUR, ubb_indt, now())
6. convert(varchar(10),regdate,120)  ==> date_format(regdate,’%Y-%m-%d’)
7. convert(varchar(10),regdate,112)  ==> date_format(regdate,’%Y%m%d’)
8. view에서 –> end if ;  세미콜론 주의
9. db connection 다수 –> 모두 찾아 대체 utf 포함.
10. with(nolock) ==> 공백 , (nolock) ==>공백
11. nCount from db return == > 타입 캐스팅 csng
12. 현재 부모경로 사용 상태임 — > 수정 변경 필요
13. mysql ltrim, rtrim 지원됨
14. exec sp_xx ==> call sp_xx  프로시져 호출
15. mysql 뷰생성시 from 절 뒤 서브쿼리는 사용 불가능하다
16. TIME_TO_SEC(TIMEDIFF(‘2007-01-09 10:24:46′,’2007-01-09 10:23:46’))
17. not in(select * frm table   limt)  ==> This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
18. select charindex(‘st’,’myteststring’)   0   ==> SELECT INSTR(‘myteststring’,’st’);  1  인덱스 주의

mssql to mysql

1. default ‘getdate()’ –> ‘000-000-000’  ==> 다시 어플에서 now()  추가
2. Visual Studio 2010 Tools for Office Runtime — mssql -> 엑셀 -> mysql 로 convert
3. mysql for excel

mssql to mysql  : exists

IF EXISTS (SELECT 1 FROM Table WHERE FieldValue=”)

BEGIN

SELECT TableID FROM Table WHERE FieldValue=”

END

ELSE

BEGIN

INSERT INTO TABLE(FieldValue) VALUES(”)

SELECT SCOPE_IDENTITY() AS TableID

END

— rewritten for MySQL

IF (SELECT 1 = 1 FROM Table WHERE FieldValue=”) THEN

BEGIN

SELECT TableID FROM Table WHERE FieldValue=”;

END;

ELSE

BEGIN

INSERT INTO Table (FieldValue) VALUES(”);

SELECT LAST_INSERT_ID() AS TableID;

END;

END IF;

myql procedure

http://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5–net-17843

 

DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))

BEGIN

SELECT *

FROM offices

WHERE country = countryName;

END //

DELIMITER ;

CALL GetOfficeByCountry(‘USA’)