文档

Affected rows行为

更新时间:

本文主要描述了PolarDB-X中的affected rows行为。

注意事项

  • PolarDB-X从5.4.17-16921956版本开始对DML在useAffectedRows=true情况下的affected rows进行兼容性适配。

  • PolarDB-X分布式场景下,逻辑DML执行较为复杂,affected rows行为和MySQL略有不同。

  • JDBC url中useAffectedRows会影响CLIENT_FOUND_ROWS标记位,useAffectedRows默认为false,CLIENT_FOUND_ROWS默认为true,而命令行CLIENT_FOUND_ROWS默认为false。

  • CLIENT_FOUND_ROWS为true时,DML返回的是匹配的行数,为false时,返回的是更新的行数。

  • MySQL官方文档中描述了CLIENT_FOUND_ROWS的影响,只影响UPDATE和UPSERT。

  • 在使用affected rows作为业务逻辑判断前,建议进行测试并固化为单元测试用例,后续PolarDB-X新版本的默认行为可能会发生变化。

  • 在使用 MySQL connector JDBC接口访问PolarDB-X时,是默认使用useAffectedRows=false配置的,即CLIENT_FOUND_ROWS = ON,可以在链接URL中添加useAffectedRows=true来使用修改行数返回。

  • 在使用MySQL client命令行程序时,默认CLIENT_FOUND_ROWS=OFF,即DML默认返回的是修改行数。

已知行为不一致

