MySQL:当存储过程参数名称与表列名称相同时 [英] MySQL : When stored procedure parameter name is the same as table column name

问题描述

比方说，有一个存储过程SetCustomerName，它具有输入参数Name，而我有一个表customer，其列名为Name. 因此，在我的存储过程中，我想设置客户的姓名.如果我写

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 see 2 other ways:

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

第一个有效，但是我没有在文档中发现可以将参数包装在`字符内.还是我在文档中错过了它(在这种情况下，感谢您提供链接).

First one works, but I didn't find in documentation that I can wrap parameters inside ` characters. Or did I miss it in the documentation (link is appreciated in this case).

还有其他方法，这种情况下的标准方法是什么?重命名输入参数对我不利(因为如果您知道我的意思，我会自动进行对象关系映射).

What other ways are there and what is the standard way for such a case? Renaming input parameter is not good for me (because I have automatic object-relational mapping if you know what I mean).

更新:

因此，有一个关于反引号的链接( http://dev .mysql.com/doc/refman/5.0/en/identifiers.html )，但是对如何使用它们(如何在参数和列名中使用它们)的解释还不够深入.

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?

推荐答案

区分参数和列的最简单方法(如果两个名称相同)是在表名中添加表名.

Simplest way to distinguished between your parameter and column (if both name is same) is to add table name in your column name.

UPDATE customers SET customers.Name = Name;

甚至您也可以添加数据库前缀

Even you can also add database prefix like

UPDATE yourdb.customers SET yourdb.customers.Name = Name;

通过添加数据库名称，您可以从单个存储过程中对多个数据库执行操作.

By adding database name you can perform action on more than 1 database from single store procedure.

这篇关于MySQL:当存储过程参数名称与表列名称相同时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！