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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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";

Method 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

Method 3

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

Method 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.

Method 5

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0