MySQL REPLACE INTO三种形式

￡神魔★判官ぃ 2021-11-10 14:26 249阅读 0赞

我们在向表中插入数据的时候，经常遇到这样的情况：1. 首先判断数据是否存在； 2. 如果不存在，则插入；3.如果存在，则更新。包括我在内的程序猿们常见的做法有三种：

第一种：MySQL很常见的一种做法，许多新手、甚至许多资深的高级coder也有这么写的，会在代码中封装三个函数，一个函数查询记录是否存在，一个函数实现直接插入，另一个函数对已有记录进行更新。在不同的情况进行调用。这种方法多次excute执行数据操作，势必造成比较大的开销。

第二种：用一条SQL代替三种情况的封装，来实现按需操作，或插入新记录，或更新旧数据。SQL Server中的语句如下：

IF NOT EXISTS(select 1 from NEWS\_bak where news\_id = 1008)

insert into NEWS\_bak(title, keyword, description) values('孙权', '三国','孙权-吴国')

else

update NEWS\_bak set title = '孙权'and keyword='三国' and description='孙权-吴国老大' where news\_id = 1008

说明：对于IF NOT EXISTS的相同表达，MySQL一般用作条件WHERE NOT EXISTS();由于exists(SELECT NULL )也会返回true，故select exists(SELECT NULL )的结果为1。

但是在MySQL 中如何实现此逻辑呢？方法有，且语法更简洁——replace into 。

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, ...

前两种形式使用频度比较高。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值，这和INSERT一样。您不能从当前行中引用值，也不能在新行中使用值。如果您使用一个例如“SET col\_name = col\_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col\_name)处理。因此，该赋值相当于SET col\_name = DEFAULT(col\_name) + 1。

举例：

replace into tableName (id,index) values('1','index-A'),('2','index-B')

此语句用于向表tableName中插入两条记录。 replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据; 否则，直接插入新的数据。

为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。

REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

PHP可以使用mysql\_affected\_rows()函数获得受影响的行数。受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：只需检查该数是否为1（添加）或更大（替换）。

必须注意：插入数据的表必须有主键PRIMARY KEY或者是唯一索引UNIQUE！否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。这时，replace into 会直接插入数据，这将导致表中出现重复的数据。

![20190803113553374.jpg][]

补充：

1.  INSERT INTO TABLE (COL1,COL2,COL3) VALUES ("123","456","789")  ON DUPLICATE KEY UPDATE COL2="654",COL3="987"

ON DUPLICATE KEY UPDATE：如果插入时数据不存在（通过主键或者索引确定）则新建一条记录，否则更新UPDATE 后的数据。

可以在UPDATE子句中使用VALUES(col\_name)函数从INSERT...UPDATE语句的INSERT部分引用列值，例如：

1.  INSERT INTO TABLE (COL1,COL2,COL3) VALUES("123","456","789") ,("987","654","321")ON DUPLICATE KEY UPDATE COL3=VALUES(COL2)

等价于以下两个语句：

1.  INSERT INTO TABLE(COL1,COL2,COL3)VALUES("123","456","789") ON DUPLICATE KEY UPDATE COL3="456"
2.  INSERT INTO TABLE (COL1,COL2,COL3)VALUES("987","654","321")ON DUPLICATE KEY UPDATE COL3="654"

[20190803113553374.jpg]: /images/20211109/db65674a202e44d2880706384c26ee6e.png