更新时间:2022-06-13 GMT+08:00

数据操作语句(DML)

本节主要介绍MySQL DML的迁移语法。迁移语法决定了关键字/功能的迁移方式。

本节包括以下内容:

INSERT

INSERT插入形式包括:HIGH_PRIORITY、LOW_PRIORITY、PARTITION、DELAYED、IGNORE、多值插入以及ON DUPLICATE KEY UPDATE。GaussDB(DWS)不支持以上类型插入,DSC工具将会对其转换。

  1. HIGH_PRIORITY

    MySQL中如果指定HIGH_PRIORITY,则会覆盖LOW_PRIORITY选项的效果。

    输入示例

    # HIGH_PRIORITY 高优先级
    INSERT HIGH_PRIORITY INTO  exmp_tb2 VALUES(100, 12.3, 'cheap', '2018-11-11');
    INSERT HIGH_PRIORITY INTO  exmp_tb2 VALUES(DEFAULT, 128.23, 'nice', '2018-10-11');
    INSERT HIGH_PRIORITY INTO  exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14');
    INSERT HIGH_PRIORITY INTO  exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT);
    INSERT HIGH_PRIORITY INTO  exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT);
    INSERT HIGH_PRIORITY INTO  exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT);
    INSERT HIGH_PRIORITY INTO  exmp_tb2 (tb2_id, tb2_price , tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT);
    INSERT HIGH_PRIORITY INTO  exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(DEFAULT, DEFAULT, DEFAULT, DEFAULT);

    输出示例

    -- HIGH_PRIORITY 高优先级
    INSERT INTO "public"."exmp_tb2"  VALUES (100,12.3,'cheap','2018-11-11');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,128.23,'nice','2018-10-11');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);
  2. LOW_PRIORITY

    MySQL INSERT插入语句使用LOW_PRIORITY修饰符时,则执行该INSERT延迟。

    输入示例

    # LOW_PRIORITY 低优先级
    INSERT LOW_PRIORITY INTO  exmp_tb2 VALUES( DEFAULT, '128.23', 'nice', '2018-10-11');
    INSERT LOW_PRIORITY INTO  exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14' );
    INSERT LOW_PRIORITY INTO  exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT);
    INSERT LOW_PRIORITY INTO  exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT);
    INSERT LOW_PRIORITY INTO  exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT);

    输出示例

    -- LOW_PRIORITY 低优先级
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,'128.23','nice','2018-10-11');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);
  3. PRATITION

    当插入到分区表中时,可以控制哪些分区和子分区接受新行。

    输入示例

    INSERT INTO employees PARTITION(p3) VALUES (19, 'Frank1', 'Williams', 1, 2);
    INSERT INTO employees PARTITION(p0) VALUES (4, 'Frank1', 'Williams', 1, 2);
    INSERT INTO employees PARTITION(p1) VALUES (9, 'Frank1', 'Williams', 1, 2);
    INSERT INTO employees PARTITION(p2) VALUES (10, 'Frank1', 'Williams', 1, 2);
    INSERT INTO employees PARTITION(p2) VALUES (11, 'Frank1', 'Williams', 1, 2);

    输出示例

    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);
  4. DELAYED

    在MySQL 5.7中,DELAYED关键字被接受,但被服务器忽略。

    输入示例

    # DELAYED 延迟
    INSERT DELAYED INTO  exmp_tb2 VALUES(99, 15.68, 'good', '2018-11-12');
    INSERT DELAYED INTO  exmp_tb2 VALUES(80, 12.3, 'cheap', '2018-11-11');
    INSERT DELAYED INTO  exmp_tb2 VALUES(DEFAULT, 128.23, 'nice', '2018-10-11');
    INSERT DELAYED INTO  exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14');
    INSERT DELAYED INTO  exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT);
    INSERT DELAYED INTO  exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT);
    INSERT DELAYED INTO  exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT);
    INSERT DELAYED INTO  exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(DEFAULT, DEFAULT, DEFAULT, DEFAULT);

    输出示例

    -- DELAYED 延迟
    INSERT INTO "public"."exmp_tb2"  VALUES (99,15.68,'good','2018-11-12');
    INSERT INTO "public"."exmp_tb2"  VALUES (80,12.3,'cheap','2018-11-11');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,128.23,'nice','2018-10-11');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);
  5. IGNORE

    MySQL INSERT语句如果使用IGNORE修饰符,则执行INSERT语句时发生的错误将被忽略。

    输入示例

    # 如果表中已经存在相同的记录,则忽略当前新数据
    INSERT IGNORE INTO  exmp_tb2 VALUES(189, '189.23','nice','2017-11-12');
    INSERT IGNORE INTO  exmp_tb2 VALUES(130,'189.23','nice','2017-11-12');
    INSERT IGNORE INTO  exmp_tb2 VALUES(120,15.68,'good','2018-11-12');
    INSERT IGNORE INTO  exmp_tb2 VALUES(DEFAULT,128.23,'nice','2018-10-11');
    INSERT IGNORE INTO  exmp_tb2 VALUES(DEFAULT,DEFAULT,'nice','2018-12-14');
    INSERT IGNORE INTO  exmp_tb2 VALUES(DEFAULT,DEFAULT,'nice',DEFAULT);test
    INSERT IGNORE INTO  exmp_tb2 (tb2_id,tb2_price) VALUES(DEFAULT,DEFAULT);
    INSERT IGNORE INTO  exmp_tb2 (tb2_id,tb2_price,tb2_note) VALUES(DEFAULT,DEFAULT,DEFAULT);
    INSERT IGNORE INTO  exmp_tb2 (tb2_id,tb2_price,tb2_note,tb2_date) VALUES(DEFAULT,DEFAULT,DEFAULT,DEFAULT);

    输出示例

    -- 如果表中已经存在相同的记录,则忽略当前新数据
    INSERT INTO "public"."exmp_tb2"  VALUES (101,'189.23','nice','2017-11-12');
    INSERT INTO "public"."exmp_tb2"  VALUES (130,'189.23','nice','2017-11-12');
    INSERT INTO "public"."exmp_tb2"  VALUES (120,15.68,'good','2018-11-12');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,128.23,'nice','2018-10-11');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');
    INSERT INTO "public"."exmp_tb2"  VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);
  6. VALUES(单语句多行值插入)

    INSERT使用 VALUES语法的语句可以插入多行,以逗号分隔。

    输入示例

    INSERT INTO  exmp_tb1 (tb1_name,tb1_sex,tb1_address,tb1_number) VALUES('David','male','NewYork','01015827875'),('Rachel','female','NewYork','01015827749'),('Monica','female','NewYork','010158996743');

    输出示例

    INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_sex","tb1_address","tb1_number") VALUES ('David','male','NewYork','01015827875');
    INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_sex","tb1_address","tb1_number") VALUES ('Rachel','female','NewYork','01015827749');
    INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_sex","tb1_address","tb1_number") VALUES ('Monica','female','NewYork','010158996743');
  7. ON DUPLICATE KEY UPDATE

    INSERT使用 ON DUPLICATE KEY UPDATE子句可以使现有行更新。

    输入示例

    #ON DUPLICATE KEY UPDATE 若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE;否则执行插入操作
    INSERT INTO  exmp_tb2(tb2_id,tb2_price) VALUES(3,12.3) ON DUPLICATE KEY UPDATE tb2_price=12.3;
    INSERT INTO  exmp_tb2(tb2_id,tb2_price) VALUES(4,12.3) ON DUPLICATE KEY UPDATE tb2_price=12.3;
    INSERT INTO  exmp_tb2(tb2_id,tb2_price,tb2_note) VALUES(10,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE tb2_price=66.6;
    INSERT INTO  exmp_tb2(tb2_id,tb2_price,tb2_note,tb2_date) VALUES(11,DEFAULT,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE tb2_price=66.6;

    输出示例

    --ON DUPLICATE KEY UPDATE 若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE;否则执行插入操作
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (3,12.3);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (4,12.3);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (10,DEFAULT,DEFAULT);
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (11,DEFAULT,DEFAULT,DEFAULT);
  8. SET

    MySQL INSERT...SET语句的形式插入基于明确指定的值的行。

    输入示例

    # INSERT INTO  SET 可以针对性的执行插入操作,但是一次只能插入一行数据,不能批量添加数据
    INSERT INTO  exmp_tb2 SET tb2_price=56.1,tb2_note='unbelievable',tb2_date='2018-11-13';
    INSERT INTO  exmp_tb2 SET tb2_price=99.9,tb2_note='perfect',tb2_date='2018-10-13';
    INSERT INTO  exmp_tb2 SET tb2_id=9,tb2_price=99.9,tb2_note='perfect',tb2_date='2018-10-13';

    输出示例

    -- INSERT INTO  SET 可以针对性的执行插入操作,但是一次只能插入一行数据,不能批量添加数据
    INSERT INTO "public"."exmp_tb2" ("tb2_price","tb2_note","tb2_date") VALUES (56.1,'unbelievable','2018-11-13');
    INSERT INTO "public"."exmp_tb2" ("tb2_price","tb2_note","tb2_date") VALUES (99.9,'perfect','2018-10-13');
    INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (9,99.9,'perfect','2018-10-13');

UPDATE

MySQL的UPDATE操作形式包括:LOW_PRIORITY、ORDER BY、LIMIT 、IGNORE。GaussDB(DWS)不支持以上类型更新,DSC工具将会对其转换。

  1. LOW_PRIORITY

    MySQL UPDATE语句如果使用LOW_PRIORITY修饰符,则执行UPDATE延迟。

    输入示例

    #测试 LOW_PRIORITY 语法点
    UPDATE LOW_PRIORITY employees SET department_id=2;

    输出示例

    --测试 LOW_PRIORITY 语法点
    UPDATE "public"."employees" SET "department_id" = 2;
  2. ORDER_BY

    如果一个MySQL UPDATE语句包含一个 ORDER BY子句,则这些行将按照该子句指定的顺序更新。

    输入示例

    # 测试 ORDER BY 语法点
    UPDATE  employees SET department_id=department_id+1  ORDER BY id;

    输出示例

    -- 测试 ORDER BY 语法点
    UPDATE "public"."employees" SET "department_id" = department_id+1;
  3. LIMIT

    UPDATE LIMIT语法可以用来限制的范围。一个子句是一个行匹配的限制。只要发现满足该子句的行, 语句就会停下来 ,不管它们是否真的发生了变化。

    输入示例

    #单独测试 LIMIT 语法点
    UPDATE  employees SET department_id=department_id+1   LIMIT 3 ;
    UPDATE  employees SET department_id=department_id+1   LIMIT 3 , 10 ;
    
    #测试 LIMIT + OFFSET 语法点
    UPDATE  employees SET department_id=department_id+1   LIMIT 3   OFFSET 2;
    
    #测试 LIMIT + ORDER BY 语法点搭配使用
    UPDATE  employees SET department_id=department_id+1 ORDER BY fname  LIMIT 3 ;
    
    #测试 LIMIT + WHERE + ORDER BY 语法点搭配使用
    UPDATE  employees SET department_id=department_id+1 WHERE id<5 ORDER BY  fname  LIMIT 3 ;
    
    #测试 LIMIT + WHERE + ORDER BY + OFFSET 语法点搭配使用
    UPDATE  employees SET department_id=department_id+1 WHERE id<5 ORDER BY  fname  LIMIT 3 OFFSET 2 ;

    输出示例

    --单独测试 LIMIT 语法点
    UPDATE "public"."employees" SET "department_id" = department_id+1;
    UPDATE "public"."employees" SET "department_id" = department_id+1;
    
    --测试 LIMIT + OFFSET 语法点
    UPDATE "public"."employees" SET "department_id" = department_id+1;
    
    --测试 LIMIT + ORDER BY 语法点搭配使用
    UPDATE "public"."employees" SET "department_id" = department_id+1;
    
    --测试 LIMIT + WHERE + ORDER BY 语法点搭配使用
    UPDATE "public"."employees" SET "department_id" = department_id+1 WHERE id<5;
    
    --测试 LIMIT + WHERE + ORDER BY + OFFSET 语法点搭配使用
    UPDATE "public"."employees" SET "department_id" = department_id+1 WHERE id<5;
  4. IGNORE

    MySQL UPDATE语句如果使用IGNORE修饰符,即使更新期间发生错误,update语句也不会中止。

    输入示例

    #测试 IGNORE 语法点
    UPDATE IGNORE employees SET department_id=3;

    输出示例

    --测试 IGNORE 语法点
    UPDATE "public"."employees" SET "department_id" = 3;

REPLACE

MySQL的REPLACE操作形式包括:LOW_PRIORITY、PARTITION 、DELAYED 、VALUES、SET;(下述迁移示例为临时迁移方案)

REPLACE的工作方式与INSERT完全相同,不同之处在于,如果表中的旧行与主键或唯一索引的新行具有相同的值,则在插入新行之前删除该旧行。

  1. LOW_PRIORITY

    MySQL REPLACE支持使用LOW_PRIORITY ,DSC工具将对其进行转换。

    输入

    # 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);

    输出

    -- 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);
  2. PARTITION

    MySQL REPLACE支持使用PARTITION关键字和分区,子分区或两者的逗号分隔名称列表显式分区选择。

    输入

    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);

    输出

    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);
  3. DELAYED

    DELAYED插入和替换在MySQL 5.6中被弃用。在MySQL 5.7中,DELAYED 不支持。服务器识别但忽略 DELAYED关键字,将替换处理为非延迟替换,并生成 ER_WARN_LEGACY_SYNTAX_CONVERTED警告。(“ REPLACE DELAYED不再被支持,语句被转换为REPLACE。 ”)DELAYED 关键字将在未来版本中被删除。

    输入

    #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);

    输出

    --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);
  4. VALUES

    MySQL REPLACE支持一条语句插入或删除多值,以逗号分隔。

    输入

    #有数据的话则替换replace,没有的话则插入新的数据同insert
    Replace INTO  exmp_tb1 (tb1_id,tb1_name,tb1_sex,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_sex,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_sex,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');

    输出

    --有数据的话则替换replace,没有的话则插入新的数据同insert
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number") VALUES (17,'David','male','NewYork11','01015827875');
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number") VALUES (18,'Rachel','female','NewYork22','01015827749');
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number") VALUES (20,'Monica','female','NewYork','010158996743');
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number") VALUES (17,'David1','male','NewYork11','01015827875');
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number") VALUES (21,'Rachel','female','NewYork22','01015827749');
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number") VALUES (22,'Monica','female','NewYork','010158996743');
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","tb1_address","tb1_number","tb1_date") VALUES (17,'David2',DEFAULT,'NewYork11','01015827875',DEFAULT);
    INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_sex","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_sex","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');
  5. SET

    MySQL REPLACE支持使用SET设置值,DSC工具将对其转换。

    输入

    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 ;

    输出

    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);