MySql tables containing keyword behaving weird!!!

Here are some queries that will behave weird

Consider the following table.

[code]

mysql> desc person;
+———-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
| address1 | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| phone | tinyint(10) | YES | | NULL | |
+———-+————-+——+—–+———+—————-+
[/code]

I want to add another column to this table. So I used the Alter table command as follows.

[code]

mysql> alter table person add column accessible tinyint(1);

[/code]

However I got the following error.

[code]

mysql> alter table person add column accessible tinyint(1);
ERROR 1064 (42000): 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 ‘acces
sible tinyint(1)’ at line 1
mysql> alter table person add column accessible tinyint(1);

[/code]

I was wondering what is wrong with the syntax. To make sure that query is correct I decided to create a new column and it worked.

[code]

mysql> alter table person add column email varchar(30);
Query OK, 6 rows affected (0.19 sec)
Records: 6 Duplicates: 0 Warnings: 0

[/code]

This convinced me that there is no problem with the syntax and so it could be an issue with the column name.

On checking the documentation of MySQL, it is found that ‘accessible’ is a reserved word.

But does that mean we cannot use reserved words as column names? Ofcourse yes, by using a backtick ( `)

[code]

mysql> alter table person add column `accessible` tinyint(1);
Query OK, 6 rows affected (0.19 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql>

[/code]

Similarly we can use these columns in our select queries with the help of back tick.

[code]

mysql> select accessible from person;
ERROR 1064 (42000): 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 ‘acces
sible from person’ at line 1
mysql> select `accessible` from person;
+————+
| accessible |
+————+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+————+
6 rows in set (0.00 sec)

mysql>

[/code]

Using keyword as column names is possible in MySQL. However it can lead others to confusion.

Posted in: Database, Programming

Leave a Comment