Migrating from SQLite to MySQL

The last few days I've been working on refactoring code and simultaneously changing SQL queries to work with MySQL instead of SQLite.

The one thing I have noticed is that SQLite is so much more forgiving when it comes to syntax for column names or comparison.

Below are a few (read, all) samples that I have ran in to:

(note, all are samples)

Table names

In SQLite there was a query:

# SQLite
select blah from Device where 1;

The table's name was in reality not written with a capital.

# MySQL
select blah from device where 1;

Is the correct syntax for MySQL.

Reserved words

and special characters in column names

In SQLite columns with special characters and/or reserved words are between double quotes, in MySQL between back-ticks:

# SQLite
select "bl-ah" from somewhere;

# MySQL
select `bl-ah` from somewhere;

Note, in MySQL something between doublequotes turned out to be printed fully instead of the underlying value. Which means that "ipaddress" would print the word 'ipaddress' rather than the value '1.3.3.7'

Length

The original import was from .csv files, which meant that in many cases 'int' is not always 'int' and often enough 'null' is not 'Null'. Which meant that in cases where you would expect a specific value (length) it was better to look for that rather than trying to find out is null was null etc.

# SQLite
select MAC from networking where length(MAC) == 17 limit 1;

# MySQL
select MAC from networking where length(MAC) = 17 limit 1;

To be fair, SQLite does allow just one '='.

Concattenation

Sometimes you just want to display it all together, this has also been the majority of changes:

# SQLite
select (firstname ||' '|| lastname) as fullname from privacy;

# MySQL
select concat(firstname ,' ', lastname) as fullname from privacy;

And those were it. As for updating and inserting into the tables there seem to have been no issues.