Mysql: 数据存在更新,不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into

 

一般有三种方法:

我们用名为 books 的表做演示

mysql> SELECT * FROM books LIMIT 3;
+----+-------------------------+---------------------+----------------+
| id | title                   | author              | year_published |
+----+-------------------------+---------------------+----------------+
|  1 | In Search of Lost Time  | Marcel Proust       |           1913 |
|  2 | Ulysses                 | James Joyce         |           1922 |
|  3 | Don Quixote             | Miguel de Cervantes |           1605 |
+----+-------------------------+---------------------+----------------+
3 rows in set (0.00 sec)

 

1. INSERT IGNORE INTO

如果已经存在的数据,直接插入会出现错误,如:

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

用 INSERT IGNORE 话,插入不成也会忽略错误:

mysql> INSERT IGNORE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 0 rows affected (0.00 sec)

 

2.REPLACE INTO

Mysql: 存在更新,不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into
Mysql: 存在更新,不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into

这个有几点要说:

  1. 必须要有PrimaryKey,或者unique索引。
  2. 如果不存在数据(基于PrimaryKey或unique索引),就执行插入,跟 insert into一样。
  3. 如果存在数据(基于PrimaryKey或unique索引),它会先删除(跟delete 一样)已有的,然后重新插入(insert into 一样)。注意,如果你是基于unique索引,而非PrimaryKey,那么这个语句将会改变原来PrimaryKey值!
    基于PrimaryKey更新,PrimaryKey不会发生变化

    mysql> REPLACE INTO books
        (id, title, author, year_published)
    VALUES
        (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
    Query OK, 2 rows affected (0.00 sec)
    

    基于unique索引更新(本例为title),PrimaryKey会发生变化

    mysql> REPLACE INTO books
        (title, author, year_published)
    VALUES
        ( 'Green Eggs and Ham', 'Dr. Seuss', 1960);
    Query OK, 2 rows affected (0.00 sec)

    先删除,后添加,id值将发生变化,为自增到最大。

 

请注意,即使我们只更改了一行,结果也表明行受到影响,因为我们实际上DELETED是现有行,然后INSERTED是新行来替换它。

3.INSERT … ON DUPLICATE KEY UPDATE

这个有几点要说:

  1. 必须要有PrimaryKey,或者unique索引。
  2. 如果不存在数据(基于PrimaryKey或unique索引),就执行插入,跟 insert into一样。
  3. 如果存在数据(基于PrimaryKey或unique索引),它直接更新数据。注意,如果你是基于unique索引,而非PrimaryKey,那么这个语句将会改变原来最大PrimaryKey的值!

    mysql> SET @id = 1,
        @title = 'In Search of Lost Time',
        @author = 'Marcel Proust',
        @year_published = 1913;
    INSERT INTO books
        (id, title, author, year_published)
    VALUES
        (@id, @title, @author, @year_published)
    ON DUPLICATE KEY UPDATE
        title = @title,
        author = @author,
        year_published = @year_published;
    INSERT INTO books
        (id, title, author, year_published)
    VALUES
        (1, 'In Search of Lost Time', 'Marcel Proust', 1913)
    ON DUPLICATE KEY UPDATE
        title = VALUES(title),
        author = VALUES(author),
        year_published =VALUES(year_published),

    原有id值不发生变化,但是整个表的最大id值将发生变化,即自增一次。

    mysql> SELECT * FROM books LIMIT 1;
    +----+------------------------+---------------+----------------+
    | id | title                  | author        | year_published |
    +----+------------------------+---------------+----------------+
    |  1 | In Search of Lost Time | Marcel Proust |           1913 |
    +----+------------------------+---------------+----------------+
    1 row in set (0.00 sec)

     

INSERT ... ON DUPLICATE KEY UPDATE非破坏性的,因为它仅会发出INSERTUPDATE声明,但从来没有DELETE

 

总结:

insert into表示插入数据,数据库会检查主键,如果出现重复会报错;
replace into表示插入替换数据,需求表中有PrimaryKey,或者unique索引,如果数据库已经存在数据,则用新数据替换,方法是先删除后添加,如果没有数据效果则和insert into一样;
insert ignore表示,如果中已经存在相同的记录,则忽略当前新数据;
insert on duplicate key update表示,如果中已经存在相同的记录,则更新当前数据,否则将执行插入,跟replace into不一样的地方是,它不执行删除命令;

本文:Mysql: 存在更新,不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into

Leave a Reply