While working on Database migration between MS SQL and MySQL(MSSQL-> MySQL) ,i compared different datatype available in MS SQL and MySQL. Below is complete list.
|
Data
Type Comparison Between MySQL and MS SQL Server
|
||
|
MySQL
|
MS SQL
|
Comments
|
|
Integer
|
||
|
TINYINT(1)
|
BIT
|
|
|
TINYINT
|
TINYINT
|
Tinyint in MySQL is always unsigned
|
|
SMALLINT
|
SMALLINT
|
|
|
MEDIUMINT, INT
|
INT
|
|
|
BIGINT
|
BIGINT
|
|
|
|
||
|
Decimal
|
||
|
DECIMAL(M,D)
|
decimal(p,s)
|
Default precision is 18.
The maximum precision is 38. The default scale is 0. |
|
|
||
|
Floating
Data types
|
||
|
FLOAT(N)
|
float(n)
|
|
|
FLOAT(M,D)
|
float(24)
|
|
|
DOUBLE(M,D); REAL(M,D)
|
float(53)
|
|
|
DOUBLE(M,D)
|
smallmoney, money
|
|
|
|
||
|
Date
and Time Data Types
|
||
|
DDATETIME
|
datetime2*
|
It supports dates from 0001-01-01
through 9999-01-01
|
|
DATE
|
date*
|
It supports dates from 0001-01-01 to
9999-12-31
|
|
TIME
|
time2*
|
Time range is 00:00:00.0000000 through
23:59:59.9999999
|
|
TIMESTAMP
|
smalldatetime
|
smalldatetime value range is 1900-01-01
through 2079-06-06
|
|
YEAR
|
Smallint
|
-
|
|
|
||
|
String
Data type
|
||
|
CHAR
|
nchar(n), uniqueinentifier
|
nchar allows 4000 characters
|
|
VARCHAR, TINYTEXT, TEXT(M), MEDIUMTEXT,
LONGTEXT
|
nvarchar(n|max)
|
nvarchar allows 4000 characters but max
indicates maximum storage size of 2^31-1 bytes.
|
|
|
||
|
Binary
Data type
|
||
|
BINARY(M)
|
binary(n)
|
binary allows 8000 bytes
|
|
VARBINARY(M), TINYBLOB, BLOB,
MEDIUMBLOB, LONGBLOB
|
varbinary(n|max)
|
nvarbinary allows 8000 bytes but max
indicates maximum storage size of 2^31-1 bytes.
|
|
BLOB
|
sql_variant
|
-
|
|
LONGBLOB
|
image
|
-
|
