mySQL import failing on DECIMAL

Question

I am trying to import some fields into a MySQL database, here is my import file...

CREATE TABLE mytable(
 zip       VARCHAR(7) NOT NULL PRIMARY KEY
,lat       DECIMAL(10,8) NOT NULL
,long      DECIMAL(11,8) NOT NULL
,data      VARCHAR(3) NOT NULL
);

It is giving me the following error...

check the manual that corresponds to your MySQL server version for the right syntax to use near 'long DECIMAL(11,8) NOT NULL ,data VARCHAR(3) NOT NULL )' at line 4

It is not liking the long field that I am trying to set as DECIMAL type

Where am I going wrong?

The clue is in the error message. The error is always at (or immediately to the left of) the first word in the error message: dev.mysql.com/doc/refman/5.5/en/keywords.html — Strawberry
– Strawberry, Commented Jan 19, 2017 at 23:01
Incidentally, if you do the arithmetic, VARCHAR(3) probably doesn't make a whole lot of sense (but then again, I haven't actually done the arithmetic). — Strawberry
– Strawberry, Commented Jan 19, 2017 at 23:04
Can you elaborate at all? Not sure I follow what you mean by do the arithmetic — fightstarr20
– fightstarr20, Commented Jan 19, 2017 at 23:06
Well, this is a bit geeky, but... CHAR(3) always requires 3 bytes. VARCHAR(3) always requires at least 1 byte. If the string is 2 characters in length, then it requires 3 bytes, and if it's 3 characters then it requires 4 bytes. So, it comes down to what percentage of your strings are less than 2 characters in length? — Strawberry
– Strawberry, Commented Jan 19, 2017 at 23:10

Strawberry · Accepted Answer · 2017-01-19 23:12:00Z

3

The long is the reserved keyword in MySQL. Try using escaping like this:

CREATE TABLE mytable
(zip       VARCHAR(7) NOT NULL PRIMARY KEY
,lat       DECIMAL(10,8) NOT NULL
,`long`    DECIMAL(11,8) NOT NULL
,data      VARCHAR(3) NOT NULL
);

Or, what is better, try using another name. For latitude and longitude this might be lat and lng or lat and lon.

edited Jan 19, 2017 at 23:12

Strawberry

34k14 gold badges43 silver badges58 bronze badges

answered Jan 19, 2017 at 23:04

shybovycha

12.4k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Strawberry Over a year ago

Try not using long. No one else does, and now we know why.

fightstarr20 Over a year ago

Thank you, it was staring me in the face all along! I had reserved keywords in my checklist but hadn't spotted it

Collectives™ on Stack Overflow

mySQL import failing on DECIMAL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related