Microsoft SQL Server (MS SQL) to MySQL Migration

SQLines provides tools to 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 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000, Azure SQL Database, Azure Synapse
• MySQL 8.x and 5.x

Technical information on migration from Microsoft SQL Server to MySQL.

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

Converting SQL language elements from SQL Server to MySQL:

	SQL Server		MySQL
1	-- comment	Single line comment	-- comment	A blank is required after --
2	string + string2 + …	String concatenation	CONCAT(string, string2, …)
3	0x1234567890ABCDEF	Hexadecimal number (literal)	0x1234567890ABCDEF
4	@@ROWCOUNT	Get the number of affected rows	FOUND_ROWS()

Datetime arithmetics:

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

Data type synonyms:

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	CHAR(ascii_code)	Convert ASCII code to character	CAST(CHAR(ascii_code) AS CHAR)
3	CONVERT(DATETIME, string, style)	Convert string to datetime	STR_TO_DATE(string, format)
4	CONVERT(VARCHAR, datetime, style)	Convert datetime to string	DATE_FORMAT(datetime, format)
5	DATEADD(unit, value, exp)	Add datetime interval	TIMESTAMPADD(unit, value, exp)
6	DATEDIFF(units, start, end)	Get datetime difference in specified units	TIMESTAMPDIFF(units, start, end)
7	GETDATE()	Get the current date and time	NOW()
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 functions:

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

Modifying a table:

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

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	PRIMARY KEY CLUSTERED	Primary key Constraint	PRIMARY KEY

Storage clause:

Converting SQL queries from SQL Server to MySQL:

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 further information, see Conversion of Transact-SQL Statements.

Converting Transact-SQL user-defined functions from SQL Server to MySQL:

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

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

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:

Other Transact-SQL statements:

	SQL Server		MySQL
1	EXEC (@var)	Execute dynamic SQL	SET @session_var = var; PREPARE stmt FROM @session_var; EXECUTE stmt; DEALLOCATE PREPARE stmt;
2	PRINT text	Send message to the client	Not supported, commented

Converting SQL statements from SQL Server to MySQL:

Converting system procedure calls from SQL Server to MySQL: