CODEWITHSUNDEEP
mysqlsqlalter-table
Rpp
Rpp

Reputation: 113

mysql syntax error when altering table

I have a syntax error when adding a new column to a table. Every other column is added correct apart from this:

ALTER TABLE stock.stock_data ADD LEGAL_&_GENERAL VARCHAR(40);

Thanks for your help.

Upvotes: 0

Views: 79

Answers (2)

gbn
gbn

Reputation: 432677

Use ` to escape &

ALTER TABLE stock.stock_data ADD `LEGAL_&_GENERAL` VARCHAR(40);

See Identifier Qualifiers

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271091

MySQL (as with all databases) limits the characters in unquoted identifiers:

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

Extended: U+0080 .. U+FFFF

Note that "&" is not one of them. The preferred way to fix this is to use "normal" characters:

ALTER TABLE stock.stock_data ADD LEGAL_AND_GENERAL VARCHAR(40);

If you really want to, you can quote the identifier:

 ALTER TABLE stock.stock_data ADD `LEGAL_&_GENERAL` VARCHAR(40);

However, you will have to quote the name wherever you use the column, just cluttering your queries and wearing out the back-tick key.

Upvotes: 2

Related Questions