mySQL INSERT ... ON DUPLICATE KEY列= VALUE(列)+ 1如何工作? [英] How does the mySQL INSERT ... ON DUPLICATE KEY column = VALUE( column ) + 1 work?

问题描述

请参阅第一个问题之后的该问题的更新"部分.

我有两个表格，一个表格用于包含 id ， users_id ，图像， pcimagename ，和视频URL 列；另一个用于包含 id ， users_id ，文件名， user_file_name 和使用计数的图像strong>列.

I have two tables, one for videos that contains a id, users_id, image, pcimagename, and video URL columns; and another for images that contains a id, users_id, file_name, user_file_name, and usage_count columns.

两个表的主键都是 id 列，它们是自动递增的，但是两个 id 值互不相关.图像表在文件名列上还具有唯一键. usage_count 列的默认值为1.

The primary key of both tables are the id columns, which are auto-incrementing, but the two id values are not related to each other. The images table also has a unique key on the file_name column. The usage_count column has 1 for its default value.

phpMyAdmin显示有关我正在使用的mySQL数据库的以下信息:

The phpMyAdmin shows the following information about the mySQL database that I'm using:

Database server
  Server:           Localhost via UNIX socket
  Server type:      MariaDB
  Server version:   5.5.52-MariaDB - MariaDB Server
  Protocol version: 10
  Server charset:   UTF-8 Unicode (utf8)

我的目标是，每当要从视频表中删除一行时，我想先将视频信息从视频表中复制到图像表中，然后将图像(特别是 file_name >列值)在图像表中尚不存在，请将 usage_count 设置为1，但是，如果图像确实存在，我想增加usage_count 加1，然后执行更新.我搜索了"mySQL INSERT或UPDATE".并提出了 INSERT ... ON DUPLCATE KEY UPDATE 变体，这似乎是我要尝试的方法.

My goal is that whenever a row is to be deleted from the videos table, I want to first copy the image information from the videos table into the images table, and if the image (specifically the file_name column value) doesn't already exist in the images table, set the usage_count to 1, but, if the image does exist, I want to increment the usage_count by 1 and perform an update. I searched for "mySQL INSERT or UPDATE" and came up with with the INSERT ... ON DUPLCATE KEY UPDATE variant, which appears to be what I'm trying to do.

但是，我之前从未使用过 INSERT ... ON DUPLICATE KEY UPDATE ... 语句.查看对重复密钥更新进行MySQL插入和插入MySQL表或更新(如果存在)页，我想出了以下内容；不幸的是，我现在可以连接到我的数据库，所以无法确定它是否正确:

However, I've never used the INSERT ... ON DUPLICATE KEY UPDATE ... statement before. Looking at the MySQL INSERT ON DUPLICATE KEY UPDATE and Insert into a MySQL table or update if exists pages, I came up with the following; unfortunately, I'm able to connect to my database at this time, so I'm unable to determine if it is correct:

INSERT
  INTO `photos`
        ( `users_id`, `file_name`, `user_file_name` )
SELECT `users_id`, `image`, `pcimagename`
FROM `videos`
WHERE `status` = \'Deleted\'
   AND `last_updated_on` <= DATE_SUB( NOW(), INTERVAL 1 DAY )
   AND `tracks_id` = 0
ON DUPLICATE KEY UPDATE usage_count = VALUES( usage_count ) + 1;

我的问题是:

1. 语法是否正确，具体来说:

1. Is the syntax correct, specifically:

1a) ON DUPLICATE 短语的位置是否正确?

1a) Is the placement of the ON DUPLICATE phrase correct?

1b)我是否需要将 SELECT 语句括在括号中，我已经看到了可以使用的示例，而其他示例则没有.

1b) Do I need to enclose the SELECT statement in parenthesizes, I've seen examples which did and others that didn't.

使用 UPDATE usage_count = VALUES(用法_计数)+ 1 短语会导致插入图像表的每一行都向该行的插入前use_count值加1，否则所有来自从视频中选择的行是否具有相同的值?我对 ON DUPLICAT 语句的这一部分的实际工作方式了解甚少.大多数示例只是分配了固定值.

Will the UPDATE usage_count = VALUES( usage_count ) + 1 phrase cause each row inserted into the image table to add 1 to the row's pre-insert usage_count value, or will all of the rows coming from the SELECT FROM videos be given the same value? I didn't see very much about how this part of the ON DUPLICAT statement actually worked. Most examples just assigned a fixed value.

