Parsing mysqldump files

MySQL and MS SQLServer both can be accessed using SQL, but if you look closely at both SQL dialects, you’ll find a lot of differences.

Backups of MySQL databases are created using the utility mysqldump, which simply dumps all the SQL statements required to recreate the database into a SQL file, as opposed to SQLServer backup, which creates a binary file representing the database contents.

As we are interesting in parsing the SQL and converting it into valid T-SQL statements, we have to look at the differences between the SQL dialects. Fortunately, since the SQL files are output by a tool, the generated statements are fairly regular.

Here are some special features of the MySQL dialect, which need to be handled for conversion into T-SQL:

• Identifiers are quoted with a backtick `, rather than square brackets
• IDENTITY columns are marked by the auto_increment keyword
• Binary columns may store text values
• Text values may contain character codes with leading backslash \, as known from C# or C
• Values for DATETIME, DATE and TIME columns need not be quoted as strings
• ‘0000-00-00’ is a valid DATE
• A CREATE TABLE statement may include the definition of indexes, unique indexes and fulltext indexes
• Both SQL engines provide support for collations, but they are completely different

I have previously written about converting MySQL SQL into T-SQL manually, but having a tool perform the repetitive actions would be very nice 😉