Strange MYSQL behavior on TABLE CREATE

Question

The command:

CREATE TABLE IRdata (ego int, altr VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

works, but

CREATE TABLE IRdata (ego int, alter VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

does not. The only difference is the "e" in "alter" for the second column.

I have the latest MySQL install on the Ubuntu repos, running Ubuntu 18.04.

Any help is appreciated.

Thanks!

`ALTER` is a SQL keyword, to use it as a table column name, you'll have to escape it. — jmoerdyk, Feb 20 '19 at 19:23

score 1 · Answer 1 · answered Feb 20 '19 at 19:35

ALTER is a MySQL reserved keyword. It's recommended you use names that don't map to any of the reserved keywords, or you may run into similar bugs.

If you absolutely have to use that keywords for your field name, you can wrap it in ` (back tick) character:

CREATE TABLE IRdata (`ego` int, `alter` VARCHAR(20), `species` VARCHAR(20), `sex` CHAR(1), `birth` DATE, `death` DATE);

But avoid it either way.

1 Answers1