MySQL 变量名与字段名冲突 [英] MySQL variable name conflicts with field name

问题描述

我正在为 MySQL 编写一个存储过程，它需要一个与表中字段同名的输入参数.我知道我可以将该字段称为 tableName.fieldName.但是，在过程中查询该表时，如何以明确的方式引用变量?

I am writing a stored procedure for MySQL which needs to have an input parameter that has the same name as a field in a table. I know that I can refer to the field as tableName.fieldName. But how can I refer to the variable in a non-ambiguous way when querying that table within the procedure?

Table files
+----+---------+---------+
| id | ownerId | content |
+----+---------+---------+

CREATE PROCEDURE getFilesOfOwner(IN ownerId INT)
BEGIN
  SELECT * FROM files WHERE files.ownerId = ___________      <- ?
  ...

推荐答案

如果过程参数的名称与表的字段名称相同，则在对该表的查询中使用该标识符将被 MySQL 解释为对变量的引用，即正式不需要限定符，但这只是供应商特定的冲突解决方案.

If the name of a procedure argument is the same as a field name of a table, using that identifier in a query to that table will be interpreted by MySQL as a reference to the variable, i.e. no qualifier is formally needed, however this is a vendor-specific conflict resolution only.

保持参数名称相同的解决方案是在过程范围内声明一个别名变量，但以所需的名称将其公开给调用者:

The solution for keeping the argument name the same is to declare an alias variable within the scope of the procedure, but expose it to the caller under the desired name:

CREATE PROCEDURE getFilesOfOwner(IN ownerId INT)
BEGIN
  DECLARE _ownerId INT DEFAULT ownerId;
  SELECT * FROM files WHERE files.ownerId = _ownerId
  ...

这需要额外的一行代码，但它使过程签名与实现特定的细节保持干净.

This requires one additional line of code, but it keeps the procedure signature clean from implementation-specific details.

这篇关于MySQL 变量名与字段名冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！