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.

SQLines SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000, Azure SQL Database, Azure Synapse
MySQL 9.x, 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 constant (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

Numeric data types:

	SQL Server		MySQL
1	BIGINT	64-bit integer	BIGINT
2	DECIMAL(p,s)	Fixed-point number	DECIMAL(p,s)
3	DOUBLE PRECISION	Double-precision floating-point number	DOUBLE PRECISION
4	FLOAT(p)	Floating-point number	DOUBLE
5	INT, INTEGER	32-bit integer	INT, INTEGER
6	MONEY	64-bit monetary data	DECIMAL(19,4)
7	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
8	REAL	Single-precision floating-point number	REAL
9	SMALLINT	16-bit integer	SMALLINT
10	SMALLMONEY	32-bit monetary data	DECIMAL(10,4)
11	TINYINT	8-bit unsigned integer, 0 to 255	TINYINT UNSIGNED

Other data types:

	SQL Server		MySQL
1	BINARY(n)	Fixed-length byte string, 1 ⇐ n ⇐ 8000	BINARY(n)
2	BIT	1, 0 or NULL	TINYINT
3	CHAR(n)	Fixed-length string, 1 ⇐ n ⇐ 8000	CHAR(n)	n <= 255	TEXT	n > 255
4	DATE	Date (year, month and day)	DATE
5	DATETIME	Date and time with fraction	DATETIME(3)
6	DATETIME2(p)	Date and time with fraction	DATETIME(p)
7	DATETIMEOFFSET(p)	Date and time with fraction and time zone	DATETIME(p)
8	IMAGE	Variable-length binary data, ⇐ 2G	LONGBLOB
9	NCHAR(n)	Fixed-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000	NCHAR(n)	n <= 255	TEXT	n > 255
10	NTEXT	Variable-length Unicode UCS-2 data, ⇐ 2G	LONGTEXT
11	NVARCHAR(n)	Variable-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000	NVARCHAR(n)
12	NVARCHAR(max)	Variable-length Unicode UCS-2 data, ⇐ 2G	LONGTEXT
13	ROWVERSION	Automatically updated binary data	BINARY(8)
14	SMALLDATETIME	Datetime (year, month, day, hour, minutes and 00 seconds )	DATETIME
15	TEXT	Variable-length character data, ⇐ 2G	LONGTEXT
16	TIME(p)	Time (Hour, minute, second and fraction)	TIME(p)
17	TIMESTAMP	Automatically updated binary data	BINARY(8)
18	UNIQUEIDENTIFIER	16-byte GUID (UUID) data	CHAR(16)
19	VARBINARY(n)	Variable-length byte string, 1 ⇐ n ⇐ 8000	VARBINARY(n)
20	VARBINARY(max)	Variable-length binary data, ⇐ 2G	LONGBLOB
21	VARCHAR(n)	Variable-length character string, 1 ⇐ n ⇐ 8000	VARCHAR(n)
22	VARCHAR(max)	Variable-length character data, ⇐ 2G	LONGTEXT
23	XML	XML data, ⇐ 2G	LONGTEXT

System 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)
1	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	CONSTRAINT name PRIMARY KEY	Named primary key	PRIMARY KEY	Name ignored
5	PRIMARY KEY CLUSTERED	Clustered primary key	PRIMARY KEY
6	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	BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ... 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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

Microsoft SQL Server (MS SQL) to MySQL Migration

Migration Reference

SQL Language Elements

Data Types

Built-in SQL Functions

SELECT Statement

CREATE TABLE Statement

CREATE FUNCTION Statement

CREATE PROCEDURE Statement

CREATE TRIGGER Statement

ALTER TABLE Statement

ALTER TRIGGER Statement

SET Option Statement

Transact-SQL Statements

SQL Statements

System Procedures