MySQL : When stored procedure parameter name is the same as table column name [continue]

Question

Hello,

Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write

UPDATE customers SET Name = Name;

this is incorrect and I have to write (for example)

UPDATE customers SET `Name` = Name;

So, there is a link about backticks (http://dev.mysql.com/doc/refman/5.0/en/identifiers.html) but it's not explained deep enough how to use them (how to use them with parameters and column names).

And there is a very strange thing (at least for me): You can use backticks either way:

UPDATE customers SET Name = `Name`;
//or
UPDATE customers SET `Name` = Name;
//or even
UPDATE customers SET `Name` = `Name`;

and they all work absolutely the same way.

Don't you think this is strange? Is this strange behavior explained somewhere?

Answer 1

I do not understand why you need to escape using backticks in the first place. In a statement UPDATE x SET a = b, a must always refer to a column of x. b however can either be a variable or a column. Given how local scope and variable resolution works in stored procedures, b will always refer to the local variable, even if a column with the same name in x exists.

Thus, I am unable to reproduce your problem. I tried this way:

mysql> SELECT * FROM comments;
+----+-----------+---------+
| id | parent_id | content |
+----+-----------+---------+
|  1 |         0 | bar     | 
|  2 |         0 | baz     | 
+----+-----------+---------+
2 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE foo(IN content TEXT)
    -> BEGIN
    ->   UPDATE comments SET content = content;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL foo('changed!');
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM comments;
+----+-----------+----------+
| id | parent_id | content  |
+----+-----------+----------+
|  1 |         0 | changed! | 
|  2 |         0 | changed! | 
+----+-----------+----------+
2 rows in set (0.00 sec)

As you can see, the comment-table's column content gets updated, even though content is also the name of the parameter of the stored procedure foo.

Are you sure that UPDATE customers SET Name = Name; gives you an error? With the above explanation, it seems logical that

UPDATE customers SET Name = `Name`;
UPDATE customers SET `Name` = Name;
UPDATE customers SET `Name` = `Name`;

all have the same effect.

Edit: The situation would be different for SELECT statements, of course.