Can a number be used to name a MySQL table column?

mysql ddl
Daniel Hunter picture Daniel Hunter · Nov 2, 2011 · Viewed 53.2k times · Source

I have a table that has column names like 25, 50, 100, etc..

When trying to update the table I get an error, no matter how I do it

UPDATE table SET '25'='100' WHERE id = '1'

I have tried quoting and backticking every which way but without success.

The error is always along the lines of:

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 ''25'=100 WHERE id=1' at line 1

If I change the column name to twentyfive - I don't have a problem, but that's not what I want. Is it possible to use a number as a column name?

Answer

Matthew picture Matthew · Nov 2, 2011

From the docs:

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

Which means you must quote it with back ticks like `25`:

UPDATE table SET `25`='100' WHERE id='1'

Related questions

How to delete a column from a table in MySQL

Given the table created using: CREATE TABLE tbl_Country ( CountryId INT NOT NULL AUTO_INCREMENT, IsDeleted bit, PRIMARY KEY (CountryId) ) How can I delete the column IsDeleted?

Read More
Adding multiple columns AFTER a specific column in MySQL

I need to add multiple columns to a table but position the columns after a column called lastname. I have tried this: ALTER TABLE `users` ADD COLUMN ( `count` smallint(6) NOT NULL, `log` varchar(12) NOT NULL, `status` int(10) unsigned NOT NULL ) …

Read More
There can be only one auto column

How do I correct the error from MySQL 'you can only have one auto increment column'. CREATE TABLE book ( id INT AUTO_INCREMENT NOT NULL, accepted_terms BIT(1) NOT NULL, accepted_privacy BIT(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Read More