重复键上的PHP + MYSQL仍会增加索引键 [英] PHP + MYSQL on Duplicate KEY still increase the INDEX KEY

问题描述

我的桌子有这种风格:

ID - EMAIL - VERSION - LASTUPDATE

其中id =是自动递增的值，并且是Primary:

where id = is auto increment and Primary:

并且电子邮件是唯一

因此，每当有人使用我的应用程序时，它都会获得用户电子邮件并尝试插入(如果该电子邮件已经存在)，则将行更新为$ version和$ lastupdate的新值

so everytime someone use my Application it get the user Email and try to insert, if the email already exist, it UPDATE the row to new values for $version and $lastupdate

它几乎可以工作100％，它已经完美地更新了行，问题是当它执行UPDATE时，仍然增加了自动增量ID，因此下一个INSERT将不是lastID加一个，而将是另一个数字例如:

it almostly works 100%, its already updating the row perfectly, the problem is that when it do the UPDATE, still increase the auto increment id, so the next INSERT will not be the lastID plus one, and will be another number example:

ID - EMAIL - VERSION - LASTUPDATE
-----------------------------------
1 - test1@... - 1.0 - currentTime();

如果test2加载了我的应用程序，它将添加并且数据库将为:

if the test2 load my app it will add and the database will be:

ID - EMAIL - VERSION - LASTUPDATE
-----------------------------------
1 - test1@.. - 1.0 - currentTime();
2 - test2@.. - 1.0 - currentTime();

因此，现在想象一下test2具有新版本并更新到此版本:

so imagine now that the test2 has the new version and it update to this:

ID - EMAIL - VERSION - LASTUPDATE
-----------------------------------
1 - test1@.. - 1.1 - currentTime();
2 - test2@.. - 1.0 - currentTime();

完全不是吗?所以问题开始于test3打开我的应用程序时，id不会是3，它将是4，因为更新为id添加了1(我不知道为什么)

perfectly isnt it? so the problem start when the test3 open my app, the id will not be 3, it will be 4 because the update added 1 more to the id(i dont know why)

ID - EMAIL - VERSION - LASTUPDATE
-----------------------------------
1 - test1@.. - 1.1 - currentTime();
2 - test2@.. - 1.0 - currentTime();
4 - test3@.. - 1.1 - currentTime();

为什么在更新时增加ID的加一?我不想要=(

why its increasing the plus one to the ID when do the update? i dont want it =(

我的SQL语法:

$sql = "INSERT INTO `backups`.`regs` (`email`, `version`, `lastupdate`) VALUES ('$email', '$version', '$lastupdate')  ON DUPLICATE KEY UPDATE version='$version', lastupdate='$lastupdate'";

推荐答案

好的，现在我记得这个问题.曾经有一个人想要插入，但是如果可以想象的话，每个插入必须以100为增量，从@ 1000开始.而且我们必须将整个程序包装在一个存储的proc中，以使漏洞存在一个地方.您的问题浮出水面，这使他的编号减少了大约1.

ok yeah, now I remember this issue. There was a guy once that wanted to do inserts, but each insert had to be in increments of 100 if you could imagine, starting @1000. And we had to wrap the whole thing in a stored proc to have one place of vulnerability. Your issue surfaced and it threw off his numbering by 1 or so.

通过包装它，我们可以合理地做到这一点，并带有锁，并使用ALTER TABLE

By wrapping it, we could sanely have one point of doing it, with a lock, and maintain the auto_inc value with ALTER TABLE

我对他说的另一种方法是拥有一个增量器表，锁定第一行，获取该行中的值，使用它，将incTable更新100.

The other approach I said to him was have an incrementer table, lock the 1 row, get the value in that row, use it, update that incTable by 100. unlock.

我们一直在谈论强迫症问题.我认为他只喜欢10的倍数，idk

The whole time we were laughing about OCD issues. I think he liked multiples of 10 only, idk

-- drop table ocd_nextnums;
create table ocd_nextnums
(   -- id table for nextnum, for the OCD impaired
    tableName varchar(100) not null,
    nextnum int not null
    -- won't bother with indexes, go for it if you want
)engine=INNODB; -- note engine type

insert ocd_nextnums(tableName,nextnum) values('thing',1);
insert ocd_nextnums(tableName,nextnum) values('some_other_table',1);

-- drop table thing;
create table thing
(   id int primary key, -- NOT an auto_increment, but is a PK
    email varchar(100) not null,
    version varchar(20) not null,
    lastupdate datetime not null,
    UNIQUE KEY (email)
)engine=MyIsam;

存储过程:

-- drop procedure putInThing;
delimiter $$
create procedure putInThing
(
    email_In varchar(100), version_In varchar(20)
)
BEGIN
    declare toUse int;
    declare theCount int;

    select count(*) into theCount from thing where email=email_In;
    select id into toUse from thing where email=email_In;   -- useful for result set @end
    IF theCount=1 THEN
        -- was there, do UPDATE
        update thing set version=version_In,lastupdate=now() where email=email_In;
    ELSE
        -- new row, do INSERT (please note the FOR UPDATE clause)
        select nextnum into toUse from ocd_nextnums where tableName='thing' FOR UPDATE;
        update ocd_nextnums set nextnum=nextnum+1 where tableName='thing';

        insert thing(id,email,version,lastupdate) values (toUse,email_In,version_In,now());
    end if;
    select toUse;   -- <------- that was your id
END
$$

测试:

call putInThing('[e-mail]','111');
call putInThing('[e-mail]','121');
call putInThing('[e-mail]','107');
select * from thing;
+----+----------+---------+---------------------+
| id | email    | version | lastupdate          |
+----+----------+---------+---------------------+
|  1 | [e-mail] | 111     | 2015-08-14 17:08:10 |
|  2 | [e-mail] | 121     | 2015-08-14 17:08:54 |
|  3 | [e-mail] | 107     | 2015-08-14 17:08:56 |
+----+----------+---------+---------------------+

call putInThing('[e-mail]','101111007'); -- is an update
call putInThing('[e-mail]','42'); -- is an update
call putInThing('[e-mail]','10007'); -- is an update
call putInThing('[e-mail]','1'); -- is an insert

select * from thing;
+----+----------------------+---------+---------------------+
| id | email                | version | lastupdate          |
+----+----------------------+---------+---------------------+
|  1 | [e-mail]             | 111     | 2015-08-14 17:08:10 |
|  2 | [e-mail]             | 121     | 2015-08-14 17:08:54 |
|  3 | [e-mail]             | 10007   | 2015-08-14 17:22:09 |
|  4 | [e-mail] | 1       | 2015-08-14 17:22:47 |
+----+----------------------+---------+---------------------+

从手册的Mysql INNODB部分中>:

From the Mysql INNODB part of Manual:

要实现读取和递增计数器，请先执行 使用FOR UPDATE锁定计数器的读取，然后递增 柜台.....

To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter.....

您是否可能会看到我使用它.只是显示它.我很好，有空隙，晚上睡觉.这就是为什么我将第一个表命名为我所做的事情的原因:>

Will you see me using this, probably not. Just showing it. I am fine with gaps and sleeping at night. Which is why I named the first table what I did :>

这篇关于重复键上的PHP + MYSQL仍会增加索引键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！