mysql replace into与on duplicate key update区别

逃离我推掉我的手 2024-03-22 09:30 23阅读 0赞

### 1、replace into ###

REPLACE INTO 首先判断数据是否存在；如果不存在，则插入；如果已存在则更新（先删除再插入

*  根据主键或唯一索引判断记录是否已存在，所以插入数据的表必须要有主键或者唯一索引！否则的话，REPLACE INTO 会直接插入数据（相当于INSERT），会导致表中出现重复数据。
 *  如果不写某个字段的值则会使用默认值，如果该字段没有定义默认值则报错。
 *  要使用REPLACE INTO，必须同时拥有表的INSERT和 DELETE权限。

MySQL replace into 有三种形式
    1. replace into tbl_name(col_name, ...) values(...)
    2. replace into tbl_name(col_name, ...) select ...
    3. replace into tbl_name set col_name=value, ...

#### 1.1、主键自增 ####

alter table t_user change id id int(11) not null AUTO_INCREMENT; 
    
    CREATE TABLE `t_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(40) NOT NULL,
      `password` varchar(40) NOT NULL,
      `sex` varchar(4) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `save_unique_index` (`username`,`password`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

#### 1.2、replace into tbl\_name(col\_name, ...) values(...) ####

1. replace into tbl_name(col_name, ...) values(...)
    -- 修改前的数据
    1  Tom  123  男
    2  Tom  1234  男
    3  Jerry  234  女
    4  Tom  12345  男
     
    replace into t_user(username,password) values('Tom','123');
    -- 报错，Field 'sex' doesn't have a default value
     
    replace into t_user(username,password,sex) values('Tom','123','男');
    -- 受影响的行: 2 ，先删除，再插入
    -- 修改后的数据
    2  Tom  1234  男
    3  Jerry  234  女
    4  Tom  12345  男
    5  Tom  123  男
     
    replace into t_user(username,password,sex) values('Tom','123','女');  
    -- 受影响的行: 2
    -- 修改后的数据
    2  Tom  1234  男
    3  Jerry  234  女
    4  Tom  12345  男
    6  Tom  123  女
     
    replace into t_user(username,password,sex) values('Tom','123456','男'); 
    -- 受影响的行: 1
    -- 修改后的数据
    2  Tom  1234  男
    3  Jerry  234  女
    4  Tom  12345  男
    6  Tom  123  女
    7  Tom  123456  男

#### 1.3、replace into tbl\_name(col\_name, ...) select ... ####

CREATE TABLE `t_user2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(40) NOT NULL,
      `pwd` varchar(40) NOT NULL,
      `sex` varchar(4) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
     
    t_user:
    2  Tom  1234  男
    3  Jerry  234  女
    4  Tom  12345  男
    6  Tom  123  女
    7  Tom  123456  男
     
    t_user2:
    1  Tom  123  男
    2  Tonny  123  女
    3  Jerry  234  女
    replace into t_user(username,password,sex) select  name,pwd,sex from t_user2 ; 
    -- 受影响的行: 5
    -- 修改后，(t_user)6删除，(t_user)8添加，(t_user)9添加，(t_user)3删除，(t_user)10添加 
    -- （唯一索引，所以‘Tom  123  女’会删除，再插入‘Tom  123  男’）
    t_user:
    2  Tom  1234  男
    4  Tom  12345  男
    7  Tom  123456  男
    8  Tom  123  男
    9  Tonny  123  女
    10  Jerry  234  女

#### 1.4、replace into tbl\_name set col\_name=value, ... ####

replace into  t_user set username = 'Tommy' ; 
    -- Field 'password' doesn't have a default value
    
    replace into  t_user set username = 'Tommy'  ,password = '1234567';  
    -- Field 'sex' doesn't have a default value
    
    replace into  t_user set username = 'Tommy'  ,password = '1234567',sex = '男';  
    -- 受影响的行: 1
    -- 修改后
    2  Tom  1234  男
    4  Tom  12345  男
    7  Tom  123456  男
    8  Tom  123  男
    9  Tonny  123  女
    10  Jerry  234  女
    11  Tommy  1234567  男  -- 这一行是新加的
     
    replace into  t_user set username = 'Tommy'  ,password = '1234567',sex = '女';  
    -- 受影响的行: 2
    -- 修改后
    2  Tom  1234  男
    4  Tom  12345  男
    7  Tom  123456  男
    8  Tom  123  男
    9  Tonny  123  女
    10  Jerry  234  女
    12  Tommy  1234567  女 
    -- 把id=11的记录delete后，再insert id=12的记录

