更新时间：2024-11-15 GMT+08:00
REPLACE
MySQL的REPLACE操作形式包括：LOW_PRIORITY、PARTITION 、DELAYED 、VALUES、SET；（下述迁移示例为临时迁移方案）
REPLACE的工作方式与INSERT完全相同，不同之处在于，如果表中的旧行与主键或唯一索引的新行具有相同的值，则在插入新行之前删除该旧行。
LOW_PRIORITY
MySQL REPLACE支持使用LOW_PRIORITY ，DSC工具将对其进行转换。
输入
1 2 3 4 5 6 |
# LOW_PRIORITY 低优先级 Replace LOW_PRIORITY INTO exmp_tb2 VALUES(1, '128.23', 'nice', '2018-10-11 19:00:00'); Replace LOW_PRIORITY INTO exmp_tb2 VALUES(2, DEFAULT, 'nice', '2018-12-14 19:00:00' ); Replace LOW_PRIORITY INTO exmp_tb2 VALUES(3, DEFAULT, 'nice', DEFAULT); Replace LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price) VALUES(5, DEFAULT); Replace LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(4, DEFAULT, DEFAULT); |
输出
1 2 3 4 5 6 |
-- LOW_PRIORITY 低优先级 INSERT INTO "public"."exmp_tb2" VALUES (1,'128.23','nice','2018-10-11 19:00:00'); INSERT INTO "public"."exmp_tb2" VALUES (2,DEFAULT,'nice','2018-12-14 19:00:00'); INSERT INTO "public"."exmp_tb2" VALUES (3,DEFAULT,'nice',DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (5,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (4,DEFAULT,DEFAULT); |
PARTITION
MySQL REPLACE支持使用PARTITION关键字和分区，子分区或两者的逗号分隔名称列表显式分区选择。
输入
1 2 3 4 5 |
replace INTO employees PARTITION(p3) VALUES (19, 'Frank1', 'Williams', 1, 2); replace INTO employees PARTITION(p0) VALUES (4, 'Frank1', 'Williams', 1, 2); replace INTO employees PARTITION(p1) VALUES (9, 'Frank1', 'Williams', 1, 2); replace INTO employees PARTITION(p2) VALUES (10, 'Frank1', 'Williams', 1, 2); replace INTO employees PARTITION(p2) VALUES (11, 'Frank1', 'Williams', 1, 2); |
输出
1 2 3 4 5 |
INSERT INTO "public"."employees" VALUES (19,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (4,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (9,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (10,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (11,'Frank1','Williams',1,2); |
DELAYED
- DELAYED插入和替换在MySQL 5.6中被弃用。在MySQL 5.7中，DELAYED不支持。服务器识别但忽略DELAYED关键字，将替换处理为非延迟替换，并生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告。
- REPLACE DELAYED不再被支持，语句被转换为REPLACE。
- DELAYED 关键字将在未来版本中被删除。
输入
1 2 3 4 5 6 7 8 9 |
#DELAYED INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. #If you execute INSERT DELAYED with another storage engine, #you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported Replace DELAYED INTO exmp_tb2 VALUES(10, 128.23, 'nice', '2018-10-11 19:00:00'); Replace DELAYED INTO exmp_tb2 VALUES(6, DEFAULT, 'nice', '2018-12-14 19:00:00'); Replace DELAYED INTO exmp_tb2 VALUES(7, 20, 'nice', DEFAULT); Replace DELAYED INTO exmp_tb2 (tb2_id, tb2_price) VALUES(11, DEFAULT); Replace DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(12, DEFAULT, DEFAULT); Replace DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(13, DEFAULT, DEFAULT, DEFAULT); |
输出
1 2 3 4 5 6 7 8 9 |
--DELAYED INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. --If you execute INSERT DELAYED with another storage engine, --you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported. INSERT INTO "public"."exmp_tb2" VALUES (10,128.23,'nice','2018-10-11 19:00:00'); INSERT INTO "public"."exmp_tb2" VALUES (6,DEFAULT,'nice','2018-12-14 19:00:00'); INSERT INTO "public"."exmp_tb2" VALUES (7,20,'nice',DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (11,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (12,DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (13,DEFAULT,DEFAULT,DEFAULT); |
VALUES
MySQL REPLACE支持一条语句插入或删除多值，以逗号分隔。
输入
1 2 3 4 5 |
#有数据的话则替换replace，没有的话则插入新的数据同INSERT Replace INTO exmp_tb1 (tb1_id,tb1_name,tb1_gender,tb1_address,tb1_number) VALUES(17,'David','male','NewYork11','01015827875'),(18,'Rachel','female','NewYork22','01015827749'),(20,'Monica','female','NewYork','010158996743'); Replace INTO exmp_tb1 (tb1_id,tb1_name,tb1_gender,tb1_address,tb1_number) VALUES(17,'David1','male','NewYork11','01015827875'),(21,'Rachel','female','NewYork22','01015827749'),(22,'Monica','female','NewYork','010158996743'); Replace INTO exmp_tb1 (tb1_id,tb1_name,tb1_gender,tb1_address,tb1_number,tb1_date) VALUES(17,'David2',DEFAULT,'NewYork11','01015827875',DEFAULT),(18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20'),(DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20'); Replace INTO exmp_tb1 VALUES(DEFAULT,'David',DEFAULT,'NewYork11','01015827875',DEFAULT),(18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20'),(DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20'); |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--有数据的话则替换replace，没有的话则插入新的数据同INSERT INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (17,'David','male','NewYork11','01015827875'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (18,'Rachel','female','NewYork22','01015827749'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (20,'Monica','female','NewYork','010158996743'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (17,'David1','male','NewYork11','01015827875'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (21,'Rachel','female','NewYork22','01015827749'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (22,'Monica','female','NewYork','010158996743'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number","tb1_date") VALUES (17,'David2',DEFAULT,'NewYork11','01015827875',DEFAULT); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number","tb1_date") VALUES (18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20'); INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number","tb1_date") VALUES (DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20'); INSERT INTO "public"."exmp_tb1" VALUES (DEFAULT,'David',DEFAULT,'NewYork11','01015827875',DEFAULT); INSERT INTO "public"."exmp_tb1" VALUES (18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20'); INSERT INTO "public"."exmp_tb1" VALUES (DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20'); |
SET
MySQL REPLACE支持使用SET设置值，DSC工具将对其转换。
输入
1 2 3 4 5 |
replace INTO `runoob_datatype_test` VALUES (100, 100, 100, 0, 1); replace INTO `runoob_datatype_test` VALUES (100.23, 100.25, 100.26, 0.12,1.5); replace INTO `runoob_datatype_test` (dataType_numeric,dataType_numeric1) VALUES (100.23, 100.25); replace INTO `runoob_datatype_test` (dataType_numeric,dataType_numeric1,dataType_numeric2) VALUES (100.23, 100.25, 2.34); replace into runoob_datatype_test set dataType_numeric=23.1, dataType_numeric4 = 25.12 ; |
输出
1 2 3 4 5 |
INSERT INTO "public"."runoob_datatype_test" VALUES (100,100,100,0,1); INSERT INTO "public"."runoob_datatype_test" VALUES (100.23,100.25,100.26,0.12,1.5); INSERT INTO "public"."runoob_datatype_test" ("datatype_numeric","datatype_numeric1") VALUES (100.23,100.25); INSERT INTO "public"."runoob_datatype_test" ("datatype_numeric","datatype_numeric1","datatype_numeric2") VALUES (100.23,100.25,2.34); INSERT INTO "public"."runoob_datatype_test" ("datatype_numeric","datatype_numeric4") VALUES (23.1,25.12); |
父主题： 数据操作语句（DML）
