DRDS 全局二级索引对 DML 的限制
版本限制: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;
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论