避免MySQL替换逻辑SQL的坑爹操作

 

专注于为中小企业提供成都网站设计、成都做网站服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业苍梧免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上千家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

replace into和insert into on duplicate key 区别

replace的用法

  • 当不冲突时相当于insert，其余列默认值
  • 当key冲突时，自增列更新，replace冲突列，其余列默认值
  • Com_replace会加1
  • Innodb_rows_updated会加1

Insert into …on duplicate key的用法

  • 不冲突时相当于insert，其余列默认值
  • 当与key冲突时，只update相应字段值。
  • Com_insert会加1
  • Innodb_rows_inserted会增加1

实验展示

表结构   

 
 
 
 
    
  
  
  
  
  1. create table helei1( 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.     id int(10) unsigned NOT NULL AUTO_INCREMENT, 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.     name varchar(20) NOT NULL DEFAULT '', 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.     age tinyint(3) unsigned NOT NULL default 0, 
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.     PRIMARY KEY(id), 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.     UNIQUE KEY uk_name (name) 
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13.     ) 
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15.     ENGINE=innodb AUTO_INCREMENT=1 
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17.     DEFAULT CHARSET=utf8; 
    18. 
  
  
  
  
  18.  
    19. 
  
  
  
  
  19.     
     
    20.

表数据   

 
 
 
 
    
  
  
  
  
  1. root@127.0.0.1 (helei)> select * from helei1; 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.     +----+-----------+-----+ 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.     | id | name | age | 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.     +----+-----------+-----+ 
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.     | 1 | 贺磊 | 26 | 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.     | 2 | 小明 | 28 | 
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13.     | 3 | 小红 | 26 | 
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15.     +----+-----------+-----+ 
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17.     3 rows in set (0.00 sec) 
    18.

replace into用法   

 
 
 
 
    
  
  
  
  
  1. root@127.0.0.1 (helei)> replace into helei1 (name) values('贺磊'); 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.     Query OK, 2 rows affected (0.00 sec) 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.     root@127.0.0.1 (helei)> select * from helei1; 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.     +----+-----------+-----+ 
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.     | id | name | age | 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.     +----+-----------+-----+ 
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13.     | 2 | 小明 | 28 | 
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15.     | 3 | 小红 | 26 | 
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17.     | 4 | 贺磊 | 0 | 
    18. 
  
  
  
  
  18.  
    19. 
  
  
  
  
  19.     +----+-----------+-----+ 
    20. 
  
  
  
  
  20.  
    21. 
  
  
  
  
  21.     3 rows in set (0.00 sec) 
    22. 
  
  
  
  
  22.  
    23. 
  
  
  
  
  23.     root@127.0.0.1 (helei)> replace into helei1 (name) values('爱璇'); 
    24. 
  
  
  
  
  24.  
    25. 
  
  
  
  
  25.     Query OK, 1 row affected (0.00 sec) 
    26. 
  
  
  
  
  26.  
    27. 
  
  
  
  
  27.  
    28. 
  
  
  
  
  28.  
    29. 
  
  
  
  
  29.     root@127.0.0.1 (helei)> select * from helei1; 
    30. 
  
  
  
  
  30.  
    31. 
  
  
  
  
  31.     +----+-----------+-----+ 
    32. 
  
  
  
  
  32.  
    33. 
  
  
  
  
  33.     | id | name | age | 
    34. 
  
  
  
  
  34.  
    35. 
  
  
  
  
  35.     +----+-----------+-----+ 
    36. 
  
  
  
  
  36.  
    37. 
  
  
  
  
  37.     | 2 | 小明 | 28 | 
    38. 
  
  
  
  
  38.  
    39. 
  
  
  
  
  39.     | 3 | 小红 | 26 | 
    40. 
  
  
  
  
  40.  
    41. 
  
  
  
  
  41.     | 4 | 贺磊 | 0 | 
    42. 
  
  
  
  
  42.  
    43. 
  
  
  
  
  43.     | 5 | 爱璇 | 0 | 
    44. 
  
  
  
  
  44.  
    45. 
  
  
  
  
  45.     +----+-----------+-----+ 
    46. 
  
  
  
  
  46.  
    47. 
  
  
  
  
  47.     4 rows in set (0.00 sec) 
    48.

replace的用法

当没有key冲突时，replace into 相当于insert，其余列默认值

当key冲突时，自增列更新，replace冲突列，其余列默认值

