gpt4 book ai didi

MySQL 存储过程返回不同的结果

转载 作者:行者123 更新时间:2023-11-29 03:23:37 27 4
gpt4 key购买 nike

我有一个查询应该搜索表中的行,找到包含搜索词的任何行(例如“dog%”),然后返回相关父项的 ID(从不同的表连接)。因此,我按父 ID 对结果进行分组,然后对其重新排序,最后设置一个限制/偏移量以获得 10 个唯一 ID 的列表。查询在存储过程中,我从 PHP 调用它。

问题是,我经常得到不同的结果。即使 IN 参数相同,并且数据库中的任何内容都没有改变,我可以在每个请求中得到非常不同的结果 - ID 不一样,顺序不同,我在两个请求中得到重复的 ID...

这是程序:

CREATE DEFINER=`root`@`localhost` PROCEDURE `FIND_VOCABULARY_ID_BY_MEANING`(IN `in_searched_value` varchar(50)
, IN `in_offset` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
insert into test values (in_searched_value,in_offset);

SELECT
v.id
FROM vocabulary AS v
LEFT JOIN vocabulary_sense AS vs ON vs.vocabulary_id = v.id
LEFT JOIN vocabulary_sense_gloss_eng AS vsg ON vsg.sense_id = vs.id
WHERE vsg.gloss LIKE CONCAT(in_searched_value,'%')
GROUP BY v.id
ORDER BY v.calculated_prio DESC
LIMIT 10 OFFSET in_offset;
END

如何调用它:

$stmt = $this->pdo->prepare("CALL $procedureToCall('$searchedTerm', $offset)");

在 PHP 中,我将值(请求及其结果)记录到日志文件中。这里有两种不同的方法,每种方法有五个请求:

[2016-10-11 13:35:19] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 0)
[2016-10-11 13:35:19] 17141,16446,38334,58166,17121,45822,35328,37553,41185,45832
[2016-10-11 13:35:22] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 10)
[2016-10-11 13:35:22] 46659,51149,53639,55276,56388,95,63900,71780,73935,17134
[2016-10-11 13:35:25] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 20)
[2016-10-11 13:35:25] 83260,97433,17176,103416,111512,135069,147790,38335,159709,38338
[2016-10-11 13:35:27] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 30)
[2016-10-11 13:35:27] 162898,38340,163783,38359,165067,38360,171044,38364,38378,38380
[2016-10-11 13:35:31] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 40)
[2016-10-11 13:35:31] 38384,41163,41211,45832,45833,45837
[2016-10-11 13:35:33] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 50)

[2016-10-11 13:50:38] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 0)
[2016-10-11 13:50:38] 17141,16446,38334,58166,17121,45822,35328,37553,41185,56388
[2016-10-11 13:50:41] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 10)
[2016-10-11 13:50:41] 95,63900,71780,73935,17134,83260,97433,17176,103416,111512
[2016-10-11 13:50:45] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 20)
[2016-10-11 13:50:45] 135069,147790,38335,159709,38338,162898,38340,163783,38359,165067
[2016-10-11 13:50:48] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 30)
[2016-10-11 13:50:48] 38360,171044,38364,38378,38380,38384,41163,41211,45832,45833
[2016-10-11 13:50:50] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 40)
[2016-10-11 13:50:50] 45837,45841,46659,51149,53639,55276
[2016-10-11 13:50:53] CALL FIND_VOCABULARY_ID_BY_MEANING('vulgar', 50)

如您所见,在第一种方法中,45832 id 被发送给我两次(偏移量 0 和 40)。在第二种方法中,它存在一次,但在偏移量 30 中...

我还在 Mysql 中记录输入参数 - offset 和 searchedTerm - 也有正确的,与上面 php 生成的日志一致。那么为什么我有这些差异?我在这里做错了什么?

编辑

我发现直接从 MYSQL 客户端(而不是 php)调用过程会给我一致的结果 - 但是话又说回来,当我只调用普通查询时:

    SELECT
v.id
FROM vocabulary AS v
LEFT JOIN vocabulary_sense AS vs ON vs.vocabulary_id = v.id
LEFT JOIN vocabulary_sense_gloss_eng AS vsg ON vsg.sense_id = vs.id
WHERE vsg.gloss LIKE CONCAT('vulgar','%')
GROUP BY v.id
ORDER BY v.calculated_prio DESC
LIMIT 10 OFFSET 0;

结果也不同(仍然一致,我得到相同的结果,但这些结果与程序内部查询不同)...

编辑 2

这是查询中使用的表的结构:

CREATE TABLE `vocabulary` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`entry_sequence` MEDIUMINT(8) UNSIGNED NOT NULL,
`jlpt_level` TINYINT(4) NULL DEFAULT NULL,
`calculated_prio` MEDIUMINT(9) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `idx_vocabulary_calculated_prio` (`calculated_prio`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=174912
;


CREATE TABLE `vocabulary_sense` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`vocabulary_id` MEDIUMINT(8) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_vocabulary_sense_vocabulary_id` (`vocabulary_id`),
CONSTRAINT `fk_vocabulary_sense_vocabulary_id` FOREIGN KEY (`vocabulary_id`) REFERENCES `vocabulary` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=195529
;


CREATE TABLE `vocabulary_sense_gloss_eng` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`sense_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`gloss` TEXT NOT NULL,
PRIMARY KEY (`id`),
INDEX `vocabulary_sense_gloss_vocabulary_sense_id` (`sense_id`),
INDEX `vocabulary_sense_gloss_gloss` (`gloss`(255)),
CONSTRAINT `vocabulary_sense_gloss_eng` FOREIGN KEY (`sense_id`) REFERENCES `vocabulary_sense` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=317857
;

词汇是主要入口。 vocabulary_sense(一对多)指向它。 vocabulary_sense_gloss_eng(同样,一对多)指向 vocabulary_sense。

“calculated_prio”只是一个静态的 INT 值。

最佳答案

哦,该死,我太笨了:)

问题出在排序上,calculated_prio 只有前 9 行有正值,其余的只有 0。所以只有前 9 行是特定顺序,其他都是随机的。

关于MySQL 存储过程返回不同的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39977336/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com