### 2、on duplicate key update ###

on duplicate key update 首先判断数据是否存在；如果不存在，则插入；如果已存在则更新

*  on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。
 *  记录已存在时，只会更新on duplicate key update之后指定的字段。
 *  如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。

#### 2.1、创建表 ####

alter table test change id id int(11) not null AUTO_INCREMENT; 
    
    CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `organ_no` varchar(40) NOT NULL,
      `branch_no` varchar(40) NOT NULL,
      `branch_name` varchar(40) NOT NULL,
      `created_by` varchar(40) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `branch_no_index` (`branch_no`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

主键索引:id

唯一索引: branch\_no

1、organ\_no, branch\_no, branch\_name, created\_by字段都不相同

2、branch\_name, created\_by不同

3、[唯一索引][Link 1]branch\_no不同

4、修改的值包括了唯一索引值branch\_no

#### 2.2、执行sql ：organ\_no, branch\_no, branch\_name, created\_by字段都不相同 ####

![5a89cd88889c4258afddc150b3474ac3.png][]

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
    VALUES
    	( 1, '3', '浙江分行', '3' ) 
    	ON DUPLICATE KEY UPDATE
    	branch_name = '江苏分行';

![1bfadff565bc47559584a6bb0f08a863.png][]

因为没有值相同，所以一定添加

#### 2.3、branch\_name, created\_by与表中数据不同 ####

![1bfadff565bc47559584a6bb0f08a863.png][]

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
    VALUES
    	( 1, '3', '海南分行', '4' ) 
    	ON DUPLICATE KEY UPDATE
    	branch_name = '江苏分行';

![6a3d4857282c4f39a08ac2c823caf6b3.png][]

第三条数据从浙江分行改变成了江苏分行

ON DUPLICATE KEY UPDATE 后的值是要修改的值

而这个会根据唯一索引进行查询，其他普通列不做匹配（主键也是做匹配的）

#### 2.4、唯一索引branch\_no不同 ####

![6a3d4857282c4f39a08ac2c823caf6b3.png][]

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
    VALUES
    	( 1, '4', '江苏分行', '3' ) 
    	ON DUPLICATE KEY UPDATE
    	branch_name = '海南分行';

除了唯一索引branch\_no不同，其他列都相同

![3323e68a52eb4856a09e653dae00da67.png][]

执行后数据库增加一条

#### 2.5、修改的值包括了唯一索引值branch\_no ####

![3323e68a52eb4856a09e653dae00da67.png][]

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
    VALUES
    	( 1, '4', '江苏分行', '3' ) 
    	ON DUPLICATE KEY UPDATE
    	branch_name = '海南分行',
    	branch_no = '8';

修改branch\_no 4为8，并修改branch\_name为海南分行

![ef5b969aa11f41ac92e2c0e1742d3394.png][]

[Link 1]: https://so.csdn.net/so/search?q=%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95&spm=1001.2101.3001.7020
[5a89cd88889c4258afddc150b3474ac3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/1ffc63b7bcf64387933b237b7b6b0f87.png
[1bfadff565bc47559584a6bb0f08a863.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/75491ad38cb44a7393b6e2d163f7a952.png
[6a3d4857282c4f39a08ac2c823caf6b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2a679299c42a4116aaa5636b8bf34c5b.png
[3323e68a52eb4856a09e653dae00da67.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/23f3fadcb084486391f2a8eb70dcc59e.png
[ef5b969aa11f41ac92e2c0e1742d3394.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/3a669c5f14e04bf09d232e2dee2e0ae5.png