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.
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:
| SQL Server | MySQL |
1 | GETDATE() + n | Add n days to the current datetime | ADDDATE(NOW(), n) |
DATE_ADD(NOW(), INTERVAL n DAY) |
TIMESTAMPADD(DAY, n, NOW()) |
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:
SQL Server Data Type | SQL Server Synonym | MySQL Synonym |
CHAR | CHARACTER | CHARACTER |
DECIMAL | DEC | DEC |
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:
| 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 |
Converting SQL queries from SQL Server to MySQL:
| 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 … |
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:
| 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 |
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; |
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:
| SQL Server | MySQL |
1 | USE dbname | Change the database | USE dbname |
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' |