Should all table names in MySQL be enclosed in backticks (`) to prevent collisions with reserved keywords? The reason I ask is because their use makes the SQL less portable as not all databases allow backticks.
So would avoiding table and column names containing keywords be a better course of action? If so what can be done to mitigate the risk of MySQL adding a new keyword in the next version that might collide with your schema.
Is there a best practice regarding this?
The most portable way (between the systems) is to use double quotes, however, it would require enabling ANSI_QUOTES which is off by default on most installations.
So while keeping arguably useful compatibility between different engines (and incompatibility does not limit itself to backticks only but to zillion other things different between MySQL and other systems) you are killing the compatibility between different setups of MySQL which is by far more important.
Avoiding the reserved keywords is always the best solution.
This is a matter of opinion. But portable code outweighs their use. As you noted, backticks can allow you to use reserved words, which is never a good thing. That, for me, already proves they do more harm than good.
So would avoiding table and column names containing keywords be a better course of action?
In short, yes. And there isn't much you can do with respect to future keywords except avoiding obvious candidates, e.g. with, window, over, etc.
One common practice is to prefix all your table names with a few letters and an underscore. It prevents collisions if you need to house two different applications in the same database, and you'll likely never run into reserved words.
Not escaping and manually avoiding reserved name collisions with keywords can be quite a tedious endeavor as reserved names vary greatly across databases. E.g you can use User in MySQL but not on MSSQL.
It also boils down to what the SQL queries are aimed at: are they table creation queries? initialization queries? "regular" queries? This is important as there are other factors that will make the SQL database dependent (e.g the use of AUTO_INCREMENT when creating a table).
It also depends if it is handwritten SQL files that you load and run directly into the database or programmatically constructed/filled ones. In the latter case I would use available facilities or write a micro driver that encapsulates database dialect specificities. We're not talking ORM here, just something that will help encapsulate this problem away.
To me the answer "try to avoid them" is a path of least resistance solution that might or might not bite you back at some point depending on the role of your queries. Maybe your question is a bit too broad?
I don't worry much about portability issues that can be handled with automation.
Standard SQL doesn't have any use for backticks. So can't backticks in DDL simply be replaced globally with SQL-standard double quotes? If so, that's just a one-liner in a make file.
If you use backticks, you avoid that your code stops working if MySQL introduces a new reserved keyword. Imagine all the websites you created, all stopping to work because a MySQL update introduced a new keyword that you previously used as a table name!
Your SQL may be slightly less portable, but really.. replacing a backtick with a double quote is a matter of a single search/replace in a file (unless you are using also the PHP backtick execute operator in the same file). You can't do this in reverse: replace double quotes to backticks, as other strings may be changed too (all to the PHP "execute" operator, ugh!)!
Or if you want the code to be compatible with both you can do the replace inside a few functions that process/prepare the SQL:
function myExecute($sql,$params) {
if(NOT_MYSQL) $sql=str_replace('`','"',$sql);
return execute($sql,$params);
}
What you should NEVER do is using double quotes to enclose string values in SQL. It is allowed by MySQL, but very bad for portability. You may have to replace all your strings manually.
<?php
// Don't. Use ' for strings instead
$sql='SELECT "col1" FROM "tab" WHERE "col2"="very bad"';
// Better
$sql="SELECT `col1` FROM `tab` WHERE `col2`='not that bad'";
// May crash later if tab becomes a keyword (or col1, or col2,..)
$sql="SELECT col1 FROM tab WHERE col2='not that bad'";
// Standard. But harder to replace ""s to ``s later, and annoying \'s in code
$sql='SELECT "col1" FROM "tab" WHERE "col2"=\'not that bad\'';
// Safe. Annoying.
$sql="SELECT my_unique_col1 FROM my_unique_tab WHERE my_unique_col2='not that bad'";
?>
As you see in the last example, you can name your tables and fields in a way that is probably unique (add some prefix to all, in this case "my_unique_") it is boring but mostly safe and portable.
