0

While creating the table with name spaces using MySQL Database, it's throwing the below error.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[school name] VARCHAR(20))' at line 1

Here is my simple table creation of the code :

create table table_dump(name varchar(20), address varchar(20), [school name] varchar(20));

 or

create table table_dump(name varchar(20), address varchar(20), "school name" varchar(20));
user 98
  • 167
  • 2
  • 12

1 Answers1

3

You have to use backticks around the name.

create table table_dump(name varchar(20), address varchar(20), `school name` varchar(20));

The same goes for the use of MySQL keywords as many people for example might want to name a column in a table date or datetime, and you will have a similar issue if you try and do this without the backticks. Many people, and all relational design tools that generate DDL will add backticks around all table and column names for this exact reason.

With all this stated, don't use spaces in your names. That is a poor standard and hard to read and maintain. As an unofficial standard, use underscores between the words in your table and column names.

gview
  • 14,876
  • 3
  • 46
  • 51
  • yes it's working fine. But is it good practice to use backticks. – user 98 Feb 18 '21 at 16:38
  • is it good practice to use backticks? - you don't have a choice if you use whitespaces in column names or reserved words. – P.Salmon Feb 18 '21 at 16:57
  • Many people use them in all cases, so I would say it probably is best practice. If you execute a `show create table {tablename}` you will see that mysql generates them regardless of what the original DDL is. With that said, in most cases, you run the DDL once, so if you know the rules and the DDL works, that's all that really matters. – gview Feb 18 '21 at 18:39