Cannot insert column containing colon in column name

Question

I am trying to insert a column with column name 00:18:e7:f9:65:a6 with the statement

ALTER IGNORE TABLE tblwifi_information ADD "00:18:e7:f9:65:a6" INT

...but it throws an error:

/* SQL 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 '"00:18:e7:f9:65:a6" INT' at line 1 */

in Heidi SQL. I am using MySQL database.

When I try to add the column manually b going to the structure of the table it works and does not give any error but when I run this statement it does not allow me. How can I solve this?

Did you try it using backticks? i.e. `\`` instead of `"` around the identifier? — DrCopyPaste, May 08 '14 at 09:39
possible duplicate of [Can a number used to name a sql column](http://stackoverflow.com/questions/7975417/can-a-number-used-to-name-a-sql-column) — Shankar Narayana Damodaran, May 08 '14 at 09:40
why in all that is holy are you creating columns with mac/ipv6? addresses — Dave, May 08 '14 at 09:40
He means that this column name, for a lack of a better word, is stupid. — N.B., May 08 '14 at 09:41
can you add the php command you use for this, maybe there is an error there. I also think the problem is the mixtute of different symbols, so you should try yo create this field in your database as a Char. — Dimitri, May 08 '14 at 09:42
So, you are going to write `SELECT 00:18:e7:f9:65:a6 FROM tblwifi_information`? — Raj, May 08 '14 at 09:42
You need to go away, relook at how your structuring your database and please to save our sanity read up on normalised database structures — Dave, May 08 '14 at 09:46

Mark Miller · Accepted Answer · 2014-05-08T09:50:35.643

3

I just ran your query:

ALTER IGNORE TABLE tblwifi_information ADD "00:18:e7:f9:65:a6" INT

and got the same error. I then ran:

ALTER IGNORE TABLE tblwifi_information ADD `00:18:e7:f9:65:a6` INT

and it worked. Pretty sure you need to change " to `

Fail with double qoutes: Failed with double quotes

Success with backticks:

edited May 08 '14 at 09:50

answered May 08 '14 at 09:42

Mark Miller

7,442
2
16
22

Hmmm, I'm not sure but I suspect that it could have something to do with your php, and not the query itself. If you haven't done so already, I would try executing another very basic query with php, like `SELECT * FROM ...` just to confirm your db connection is working ok. – Mark Miller May 09 '14 at 03:06

Nagaraj S · Answer 2 · 2014-05-08T09:53:17.447

2

use backticks for columnname

change

ALTER IGNORE TABLE tblwifi_information ADD "00:18:e7:f9:65:a6" INT

TO

ALTER IGNORE TABLE tblwifi_information ADD `00:18:e7:f9:65:a6` INT

SQL FIDDLE

edited May 08 '14 at 09:53

answered May 08 '14 at 09:46

Nagaraj S

13,316
6
32
53

score 1 · Answer 3 · answered Sep 14 '15 at 17:08

To add something to the story, when querying multiple tables, you have to use following expression:

`tablename`.`columnname`

(NOT `tablename.columnname` )

I took me some time to figure it out, so I hope it helps someone :)

Example:

SELECT * 
FROM tb_product p
JOIN tb_brand b ON (  `p`.`tb_brand:IDbrand` =  `b`.`IDbrand` ) 
WHERE IDfamily = 2

score 0 · Answer 4 · answered May 08 '14 at 09:49

0

i dont really know what you are trying to achive here pal. instead of:

00:18:e7:f9:65:a6

to

00_18_e7_f9_65_a6

then adjust your code to replace ":" to "_" in php or mysql.

in sql:

Replace(table_name, ':', '_');

in PHP:

srt_replace(':','_',$query);

answered May 08 '14 at 09:49

Viscocent

2,024
2
19
26

Cannot insert column containing colon in column name

4 Answers4

SQL FIDDLE