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 Data - Data transfer, schema migration and validation tool
• SQLines SQL Converter - SQL scripts assessment and conversion tool

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

• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT
• CREATE TABLE
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE TRIGGER
• ALTER TABLE
• ALTER TRIGGER
• SET Options
• Transact-SQL Statements
• SQL Statements
• System Procedures

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:

Bitwise operators:

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:

Data type synonyms:

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:

Converting JSON functions:

Converting other functions:

Converting SQL queries:

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:

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.

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.

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:

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

Modifying a table:

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

Modifying a trigger:

For more information, see CREATE TRIGGER statement.

Converting SET statement for options from SQL Server to MySQL:

Converting Transact-SQL statements from SQL Server to MySQL:

Variable declaration and assignment:

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:

Exception block:

Execute SQL:

Other Transact-SQL statements:

Converting SQL statements from SQL Server to MySQL:

Converting system procedure calls from SQL Server to MySQL: