CODEWITHSUNDEEP
phpmysql
pjedwabny
pjedwabny

Reputation: 75

MySQL syntax error in CREATE TABLE statement with hash column names

I am parsing xls/csv files into database. For each file I have one table with columns named by hashes of file column names.

For columns

I have SQL:

CREATE TABLE premium_import_temp_worksheet_9 (
id                       INTEGER NOT NULL AUTO_INCREMENT,
f37c4446e4a882972cd3a80053dd795f VARCHAR(255),
d5bfdd6746ff1a364399acb6e6526b7a VARCHAR(255),
f7e2db7542ea17f0fcc1a46ee21f860b VARCHAR(255),
7161b0ab070daf8e20a2b62f2ef13b81 VARCHAR(255),
4f4e297d754b67e7a104f58b2bcdb850 VARCHAR(255),
0975cf6baccb3862c31522c2b5b8fabc VARCHAR(255),
d1c0419824dea874935502cf6d7c946f VARCHAR(255),
ce8ae9da5b7cd6c3df2929543a9af92d VARCHAR(255),
d7f1213d49993b7ce9ce02463ea1eeda VARCHAR(255),
                 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

and everything is executed without any errors, but for:

I have:

CREATE TABLE premium_import_temp_worksheet_8 (
id                       INTEGER NOT NULL AUTO_INCREMENT,
e7b47c58815acf1d3afa59a84b5db7fb VARCHAR(255),
bcc254b55c4a1babdf1dcb82c207506b VARCHAR(255),
6e87a4f6ea8244d7e04cb52e495d6693 VARCHAR(255),
d9729feb74992cc3482b350163a1a010 VARCHAR(255),
c90e651df62a9057d9bc8f48b42da7d0 VARCHAR(255),
                 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

And error:

mysqlt error: [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 '6e87a4f6ea8244d7e04cb52e495d6693 VARCHAR(255), d9729feb74992cc3482b350163a1a010 ' at line 5] in EXECUTE("CREATE TABLE premium_import_temp_worksheet_7 ( id INTEGER NOT NULL AUTO_INCREMENT, e7b47c58815acf1d3afa59a84b5db7fb VARCHAR(255), bcc254b55c4a1babdf1dcb82c207506b VARCHAR(255), 6e87a4f6ea8244d7e04cb52e495d6693 VARCHAR(255), d9729feb74992cc3482b350163a1a010 VARCHAR(255), c90e651df62a9057d9bc8f48b42da7d0 VARCHAR(255), PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci")

I don't know what is the difference between syntaxes of these two statements. Please help.

Upvotes: 1

Views: 431

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

What you are observing appears to be a bug. As excerpted from the documentation:

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

The confusion appears to be due to the fact that "6e87" is a valid number, even though it does not consist entirely of digits. This is exponential notation. For some reason, although an unquoted identifier can start with a digit, it cannot start with a number in exponential notation.

This is easy enough to fix by quoting the identifiers with either backticks or double quotes.

EDIT:

You can replicate the problem with these two simple queries:

select 6 as 5_1

select 6 as 5e1

select 6 as 5f1

select 6 as 5e1f

The first and third work. The second correctly fails because the identifier is a number (in exponential form). The last fails incorrectly. By the way, I think it is a bad idea to start identifiers with numbers, but that is a separate matter from MySQL behaving the way it is documented to behave.

Upvotes: 2

echo_Me
echo_Me

Reputation: 37253

you should escape by backticks

try this

   CREATE TABLE premium_import_temp_worksheet_8 (
 id                       INTEGER NOT NULL AUTO_INCREMENT,
 e7b47c58815acf1d3afa59a84b5db7fb VARCHAR(255),
 bcc254b55c4a1babdf1dcb82c207506b VARCHAR(255),
 `6e87a4f6ea8244d7e04cb52e495d6693` VARCHAR(255),
 d9729feb74992cc3482b350163a1a010 VARCHAR(255),
 c90e651df62a9057d9bc8f48b42da7d0 VARCHAR(255),
             PRIMARY KEY (id)
 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

http://sqlfiddle.com/#!2/03d6fd

Upvotes: 2

Related Questions