MySQL: как вы запрашиваете составной первичный ключ? В частности, запрос NOT IN?

У меня есть таблица comment и таблица comment_edit, а также таблица olddb_edit. Упрощенно соответствующая таблица выглядит так:

CREATE TABLE `olddb_edit` (
    edit_id INT NOT NULL,
    edit_time INT NOT NULL,
    edit_text TEXT NOT NULL,

    PRIMARY KEY (edit_id, edit_time)

) ENGINE=InnoDB;

И теперь я хочу перенести содержимое из другой БД в таблицу редактирования, но пропустить некоторые строки таблицы, такие как тестовые комментарии. Я использую для этого CakePHP (на самом деле Phinx).

Обычно этого достаточно:

$skippable = array(
    12345, 23456, 34567, 45678,
);
$rows = $this->getQueryBuilder()
    ->select('*')
    ->from('olddb_comment')
    ->where(array(
        'comment_id NOT IN' => $skippable,
    ))
    ->execute()
    ->fetchAll('assoc')
;

Но простое предложение NOT IN, очевидно, не работает с составными первичными ключами.

Я думал, что массив $skippable должен выглядеть так:

$skippable = array(
    array('id' => 707969,   'time' => 1434462225),
    array('id' => 707969,   'time' => 1434462463),
    array('id' => 707969,   'time' => 1434462551),
);

А затем я прогоню предложение where через цикл for или что-то в этом роде. Но, честно говоря, я даже не знаю, как это сделать в vanilla-MySQL.

Возможно, на SO уже было опубликовано решение, но я не смог найти его (кроме тех, которые относятся к другим приложениям). Думаю, алгоритм мне не друг.


mysql cakephp composite-primary-key notin phinx
person WoodrowShigeru    schedule 16.05.2020    source источник
comment
Пожалуйста, задайте 1 конкретный исследовательский вопрос, не повторяющийся, о том, где вы застряли. Для вопросов по коду дайте минимальный воспроизводимый пример -- вырезайте, вставляйте и запускайте код, включая наименьший репрезентативный пример ввода как код; желаемый и фактический вывод (включая дословные сообщения об ошибках); теги и версии; четкая спецификация и объяснение. Для SQL, СУБД и DDL (включая ограничения и индексы) и ввода в виде кода, отформатированного в виде таблицы. PS Ключи для запроса не нужны. Соединения выполняются по условиям, а не по ключам. IN для подзапроса принимает строку, а не ключ. Является ли он многоколоночным, зависит от СУБД. Обычно EXISTS является лучшим выбором.   -  person philipxy    schedule 16.05.2020

Ответы (2)


arrow_upward
0
arrow_downward

Ваш запрос оценивается следующим образом; ты уверен, что ты этого хочешь?

select edit_id 
     , edit_time
     , edit_text 
  from olddb_edit 
 where 
     (
       (
         (edit_id <> 707969) 
      or (edit_time <> 1434461454)
       ) 
   and (
         (edit_id <> 707969) 
      or (edit_time <> 1434461503)
       ) 
   and (
         (edit_id <> 707969) 
      or (edit_time <> 1434461925)
        )
      );
person Strawberry    schedule 16.05.2020
comment
Странный взгляд на это, но по существу: да. Это то, что я хочу. Те строки таблицы, которые одновременно являются отрицанием каждой отдельной пропускаемой строки. В конце концов, это и есть NOT IN. - person WoodrowShigeru; 17.05.2020

arrow_upward
0
arrow_downward

Ничего, я сам догадался, пока формулировал вопрос. Я все равно опубликую ответ для других с аналогичным вопросом.

Во-первых, ваниль-MySQL. Это так же интуитивно, как вы могли бы подумать, если разбить NOT IN (imo):

SELECT * FROM olddb_edit WHERE
NOT (
    (edit_id = 707969 AND edit_time = 1434461454)
OR  (edit_id = 707969 AND edit_time = 1434461503)
OR  (edit_id = 707969 AND edit_time = 1434461925)
);

А с конструктором запросов CakePHP/Phinx вы используете анонимную функцию, цикл for и конструкцию not-or:

$qb = $this->getQueryBuilder()
    ->select('*')
    ->from('olddb_edit')
    ->where(array(
        'edit_some_other_optional_condition = 1',
    ))

    // also skip skippables.
    ->where(function($exp) use ($skippable) {
        $ORed = array();
        foreach ($skippable as $edit) {
            array_push($ORed, array(
                'edit_id'   => $edit['id'],
                'edit_time' => $edit['time'],
            ));
        }
        return $exp->not($exp->or_($ORed));
    })
;

ОБНОВЛЕНИЕ: На основе комментария @ndm я представляю удовлетворительное решение с использованием TupleComparison. — (@ndm, не стесняйтесь опубликовать свой ответ, если хотите. Я удалю / отредактирую свой и выберу ваш. Вы заслуживаете похвалы).

// remove the keys from my previous solution.
$skippable = array(
    array(707969,   1434462225),
    array(707969,   1434462463),
    array(707969,   1434462551),
);

$qb = $this->getQueryBuilder()
    ->select('*')
    ->from('olddb_edit')
    ->where(array(
        'edit_some_other_optional_condition = 1',
    ))

    // also skip skippables.
    ->where(new Cake\Database\Expression\TupleComparison(
        array('edit_id', 'edit_time'),
        $skippable,
        array('integer', 'integer'),
        'NOT IN'
    ))
;
person WoodrowShigeru    schedule 16.05.2020
comment
В SQL вместо развертывания NOT IN также будут работать кортежи. Как 2_ - person sticky bit; 16.05.2020
comment
@stickybit Я нахожу OR NOT AND очень запутанным, поэтому могу ошибаться, но я думаю, что ваш запрос был бы логически другим запросом (и, вполне вероятно, тем, который предназначался OP). - person Strawberry; 16.05.2020
comment
@Strawberry: Хм, я не знаю ... Во-первых, обратите внимание, что в этом ответе я ссылаюсь на SQL, а не на что-либо из вопроса и не на что-либо PHP, если это неясно. Тогда вот мое рассуждение: все в скобках после NOT может быть выражено как IN с кортежами. AND покрываются с помощью кортежей, а затем это группа OR с одним и тем же левым операндом, который может быть выражен как IN. Таким образом, мы получаем NOT (... IN ...). И это можно упростить до NOT IN .... Но, конечно, я могу ошибаться где-то. Может быть, вы можете построить контрпример? - person sticky bit; 16.05.2020
comment
Я не знал, что NOT IN поддерживает кортежи или что это вообще такое (кроме операторов INSERT). Я не мог (и до сих пор не могу) найти какие-либо официальные документы по этому поводу на dev.mysql.com… Я изучу это и посмотрю, есть ли для этого функция CakePHP. - person WoodrowShigeru; 17.05.2020
comment
@Strawberry Я не вижу разницы, о которой ты говоришь. - person WoodrowShigeru; 17.05.2020
comment
Существует выражение для сравнения кортежей: stackoverflow.com/questions/37919141/ - person ndm; 18.05.2020
comment
@ndm, это именно то, что я искал, и это прекрасно работает. Вы хотите написать правильный ответ на это, чтобы я мог его принять? - person WoodrowShigeru; 21.05.2020