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?
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.
Rules for naming objects, including tables in MySql:
Identifiers may begin with a digit but
unless quoted may not consist solely
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";
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
use backtick(`) to quote the table name if it is purely numbers. it is below the esc button on keyboard
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.
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.