March 2016 – 15seconds

mssql to mysql conversion

mssql to mysql

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

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

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

mssql to mysql

default ‘getdate()’ –> ‘000-000-000’ ==> 다시 어플에서 now() 추가
Visual Studio 2010 Tools for Office Runtime — mssql -> 엑셀 -> mysql 로 convert
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’)

Microsoft SQL Server Type Mapping

10.5.4 Microsoft SQL Server Type Mapping

Table 10.2 Type mapping

Source Type	MySQL Type	Comment
INT	INT
TINYINT	TINYINT	UNSIGNED flag set in MySQL
SMALLINT	SMALLINT
BIGINT	BIGINT
BIT	TINYINT(1)
FLOAT	FLOAT	Precision value is used for storage size in both
REAL	FLOAT
NUMERIC	DECIMAL
DECIMAL	DECIMAL
MONEY	DECIMAL
SMALLMONEY	DECIMAL
CHAR	CHAR/LONGTEXT	Depending on its length. MySQL Server 5.5 and above can have CHAR columns with a length up to 255 characters. Anything larger is migrated as LONGTEXT
NCHAR	CHAR/LONGTEXT	Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
VARCHAR	VARCHAR/MEDIUMTEXT/LONGTEXT	Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types.
NVARCHAR	VARCHAR/MEDIUMTEXT/LONGTEXT	Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
DATE	DATE
DATETIME	DATETIME
DATETIME2	DATETIME	Date range in MySQL is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. Note: fractional second values are only stored as of MySQL Server 5.6.4
SMALLDATETIME	DATETIME
DATETIMEOFFSET	DATETIME
TIME	TIME
TIMESTAMP	TIMESTAMP
ROWVERSION	TIMESTAMP
BINARY	BINARY/MEDIUMBLOB/LONGBLOB	Depending on its length
VARBINARY	VARBINARY/MEDIUMBLOB/LONGBLOB	Depending on its length
TEXT	VARCHAR/MEDIUMTEXT/LONGTEXT	Depending on its length
NTEXT	VARCHAR/MEDIUMTEXT/LONGTEXT	Depending on its length
IMAGE	TINYBLOB/MEDIUMBLOB/LONGBLOB	Depending on its length
SQL_VARIANT	not migrated	There is not specific support for this datatype.
TABLE	not migrated	There is not specific support for this datatype.
HIERARCHYID	not migrated	There is not specific support for this datatype.
UNIQUEIDENTIFIER	VARCHAR(64)	A unique flag set in MySQL. There is not specific support for inserting unique identifier values.
SYSNAME	VARCHAR(160)
XML	TEXT

Source Type	MySQL Type	Comment

mssql vs mysql data type mapping

@@ mssql vs mysql data type mapping

http://dev.mysql.com/doc/workbench/en/wb-migration-database-mssql-typemapping.html

http://www.sqlines.com/sql-server-to-mysql/functions/convert_string

mariadb, mysql feartures

timestamp default는 테이블에 오직 하나만 가능.
테이블 upt-date 는 timestap –> datetime으로 변경 default 제거, null 허용해 해결
timestamp는 default 가능한 2038년에 만료됨.

mssql server 2005 paging

SET @RowStart = @PageSize * (@Page-1) + 1;
SET @RowEnd = @RowStart + @PageSize – 1 ;

SELECT * FROM (

SELECT RowNumber() OVER(order by USER_ID) As RowID,

COUNT (USER_ID) OVER (PARTITION BY null) AS TOTAL_ROWS,

name

from usertbl

) As RowResults

WHERE RowID Between @RowStart AND @RowEnd

Order by RowID asc

mssql Lock

mssql lock

http://blog.naver.com/PostView.nhn?blogId=tarius74&logNo=220272058523

Table definition 테이블 정의서 인덱스 정의서

테이블 정의서 2008 @@

select
case row_number() over(partition by o.name order by o.name, c.column_id)
when 1 then o.name else ” end “Table Name”
,c.name “Column Name”
,case t.name
when ‘varchar’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
when ‘char’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
when ‘datetime’ then t.name
else t.name+'(‘+cast(c.precision as varchar)+’,’+cast(c.scale as varchar)+’)’ end
as “Column Type”
,case c.is_nullable when 0 then ‘NOT NULL’ else ” end “Nullable”
,isnull(d.definition,”) “Column Default”
,case when k.index_id is null then ” else ‘YES’ end “PK”
,e.value “Column Comment”
from sys.tables o
join sys.columns c on(o.object_id = c.object_id and o.type=’U’)
join sys.types t on(c.system_type_id = t.system_type_id and t.schema_id=4)
left outer join sys.extended_properties e on(c.object_id = e.major_id and c.column_id = e.minor_id)
left outer join sys.index_columns k on(c.object_id = k.object_id and c.column_id = k.column_id and index_id=1)
left outer join sys.default_constraints d on(c.object_id=d.parent_object_id and c.column_id=d.parent_column_id and d.type=’D’)
where o.name <> ‘sysdiagrams’
order by o.name, c.column_id
;

[출처] [2008] 테이블 칼럼 정의서 뽑는 쿼리|작성자 젠틀맨

인덱스 정의서 2008

SELECT
CASE table_RN WHEN 1 THEN A.table_name ELSE ” END table_name,
CASE RN WHEN 1 THEN A.index_name ELSE ” END index_name,
index_column_id,
colum_name,
is_unique,
is_primary_key
FROM
(

select
ROW_NUMBER() OVER(ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) sort_RN,
ROW_NUMBER() OVER(PARTITION BY o.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) table_RN,
ROW_NUMBER() OVER(PARTITION BY o.name, i.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) RN,
o.name table_name,
i.name index_name,
b.name colum_name,
a.index_column_id,
case i.is_unique when 1 then ‘YES’ else ” end is_unique,
case i.is_primary_key when 1 then ‘YES’ else ” end is_primary_key,
i.is_primary_key is_primary_key_idx
from
sys.all_objects o join
sys.indexes i on(o.object_id=i.object_id) join
sys.index_columns a on(i.object_id=a.object_id and i.index_id = a.index_id)
join sys.columns b on
(a.object_id = b.object_id and a.column_id = b.column_id)
where o.type=’U’
and i.index_id > 0
and o.name != ‘sysdiagrams’
) A
order by sort_RN