Microsoft SQL Server (MS SQL) to MySQL Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server (MSSQL, MS SQL), Azure SQL Database, Azure Synapse to MySQL.

Databases:

  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000, Azure SQL Database, Azure Synapse
  • MySQL 8.x and 5.x

Migration Reference

SQL Language Elements

Converting SQL language elements and constructs:

SQL Server MySQL
1 -- comment Single line comment -- comment A blank is required after --
2 '\' Backslash in string literal '\\' Unless NO_BACKSLASH_ESCAPES
is enabled
3 func (param, ...) Blank after function name func(param, ...) Blank is not always allowed
4 string + string2 + ... String concatenation CONCAT(string, string2, ...)
5 0x1234567890ABCDEF Hexadecimal number (literal) 0x1234567890ABCDEF
6 @@ROWCOUNT Get the number of affected rows FOUND_ROWS()

Datetime arithmetics:

SQL Server MySQL
1 GETDATE() + n Add n days to a datetime ADDDATE(NOW(), n)
DATE_ADD(NOW(), INTERVAL n DAY)
TIMESTAMPADD(DAY, n, NOW())
2 GETDATE() + 0.1 Add 0.1 of 24 hours i.e. 144 minutes DATE_ADD(NOW(), INTERVAL 144 MINUTE)

Bitwise operators:

SQL Server MySQL
1 exp1 ^ exp2 Bitwise OR (XOR) exp1 ^ exp2

Data Types

Converting data types from SQL Server to MySQL:

SQL Server MySQL
1 BIGINT 64-bit integer BIGINT
2 BINARY(n) Fixed-length byte string, 1 ⇐ n ⇐ 8000 BINARY(n)
3 BIT 1, 0 or NULL TINYINT
4 CHAR(n) Fixed-length string, 1 ⇐ n ⇐ 8000 CHAR(n) n <= 255 TEXT n > 255
5 DATE Date (year, month and day) DATE
6 DATETIME Date and time with fraction DATETIME(3)
7 DATETIME2(p) Date and time with fraction DATETIME(p)
8 DATETIMEOFFSET(p) Date and time with fraction and time zone DATETIME(p)
9 DECIMAL(p,s) Fixed-point number DECIMAL(p,s)
10 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
11 FLOAT(p) Floating-point number DOUBLE
12 IMAGE Variable-length binary data, ⇐ 2G LONGBLOB
13 INT, INTEGER 32-bit integer INT, INTEGER
14 MONEY 64-bit currency amount DECIMAL(15,4)
15 NCHAR(n) Fixed-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000 NCHAR(n) n <= 255 TEXT n > 255
16 NTEXT Variable-length Unicode UCS-2 data, ⇐ 2G LONGTEXT
17 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
18 NVARCHAR(n) Variable-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000 NVARCHAR(n)
19 NVARCHAR(max) Variable-length Unicode UCS-2 data, ⇐ 2G LONGTEXT
20 REAL Single-precision floating-point number REAL
21 ROWVERSION Automatically updated binary data BINARY(8)
22 SMALLDATETIME Datetime (year, month, day, hour, minutes
and 00 seconds )
DATETIME
23 SMALLINT 16-bit integer SMALLINT
24 SMALLMONEY 32-bit currency amount DECIMAL(6,4)
25 TEXT Variable-length character data, ⇐ 2G LONGTEXT
26 TIME(p) Time (Hour, minute, second and fraction) TIME(p)
27 TIMESTAMP Automatically updated binary data BINARY(8)
28 TINYINT 8-bit unsigned integer, 0 to 255 TINYINT UNSIGNED
29 UNIQUEIDENTIFIER 16-byte GUID (UUID) data CHAR(16)
30 VARBINARY(n) Variable-length byte string, 1 ⇐ n ⇐ 8000 VARBINARY(n)
31 VARBINARY(max) Variable-length binary data, ⇐ 2G LONGBLOB
32 VARCHAR(n) Variable-length character string, 1 ⇐ n ⇐ 8000 VARCHAR(n)
33 VARCHAR(max) Variable-length character data, ⇐ 2G LONGTEXT
34 XML XML data, ⇐ 2G LONGTEXT

Other data types:

SQL Server MySQL
1 SYSNAME System-defined type for identifiers NVARCHAR(128)

Data type synonyms:

SQL Server Data Type SQL Server Synonym MySQL Synonym
CHAR CHARACTER CHARACTER
DECIMAL DEC DEC

Built-in SQL Functions

Converting string functions:

SQL Server MySQL
1 CHAR(ascii_code) Convert ASCII code to character CHAR(ascii_code USING ASCII)
2 LEN(string) Length in characters excluding
trailing spaces
CHAR_LENGTH(RTRIM(string))

Converting datetime functions:

