MySQL issue with column name "range"

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)
);

score 9 · Accepted Answer · answered Oct 04 '12 at 20:35

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)
);

score 3 · Answer 2 · answered Oct 04 '12 at 20:36

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)
);

score 1 · Answer 3 · answered Oct 04 '12 at 20:36

1

RANGE is a reserved word. http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

answered Oct 04 '12 at 20:36

Alain Collins

16,035
2
31
54

MySQL issue with column name "range"

3 Answers3