确保最新数据在 SQL 查询中优先

Question

我有一个循环，底部有以下查询，当它与customer_id匹配时，它会将updated_in_sib中的列值更改为1：

$wpdb->update('imp_customer_log', ['updated_in_sib' => 1], ['customer_id' => $customer_id]);

这是我的数据库设置：

CREATE TABLE `imp_customer_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` varchar(128) DEFAULT NULL,
`event` varchar(128) DEFAULT NULL,
`data` varchar(128) DEFAULT NULL,
`updated_in_sib` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=74 DEFAULT CHARSET=latin1

目前正在添加行(同一数据可以有多个行(，但查询选择了最旧的id行，因此当添加新行时，它将被忽略。

如何选择最新行？

例如，id72 行中的数据用于id73、73 的行中的数据。

对于某些上下文，以下是我在每一行中循环的完整代码：

// Update SiB contact hourly
function sendinblue_update() {
global $wpdb;
$customer_data = $wpdb->get_results("SELECT * FROM imp_customer_log WHERE updated_in_sib = '0'");
foreach( $customer_data as $customer ) {
$customer_id = $customer->customer_id;
$customer_event = $customer->event;
$customer_data = $customer->data;
$user = get_user_by( 'id', $customer_id );
$user_email = $user->user_email;
$data_in = array(
$customer_event => $customer_data,
);
$result = $this->sendinblue_update_user($user_email, $data_in);
$wpdb->update('imp_customer_log', ['updated_in_sib' => 1], ['customer_id' => $customer_id]);
}
}

Answer 1

ORDER BY id DESC LIMIT 1，如果对与多行匹配的条件进行选择。你不是说哪个查询"选择了最旧的id">

Answer 2

select * from imp_customer_log icl where icl.customer_id = 'xxx' 
and icl.id = (select max(icl2.id) from imp_customer_log icl2 where icl2.customer_id = 'xxx')

或

select max(id) from imp_customer_log where customer_id = xxx

考虑到ID是自动增量，这将始终为您提供最新行

Answer 3

在第一个get_results查询之后，我刚刚反转了数组以获取最新的：

$customer_data = array_reverse($customer_data);