遇到权限数据变更的需要批量到别的平台, 在175平台添加一个权限需要, 批量到别的现有平台, 以后的建站, 会把sql放到自动建站里面;
权限的 insert into select
表一: `ouser`.`u_function` 权限表
表二: misc.gxej_company 平台表
sql:
INSERT INTO `ouser`.`u_function` ( `code`, `parent_code`, `product_type`, `type`, `domain`, `path`, `name`, `desc_`, `level`, `target`, `icon`, `sort_value`, `is_available`, `is_deleted`, `version_no`, `create_userid`, `create_username`, `create_userip`, `create_usermac`, `create_time_db`, `server_ip`, `update_userid`, `update_username`, `update_userip`, `update_usermac`, `update_time_db`, `client_versionno`, `company_id`, `platform_id`) SELECT '307788', '3077', NULL, '1', '/back-finance-web', '/#/stmMerchantSoStatementListOld/2', '商家销售结算单旧', NULL, '2', NULL, NULL, '99', NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, c.id, '2'FROM misc.gxej_company c where c.id != 175; #去掉175平台
角色的insert into select
涉及到左连接:
insert into ... select ... where ... join语法 例子
insert into T2(c1, c2, c3)selectt1.c1,t1.c2,t1.c3from T1 t1wheret1.c2 = 'y'left join T3 t3 on t1.c1 = t3.c1left join T4 t4 on t1.c1 = t4.c1;
权限关联表 : u_role_function
角色表: u_role
权限表: u_function
注意: on 条件生产临时表
INSERT INTO `ouser`.`u_role_function` ( `role_id`, `function_id`, `is_available`, `is_deleted`, `version_no`, `create_userid`, `create_username`, `create_userip`, `create_usermac`, `create_time`, `create_time_db`, `server_ip`, `update_userid`, `update_username`, `update_userip`, `update_usermac`, `update_time_db`, `client_versionno`, `company_id`)SELECT r.id, f.id, NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, r.company_id FROM ouser.u_role r LEFT JOIN ouser.u_function f on r.company_id = f.company_id where r.code ='merchant_role_code_enter_type_1' and f.code LIKE '%307788%'