SQL Server MySQL
1 CAST(string AS DATETIME) Convert string to datetime STR_TO_DATE(string, format)
CAST(0x00.. AS DATETIME) Convert hex value to datetime CAST and interval expression
2 CONVERT(DATETIME, string, style) Convert string to datetime STR_TO_DATE(string, format)
3 CONVERT(VARCHAR, datetime, style) Convert datetime to string DATE_FORMAT(datetime, format)
4 DATEADD(unit, value, exp) Add datetime interval TIMESTAMPADD(unit, value, exp)
5 DATEDIFF(units, start, end) Get datetime difference in specified units TIMESTAMPDIFF(units, start, end)
6 FORMAT(datetime, format) Convert to string with format DATE_FORMAT(datetime, format)
7 GETDATE() Get the current date and time NOW(3)
8 GETUTCDATE() Get the current UTC date and time UTC_TIMESTAMP()
9 MONTH(datetime) Extract month from datetime MONTH(datetime)
10 YEAR(datetime) Extract year from datetime YEAR(datetime)

Converting numeric functions:

SQL Server MySQL
1 CONVERT(NUMERIC, exp) Convert exp to number or truncate CONVERT(exp, DECIMAL) Param order,
NUMERIC not supported
CONVERT(DECIMAL, exp)

Data type conversion functions:

SQL Server MySQL
1 CAST(exp AS datatype) Convert expression to another data type CAST(exp AS datatype)

NULL handling functions:

SQL Server MySQL
1 ISNULL(exp, replacement) Replace NULL with the specified value IFNULL(exp, replacement)

Converting JSON functions:

SQL Server MySQL
1 JSON_QUERY(json, path) Query JSON JSON_EXTRACT(json, path)
2 OPENJSON(json, path) Transform JSON to rows JSON_TABLE(json, path COLUMNS ...)

Converting other functions:

SQL Server MySQL
1 HOST_NAME() Get the host name @@HOSTNAME
2 LEFT(string, n) Return n leftmost characters from string LEFT(string, n)
3 NEWID() Generate UUID UUID()
4 RAISERROR(text, sv, st) Raise an error SIGNAL statement
5 RIGHT(string, n) Return n rightmost characters from string RIGHT(string, n)
6 SCOPE_IDENTITY() Get last identity value LAST_INSERT_ID()
7 STR(float, len, decimal) Convert float to string CONVERT(float, CHAR)
8 STUFF(str, start, len, new) Replace substring at start position
with new substring
INSERT(str, start, len, new)
9 SUSER_NAME() Get the user name CURRENT_USER()

SELECT Statement

Converting SQL queries:

