|
|
|
|
移动端

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

本文最主分享一些如何避免MySQL替换逻辑SQL的坑爹操作,关于replace into和insert into on duplicate key 区别操作。

作者:贺磊来源:51CTO博客|2018-01-11 16:30

年前最后一场技术盛宴 | 1月27日与京东、日志易技术大咖畅聊智能化运维发展趋势!


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.  
  3.     id int(10) unsigned NOT NULL AUTO_INCREMENT, 
  4.  
  5.     name varchar(20) NOT NULL DEFAULT ''
  6.  
  7.     age tinyint(3) unsigned NOT NULL default 0, 
  8.  
  9.     PRIMARY KEY(id), 
  10.  
  11.     UNIQUE KEY uk_name (name
  12.  
  13.     ) 
  14.  
  15.     ENGINE=innodb AUTO_INCREMENT=1 
  16.  
  17.     DEFAULT CHARSET=utf8; 
  18.  
  19.     </br> 

表数据 

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

replace into用法 

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

replace的用法

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

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

Insert into …on duplicate key: 

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

总结

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

【编辑推荐】

  1. 如何10分钟搭建MySQL Binlog分析+可视化方案
  2. DB-Engines 2017 年度数据库:PostgreSQL 实至名归
  3. 大白话理解“最晦涩”的Paxos算法及在数据库高可用上的使用
  4. 再见乱码:5分钟读懂MySQL字符集设置
  5. SQL自动审核-自助上线平台
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

软件工程:实践者的研究方法

20多年以来,《软件工程:实践者的研究方法》一书是最受学生和行业专业人员欢迎的软件工程指南。它在全面而系统、概括而清晰地介绍软件工程...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