Are you allowed to use numbers as table names in MySQL?

Question

I'm thinking of having a program that dynamically creates new tables as the need arises. Can I have tables named with just numbers in MySQL?

Answer 1

Rules for naming objects, including tables in MySql:

http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Answer 2

as Karim and Steve Weet pointed out, yes, you can, but you'll have to quote them like this:

SELECT * FROM `3516`

Could I suggest perhaps rethinking your script though? Add a prefix: a table named "t3516" won't be as confusing as just "3516".

Also, you could convert the number to just use letters rather than any numbers:

table 0 - t_a
table 1 - t_b
table 2 - t_c
table 25 - t_z
table 26 - t_aa
table 27 - t_ab
... etc

Answer 3

The answer is no, as given by karim79. You could of course use a prefix with a numer, eg. mytable1, mytable2, ... ; MySQL would accept that.

That said, you should really think about why you want to create so many tables. The accepted way of doing things is to have everything that belongs together in one table. So rather than having table1, table2... you would use one table, and store the number in a column.

That is just the natural way. Your way of doing things could easily lead to many problems (changing DB schema is problematic for backups, makes it hard for other tools to work with the DB because of many tables, schema changes have to be done to all tables). Dynamically altering your schema at runtime is usually not a good idea.

Answer 4

i think it should not be preferable at all coz it will be more confusing and difficult to remember. table name should be relevant to the data it is storing. It's a better habit.