mysql syntax error when altering table

Question

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.

Remove the &. Use and.

Gordon Linoff
– Gordon Linoff

2017-07-14 12:35:53 +00:00
Commented Jul 14, 2017 at 12:35 — Gordon Linoff
– Gordon Linoff, Commented Jul 14, 2017 at 12:35
Don't use & in you table name

John Conde
– John Conde

2017-07-14 12:35:56 +00:00
Commented Jul 14, 2017 at 12:35 — John Conde
– John Conde, Commented Jul 14, 2017 at 12:35

Gordon Linoff · Accepted Answer · 2017-07-14 12:38:15Z

2

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.

answered Jul 14, 2017 at 12:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

gbn · Accepted Answer · 2017-07-14 12:38:41Z

1

Use ` to escape &

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

See Identifier Qualifiers

answered Jul 14, 2017 at 12:38

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

mysql syntax error when altering table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related