`;`.`*`.`.` is a valid column name

And the following query:

SELECT `;`.`*`.`.` FROM `;`.`*`;

is valid as well. So are the following:

DROP DATABASE IF EXISTS `;`;
CREATE DATABASE `;`;
CREATE TABLE `;`.`*` (`.` INT);
CREATE TABLE `;`.```` (`.` INT);
CREATE TABLE `;`.`$(ls)` (`.` INT);

So, on my Linux machine:

root@mymachine:/usr/local/mysql/data# ls -l
total 30172
drwx------ 2 mysql mysql     4096 2009-01-11 08:00 ;
-rw-rw---- 1 mysql mysql 18874368 2009-01-09 19:08 ibdata1
-rw-rw---- 1 mysql mysql  5242880 2009-01-09 19:08 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2009-01-09 19:08 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 2008-12-09 11:38 mysql
-rw-rw---- 1 mysql mysql  1423612 2009-01-11 08:00 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 2009-01-04 09:05 mysql-bin.index
drwx------ 2 mysql mysql     4096 2008-12-21 13:58 sakila
-rw-rw---- 1 mysql root      9783 2009-01-04 09:05 mymachine.err
-rw-rw---- 1 mysql mysql        6 2009-01-04 09:05 mymachine
.pid
drwx------ 2 mysql mysql     4096 2009-01-04 08:30 world

Well then…

root@mymachine:/usr/local/mysql/data# cd ;
root@mymachine:~#

Trying again:

root@mymachine:~# cd -
/usr/local/mysql/data
root@mymachine:/usr/local/mysql/data# cd ";"
root@mymachine:/usr/local/mysql/data/;#

And now:

root@mymachine:/usr/local/mysql/data/;# ls -l *.frm
-rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 `.frm
-rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 *.frm
-rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 $(ls).frm

Oh, sorry, I meant:

root@mymachine:/usr/local/mysql/data/;# ls -l "*".frm
-rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 *.frm

Weird.

As a nice surprise, though, the dot (.) is not allowed in database or table names (but is allowed in column names). Nor are the slash (/) and backslash (\). Look here for more on this.

Support for non English naming

I kinda new about this all along, but never thought of the consequences. It’s nice to have a relaxed naming rule (I can even name my tables in Hebrew if I like), but “nice” doesn’t always play along with “practical”.

As a Hebrew speaker, I repeatedly encounter issues with using my language. In many applications Hebrew encoding is not supported (many times even UTF8 isn’t). Not to mention the fact that Hebrew is written from right to left. On many occasion I was irritated by the lack of support for non-English or non-ASCII characters.

But not always and not everywhere. I’ve had my share of programming languages, and, to be honest, I never expected my programming language to support UTF8 encoding for function names, variables, modules, packages or whatever. Using “a-zA-Z0-9_” is just fine. Many people who are not well familiar with English just name their variables in their native language, but written with English characters. This works well till you get someone from outside the country, who doesn’t speak the language and does not understand (nor can pronounce, nor has the matching keyboard layout or knows how to use it) the names.

In the same way, I have no wish for my table names to be named in Hebrew, German or Japanese names. English is just fine.

Using non-letter characters just adds to the mess. Popular “command” characters such as ‘~’, ‘,’, ‘:’, ‘;’, ‘*’, ‘?’, ‘(‘, ‘$’ are better left alone. They don’t belong in database or table names (mapped to file names) or column names (internally handled by MySQL).

English has become the de-facto computer world language. Programming languages, file systems, TCP/IP protocols, SQL: everything “speaks” English.

Security

There’s another aspect, though: security. It may sound silly, but you can actually write complete scripts in a table’s name! Not wanting to give the wrong idea, I’m not presenting some table names which can wreak havoc on your machine if used improperly.

But think about it: don’t we all use a couple of scripts which backup/clean/automate some stuff for us? Don’t these scripts just go ahead and read some table names, then do stuff on those tables? How well do they trust table names?