全部产品
云市场

DRDS全局二级索引对DML的限制

更新时间:2020-05-26 09:36:57

本文将介绍DRDS上全局二级索引对DML的限制。

使用限制

MySQL版本需为5.7或以上,且DRDS版本为5.4.1或以上。

示例表结构

  1. CREATE TABLE t_order(
  2. `id` bigint(11) NOT NULL AUTO_INCREMENT,
  3. `order_id` varchar(20) DEFAULT NULL,
  4. `buyer_id` varchar(20) DEFAULT NULL,
  5. `seller_id` varchar(20) DEFAULT NULL,
  6. `order_snapshot` longtext DEFAULT NULL,
  7. `order_detail` longtext DEFAULT NULL,
  8. PRIMARY KEY (`id`),
  9. UNIQUE KEY `l_i_order` (`order_id`),
  10. GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
  11. GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
  • 唯一键中任何一列的值不允许为NULL
  1. # 唯一键 buyer_id 不能为 NULL
  2. INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', NULL, 'seller_1');
  3. # 唯一键 order_id 不能为 NULL
  4. UPDATE t_order SET order_id=NULL WHERE buyer_id='buyer_1';
  • BATCH INSERT语句中不允许两行的主键或唯一键重复
  1. # order_id 重复,不支持
  2. INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id)
  3. VALUES('order_1', 'buyer_1', 'seller_1'), ('order_1', 'buyer_2', 'seller_2');
  • INSERT ON DUPLICATE KEY UPDATE 不允许修改主键、唯一键、主表或索引表拆分键
  1. # 唯一键 order_id 不允许被修改
  2. INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1')
  3. ON DUPLICATE KEY UPDATE order_id=VALUES(order_id);
  4. # 索引表拆分键 seller_id 不允许被修改
  5. INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1')
  6. ON DUPLICATE KEY UPDATE seller_id=VALUES(seller_id);
  • INSERT 语句要包含所有非自增的主键、唯一键、主表或索引表拆分键,且不为DEFAULT
  1. # 索引表拆分键 seller_id 不允许为 DEFAULT
  2. INSERT INTO t_order(order_id, buyer_id) VALUES('order_1', 'buyer_id');
  3. # 唯一键 order_id 不允许为 DEFAULT
  4. INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES(DEFAULT, 'buyer_id', 'seller_id');
  • INSERT SELECT、REPLACE SELECT、UPDATE和DELETE的更新行数不超过10000行
  1. # INSERT SELECT 插入行数超过 10000
  2. INSERT INTO t_order SELECT * FROM t_order_bak WHERE id BETWEEN 0 AND 20000;
  3. # DELETE 删除行数超过 10000
  4. DELETE FROM t_order WHERE id BETWEEN 0 AND 20000;
  • 不支持多表UPDATE或DELETE
  1. # 不支持多表 UPDATE
  2. UPDATE t_order, t_item SET t_order.order_detail=t_item.item_detail
  3. WHERE t_order.seller_id=t_item.seller_id;
  4. # 不支持多表 DELETE
  5. DELETE t_order FROM t_order JOIN t_item WHERE t_order.seller_id=t_item.seller_id;
  • INSERT IGNORE、INSERT ON DUPLICATE KEY UPDATE、REPLACE可能报主键冲突
  1. INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
  2. # IGNORE 仍有可能报主键冲突
  3. INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
  • 写索引失败后,不允许继续执行其他语句或提交事务
  1. SET DRDS_TRANSACTION_POLICY='XA';
  2. INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
  3. # 失败
  4. INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
  5. # 失败不允许继续执行
  6. INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
  7. # 失败后不允许提交事务
  8. COMMIT;