MySQL 几种DML方式:ignore、replace into、DUPLICATE KEY UPDATE、类似merge into
来源:互联网 发布:图像分类算法 简单 编辑:程序博客网 时间:2024/05/16 12:27
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.24-log |
+------------+
DROP TABLE t;
CREATE TABLE t(
id INT NOT NULL PRIMARY KEY,
id1 INT AUTO_INCREMENT,
NAME VARCHAR(10),
UNIQUE KEY(id1)
);
INSERT INTO t(id,NAME) VALUE(1,'gg1');
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
| 1 | 1 | gg1 |
+----+-----+------+
1 row in set (0.00 sec)
mysql> INSERT INTO t(id,NAME) VALUE(1,'gg1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> INSERT IGNORE INTO t(id,NAME) VALUE(1,'gg1');
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
| 1 | 1 | gg1 |
+----+-----+------+
1 row in set (0.00 sec)
mysql> REPLACE INTO t(id,NAME) VALUES(1,'gg1');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
| 1 | 4 | gg1 |
+----+-----+------+
1 row in set (0.00 sec)
VALUES(1,'gg10')
ON DUPLICATE KEY UPDATE NAME='gg10';
mysql> SELECT * FROM t;
+----+-----+------+
| id | id1 | NAME |
+----+-----+------+
| 1 | 4 | gg10 |
+----+-----+------+
mysql> UPDATE t AS a INNER JOIN
(SELECT 1 id,'gg2' NAME ) b
USING (id)
SET a.id = IF(a.id= b.id,b.id,a.id),
a.name = IF(a.id= b.id,b.name,a.name);
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
| 1 | 4 | gg2 |
+----+-----+------+
1 row in set (0.00 sec)
0 0
- MySQL 几种DML方式:ignore、replace into、DUPLICATE KEY UPDATE、类似merge into
- MySql避免"重复插入记录"的方法(INSERT ignore into,Replace into,ON DUPLICATE KEY UPDATE)
- INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE INTO 和 INSERT IGNORE INTO
- mysql中ON DUPLICATE KEY UPDATE和REPLACE INTO用法
- mysql中的replace into和 on duplicate key update
- REPLACE INTO 和 INSERT INTO ... ON DUPLICATE KEY UPDATE SET ...
- replace into 和 insert into *** on duplicate key update
- Mysql replace into 与 insert into on duplicate key update 的区别
- MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
- Mysql中Insert into xxx on duplicate key update和REPLACE INTO使用
- MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
- Mysql中Insert into xxx on duplicate key update和REPLACE INTO使用
- Mysql replace into 与 insert into on duplicate key update 死锁和性能测试
- MySQL的Replace into 与Insert into ..... on duplicate key update
- MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
- MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
- MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
- MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
- 变量、常量
- php调整图像大小
- 2.4_颜色与透明度
- Kong插件定制示例
- 微信小程序swiper滑动页面实践-类似于安卓ViewPager
- MySQL 几种DML方式:ignore、replace into、DUPLICATE KEY UPDATE、类似merge into
- shiro的加密 深入浅出
- 4月份月报总结
- spring security xml配置官方详解
- 移动端meta标签
- PhpStorm+Xdebug调试配置
- 二分图判定(染色法)
- spring—容器启动载入bean过程
- iOS 开发 Xcode 一个工程 Project 创建多个 target