Create Table in SQL where table name is from digits

Question

When I try do next

mysql> CREATE TABLE '20181020';

sql return an error:

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
 that corresponds to your MariaDB server version for the right syntax to use 
 near ''20181020'' at line 1

How can I solve it?

Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — Nick, Oct 20 '18 at 07:40

Lukasz Szozda · Answer 1 · 2018-10-20T07:36:01.940

1

You needt to wrap identifier with backticks:

CREATE TABLE `20181020`(id INT);

I propose not to do so and use proper meaningful naming. Using date as name of table suggest that it could be table-per-date antipattern.

Related article: SELECT * FROM sales + @yymm

edited Oct 20 '18 at 07:36

answered Oct 20 '18 at 07:29

Lukasz Szozda

162,964
23
234
275

score 1 · Answer 2 · answered Oct 20 '18 at 07:36

1

your can also use double quote for this type of table name

CREATE TABLE "20181020" (id INT);
insert into "20181020" values(1)

But this type of naming is not standard practice

answered Oct 20 '18 at 07:36

Zaynul Abadin Tuhin

31,407
5
33
63

Don't you also need "ansi quote" mode? – Rick James Dec 27 '18 at 01:26

score 0 · Answer 3 · answered Oct 20 '18 at 11:57

The other answers cover the solution, which is to use backticks.

Let me add that using non-standard identifiers is a bad idea. If you start naming columns as number as well, who wants to figure out the differences between 20181020.1, 20181020.1, and 20181020.1. Is it so hard to use a prefix: t_20181020?

But you don't want to do that either. Let me explain. Presumably, if you have one table called 20181020, then tomorrow you will want another table 20181021. This is a really bad database design.

You should be putting all the data into a single table, with a date column specifying the date. You can then run queries over one table to see changes over time -- for instance.

If the tables are large and you want the ability to manipulate each day separately, then you use table partitioning to store each day's worth separately. This is handy, because partitions can be archived, restored, and dropped independently of other partitions.

Create Table in SQL where table name is from digits

3 Answers3