0

I have a MySQL database table that contains a column named GROUP.

I am trying to add an index on this column using the following SQL:

alter table MY_TABLE add index (GROUP);

This gives me the error:

Error Code: 1064. 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 'GROUP)' at line 1

I have tried qualifying the column as MY_TABLE.GROUP and tried creating the index using the MySQL Workbench UI but both throw the same error.

AndyW
  • 985
  • 3
  • 10
  • 21
  • 1
    See [https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table](https://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table) – Stu Oct 31 '21 at 12:53

1 Answers1

2

Like all reserved words it is best not to use them at all

But you can use backticks, but you need to use them in all queries with that index

also you can read more about that on When to use single quotes, double quotes, and backticks in MySQL

alter table MY_TABLE add index (`GROUP`);
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thank you! I had tried 'GROUP' but did not realise this was a different character. Agreed on not using reserved words. Have certainly learned this the hard way. – AndyW Oct 31 '21 at 13:00