<legend id='HXmyk'><style id='HXmyk'><dir id='HXmyk'><q id='HXmyk'></q></dir></style></legend>
  1. <i id='HXmyk'><tr id='HXmyk'><dt id='HXmyk'><q id='HXmyk'><span id='HXmyk'><b id='HXmyk'><form id='HXmyk'><ins id='HXmyk'></ins><ul id='HXmyk'></ul><sub id='HXmyk'></sub></form><legend id='HXmyk'></legend><bdo id='HXmyk'><pre id='HXmyk'><center id='HXmyk'></center></pre></bdo></b><th id='HXmyk'></th></span></q></dt></tr></i><div id='HXmyk'><tfoot id='HXmyk'></tfoot><dl id='HXmyk'><fieldset id='HXmyk'></fieldset></dl></div>

      • <bdo id='HXmyk'></bdo><ul id='HXmyk'></ul>

      <small id='HXmyk'></small><noframes id='HXmyk'>

      <tfoot id='HXmyk'></tfoot>

    1. ON DUPLICATE KEY UPDATE 的自动增量太多

      时间:2023-07-17

        <legend id='njCDV'><style id='njCDV'><dir id='njCDV'><q id='njCDV'></q></dir></style></legend>

        <small id='njCDV'></small><noframes id='njCDV'>

        • <tfoot id='njCDV'></tfoot>

            <i id='njCDV'><tr id='njCDV'><dt id='njCDV'><q id='njCDV'><span id='njCDV'><b id='njCDV'><form id='njCDV'><ins id='njCDV'></ins><ul id='njCDV'></ul><sub id='njCDV'></sub></form><legend id='njCDV'></legend><bdo id='njCDV'><pre id='njCDV'><center id='njCDV'></center></pre></bdo></b><th id='njCDV'></th></span></q></dt></tr></i><div id='njCDV'><tfoot id='njCDV'></tfoot><dl id='njCDV'><fieldset id='njCDV'></fieldset></dl></div>
              <bdo id='njCDV'></bdo><ul id='njCDV'></ul>

                  <tbody id='njCDV'></tbody>
              • 本文介绍了ON DUPLICATE KEY UPDATE 的自动增量太多的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我有一个带列的基本表格:

                I have a basic table with columns:

                • id(主要与 AI)
                • 名称(唯一)

                如果唯一列不存在,则插入该行,否则更新该行....

                If the unique column doesn't exist, INSERT the row, otherwise UPDATE the row....

                INSERT INTO pages (name, etc)
                VALUES
                  'bob',
                  'randomness'
                ON DUPLICATE KEY UPDATE
                 name = VALUES(name),
                 etc = VALUES(etc)
                

                问题在于,如果它执行 UPDATE,则 id 列上的 auto_increment 值会上升.因此,如果执行了一大堆 UPDATES,则 id auto_increment 就会突破.

                The problem is that if it performs an UPDATE, the auto_increment value on the id column goes up. So if a whole bunch of UPDATES are performed, the id auto_increment goes through the roof.

                显然这是一个错误:http://bugs.mysql.com/bug.php?id=28781

                ...但我在共享主机上的 mySQL 5.5.8 上使用 InnoDB.

                ...but I'm using InnoDB on mySQL 5.5.8 on shared hosting.

                其他人在几年前遇到了没有解决方案的问题:防止 MYSQL 重复插入的自动增量和为什么插入失败时 MySQL 自动增量会增加?

                Other people having issues with no solution years ago: prevent autoincrement on MYSQL duplicate insert and Why does MySQL autoincrement increase on failed inserts?

                关于修复的想法?我是否以某种方式错误地构建了数据库?

                Ideas on a fix? Have I maybe structured the database incorrectly somehow?

                ******EDIT****:似乎在 my.ini 文件中添加 innodb_autoinc_lock_mode = 0 可以解决问题,但我有哪些共享托管选项?

                ******EDIT****: It appears adding innodb_autoinc_lock_mode = 0 to your my.ini file fixes the problem but what options do I have for shared hosting?

                ******EDIT 2******:好的,我认为我唯一的选择是更改为 MyISAM 作为存储引擎.作为一个大型 mySQL 新手,我希望这不会引起很多问题.是吗?

                ******EDIT 2******: OK, I think my only option is to change to MyISAM as the storage engine. Being a mega mySQL newbie, I hope that doesn't cause many issues. Yeah?

                推荐答案

                我认为没有办法绕过 INSERT ... ON DUPLICTE KEY UPDATE 的这种行为.

                I don't think there is a way to bypass this behaviour of INSERT ... ON DUPLICTE KEY UPDATE.

                然而,您可以将两个语句,一个 UPDATE 和一个 INSERT,放在一个 交易:

                You can however put two statements, one UPDATE and one INSERT, in one transaction:

                START TRANSACTION ;
                
                UPDATE pages
                SET etc = 'randomness'
                WHERE name = 'bob' ;
                
                INSERT INTO pages (name, etc)
                SELECT 
                      'bob' AS name
                    , 'randomness' AS etc 
                FROM dual 
                WHERE NOT EXISTS
                      ( SELECT *
                        FROM pages p
                        WHERE p.name = 'bob'
                      ) ;
                
                COMMIT ;
                

                这篇关于ON DUPLICATE KEY UPDATE 的自动增量太多的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                上一篇:在 MySQL 中定义具有自动增量的复合键 下一篇:SQL Server 两字段唯一复合键,第二字段自增

                相关文章

                • <bdo id='BUaoJ'></bdo><ul id='BUaoJ'></ul>

                  <i id='BUaoJ'><tr id='BUaoJ'><dt id='BUaoJ'><q id='BUaoJ'><span id='BUaoJ'><b id='BUaoJ'><form id='BUaoJ'><ins id='BUaoJ'></ins><ul id='BUaoJ'></ul><sub id='BUaoJ'></sub></form><legend id='BUaoJ'></legend><bdo id='BUaoJ'><pre id='BUaoJ'><center id='BUaoJ'></center></pre></bdo></b><th id='BUaoJ'></th></span></q></dt></tr></i><div id='BUaoJ'><tfoot id='BUaoJ'></tfoot><dl id='BUaoJ'><fieldset id='BUaoJ'></fieldset></dl></div>
                1. <small id='BUaoJ'></small><noframes id='BUaoJ'>

                2. <tfoot id='BUaoJ'></tfoot>
                  <legend id='BUaoJ'><style id='BUaoJ'><dir id='BUaoJ'><q id='BUaoJ'></q></dir></style></legend>