When you build an application that works with different kinds of databases, you have to be aware of and deal with many subtle differences between databases. It’s no different when you write an application that works with MySQL and Amazon Redshift.
While developing Integrate.io Sync, we encounter many such differences. In this article, we’d like to share our from-the-trench experience.
Express Your Column with The Quoted Column Name
If you specify a column name without quotes in your query, your column name may have a very limited set of characters: ASCII alphabets, digits and a few other characters.
ALTER TABLE DROP COLUMN my_column;
However, if you quote a column name, you may use a lot more characters. Especially in MySQL, you can even use almost any Unicode character in your column name. (If this is a good practice is another matter…)
MySQL
ALTER TABLE my_table ADD COLUMN `Écoles; 学校\` TEXT; -- valid column name
Amazon Redshift
ALTER TABLE my_table ADD COLUMN “schools (address@domain.edu)” TEXT; -- valid column name
Note that MySQL and Redshift use a different character as a quote character.
GET BACK TO WORK.
We'll handle your ETL pipeline.
Case Sensitive or Case Insensitive
Case matters with MySQL. You can create a column such as “Local Governments”. On the other hand, Amazon Redshift’s column names are not case sensitive. You can use upper case letters in your query, but they get converted to lowercase.
Valid Column Name Characters in MySQL and Amazon Redshift
The following table shows the differences in supported characters between MySQL and Redshift.
| Characters |
MySQL (unquoted) |
Redshift (unquoted) |
MySQL (quoted) |
Redshift (quoted) |
| ASCII Alphabet |
YES (case sensitive) |
YES (case insensitive) |
YES (case sensitive) |
YES (case insensitive) |
| Digit, underscore (_), dollar sign ($)< |
YES (*1) |
YES (*2) |
YES |
YES |
| Non-ASCII Characters (Unicode Extended U+0080..U+FFFF) |
YES |
NO |
YES |
NO |
| Space ( ) |
NO |
NO |
YES (*3) |
YES |
| !#%&’()*+,-./:;<=>?@[]^_~ |
NO |
NO |
YES |
YES |
| Double quotes (“) |
NO |
NO |
YES |
YES (*4) |
| Grave accent (`) |
NO |
NO |
YES (*5) |
YES |
| Backslash (\) |
NO |
NO |
YES (*6) |
YES |
*1) Digit-only column name is not allowed unless quoted.
*2) Column name may not start with a digit or dollar sign ($) unless quoted.
*3) Trailing spaces are not allowed.
*4) Must be escaped with another double quotes (“”)
*5) Must be escaped with another grave accent (``)
*6) Must be escaped with another backslash only if it’s the last character.
*7) MySQL 5.6 and Amazon Redshift as of June 1, 2015