MySQL的存储过程中参数名和列名同名时使用的究竟是哪一个？

在一些编码风格较差的 PL/SQL 代码中，往往会遇到传入的参数/用户自定义变量和表的列名同名的情况，这种情形下，PL/SQL 解析器究竟引用的是哪一个呢？ 本文主要介绍这种场景下PL/SQL解析器实际的解析情况。

详细说明

在 MySQL 中，如果在存储过程或函数中参数和列同名，参数将优先。如果需要引用表中的列，需要使用表的别名或者是 TABLE_NAME.COLUMN_NAME 的形式来明确指出是列而不是参数。 例如，假设有一个名为 users 的表，其中包含一个名为 name 的列，你有一个存储过程 GetUser，它有一个名为 name 的参数：

CREATE PROCEDURE GetUser(IN name VARCHAR(255))
BEGIN
    SELECT name FROM users WHERE name = name;
END;

在这个存储过程中，SELECT 语句中引用到的三个 name 会引用参数 name，而不是 users 表的列。为了引用表的列，你需要修改查询如下：

CREATE PROCEDURE GetUser(IN name VARCHAR(255))
BEGIN
    SELECT users.name FROM users WHERE users.name = name;
END;

或者使用表的别名：

CREATE PROCEDURE GetUser(IN name VARCHAR(255))
BEGIN
    SELECT u.name FROM users AS u WHERE u.name = name;
END;

在上述这两个示例中，users.name 或 u.name 都指向 users 表的 name 列，而不是存储过程的参数。

OceanBase 数据库 MySQL 租户中针对这种歧义时的行为表现和原生 MySQL一样。示例如下：

1. 通过 MySQL 客户端连接 OceanBase 数据库的 MySQL 模式租户。

   [root@xxx ~]# mysql -h127.1 -uroot@mysql -P2881 -pxxx -A -c test
   

   登录成功后，返回如下结果：

   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MySQL connection id is 3221658603
   Server version: 5.7.25 OceanBase 4.2.1.8 (r108000052024072217-77f9516419866bc291fc79b950b0865f2f4194e0) (Built Jul 22 2024 17:42:10)
   
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   

2. 创建表 users。

   MySQL [test]> create table users (name varchar(100));
   Query OK, 0 rows affected (0.094 sec)
   

3. 向表 users 插入数据。

   MySQL [test]> insert into users values ('Bob'),('Jack'),('Tom');
   Query OK, 3 rows affected (0.022 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   

4. 提交数据。

   MySQL [test]> commit;
   Query OK, 0 rows affected (0.000 sec)
   

5. 查询表 users 内容。

   MySQL [test]> select * from users;
   

   输出结果如下：

   +------+
   | name |
   +------+
   | Bob  |
   | Jack |
   | Tom  |
   +------+
   3 rows in set (0.003 sec)
   

6. 使用 delimiter 命令修改结束符为 ;;。

   MySQL [test]> delimiter ;;
   

7. 创建存储过程 GetUser。

   MySQL [test]> CREATE PROCEDURE GetUser(IN name VARCHAR(255))
   BEGIN
    SELECT name FROM users WHERE name = name;
   END;
   ;;
   Query OK, 0 rows affected (0.048 sec)
   

   说明

   这边如果将 BEGIN ... END 之间的 name 都加上反引号 `name`，效果是一样的。

8. 使用 delimiter 命令修改结束符为默认符号分号（;）。

   MySQL [test]> delimiter ;
   

9. 调用存储过程 GetUser。

   MySQL [test]> call GetUser('Bob');
   

   输出结果如下：

   +------+
   | name |
   +------+
   | Bob  |
   | Bob  |
   | Bob  |
   +------+
   3 rows in set (0.001 sec)
   
   Query OK, 0 rows affected (0.001 sec)
   

   MySQL [test]> call GetUser('xxx');
   

   输出结果如下：

   +------+
   | name |
   +------+
   | xxx  |
   | xxx  |
   | xxx  |
   +------+
   3 rows in set (0.001 sec)
   
   Query OK, 0 rows affected (0.001 sec)
   

10. 删除数据库中已经存在的存储过程 GetUser 并防止因删除不存在的存储过程而引发的错误。

    MySQL [test]> drop procedure if exists GetUser;
    Query OK, 0 rows affected (0.047 sec)
    

11. 使用 delimiter 命令修改结束符为 ;;。

    MySQL [test]> delimiter ;;
    

12. 创建存储过程 GetUser。

    MySQL [test]> CREATE PROCEDURE GetUser(IN name VARCHAR(255))
    BEGIN
    SELECT u.name FROM users AS u WHERE u.name = name;
    END;
    ;;
    Query OK, 0 rows affected (0.046 sec)
    

13. 使用 delimiter 命令修改结束符为默认符号分号（;）。

    MySQL [test]> delimiter ;
    

14. 调用存储过程 GetUser。

    MySQL [test]> call GetUser('Bob');
    

    输出结果如下：

    +------+
    | name |
    +------+
    | Bob  |
    +------+
    1 row in set (0.007 sec)
    
    Query OK, 0 rows affected (0.007 sec)
    

    MySQL [test]> call GetUser('xxx');
    Empty set (0.001 sec)
    
    Query OK, 0 rows affected (0.001 sec)
    

更权威的解释可以参考 MySQL 官方文档的内容：15.6.4.2 Local Variable Scope and Resolution，涉及的内容如下。

A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. Consider the following procedure definition:
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;

  SELECT xname, id INTO newname, xid
    FROM table1 WHERE xname = xname;
  SELECT newname;
END;
MySQL interprets xname in the SELECT statement as a reference to the xname variable rather than the xname column. Consequently, when the procedure sp1()is called, the newname variable returns the value 'bob' regardless of the value of the table1.xname column.

适用版本

OceanBase 数据库 V2.x、V3.x、V4.x 版本。