Mysql mistake when adding a table so a script

Question

Mysql mistake (don't laught please)

I made a script that generates statistics table for my site. This script generates over 2000 tables that are used for different statistics data with a different granularity like:

• tables by years
• tables by day
• tables by users
• tables by user_agent

etc... (don't ask me why this, it was made by the previous so called architect - altought they load in less than 0.02 seconds)

So I updated the script to add new tables but i made a mistake.

I put a space in the table name and now I cannot delete it. so i try to put a \ to escape it but no success:

root@summary:reports> drop table xd_2012_02_\ ua;
ERROR: 
Unknown command '\ '.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\ ua' at line 1

Answer 1

You have to use the ` - This will treat the string passed as the actual table name and not a bunch of commands you want to execute.

root@summary:reports> drop table `xd_2012_02_\ ua`;

This should show:

Query OK, 0 rows affected (0.00 sec)

I would recommend to use the ` not only for this case, but also that will prevent errors if you use reserved MySQL words like type or desc (as description) for columns or table names. This might save you headaches later. Although, using reserved or special characters is not a good practice, but that's just me.