更新变量的MYSQL存储过程为0

2021-12-20 00:00:00 mysql stored-procedures

我在 MYSQL 数据库中有以下存储过程.存储过程得到 lon、lat 并且我正在对不同的数据库和表进行更新.

I have the following stored procedure in a MYSQL database. The stored procedure gets lon,lat and than I'm doing an update on a different database and table.

DROP PROCEDURE IF EXISTS annuals.updateSalesFlorida;
CREATE DEFINER=`dddd`@`%` PROCEDURE `updateSales`(propertyId int)
BEGIN

   DECLARE lat   FLOAT;
    DECLARE lon FLOAT;


  SELECT  SitusLongitude,SitusLatitude
  INTO lon,lat
  FROM annuals.florida
  WHERE PropertyID=propertyId
  LIMIT 1 FOR UPDATE;

  UPDATE sales.florida
  SET
    `SitusLongitude` = lon,
    `SitusLatitude` = lat
  WHERE PROPERTYUNIQUEID=propertyId;

END;

每次运行存储过程时，SitusLongitude 和 SitusLatitude 列都是 0.我知道之前选择的 SitusLongitude 和SitusLatitude 在那里有实际值.出于某种原因，变量没有设置在纬度，经度.知道我做错了什么吗?

Every time I run the stored procedure the SitusLongitude and SitusLatitude columns are 0. I know for a fact that the previous selected SitusLongitude and SitusLatitude have actual values in there. For some reason the variables are not being set in lat,lon. Any idea what I'm dong wrong?

推荐答案

问题是过程参数与表中的列同名.当您在查询中引用 propertyid 时，它使用的是列，而不是参数.列名和变量名不区分大小写，因此您拼写其中一个 PropertyID 和另一个 propertyId 无关紧要.

The problem is that the procedure parameter has the same name as a column in the tables. When you refer to propertyid in the queries, it uses the column, not the parameter. Column and variable names are case-insensitive, so it doesn't matter that you spelled one of them PropertyID and the other propertyId.

为参数使用不同的名称，例如p_propertyId

Use a different name for the parameter, e.g. p_propertyId

此外，不需要两个查询，您可以使用 JOIN 将其合二为一.

Also, there's no need for two queries, you can do it in one with a JOIN.

UPDATE sales.florida AS s
CROSS JOIN (
    SELECT *
    FROM annuals.florida
    WHERE propertyId = p_propertyId
    LIMIT 1) AS a
SET s.SitusLongitude = a.SitusLongitude, s.SitusLatitude = a.SitusLatitude
WHERE s.PROPERTYUNIQUEID = p_propertyId

请注意，使用 LIMIT 而没有 ORDER BY 意味着被选中的行将是不可预测的.

Note that using LIMIT without ORDER BY means that the row that's selected will be unpredictable.

相关文章