mysql:insert ignore、insert和replace区别

指令已存在不存在举例
insert报错插入insert into names(name, age) values(“小明”, 23);
insert ignore忽略插入insert ignore into names(name, age) values(“小明”, 24);
replace替换插入replace into names(name, age) values(“小明”, 25);

表要求:有PrimaryKey,或者unique索引
结果:表id都会自增

测试代码

创建表

CREATE TABLE names(
    id INT(10) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) UNIQUE,
    age INT(10)
)

插入数据

mysql> insert into names(name, age) values("小明", 24);
mysql> insert into names(name, age) values("大红", 24);
mysql> insert into names(name, age) values("大壮", 24);
mysql> insert into names(name, age) values("秀英", 24);

mysql> select * from names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小明   |   24 |
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
+----+--------+------+

insert

插入已存在, id会自增,但是插入不成功,会报错

mysql> insert into names(name, age) values("小明", 23);

ERROR 1062 (23000): Duplicate entry '小明' for key 'name'

replace

已存在替换,删除原来的记录,添加新的记录

mysql> replace into names(name, age) values("小明", 23);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
+----+--------+------+

不存在替换,添加新的记录

mysql> replace into names(name, age) values("大名", 23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
|  7 | 大名   |   23 |
+----+--------+------+

insert ignore

插入已存在,忽略新插入的记录,id会自增,不会报错

mysql> insert ignore into names(name, age) values("大壮", 25);
Query OK, 0 rows affected, 1 warning (0.00 sec)

插入不存在,添加新的记录

mysql> insert ignore into names(name, age) values("壮壮", 25);
Query OK, 1 row affected (0.01 sec)

mysql> select * from  names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
|  7 | 大名   |   23 |
| 10 | 壮壮   |   25 |
+----+--------+------+

参考:
INSERT IGNORE 与INSERT INTO的区别

已标记关键词 清除标记
<div class="post-text" itemprop="text"> <p>I need to insert a lot of values into a MySQL table. Some of them might cause an error, but I still want the valid ones to be inserted. Consequently, I'm using an <code>INSERT IGNORE</code> query.</p> <pre><code>query := "INSERT IGNORE INTO mytable " query += "(uniquekey, someotherfield) " query += "VALUES " var params []interface{} for _, element := range elements { query += "(?, ?)," params = append(params, element.UniqueKey, element.SomeOtherField) } _, err := db.Exec(query[:len(query)-1] + ";", params...) </code></pre> <p>If I run this query in my terminal, I would get the rejected rows as warnings:</p> <pre><code> Warning | 1062 | Duplicate entry '4' for key 'uk-uniquekey' </code></pre> <p>But how could I retrieve them using Go?</p> <p>Using the returned <a href="http://golang.org/pkg/database/sql/#Result" rel="nofollow"><code>Result</code></a> object, I can get the number of affected rows (and thus find the number of rejected ones), but I need a way to clearly identify these rows.</p> <p>Also, I have a lot of rows to insert, and I don't want to use an <code>INSERT</code> query for each of them.</p> <p>Are there any good solution for this problem?</p> <hr> <h1>Update</h1> <p>I thought of using a single prepared query like this:</p> <pre><code>stmt, _ := db.Prepare("INSERT INTO mytable (uniquekey, someotherfield) VALUES (?, ?);") defer stmt.Close() for _, element := range elements { stmt.Exec(element.UniqueKey, element.SomeOtherField) } </code></pre> <p>And I benchmarcked this solution, in comparison with the <em>extended insert</em> query. For 1000 entries (I admit my machine is not very competitive...), here are my results:</p> <pre><code>Loop on prepared single insert: 10.652721825 s Single extended insert: 0.092304425 s </code></pre> <p>Considering I have thousands of elements to insert everyday, I can't use this solution either.</p> </div>
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页