1064 error in MySQL Command line client when trying to view table but works fine in MySQL Workbench

Question

So i created a database in MySQL Workbench and I am trying to view them on the MySQL Command Line Client because i want to do my queries there instead of the workbench.

Here's whats happening:

mysql> use policestation(crime);
Database changed
mysql> show tables;
+--------------------------------+
| Tables_in_policestation(crime) |
+--------------------------------+
| accused                        |
| case                           |
| complainant                    |
| investigation_officer          |
| outcome                        |
| section_of_law                 |
+--------------------------------+
6 rows in set (0.04 sec)

mysql> select * from case;

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 'case' at line 1

The same query works fine in MySQL Workbech. Also it works for all other tables in Command Line Client.

have you tried adding the schema name in front of the table name? Like "SELECT * FROM crime.case" — Sari Rahal, Nov 06 '17 at 04:47
yeah i did. still getting the same error. How come it works for all other tables but this.. — Karan Kowshik, Nov 06 '17 at 04:50

score 0 · Accepted Answer · answered Nov 06 '17 at 04:54

It's b/c Case is a MySQL reserved keyword: https://dev.mysql.com/doc/refman/5.5/en/keywords.html

You'll need to seround reserved keywords with tick marks not quotes. Example:

SELECT * FROM `case`

Not the same as

SELECT * FROM 'case'

Also here is another helpful link: Syntax error due to using a reserved word as a table or column name in MySQL

1064 error in MySQL Command line client when trying to view table but works fine in MySQL Workbench

1 Answers1