SQL查询在两个不同的Mysql AWS服务器上的工作方式不同

We have two mysql sql RDS servers and we are facing an issue which i am not able to resolve. The query that we are using is a simple selection of max date row from the data set. This is working fine on the first server, where as this is not working correctly on the other server even on the same data set.

我们有两个mysql sql RDS服务器，我们面临一个问题，我无法解决。我们正在使用的查询是从数据集中简单地选择最大日期行，这在第一个服务器上运行良好，因为即使在相同的数据集中，在另一个服务器上也不能正常工作。

The query we are using is below:

我们正在使用的查询如下:

SELECT `name`,last_update_date FROM 
(SELECT * FROM `table1` ORDER BY last_update_date DESC) X  GROUP BY `name`;

I am not sure why this is happening. I checked for the global variables on both the servers and found the below variables which are not present in the new server:

我不知道为什么会这样。我检查了两个服务器上的全局变量，发现以下变量在新服务器上不存在:

binlogging_impossible_mode
innodb_additional_mem_pool_size
innodb_mirrored_log_groups
innodb_use_sys_malloc
simplified_binlog_gtid_recovery
sql_log_bin
storage_engine
thread_concurrency
timed_mutexes

Any help is appreciated.

任何帮助都是感激。

1 个解决方案

#1

This is not the correct way to get the max date for each name, and there's no reason to expect consistent results from it. The correct way is:

这不是获得每个名称的最大日期的正确方法，也没有理由期望得到一致的结果。正确的方法是:

select name, MAX(last_update_date) as last_update_date
FROM table1
GROUP BY name

The fact that it seems to be working on one server is just coincidence, and it might not last.

它似乎在一台服务器上工作只是巧合，而且可能不会持续太久。

If you want to get the entire row that contains the max date, see SQL Select only rows with Max Value on a Column

如果您想获取包含max日期的整个行，请参见SQL Select only row，其中列的值为max

#1