本文的unique列上插入重复值解决方案，主要基于MySQL平台。通过这些，可以做到一些新的功能和应用。希望本文能对大家有所帮助。 当unique列在一个UNIQUE键上插入包含重复值的记录时，我们可以控制MySQL如何处理这种情况：使用IGNORE关键字或者ON DUPLICATE K
本文的unique列上插入重复值解决方案，主要基于mysql平台。通过这些，可以做到一些新的功能和应用。希望本文能对大家有所帮助。
当unique列在一个UNIQUE键上插入包含重复值的记录时，我们可以控制MySQL如何处理这种情况：使用IGNORE关键字或者ON DUPLICATE KEY UPDATE子句跳过INSERT、中断操作或者更新旧记录为新值。
<OL class=dp-sql><LI class=alt><SPAN><SPAN>mysql> </SPAN><SPAN class=keyword>create</SPAN><SPAN> </SPAN><SPAN class=keyword>table</SPAN><SPAN> menus(id tinyint(4) </SPAN><SPAN class=op>not</SPAN><SPAN> </SPAN><SPAN class=op>null</SPAN><SPAN> auto_increment, </SPAN></SPAN><LI class=""><SPAN> -> label </SPAN><SPAN class=keyword>varchar</SPAN><SPAN>(10) </SPAN><SPAN class=op>null</SPAN><SPAN>,url </SPAN><SPAN class=keyword>varchar</SPAN><SPAN>(20) </SPAN><SPAN class=op>null</SPAN><SPAN>,</SPAN><SPAN class=keyword>unique</SPAN><SPAN> </SPAN><SPAN class=keyword>key</SPAN><SPAN>(id)); </SPAN></SPAN><LI class=alt><SPAN>Query OK, 0 </SPAN><SPAN class=keyword>rows</SPAN><SPAN> affected (0.13 sec) </SPAN></SPAN><LI class=""><SPAN>mysql> </SPAN><SPAN class=keyword>insert</SPAN><SPAN> </SPAN><SPAN class=keyword>into</SPAN><SPAN> menus(label,url) </SPAN><SPAN class=keyword>values</SPAN><SPAN>(</SPAN><SPAN class=string>'Home'</SPAN><SPAN>,</SPAN><SPAN class=string>'home.html'</SPAN><SPAN>); </SPAN></SPAN><LI class=alt><SPAN>Query OK, 1 row affected (0.06 sec) </SPAN><LI class=""><SPAN>mysql> </SPAN><SPAN class=keyword>insert</SPAN><SPAN> </SPAN><SPAN class=keyword>into</SPAN><SPAN> menus(label,url) </SPAN><SPAN class=keyword>values</SPAN><SPAN>(</SPAN><SPAN class=string>'About us'</SPAN><SPAN>,</SPAN><SPAN class=string>'aboutus.html'</SPAN><SPAN>); </SPAN></SPAN><LI class=alt><SPAN>Query OK, 1 row affected (0.05 sec) </SPAN><LI class=""><SPAN>mysql> </SPAN><SPAN class=keyword>insert</SPAN><SPAN> </SPAN><SPAN class=keyword>into</SPAN><SPAN> menus(label,url) </SPAN><SPAN class=keyword>values</SPAN><SPAN>(</SPAN><SPAN class=string>'Services'</SPAN><SPAN>,</SPAN><SPAN class=string>'services.html'</SPAN><SPAN>); </SPAN></SPAN><LI class=alt><SPAN>Query OK, 1 row affected (0.05 sec) </SPAN><LI class=""><SPAN>mysql> </SPAN><SPAN class=keyword>insert</SPAN><SPAN> </SPAN><SPAN class=keyword>into</SPAN><SPAN> menus(label,url) </SPAN><SPAN class=keyword>values</SPAN><SPAN>(</SPAN><SPAN class=string>'Feedback'</SPAN><SPAN>,</SPAN><SPAN class=string>'feedback.html'</SPAN><SPAN>); </SPAN></SPAN><LI class=alt><SPAN>Query OK, 1 row affected (0.05 sec) </SPAN></LI></OL>
<OL class=dp-sql><LI class=alt><SPAN><SPAN>mysql> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>select</FONT></STRONG></SPAN><SPAN> * </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>from</FONT></STRONG></SPAN><SPAN> menus; </SPAN></SPAN><LI class=""><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+----------+---------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN>| id | label | url | </SPAN><LI class=""><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+----------+---------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN>| 1 | Home | home.html | </SPAN><LI class=""><SPAN>| 2 | About us | aboutus.html | </SPAN><LI class=alt><SPAN>| 3 | Services | services.html | </SPAN><LI class=""><SPAN>| 4 | Feedback | feedback.html | </SPAN><LI class=alt><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+----------+---------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=""><SPAN>4 </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>rows</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=op><FONT color=#808080>in</FONT></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>set</FONT></STRONG></SPAN><SPAN> (0.00 sec) </SPAN></SPAN></LI></OL>
如果现在在unique列插入一条违背唯一约束的记录，MySQL会中断操作，提示出错：
<OL class=dp-sql><LI class=alt><SPAN><SPAN>mysql> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>insert</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>into</FONT></STRONG></SPAN><SPAN> menus(id,label,url) </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>values</FONT></STRONG></SPAN><SPAN>(4,</SPAN><SPAN class=string><FONT color=#0000ff>'Contact us'</FONT></SPAN><SPAN>,</SPAN><SPAN class=string><FONT color=#0000ff>'contactus.html'</FONT></SPAN><SPAN>); </SPAN></SPAN><LI class=""><SPAN>ERROR 1062 (23000): Duplicate entry </SPAN><SPAN class=string><FONT color=#0000ff>'4'</FONT></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>for</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>key</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=string><FONT color=#0000ff>'id'</FONT></SPAN><SPAN> </SPAN></SPAN></LI></OL>
在前面的INSERT语句添加IGNORE关键字时，如果认为语句违背了唯一约束，MySQL甚至不会尝试去执行这条语句，因此，下面的语句不会返回错误：
<OL class=dp-sql><LI class=alt><SPAN><SPAN>mysql> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>insert</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>ignore</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>into</FONT></STRONG></SPAN><SPAN> menus(id,label,url) </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>values</FONT></STRONG></SPAN><SPAN>(4,</SPAN><SPAN class=string><FONT color=#0000ff>'Contact us'</FONT></SPAN><SPAN>,</SPAN><SPAN class=string><FONT color=#0000ff>'contactus.html'</FONT></SPAN><SPAN>); </SPAN></SPAN><LI class=""><SPAN>Query OK, 0 </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>rows</FONT></STRONG></SPAN><SPAN> affected (0.00 sec) </SPAN></SPAN><LI class=alt><SPAN>mysql> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>select</FONT></STRONG></SPAN><SPAN> * </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>from</FONT></STRONG></SPAN><SPAN> menus; </SPAN></SPAN><LI class=""><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+----------+---------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN>| id | label | url | </SPAN><LI class=""><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+----------+---------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN>| 1 | Home | home.html | </SPAN><LI class=""><SPAN>| 2 | About us | aboutus.html | </SPAN><LI class=alt><SPAN>| 3 | Services | services.html | </SPAN><LI class=""><SPAN>| 4 | Feedback | feedback.html | </SPAN><LI class=alt><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+----------+---------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=""><SPAN>4 </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>rows</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=op><FONT color=#808080>in</FONT></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>set</FONT></STRONG></SPAN><SPAN> (0.00 sec) </SPAN></SPAN></LI></OL>
当有很多的INSERT语句需要被顺序地执行时，IGNORE关键字就使操作变得很方便。使用它可以保证不管哪一个INSERT包含了重复的键值，MySQL都回跳过它（而不是放弃全部操作）。
在这种情况下，我们还可以通过添加MySQL4.1新增加的ON DUPLICATE KEY UPDATE子句，使MySQL自动把INSERT操作转换为UPDATE操作。这个子句必须具有需要更新的字段列表，这个列表和UPDATE语句使用的列表相同。
<OL class=dp-sql><LI class=alt><SPAN><SPAN>mysql> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>insert</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>into</FONT></STRONG></SPAN><SPAN> menus(id,label,url) </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>values</FONT></STRONG></SPAN><SPAN>(4,</SPAN><SPAN class=string><FONT color=#0000ff>'Contact us'</FONT></SPAN><SPAN>,</SPAN><SPAN class=string><FONT color=#0000ff>'contactus.html'</FONT></SPAN><SPAN>) </SPAN></SPAN><LI class=""><SPAN> -> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>on</FONT></STRONG></SPAN><SPAN> duplicate </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>key</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>update</FONT></STRONG></SPAN><SPAN> label=</SPAN><SPAN class=string><FONT color=#0000ff>'Contact us'</FONT></SPAN><SPAN>,url=</SPAN><SPAN class=string><FONT color=#0000ff>'contactus.html'</FONT></SPAN><SPAN>; </SPAN></SPAN><LI class=alt><SPAN>Query OK, 2 </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>rows</FONT></STRONG></SPAN><SPAN> affected (0.05 sec) </SPAN></SPAN></LI></OL>
在这种情况下，如果MySQL发现表已经包含具有相同唯一键的记录，它会自动更新旧的记录为ON DUPLICATE KEY UPDATE从句中指定的新值：
<OL class=dp-sql><LI class=alt><SPAN><SPAN>mysql> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>select</FONT></STRONG></SPAN><SPAN> * </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>from</FONT></STRONG></SPAN><SPAN> menus; </SPAN></SPAN><LI class=""><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+------------+----------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN>| id | label | url | </SPAN><LI class=""><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+------------+----------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=alt><SPAN>| 1 | Home | home.html | </SPAN><LI class=""><SPAN>| 2 | About us | aboutus.html | </SPAN><LI class=alt><SPAN>| 3 | Services | services.html | </SPAN><LI class=""><SPAN>| 4 | Contact us | contactus.html | </SPAN><LI class=alt><SPAN>+</SPAN><SPAN class=comment><FONT color=#008200>----+------------+----------------+ </FONT></SPAN><SPAN> </SPAN></SPAN><LI class=""><SPAN>4 </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>rows</FONT></STRONG></SPAN><SPAN> </SPAN><SPAN class=op><FONT color=#808080>in</FONT></SPAN><SPAN> </SPAN><SPAN class=keyword><STRONG><FONT color=#006699>set</FONT></STRONG></SPAN><SPAN> (0.01 sec) </SPAN></SPAN></LI></OL>
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
广告
收藏
收藏
收藏
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
724
