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

Question

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

问题是，我经常得到不同的结果。尽管IN参数是相同的，并且数据库中的任何东西都没有改变，但我可以在每个请求中获得非常不同的结果- in不同，顺序不同，我在两个请求中获得重复的in ...

这是一个步骤：

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...

Im还记录了Mysql中的输入参数-偏移和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值。

Answer 1

哦，见鬼，我太傻了:)

排序的问题是，calculated_prio只对前9行有正值，而对其余行只有0。因此，只有前九行是按特定顺序排列的，其他的都是随机的。