更新

使用Anina提供的 dbFiddle 时，我注意到运行以下 INSERT 语句并没有按我期望的那样递增:

While working with the dbFiddle that Anina provided, I noticed that running the following INSERT statement didn't increment as I expected:

After several INSERT ... ON DUPLICATE KEY UPDATE ... statements the test 
table contains:

id  val test    action  comment
1   1   2       update  
2   2   2       update  
3   3   1       insert  

Perform another INSERT ... ON DUPLICATE KEY UPDATE ... statement,
this time to increment val 1, expecting that the test column should increment to 3.

INSERT INTO test (val ) 
VALUES ( 1 )
ON DUPLICATE KEY UPDATE test = VALUES( test ) + 1,
                        action = 'update',
                        comment = 'Why isn''t test 3?';

 However ....

 id val test    action  comment
 1  1   2       update  Why isn't test 3?
 2  2   2       update  
 3  3   1       insert  

该表的创建过程如下:

CREATE TABLE test ( id      INT NOT NULL AUTO_INCREMENT, PRIMARY KEY( ID ),
                    val     INT NOT NULL, UNIQUE( val ),
                    test    INT DEFAULT 1,
                    action  VARCHAR( 10 ) DEFAULT 'insert',
                    comment VARCHAR( 30 ) );

谢谢

推荐答案

对更新的回答 :

为了使测试列超过2，我在创建测试表之后，在现有行上插入任何内容之前添加了以下 UPDATE TRIGGER :

In order to make the test column beyond 2, I added the following UPDATE TRIGGER after creating the test table, before any inserts on existing rows:

CREATE TRIGGER before_test_update
BEFORE UPDATE ON test FOR EACH ROW
BEGIN
  SET new.test = old.test + 1;
END;

这将导致测试列将其在现有行中的值增加1，而不是将失败的插入项的测试值(1)增加1(始终为2).

This causes the test column to increment its value in the existing row by 1, rather than the failed insert's test value (1) incremented by 1 (always 2).

请参见 dbLink ，以了解其工作原理.

See this dbLink for an example that shows how this works.

但是，因为ON DUPLICATE KEY短语只是将 INSERT 转换为 UPDATE ，所以实习生使用上面的触发器来校正测试增量.实际的 UPDATE 也将在更新现有行中的任何列时调用相同的触发器，从而导致错误的测试列值.

However, because the ON DUPLICATE KEY phrase just converts the INSERT to an UPDATE, which intern uses the above trigger to correct the test increment. Actual UPDATEs will also call the same trigger when any of the column in an existing row are updated, causing an incorrect test column value.

mySQL手册说:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the
row is inserted as a new row, 2 if an existing row is updated, and 0 if an
existing row is set to its current values. If you specify the
CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when
connecting to mysqld, the affected-rows value is 1 (not 0) if an existing
row is set to its current values.

当 ON DUPLICARE KEY UPDATE 短语转换 INSERT 时，是否有一种方法可以使用受影响的行值为2的事实到 UPDATE TRIGGER 之前的 UPDATE 或 AFTER UPDATE TRIGGER 之后的其他方法来确定发生了这种情况，而不是直接> UPDATE ，其中受影响的行值为1?

Is there a way to use the fact that the affected-rows value is 2 when the ON DUPLICARE KEY UPDATE phrase converts the INSERT to an UPDATE in the BEFORE UPDATE TRIGGER or an AFTER UPDATE TRIGGER or some other means to determine this happened, rather than when a direct UPDATE is made, where the affected-rows value is 1?

如果是的话，如何从TRIGGER中获取受影响的行值?

If so how do I get the affected-rows value from within a TRIGGER?

注意:在我的代码中，我使用的是 SELECT 子句中的 INSERT ，因此可以在向我的程序返回值之前添加多行.因此，我正在寻找一种在插入多行时运行的Purely-mySQL解决方案.

Note: In my code I'm using an INSERT from a sub-SELECT so multiple rows could be added before returning a value to my program. Therefore, I'm looking for a Purely-mySQL solution that runs when multiple rows are inserted.

这篇关于mySQL INSERT ... ON DUPLICATE KEY列= VALUE(列)+ 1如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！