Are you allowed to use numbers as table names in M

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.

So this would be invalid:

 SELECT * FROM 12345;

But the following would be valid:

 SELECT * FROM `12345`;

Or if running in ANSI mode the following would work:

SET @@session.sql_mode=ANSI_QUOTES;
SELECT * FROM "12345";

Answer 2

The answer is yes, as given by karim79, as long as you take care to quote the table names. You could of course use a prefix with a numer, eg. mytable1, mytable2, ... ; that would work without quoting.

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 3

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.

Answer 4

use backtick(`) to quote the table name if it is purely numbers. it is below the esc button on keyboard

Answer 5

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