MySQL数据库A表和B表id冲突解决办法？

方法1：不要定义自增长，用触发器来实现。
方法2：不要定义自增长，两张表共享同一个sequence。（规则唯一）
方法3：自增长，但是一张表是奇数，另一张全是偶数

基于触发器的方法三：（对于现有系统的补救方案），最好的方案还是方法二使用同一个自增sequence获取

-- 修改A表id 别和B表id重复

-- 查询最大的id
select max(order_id),max(order_code) from trade_order o ;

select max(processing_detail_id),max(processing_detail_code) from processing_order_detail o ;

-- 解决办法
-- 触发器 INSERT 语句触发 （首先保证是一个最大id是偶数，另一个是奇数，如果不是用测试数据新增修正id）
-- trade_order 偶数id，processing_order_detail 奇数id

-- 创建触发器
drop trigger if exists trade_order_id;

DELIMITER $$

CREATE TRIGGER trade_order_id

BEFORE INSERT ON trade_order

FOR EACH ROW

BEGIN


SELECT IFNULL(max_id, 0) + 2

INTO @id FROM

(SELECT MAX(order_id) max_id FROM trade_order) maxid;


SET NEW.order_id := @id;

END;

$$

DELIMITER ;


-- 创建触发器
drop trigger if exists processing_order_detail_id;
DELIMITER $$

CREATE TRIGGER processing_order_detail_id

BEFORE INSERT ON processing_order_detail

FOR EACH ROW

BEGIN

SELECT IFNULL(max_id, 0)

INTO @id FROM

(SELECT MAX(processing_detail_id) max_id FROM processing_order_detail) maxid;

SET NEW.processing_detail_id := @id + 2;


END;

$$

DELIMITER ;




-- 查询最新十条数据
select * from trade_order order by order_id desc limit 10;
select * from processing_order_detail  order by processing_detail_id desc limit 10;


-- 新增数据测试（下面两个表id都是自增主键）
INSERT INTO trade_order ( order_code, buyer_user_id, buyer_company_id, seller_id, seller_company_id,
                         manager_id, seller_manager_id, buyer_user_name, buyer_company_name, seller_name,
                         seller_company_name, manager_name, seller_manager_name, order_type, bz_type, order_status,
                         review_status, is_upload_contract, contract_img, is_payment, payment_img, is_invoice,
                         invoice_img, is_sign, sign_img, trade_time, close_time, expire_time, close_cancel_type,
                         cancel_reason, cancel_remark, reviewer_id, review_time, review_remark, order_origin,
                         contract_id, base_price_type, base_price_method, base_price_other, quality_standard,
                         quality_standard_other, quality_require, contract_effective_date, processing_required,
                         project_id, project_name, processing_status, create_operation_id, update_operation_id,
                         create_time, update_time, is_delete, version, time_flag)
VALUES ( CONCAT('DD',substring(md5(rand()), 1, 18)), 102804, 101904, 100090, 100090, 127, null, null, '测试买方公司', null,
        '测试卖方公司', '邹凯婷', null, 53, null, 7, 113, 0, null, 0, null, 0, null, 0, null, null, null, null, null, null,
        null, 1, '2023-04-13 10:23:29', null, 118, null, 460, 342, null, 348, '质量标准说明', null, '2023-06-12 08:00:00', 0,
        null, null, 424, 0, 1, '2023-04-13 10:22:56', '2023-04-13 10:23:29',1, 2, null);

INSERT INTO processing_order_detail ( processing_detail_code, processing_id, category,
                                     order_detail_id, weight, check_weight, order_quantity_total, item_web_desc,
                                     item_app_desc, cid, cid_name, props, props_name, binds, binds_name, properties,
                                     properties_name, meterage_unit_id, meterage_unit_en, meterage_unit_cn,
                                     currency_unit_id, currency_unit_en, currency_unit_cn, spec_remark, remark,
                                     create_operation_id, create_time, update_operation_id, update_time, deleted)
VALUES ( CONCAT('MX',substring(md5(rand()), 1, 18)), 1080, '原料', null, 111.00000, 33.44000, 111.00000,
        '内径:1mm;产品用途:产品_牌号:3105;状态:其他;规格:22mm_empty_empty_empty_empty',
        '内径:1mm;产品用途:产品_牌号:3105;状态:其他;规格:22mm_empty_empty_empty_empty', 5, '品种:铝板带;品名:冷轧卷;', ';221:689;222:717;',
        '内径:1mm;产品用途:产品;', null, null, ';2:430;3:214;4:524;', '牌号:3105;状态:其他;规格:22mm;', 87, 't', '吨', 1, 'Yuan', '元',
        null, null, 1, '2023-03-20 17:38:35', 1, '2023-03-20 17:54:39', 1);