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);
