Обусловлено дублированием ключевого обновления

Я пытаюсь вставить новую строку, но если ключ уже существует, я хочу обновить строку ТОЛЬКО, если другое значение в таблице отличается. Возможно ли это в запросе/заявлении mysql?

Моя таблица состоит из следующих столбцов: hat, mittens, name, last_update

hat + рукавицы составляют уникальный индекс (скажем, значения для "шляп" и "варежки" являются цветами)

Предположим, что это уже находится в таблице:

1. hat=blue mittens=green name=george last_update=tuesday
2. hat=red mittens=green name=bill last_update=monday

На новом ключе я хочу вставить как обычно. В двойном ключе, я хочу делать обновление ТОЛЬКО, если имя изменяется, иначе игнорируется. Причина этого в том, что я хочу сохранить значение last_update (метка времени).

hat=yellow mittens=purple name=jimmy -- insert new row
hat=blue mittens=green name=george -- ignore 
hat=blue mittens=green name=betty -- update row

Возможно ли, что без использования отдельных операторов сначала просмотреть существующую строку, сравнить значения и затем, если необходимо, выдать обновление? Если да, то какой будет синтаксис?

Спасибо за ваши ответы. Я попробовал их всех. Действительно, используя простой оператор UPDATE, например

update tbl set name='george' where hat='blue' and mittens='green'

не приводит к обновлению строк. Но, используя либо

 INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';

или

INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END;

каким-то образом приводит к обновлению строки (и изменилась метка времени).

FWIW, это таблица, которую я использую:

CREATE TABLE `tbl` (
`hat` varchar(11) default NULL,
`mittens` varchar(11) default NULL,
`name` varchar(11) default NULL,
`stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `clothes` (`hat`,`mittens`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

MySQL - это версия 4.1.22 (возможно, это имеет значение?) Опять же, моя оценка за все ответы.

Ответы

Ответ 1

Вы можете использовать обычные sql-конструкции в синтаксисе ON DUPLICATE KEY. Поэтому для выполнения условных обновлений во время вставки вы можете сделать следующее:

INSERT INTO tbl (hat, mittens, name) 
VALUES ('yellow','purple','jimmy')
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) 
                                    THEN VALUES(name) ELSE name END;

Это изменит значение на то, что вы указали в инструкции insert, когда оно отличается от того, что в строке, и установит значение как то, что уже есть, если оно не изменилось и приведет к тому, что MySQL ничего не сделает строка, сохраняющая временную метку last_update, как указал Квасной.

Если вы хотите сделать 100% уверенным, что вы не полагаетесь на поведение MySQL, где он не обновляет строку, если вы установите значение для себя, вы можете сделать следующее, чтобы заставить отметку времени:

INSERT INTO tbl (hat, mittens, name) 
VALUES ('yellow','purple','jimmy')
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) 
                                    THEN VALUES(name) ELSE name END
                      , last_update = CASE WHEN name <> VALUES(name) 
                                      THEN now() ELSE last_update END;

Это обновит только last_update до now(), когда имя изменилось иначе, это покажет, что MySQL сохранит значение last_update.

Кроме того, в разделе ON DUPLICATE KEY этого оператора вы можете ссылаться на столбцы в таблице по их имени, и вы можете получить значения, предоставленные в разделе значений операторов вставки, используя VALUES (column_name).

Ниже приведен журнал, который показывает, что последний предоставленный оператор работает даже на 4.1, где остальные не работают из-за ошибки, которая была исправлена ​​в версии 5.0.

C:\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `tbl` (
    -> `hat` varchar(11) default NULL,
    -> `mittens` varchar(11) default NULL,
    -> `name` varchar(11) default NULL,
    -> `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    -> UNIQUE KEY `clothes` (`hat`,`mittens`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:16 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:30 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:42 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END, stamp = CASE WHEN name <> VALUES(name) THEN now() ELSE stamp END;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:42 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql>

Сообщите мне, если у вас есть какие-либо вопросы.

НТН,

-Dipin

Ответ 2

Вам нужно синтаксис INSERT... ON DUPLICATE KEY UPDATE.

Ваш запрос будет выглядеть следующим образом:

    INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george')
    ON DUPLICATE KEY UPDATE name='george';

Если у вас была запись с синим/зеленым/джорджем для шляпы/рукавиц/имя уже, UPDATE фактически не будет выполняться, и ваша метка времени не будет обновляться. Если, однако, у вас была запись с синим/зеленым/бетти, тогда "betty" будет перезаписана "george", и ваша временная метка будет обновлена.

Ответ 3

Вам не нужно ничего делать, это поведение по умолчанию.

Если ваш запрос выбирает строку для UPDATE, но обновленное значение остается таким же, как в:

UPDATE  table
SET     col = col

временная метка также остается неизменной.

Эта строка даже не учитывается как затронутая, запрос вернет 0 rows affected.

Ответ 4

Если вы выполняете несколько INSERT (либо SELECT или предоставляя несколько строк на VALUES), вы можете сделать следующее:

INSERT INTO tbl (hat,mittens,name) VALUES
    ('yellow','purple','jimmy'),
    ('blue','green','george'),
    ('blue','green','betty')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Это будет:

  • Вставьте строки, которые не являются дубликатами w/resp. к индексам UNIQUE
  • Обновите столбец name для остальных (когда новое значение отличается)

Предостережение: комментарии в документации по синтаксису ON DUPLICATE говорят, что результат mysql_affected_rows() впоследствии не является надежным.