Resolve usage of quotes 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?

MySQLMySQLi Database

In MySQL, you can use two different types of quotation marks which is backtick and another is single quotes or double quotes. In this case, maybe you are using single quotes to the column name that’s why you are getting error. You need to use the backtick symbol (` `) instead of single quotes. Backtick can be used with column names while single quotes can be used for strings.

To understand the above error, let us create a table. The query to create a table is as follows −

mysql> create table Backtick_SymbolDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),  
   -> FileName varchar(30),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.81 sec)

The error occurs if we use single quotes instead of backtick symbol around the column as shown below. Here, we have inserted a value with single quotes and the error is visible −

mysql> insert into Backtick_SymbolDemo(`Name`,'FileName') values('John','Sum.cpp');
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 ''FileName') values('John','Sum.cpp')' at line 1

To avoid this error either use the backtick symbol around the all column name or do not use both the backtick or single quotes.

The query to insert record is as follows. Here is the use of the backtick symbol around both column name.

mysql> insert into Backtick_SymbolDemo(`Name`,`FileName`) values('John','Sum.cpp');
Query OK, 1 row affected (0.12 sec)

Here is the insert record without using the backtick symbol. The query is as follows −

mysql> insert into Backtick_SymbolDemo(Name,FileName) values('Larry','SocketProgram.java');
Query OK, 1 row affected (0.23 sec)
mysql> insert into Backtick_SymbolDemo(Name,FileName) values('Mike','UserCreationScript.sql');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from Backtick_SymbolDemo;

The following is the output −

+----+-------+------------------------+
| Id | Name  | FileName               |
+----+-------+------------------------+
|  1 | John  | Sum.cpp                |
|  2 | Larry | SocketProgram.java     |
|  3 | Mike  | UserCreationScript.sql |
+----+-------+------------------------+
3 rows in set (0.00 sec)
raja
Published on 26-Feb-2019 11:29:16
Advertisements