MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。 临时表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。 MySQL临时表只在当前连接可见,如果你使用PHP脚本来创建MySQL临时表,那没当PHP脚本执行完成后,该临时表也会自动销毁。 如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁。…
June 21, 2018
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

这个有几点要说:
- 必须要有PrimaryKey,或者unique索引。
- 如果不存在数据(基于PrimaryKey或unique索引),就执行插入,跟 insert into一样。
- 如果存在数据(基于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
这个有几点要说:
- 必须要有PrimaryKey,或者unique索引。
- 如果不存在数据(基于PrimaryKey或unique索引),就执行插入,跟 insert into一样。
- 如果存在数据(基于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
是非破坏性的,因为它仅会发出INSERT
或UPDATE
声明,但从来没有DELETE
。
总结:
insert into表示插入数据,数据库会检查主键,如果出现重复会报错;
replace into表示插入替换数据,需求表中有PrimaryKey,或者unique索引,如果数据库已经存在数据,则用新数据替换,方法是先删除后添加,如果没有数据效果则和insert into一样;
insert ignore表示,如果中已经存在相同的记录,则忽略当前新数据;
insert on duplicate key update表示,如果中已经存在相同的记录,则更新当前数据,否则将执行插入,跟replace into不一样的地方是,它不执行删除命令;