SQL Server MySQL
1 SELECT TOP n … FROM … Select n rows only SELECT … FROM … LIMIT n
2 SELECT @v = c, @v2 = c2 FROM … SELECT INTO statement SELECT c, c2 INTO v, v2 FROM …
3 SELECT ... INTO #tmp Create a temporary table
using SELECT
CREATE TEMPORARY TABLE tmp AS SELECT ...
4 CROSS APPLY Correlated inner join CROSS JOIN LATERAL
5 OUTER APPLY Correlated outer join LEFT OUTER JOIN LATERAL
6 STUFF((SELECT ... FOR XML PATH... Aggregate concatenation GROUP_CONCAT expression
7 SELECT ... FOR JSON PATH Return result as JSON SELECT JSON_ARRAYAGG(JSON_OBJECT(...))
8 LOOP | HASH | MERGE Join hints i.e. INNER LOOP JOIN Removed

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

SQL Server MySQL
1 IDENTITY(start, increment) Identity column AUTO_INCREMENT Increment is always 1
2 IDENTITY can be defined on DECIMAL/NUMERIC columns Integer columns must be used
3 CONSTRAINT name DEFAULT value Named DEFAULT DEFAULT value
4 PRIMARY KEY CLUSTERED Primary key Constraint PRIMARY KEY
5 col AS exp [PERSISTED] Computed column col type AS (exp) [STORED] Type must be specified

Storage clause:

SQL Server MySQL
1 ON PRIMARY | filegroup File group to store the table Removed
2 TEXTIMAGE_ON PRIMARY | filegroup File group to store large objects of the table Removed

CREATE FUNCTION Statement

Converting Transact-SQL user-defined functions:

SQL Server MySQL
1 CREATE FUNCTION | ALTER FUNCTION name CREATE FUNCTION name
2 @param [AS] datatype = default p_param datatype
3 () Required for empty parameters ()
4 RETURNS datatype(len) RETURNS datatype(len)
5 RETURNS TABLE RETURNS JSON array
6 Optional AS before the function body Removed
7 Optional statement delimiter ; is added after each statement

For more information, see Conversion of Transact-SQL Statements.

CREATE PROCEDURE Statement

Converting Transact-SQL stored procedures from SQL Server to MySQL:

SQL Server MySQL
1 CREATE PROCEDURE | ALTER PROCEDURE name CREATE PROCEDURE name
2 PROC keyword Changed to PROCEDURE
3 @param [AS] datatype = default OUT | OUTPUT IN | OUT | INOUT p_param datatype
4 Optional () for procedure parameters () required
5 WITH EXECUTE AS name Removed
6 WITH RECOMPILE Removed
7 AS before the procedure body Removed
8 BEGIN END is optional for the procedure body BEGIN END required
9 Optional statement delimiter ; is added after each statement
10 GO Specified by DELIMITER command

For more information, see Conversion of Transact-SQL Statements.

CREATE TRIGGER Statement

Converting Transact-SQL triggers:

SQL Server MySQL
1 CREATE [OR ALTER] TRIGGER name DROP TRIGGER IF EXISTS name; CREATE TRIGGER name
2 ON table Specified after the event type
3 FOR | AFTER | INSTEAD OF BEFORE | AFTER
4 INSERT, UPDATE, DELETE Multiple events allowed INSERT | UPDATE | DELETE Only one event per trigger
5 Specified before the event type ON table
6 Statement level trigger only FOR EACH ROW Only row level triggers
7 AS Removed
8 stmt; … BEGIN stmt; … END; //
9 GO Removed

Trigger system tables:

SQL Server MySQL
1 inserted All inserted or updated rows NEW.col1, NEW.col2, ... Direct column reference

For more information, see Conversion of Transact-SQL Statements.

ALTER TABLE Statement

Modifying a table:

SQL Server MySQL
1 ALTER TABLE table ADD DEFAULT exp FOR column Add column default Moved to CREATE TABLE

ALTER TRIGGER Statement

Modifying a trigger:

SQL Server MySQL
1 ALTER TRIGGER name trigger_defintion DROP TRIGGER name; CREATE TRIGGER name trigger_defintion

For more information, see CREATE TRIGGER statement.

SET Option Statement

Converting SET statement for options from SQL Server to MySQL:

SQL Server MySQL
1 SET ANSI_NULLS ON | OFF Use = <> with NULLs Commented
2 SET NOCOUNT ON | OFF Send messages on affected rows Removed
3 SET QUOTED_IDENTIFIER ON | OFF Quote identifiers with "" Commented

Transact-SQL Statements

Converting Transact-SQL statements from SQL Server to MySQL:

Variable declaration and assignment:

SQL Server MySQL
1 DECLARE @var [AS] type [= default_value] DECLARE var type [DEFAULT default_value]
2 DECLARE @v1 type1, @v2 type2, … DECLARE v1 type1; DECLARE v2 type2, DECLARE …
3 DECLARE @tab [AS] TABLE (…) CREATE TEMPORARY TABLE tab (…)
4 SET @var = expression SET var = expression
5 SET @var = (SELECT expression FROM …) SET var = (SELECT expression FROM …)
6 SELECT @var = exp, @var2 = exp2 FROM … SELECT exp, exp2 INTO var, var2 FROM …

Cursor declaration and operations:

SQL Server MySQL
1 DECLARE cur CURSOR [options]
FOR select_statement
Cursor declaration DECLARE cur CURSOR
FOR select_statement
2 FAST_FORWARD option Read only performance optimization Removed
3 Cursors can be declared in any place of the code Cursors must be declared
before any other statements

Flow-of-control statements:

SQL Server MySQL
1 BREAK Exit a loop LEAVE label
2 IF condition BEGIN … END IF statement IF condition THEN … END IF;
3 IF … ELSE IF … IF ELSE IF statement IF … ELSEIF …
4 WHILE condition BEGIN stmts END Conditional loop WHILE condition DO stmts END WHILE;

Exception block:

SQL Server MySQL
1 BEGIN TRY … END TRY
BEGIN CATCH … END CATCH
Exception block DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN … END

Execute SQL:

SQL Server MySQL
1 EXEC (@var) Execute dynamic SQL SET @session_var = var;
PREPARE stmt FROM @session_var;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Other Transact-SQL statements:

SQL Server MySQL
1 PRINT 'text' Send message to the client Commented inside a procedural block
SELECT 'text' AS '' Expression in SQL script
\! echo 'text'; String literal in SQL script

SQL Statements

Converting SQL statements from SQL Server to MySQL:

SQL Server MySQL
1 USE dbname Change the database USE dbname

System Procedures

Converting system procedure calls from SQL Server to MySQL:

SQL Server MySQL
1 sp_addextendedproperty 'MS_Description',
'Table comment', … 'table', 'table_name'
Comment on table ALTER TABLE table_name COMMENT 'Table comment'
2 sp_addextendedproperty 'MS_DiagramPane1', … Removed
3 sp_addextendedproperty 'MS_DiagramPaneCount', … Removed