Insert into …on duplicate key:   

 
 
 
 
    
  
  
  
  
  1. root@127.0.0.1 (helei)> select * from helei1; 
    2. 
  
  
  
  
  2.  
    3. 
  
  
  
  
  3.     +----+-----------+-----+ 
    4. 
  
  
  
  
  4.  
    5. 
  
  
  
  
  5.     | id | name | age | 
    6. 
  
  
  
  
  6.  
    7. 
  
  
  
  
  7.     +----+-----------+-----+ 
    8. 
  
  
  
  
  8.  
    9. 
  
  
  
  
  9.     | 2 | 小明 | 28 | 
    10. 
  
  
  
  
  10.  
    11. 
  
  
  
  
  11.     | 3 | 小红 | 26 | 
    12. 
  
  
  
  
  12.  
    13. 
  
  
  
  
  13.     | 4 | 贺磊 | 0 | 
    14. 
  
  
  
  
  14.  
    15. 
  
  
  
  
  15.     | 5 | 爱璇 | 0 | 
    16. 
  
  
  
  
  16.  
    17. 
  
  
  
  
  17.     +----+-----------+-----+ 
    18. 
  
  
  
  
  18.  
    19. 
  
  
  
  
  19.     4 rows in set (0.00 sec) 
    20. 
  
  
  
  
  20.  
    21. 
  
  
  
  
  21.  
    22. 
  
  
  
  
  22.  
    23. 
  
  
  
  
  23.     root@127.0.0.1 (helei)> insert into helei1 (name,age) values('贺磊',0) on duplicate key update age=100; 
    24. 
  
  
  
  
  24.  
    25. 
  
  
  
  
  25.     Query OK, 2 rows affected (0.00 sec) 
    26. 
  
  
  
  
  26.  
    27. 
  
  
  
  
  27.  
    28. 
  
  
  
  
  28.  
    29. 
  
  
  
  
  29.     root@127.0.0.1 (helei)> select * from helei1; 
    30. 
  
  
  
  
  30.  
    31. 
  
  
  
  
  31.     +----+-----------+-----+ 
    32. 
  
  
  
  
  32.  
    33. 
  
  
  
  
  33.     | id | name | age | 
    34. 
  
  
  
  
  34.  
    35. 
  
  
  
  
  35.     +----+-----------+-----+ 
    36. 
  
  
  
  
  36.  
    37. 
  
  
  
  
  37.     | 2 | 小明 | 28 | 
    38. 
  
  
  
  
  38.  
    39. 
  
  
  
  
  39.     | 3 | 小红 | 26 | 
    40. 
  
  
  
  
  40.  
    41. 
  
  
  
  
  41.     | 4 | 贺磊 | 100 | 
    42. 
  
  
  
  
  42.  
    43. 
  
  
  
  
  43.     | 5 | 爱璇 | 0 | 
    44. 
  
  
  
  
  44.  
    45. 
  
  
  
  
  45.     +----+-----------+-----+ 
    46. 
  
  
  
  
  46.  
    47. 
  
  
  
  
  47.     4 rows in set (0.00 sec) 
    48. 
  
  
  
  
  48.  
    49. 
  
  
  
  
  49.  
    50. 
  
  
  
  
  50.  
    51. 
  
  
  
  
  51.     root@127.0.0.1 (helei)> select * from helei1; 
    52. 
  
  
  
  
  52.  
    53. 
  
  
  
  
  53.     +----+-----------+-----+ 
    54. 
  
  
  
  
  54.  
    55. 
  
  
  
  
  55.     | id | name | age | 
    56. 
  
  
  
  
  56.  
    57. 
  
  
  
  
  57.     +----+-----------+-----+ 
    58. 
  
  
  
  
  58.  
    59. 
  
  
  
  
  59.     | 2 | 小明 | 28 | 
    60. 
  
  
  
  
  60.  
    61. 
  
  
  
  
  61.     | 3 | 小红 | 26 | 
    62. 
  
  
  
  
  62.  
    63. 
  
  
  
  
  63.     | 4 | 贺磊 | 100 | 
    64. 
  
  
  
  
  64.  
    65. 
  
  
  
  
  65.     | 5 | 爱璇 | 0 | 
    66. 
  
  
  
  
  66.  
    67. 
  
  
  
  
  67.     +----+-----------+-----+ 
    68. 
  
  
  
  
  68.  
    69. 
  
  
  
  
  69.     4 rows in set (0.00 sec) 
    70. 
  
  
  
  
  70.  
    71. 
  
  
  
  
  71.  
    72. 
  
  
  
  
  72.  
    73. 
  
  
  
  
  73.     root@127.0.0.1 (helei)> insert into helei1 (name) values('爱璇') on duplicate key update age=120; 
    74. 
  
  
  
  
  74.  
    75. 
  
  
  
  
  75.     Query OK, 2 rows affected (0.01 sec) 
    76. 
  
  
  
  
  76.  
    77. 
  
  
  
  
  77.  
    78. 
  
  
  
  
  78.  
    79. 
  
  
  
  
  79.     root@127.0.0.1 (helei)> select * from helei1; 
    80. 
  
  
  
  
  80.  
    81. 
  
  
  
  
  81.     +----+-----------+-----+ 
    82. 
  
  
  
  
  82.  
    83. 
  
  
  
  
  83.     | id | name | age | 
    84. 
  
  
  
  
  84.  
    85. 
  
  
  
  
  85.     +----+-----------+-----+ 
    86. 
  
  
  
  
  86.  
    87. 
  
  
  
  
  87.     | 2 | 小明 | 28 | 
    88. 
  
  
  
  
  88.  
    89. 
  
  
  
  
  89.     | 3 | 小红 | 26 | 
    90. 
  
  
  
  
  90.  
    91. 
  
  
  
  
  91.     | 4 | 贺磊 | 100 | 
    92. 
  
  
  
  
  92.  
    93. 
  
  
  
  
  93.     | 5 | 爱璇 | 120 | 
    94. 
  
  
  
  
  94.  
    95. 
  
  
  
  
  95.     +----+-----------+-----+ 
    96. 
  
  
  
  
  96.  
    97. 
  
  
  
  
  97.     4 rows in set (0.00 sec) 
    98. 
  
  
  
  
  98.  
    99. 
  
  
  
  
  99.  
    100. 
  
  
  
  
  100.  
    101. 
  
  
  
  
  101.     root@127.0.0.1 (helei)> insert into helei1 (name) values('不存在') on duplicate key update age=80; 
    102. 
  
  
  
  
  102.  
    103. 
  
  
  
  
  103.     Query OK, 1 row affected (0.00 sec) 
    104. 
  
  
  
  
  104.  
    105. 
  
  
  
  
  105.  
    106. 
  
  
  
  
  106.  
    107. 
  
  
  
  
  107.     root@127.0.0.1 (helei)> select * from helei1; 
    108. 
  
  
  
  
  108.  
    109. 
  
  
  
  
  109.     +----+-----------+-----+ 
    110. 
  
  
  
  
  110.  
    111. 
  
  
  
  
  111.     | id | name | age | 
    112. 
  
  
  
  
  112.  
    113. 
  
  
  
  
  113.     +----+-----------+-----+ 
    114. 
  
  
  
  
  114.  
    115. 
  
  
  
  
  115.     | 2 | 小明 | 28 | 
    116. 
  
  
  
  
  116.  
    117. 
  
  
  
  
  117.     | 3 | 小红 | 26 | 
    118. 
  
  
  
  
  118.  
    119. 
  
  
  
  
  119.     | 4 | 贺磊 | 100 | 
    120. 
  
  
  
  
  120.  
    121. 
  
  
  
  
  121.     | 5 | 爱璇 | 120 | 
    122. 
  
  
  
  
  122.  
    123. 
  
  
  
  
  123.     | 8 | 不存在 | 0 | 
    124. 
  
  
  
  
  124.  
    125. 
  
  
  
  
  125.     +----+-----------+-----+ 
    126. 
  
  
  
  
  126.  
    127. 
  
  
  
  
  127.     5 rows in set (0.00 sec) 
    128.

总结

replace into这种用法，相当于如果发现冲突键，先做一个delete操作，再做一个insert 操作，未指定的列使用默认值，这种情况会导致自增主键产生变化，如果表中存在外键或者业务逻辑上依赖主键，那么会出现异常。因此建议使用Insert into …on duplicate key。由于编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

文章标题：避免MySQL替换逻辑SQL的坑爹操作
文章链接：http://www.csdahua.cn/qtweb/news2/397202.html

网站建设、网络推广公司-快上网，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 快上网

成都快上网为您推荐相关内容

网站收录知识

各行业网站

平山县网站建设　　 　成都网站建设　　 　重庆小程序开发公司　　 　网站设计　　 　服务器托管　　 　成都广告设计公司　　 　成都二枢机房　　 　新华建筑装饰　　 　成都木托盘　　 　成都橡塑保温管　　 　成都网站建设　　 　节能环保网站建设方案　　 　成都公司注册　　 　网站推广优化　　 　成都全网营销　　 　网站维护报价　　 　重庆电商网站建设　　 　云主机　　 　重庆网络营销　　 　网站设计公司