MySQL IN Clause not working for column name 'index'

Question

My schema is this:

CREATE TABLE `dump` (
  `index` int(20) DEFAULT NULL,
  `uid` int(20) DEFAULT NULL,
  `time` int(20) DEFAULT NULL,
  `delete1` int(20) DEFAULT NULL,
  `delete2` int(20) DEFAULT NULL,
  `delete3` int(20) DEFAULT NULL,
  `delete4` int(20) DEFAULT NULL,
  `delete5` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Yet when I run the query:

SELECT * FROM dump WHERE 'index' IN ('1', '2', '3');

Nothing is returned. I am 100% sure that there are entries with an index of those values. It does not matter if I remove the single quotes from 1,2,3 etc but gives a syntax error if removed from index.

use backtick ` to enclose index – 1000111 Jun 06 '16 at 06:40 — 1000111, Jun 06 '16 at 06:40

MusicLovingIndianGirl · Accepted Answer · 2016-06-06T09:31:24.520

0

Instead of using reserved keywords as identifiers, use some relevant names. If you still want to do the same, enclose it within backticks (``)

SELECT * FROM dump WHERE `index` IN ('1', '2', '3');

edited Jun 06 '16 at 09:31

answered Jun 06 '16 at 06:40

MusicLovingIndianGirl

5,909
9
34
65

Note the backticks. You need to enclose any SQL reserved words with backticks whenever you use them as column, table, trigger, or other names. – Jim Dennis Jun 06 '16 at 06:44
@musiclovingindiangirl - Please provide more context about why your sql solves the issue. – Toby Allen Jun 06 '16 at 07:40

score 0 · Answer 2 · answered Jun 06 '16 at 06:45

0

In MySql, Index is reserved keyword. You cannot use reserved keywords as column name. Change the index column name to some another name. There are some other keywords like insert, select etc. Hope so it will help you. Thanks.

answered Jun 06 '16 at 06:45

kiran

71
3

You can use reserved words as column names, you just have to put backticks around them, as he's done. – Barmar Jun 06 '16 at 07:12

score 0 · Answer 3 · answered Jun 06 '16 at 06:53

In MySQL, certain words like SELECT, INSERT, DELETE etc. are reserved words. Since they have a special meaning, MySQL treats it as a syntax error whenever you use them as a table name, column name, or other kind of identifier - unless you surround the identifier with backticks.

There are two options to solve this issue:

1. Don't use reserved words as identifiers

2. Use backticks (if renaming is not possible)

So, based on option #2:

SELECT * FROM dump WHERE `index` IN ('1', '2', '3');

MySQL IN Clause not working for column name 'index'

3 Answers3