版本限制：MySQL 版本 >= 5.7, 并且 DRDS 版本 >= 5.4.1

• 示例表结构

CREATE TABLE t_order(
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `l_i_order` (`order_id`),
  GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
  GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);

• 唯一键中任何一列的值不允许为 NULL

# 唯一键 buyer_id 不能为 NULL
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', NULL, 'seller_1');
# 唯一键 order_id 不能为 NULL
UPDATE t_order SET order_id=NULL WHERE buyer_id='buyer_1';

• BATCH INSERT 语句中不允许两行的主键 / 唯一键重复

# order_id 重复，不支持
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) 
    VALUES('order_1', 'buyer_1', 'seller_1'), ('order_1', 'buyer_2', 'seller_2');

• INSERT ON DUPLICATE KEY UPDATE 不允许修改主键、唯一键、主表 / 索引表拆分键

# 唯一键 order_id 不允许被修改
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1')
    ON DUPLICATE KEY UPDATE order_id=VALUES(order_id);
# 索引表拆分键 seller_id 不允许被修改
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1')
    ON DUPLICATE KEY UPDATE seller_id=VALUES(seller_id);

• INSERT 语句要包含所有非自增的主键、唯一键、主表 / 索引表拆分键，且不为 DEFAULT

# 索引表拆分键 seller_id 不允许为 DEFAULT
INSERT INTO t_order(order_id, buyer_id) VALUES('order_1', 'buyer_id');
# 唯一键 order_id 不允许为 DEFAULT
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES(DEFAULT, 'buyer_id', 'seller_id');

• UPDATE 不允许修改主表和索引表的拆分键

# 不允许修改索引表拆分键 buyer_id
UPDATE t_order SET buyer_id='buyer_1' WHERE order_id='order_1';

• INSERT SELECT / REPLACE SELECT / UPDATE / DELETE 的更新行数不超过 10000 行

# INSERT SELECT 插入行数超过 10000
INSERT INTO t_order SELECT * FROM t_order_bak WHERE id BETWEEN 0 AND 20000;
# DELETE 删除行数超过 10000
DELETE FROM t_order WHERE id BETWEEN 0 AND 20000;

• 不支持多表 UPDATE / DELETE

# 不支持多表 UPDATE
UPDATE t_order, t_item SET t_order.order_detail=t_item.item_detail
    WHERE t_order.seller_id=t_item.seller_id;
# 不支持多表 DELETE
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 可能报主键冲突

INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
# IGNORE 仍有可能报主键冲突
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');

• 写索引失败后，不允许继续执行其他语句或提交事务

SET DRDS_TRANSACTION_POLICY='XA';
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
# 失败
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
# 失败不允许继续执行
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
# 失败后不允许提交事务
COMMIT;