相同的MySQL查询在MySQL Workbench中返回的结果不同于命令行中的结果 [英] Identical MySQL query returning different result in MySQL Workbench than from command-line

问题描述

 定界符$$ 
 
 CREATE DEFINER =`root` @`localhost `函数`calculatePrice`（cheese VARCHAR（50），meat VARCHAR（50），veg VARCHAR（50））RETURNS decimal（10,0）
 DETERMINISTIC 
 BEGIN 
 DECLARE price DECIMAL; 
 SET价格=（SELECT SUM（x.Price）
 FROM 
（
 SELECT`priceFactor` AS Price FROM`tblCheese` WHERE`cheeseName` = cheese 
 UNION ALL 
 SELECT`priceFactor` AS Price FROM`tblMeat` WHERE`meatName` = meat 
 UNION ALL 
 SELECT`priceFactor` AS Price FROM`tblVeggie` WHERE`veggieName` = veg 
）x）; 
 RETURN价格; 
 END $$ 
 
`
  

当从MySQL命令行客户端调用正确的答案时，但对于其他程序或从PHP调用时不正确（不管传递了什么参数，都返回3）。请参阅下面的内容：

调用语句，以防万一模糊，是SELECT calculatePrice（'colby'，'香肠'，'黑豆'）;

我以前从来没有见过这种奇怪。这一切都是从MySQL的同一副本等。

编辑添加：phpMyAdmin也会产生查询的正确答案。

我可以告诉你这是怎么回事。

MySQL最长的时间是latin1_swedish_ce默认字符集几乎所有东西。现在，你在创建数据库时通常会关注字符集，所以危险性很小。

然而，通过一行传输数据需要编码和解释用户端的数据也需要解释。所以也有相应的设置。 MySQL工具的标准字符集（因为它们来自同一家公司）latin1_swedish。

这对于查询来说是否成问题取决于所有数据查询结束。此外，在查询中使用常量字符串时，它们的解释方式与从数据库到客户端的数据的解释方式大致相同。

因此，字符编码通常是问题，它也在这里。

This query `

delimiter $$

CREATE DEFINER=`root`@`localhost` FUNCTION `calculatePrice`(cheese VARCHAR(50), meat VARCHAR(50), veg VARCHAR(50)) RETURNS decimal(10,0)
    DETERMINISTIC
BEGIN
DECLARE price DECIMAL;
SET price = (SELECT  SUM(x.Price) 
    FROM
    (
        SELECT `priceFactor` AS Price FROM `tblCheese` WHERE `cheeseName` = cheese
        UNION ALL 
        SELECT `priceFactor` AS Price FROM `tblMeat` WHERE `meatName` = meat 
        UNION ALL 
        SELECT `priceFactor` AS Price FROM `tblVeggie` WHERE `veggieName` = veg
    ) x );
RETURN price;
END$$

`

is returning the mathematically-correct answer when called from the MySQL command-line client, but incorrect for any other program or when called from PHP (it's returning 3 there, no matter what parameters are passed.) See below:

The calling statement, in case it's blurry, is SELECT calculatePrice('colby', 'sausage', 'black beans');

I've never seen this weirdness before. It's all going off of the same copy of MySQL, etc.

Edit to add: phpMyAdmin also yields the correct answer to the query.

解决方案

I can tell you how that went.

MySQL has shipped for the longest time with latin1_swedish_ce as default character set for practically everything. Now, you usually take care of the character set when creating databases, so the danger there is minimal.

However, transferring the data over a line needs an encoding, and interpreting the data on the user end needs interpretation, too. So there are settings for that as well. And the standard character sets for MySQL tools are (as they are from the same company) latin1_swedish.

Whether this will be a problem for a query highly depends on all the data that the query runs over. Also, when using constant strings in your query, they are interpreted much the same way as data coming from the database to your client.

Therefor, character encoding is often the problem, and it was here as well.

这篇关于相同的MySQL查询在MySQL Workbench中返回的结果不同于命令行中的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！