mysql四大数据库语句_四种插入数据的MySQL语句比较

整理如下:

INSERT INTO

表示插入数据，数据库会检查主键(Primary-Key)和唯一索引(Unique-Index)，如果出现重复会出现类似以下报错:

INSERT INTO table_name (column1, column2, column3) VALUES (1,2,3);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

INSERT IGNORE INTO

表示，如果表中已经存在相同的数据记录(如主键和唯一索引)，则忽略当前新数据，不进行插入或更新操作，也不报错，只插入不相同的新数据。

INSERT IGNORE INTO table_name (column1, column2, column3) VALUES (1,2,3);

Query OK, 0 rows affected (0.00 sec)

REPLACE INTO

表示插入或替换数据。

1,如果目标表中有Primary-Key，或者Unique索引的话，当出现相同值时，则用新数据替换旧数据。

注意这里的替换，它包含两个意思:

1.1, 原数据行将被物理删除，从而生成一条新行;

1.2, REPLACE语句中含有的字段，原数据行的该字段的值将被新数据值替换，而对于语句中未含有的表中的其他字段，它们的旧数据值被删除后，如果这些字段都有默认值，则会被更新为默认值，如果有任何一个字段没有默认值，则会报错。

从这两个角度来看，REPLACE INTO语句是存在风险的。

ERROR 1364 (HY000): Field 'column1' doesn't have a default value

2,如果没有则和INSERT INTO一样插入数据;

3, REPLACE INTO 语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。

REPLACE INTO table_name (column1, column2, column3) VALUES (1,2,3);

Query OK, 2 rows affected (0.00 sec)

对于一条只操作一行记录的REPLACE INTO语句来说，

如果它的受影响数为1，则表示新行被插入，同时没有旧行被删除，等同于INSERT INTO。

如果该数大于1，则在新行被插入前，有一个或多个旧记录行被删除。

如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

INSERT INTO ... ON DUPLICATE KEY UPDATE

表示如果和已存在的主键或唯一键(两者都有或者有多个的话会依次进行检查)，有任何一个相同，则按照UPDATE后的语句对原数据行进行更新。如果都不存在，则进行插入操作。

其实这个是原本需要执行3条SQL语句(SELECT,INSERT,UPDATE)，缩减为1条语句。

即

IF (SELECT * FROM where 存在) {

UPDATE SET WHERE ;

} else {

INSERT INTO;

}

如

INSERT INTO table_name(column1, column2, column3) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE column3 = 4;

Query OK, 2 rows affected (0.00 sec)

上面语句用伪代码表示即为

if (select * from table_name where column1=1) {

update table_name set column3 = 4 where column1=1

} else {

insert into table_name(column1,column2,column3) values (1,2,3)

}

和REPLACE INTO一样，这个语句返回的受影响行数也是被删除和被插入的行数的和，但是它的删除是对旧历史数据版本的删除。

例如:

//创建表

mysql> CREATE TABLE test(

-> id INT NOT NULL AUTO_INCREMENT,

-> col1 VARCHAR(32) NOT NULL,

-> col2 VARCHAR(32) DEFAULT NULL,

-> PRIMARY KEY(id),

-> UNIQUE KEY(col1)

-> );

Query OK, 0 rows affected (0.46 sec)

//插入数据

mysql> insert into test (id, col1, col2) VALUES (null,'unique','123456');

Query OK, 1 row affected (0.04 sec)

mysql> select * from test;

+----+--------+--------+

| id | col1 | col2 |

+----+--------+--------+

| 1 | unique | 123456 |

+----+--------+--------+

1 row in set (0.00 sec)

//执行操作

mysql> insert into test (id, col1, col2) values(null, 'unique', '654321') on duplicate key update col1 = 'update_unique';

Query OK, 2 rows affected (0.03 sec)

//结果

mysql> select * from test;

+----+---------------+--------+

| id | col1 | col2 |

+----+---------------+--------+

| 1 | update_unique | 123456 |

+----+---------------+--------+

1 row in set (0.00 sec)

可以看到，操作返回2条记录受影响．但是主键ID并未改变，只有唯一键受到影响．

如果更新多个字段可以像下面这样写

INSERT INTO table_name(column1, column2, column3) VALUES (1, 2, 3)

ON DUPLICATE KEY UPDATE column2=3, column3 = 4;

扩展阅读: UPDATE操作一定是先DELETE再INSERT吗？