mySQL syntax error when using an integer

Question

I'm trying to create a table using mySQL and Java. What I have is:

String sql = "CREATE TABLE IF NOT EXISTS " + Userinput.getTableName2() +
" (participant INT(255), " +
" 0 INT(255),"+ 
" name INT(3), " + 
" occurances INT(255))";
stmt.executeUpdate(sql);

The Zero naming the second column is arbitrary, but I will need to have the column name be an integer. The error I'm getting is:

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 '0 INT(255), name INT(3), occurances INT(255))' at line 1

I would appreciate any and all help! Thanks!

Scary Wombat · Accepted Answer · 2014-02-26 01:57:30Z

4

According to this http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

so you have to quote the 0

String sql = "CREATE TABLE IF NOT EXISTS " + Userinput.getTableName2() +
" (participant INT(255), " +
" `0` INT(255),"+                     // using backticks
" name INT(3), " + 
" occurances INT(255))";
stmt.executeUpdate(sql);

Although, it seems a dumb name for a column.

edited Feb 26, 2014 at 1:57

answered Feb 26, 2014 at 1:06

Scary Wombat

44.9k6 gold badges38 silver badges67 bronze badges

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

4 Comments

Gordon Linoff Over a year ago

The better way to quote the identifier is using backticks. To quote the documentation: "The identifier quote character is the backtick (“`”)" (dev.mysql.com/doc/refman/5.7/en/identifiers.html).

user3353778 Over a year ago

Hi, I had tried adding the single quotes in before as per the documentation, but I receive a very similar error when i do so. "MySQL server version for the right syntax to use near ''0' INT(255), name INT(3), occurances INT(255))' at line 1"

user3353778 Over a year ago

@GordonLinoff Using backsticks seemed to fix my error! Thanks! and thank you user2310289 for your suggestion too!

MirroredFate Over a year ago

+1 For being right, but mostly because 0 really does seem like a dumb name for a column.

Mohamed Abd El Raouf · Accepted Answer · 2014-02-26 01:10:17Z

0

Please read mysql documentation:

http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

answered Feb 26, 2014 at 1:10

Mohamed Abd El Raouf

9281 gold badge9 silver badges21 bronze badges

Collectives™ on Stack Overflow

mySQL syntax error when using an integer

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related