mysql支持update ignore语法,:
drop TABLE if EXISTS students;
CREATE TABLE `students` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO students (id, name, age) VALUES (1, 'Alice', 18);
INSERT INTO students (id, name, age) VALUES (2, 'Bob', 20);
INSERT INTO students (id, name, age) VALUES (3, 'TOM', 19);
mysql> SELECT * from students;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | Alice | 18 |
| 2 | Bob | 20 |
| 3 | TOM | 19 |
+----+-------+------+
更新年龄
mysql> UPDATE IGNORE students
-> SET age = 18
-> WHERE name = 'TOM';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> SELECT * from students;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | Alice | 18 |
| 2 | Bob | 20 |
| 3 | TOM | 19 |
+----+-------+------+
想要将TOM的年龄改成18,碰到age字段的unique约束冲突,mysql会自动忽略该记录,不修改,并且也不报错。
达梦是否支持该语法,或者有办法改造么?
更新的现在貌似没有,insert倒是有个hint可以支持
INSERT/+ IGNORE_ROW_ON_DUPKEY_INDEX(students)/ INTO students (id, name, age) VALUES (4, 'TOM', 19);
dm使用merge into
merge into的用法参加如下:
https://blog.csdn.net/spw55381155/article/details/79891305