Flite Careers

Avoiding MySQL ERROR 1052 by Prefixing Column Names in Multi Table Queries

MySQL is fairly relaxed about prefixing column names with a table name or alias in a query involving multiple tables. As long as the column name you use only exists in one of the tables in your query, MySQL does not require you to prefix it, regardless of SQL mode.

My policy has always been to prefix all column names with the table name/alias in a multiple table query. This is not simply a syle choice. If I reference a column name without prefixing it the query may work today, but it is not future-proof. If the schema changes in the future and I add a column with the same name to one of the other tables involved in that query, then the query will fail with ERROR 1052:

1
ERROR 1052 (23000): Column 'used_to_be_unique' in field list is ambiguous

Therefore I recommend always prefixing all column names in a multi table query to protect your queries against future additive schema changes.

Here’s some code to illustrate the problem:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (a int, b int);
Query OK, 0 rows affected (0.02 sec)

mysql> select t1.a, b 
    -> from t1
    ->   inner join t2 on t2.a = t1.a;
Empty set (0.00 sec)

mysql> alter table t1 add column b int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select t1.a, b 
    -> from t1
    ->   inner join t2 on t2.a = t1.a;
ERROR 1052 (23000): Column 'b' in field list is ambiguous
Comments

Comments