replace into

  • 只有唯一键,没有主键的表,且插入的新老数据完全一样。

    说明

    该情况行为和MySQL不一样,但是是预期行为。

    PolarDB-X 2.0

    CREATE TABLE `replace_test_tb_no_pk_with_uk` (
    	`id` bigint(11) NOT NULL DEFAULT '1',
    	`c1` bigint(20) DEFAULT NULL,
    	`c2` bigint(20) DEFAULT NULL,
    	`c3` bigint(20) DEFAULT NULL,
    	`c4` bigint(20) DEFAULT NULL,
    	`c5` varchar(255) DEFAULT NULL,
    	`c6` datetime DEFAULT NULL,
    	`c7` text,
    	`c8` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    	`_drds_implicit_id_` bigint(20) NOT NULL AUTO_INCREMENT,
    	PRIMARY KEY (`_drds_implicit_id_`),
    	UNIQUE LOCAL KEY `u_id` (`id`)
    ) ENGINE = InnoDB AUTO_INCREMENT = 100022 DEFAULT CHARSET = utf8
    PARTITION BY KEY(`id`)
    PARTITIONS 7
    /* tablegroup = `tg602` */
    
    delete from replace_test_tb_no_pk_with_uk;
    Query OK, 2 rows affected (0.09 sec)
    
    replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
    Query OK, 1 row affected (0.05 sec)
    
    replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
    Query OK, 2 rows affected (0.03 sec)
    
    mysql> replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
    Query OK, 2 rows affected (0.11 sec)

    MySQL

    CREATE TABLE `replace_test_tb_no_pk_with_uk` (
      `id` bigint(11) NOT NULL DEFAULT '1',
      `c1` bigint(20) DEFAULT NULL,
      `c2` bigint(20) DEFAULT NULL,
      `c3` bigint(20) DEFAULT NULL,
      `c4` bigint(20) DEFAULT NULL,
      `c5` varchar(255) DEFAULT NULL,
      `c6` datetime DEFAULT NULL,
      `c7` text,
      `c8` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY `u_id` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    delete from replace_test_tb_no_pk_with_uk;
    Query OK, 2 rows affected (0.00 sec)
    
    replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
    Query OK, 1 row affected (0.00 sec)
    
    replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
    Query OK, 1 row affected (0.00 sec)
    
    replace into replace_test_tb_no_pk_with_uk(id, c1, c5, c8) values(1, 1, 'a', '2020-06-16 06:49:32');
    Query OK, 1 row affected (0.00 sec)

    原因为:无主键的表会创建隐式主键,基于性能原因,会直接下推replace into,隐式主键会自动带上自增的sequence,导致即使插入的数据完全一致,隐式主键也不一致,affected rows为一次delete一次insert,即为2。

  • 本地唯一键非拆分键,唯一约束作用域仅在当前分片。

    说明

    该情况行为和 MySQL 不一样,但是是预期行为。

    PolarDB-X 2.0

    CREATE TABLE `replace_test_tbl` (
    	`id` int(11) NOT NULL,
    	`a` int(11) DEFAULT '1',
    	`b` int(11) DEFAULT '0',
    	PRIMARY KEY (`id`),
    	UNIQUE KEY `b` (`b`)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
    PARTITION BY KEY(`id`)
    PARTITIONS 3
    
    replace into replace_test_tbl (id,a,b) values (0,1,1),(1,2,2),(2,3,3),(100,100,100),(101,103,103);
    Query OK, 5 rows affected (0.03 sec)
    
    replace into replace_test_tbl (id) values (1);
    Query OK, 2 rows affected (0.01 sec)
    
    replace into replace_test_tbl (id,a,b) values (3,0+2,0+2);
    Query OK, 1 row affected (0.01 sec)
    
    select * from replace_test_tbl;
    +-----+------+------+
    | id  | a    | b    |
    +-----+------+------+
    |   1 |    1 |    0 |
    | 101 |  103 |  103 |
    | 100 |  100 |  100 |
    |   0 |    1 |    1 |
    |   2 |    3 |    3 |
    |   3 |    2 |    2 |
    +-----+------+------+
    6 rows in set (0.03 sec)
    
    replace into replace_test_tbl (id,a,b) values (1,2,2),(2,3,3);
    Query OK, 4 rows affected (0.02 sec)
    
    mysql> select * from replace_test_tbl;
    +-----+------+------+
    | id  | a    | b    |
    +-----+------+------+
    |   0 |    1 |    1 |
    |   2 |    3 |    3 |
    |   3 |    2 |    2 |
    |   1 |    2 |    2 |
    | 101 |  103 |  103 |
    | 100 |  100 |  100 |
    +-----+------+------+
    6 rows in set (0.04 sec)

    MySQL

    CREATE TABLE `replace_test_tbl` (
      `id` int(11) NOT NULL,
      `a` int(11) DEFAULT '1',
      `b` int(11) DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY `b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    replace into replace_test_tbl (id,a,b) values (0,1,1),(1,2,2),(2,3,3),(100,100,100),(101,103,103);
    Query OK, 5 rows affected (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    replace into replace_test_tbl (id) values (1);
    Query OK, 2 rows affected (0.01 sec)
    
    replace into replace_test_tbl (id,a,b) values (3,2,2);
    Query OK, 1 row affected (0.00 sec)
    
    select * from replace_test_tbl;
    +-----+------+------+
    | id  | a    | b    |
    +-----+------+------+
    |   0 |    1 |    1 |
    |   1 |    1 |    0 |
    |   2 |    3 |    3 |
    |   3 |    2 |    2 |
    | 100 |  100 |  100 |
    | 101 |  103 |  103 |
    +-----+------+------+
    6 rows in set (0.00 sec)
    
    replace into replace_test_tbl (id,a,b) values (1,2,2),(2,3,3);
    Query OK, 5 rows affected (0.00 sec)
    Records: 2  Duplicates: 3  Warnings: 0
    
    select * from replace_test_tbl;
    +-----+------+------+
    | id  | a    | b    |
    +-----+------+------+
    |   0 |    1 |    1 |
    |   1 |    2 |    2 |
    |   2 |    3 |    3 |
    | 100 |  100 |  100 |
    | 101 |  103 |  103 |
    +-----+------+------+
    5 rows in set (0.00 sec)

insert on duplicate key update

  • 使用DML_SKIP_TRIVIAL_UPDATE=false hint时。

    说明

    该情况行为和MySQL不一样,但是是预期行为。

    /*+TDDL: cmd_extra(DML_SKIP_TRIVIAL_UPDATE=FALSE)*/

    当包含该hint时,并不会判断update前后的值是否一致,都会当成不一致进而执行update操作,用于处理等值判断失误的情况,affected rows也不再准确。正常情况下不建议使用,仅在工单反馈、值班研发推荐下才可以使用,并需要评估影响。

  • batch insert中对原数据A->B->C->A认为是没有更新。

    说明

    兼容性缺陷,已于5.4.17-16971811版本修复。

    PolarDB-X 2.0

    CREATE PARTITION TABLE `tb` (
    	`id` int(11) NOT NULL,
    	`a` int(11) DEFAULT NULL,
    	`b` int(11) DEFAULT NULL,
    	PRIMARY KEY (`id`),
    	UNIQUE GLOBAL INDEX /* ua_$b525 */ `ua` (`a`)
    		PARTITION BY KEY(`a`)
    		PARTITIONS 3,
    	UNIQUE LOCAL KEY `_local_ua` (`a`)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
    PARTITION BY KEY(`id`)
    PARTITIONS 3
    
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 8 rows affected (0.44 sec)
    
    # 错误结果
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 0 rows affected (0.15 sec)
    
    # 错误结果
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 0 rows affected (0.16 sec)
    
    # 修复后
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 6 rows affected (0.23 sec)

    MySQL

    CREATE TABLE `tb` (
      `id` int(11) NOT NULL,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `ua` (`a`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 8 rows affected (0.00 sec)
    Records: 5  Duplicates: 3  Warnings: 0
    
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 6 rows affected (0.00 sec)
    Records: 5  Duplicates: 3  Warnings: 0
    
    insert into tb values (1,1,1),(2,2,2),(3,1,3),(4,1,4),(5,1,1) on duplicate key update b=values(b);
    Query OK, 6 rows affected (0.00 sec)
    Records: 5  Duplicates: 3  Warnings: 0

update

reloacte情况下affected rows计算有误。

说明

兼容性缺陷,已于5.4.17-16971811版本修复。

PolarDB-X 2.0

CREATE TABLE `update_relocate_tb` (
	`id` int(11) NOT NULL,
	`a` varchar(100) DEFAULT NULL,
	`b` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 3

insert into update_relocate_tb (id,a)values (1, 'fdas');
Query OK, 1 row affected (0.11 sec)

update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 row affected (0.08 sec)

# 错误结果
update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 rows affected (0.11 sec)

# 修复后
update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 0 rows affected (0.11 sec)

MySQL

CREATE TABLE `update_relocate_tb` (
  `id` int(11) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into update_relocate_tb (id,a)values (1, 'fdas');
Query OK, 1 row affected (0.00 sec)

update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

update update_relocate_tb set id=1,a=0 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

JSON

该情况行为和MySQL不一样。目前PolarDB-X不支持JSON类型列的数据比较,所以关于JSON的数据更新仅通过字符串对比判断,affected rows结果可能并不准确。