MySQL issue with column name "range" [duplicate]

Question

When I try to run this script I am getting an error at "range" if I remove or change the name I can run the script however the application need this column to store data. Any idea how to insert this into MySQL?

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 '"range" CHAR(5) NOT NULL, range_max_value NUMERIC(18,3)' at line 22

CREATE TABLE My_table (
  chart_id                INTEGER NOT NULL,
  u_range                 CHAR(5),
  l_range                 CHAR(5),
  "range"               CHAR(5) NOT NULL,
  range_max_val         NUMERIC(18,3),
  range_min_val         NUMERIC(18,3),
  PRIMARY KEY (chart_id)
);

Answer 1

Range is a reserved keyword that needs to be escaped with backticks.

CREATE TABLE My_table (
  chart_id                INTEGER NOT NULL,
  u_range                 CHAR(5),
  l_range                 CHAR(5),
  `range`               CHAR(5) NOT NULL,
  range_max_val         NUMERIC(18,3),
  range_min_val         NUMERIC(18,3),
  PRIMARY KEY (chart_id)
);

Answer 2

You should take a look at the reserved words list. Range is one of those words. You need to escape it with tick marks:

CREATE TABLE My_table (
  chart_id                INTEGER NOT NULL,
  u_range                 CHAR(5),
  l_range                 CHAR(5),
  `range`               CHAR(5) NOT NULL,
  range_max_val         NUMERIC(18,3),
  range_min_val         NUMERIC(18,3),
  PRIMARY KEY (chart_id)
);