/*为权限生成layercode*/ UPDATE auth_privilege SET privilege_layer_code=NULL WHERE application_id=2; UPDATE auth_privilege SET privilege_layer_code=CONCAT('0,',privilege_id) WHERE application_id=2 AND privilege_key = "ITIM.PRIVS"; /* 反复执行,直到为0 */ UPDATE auth_privilege a SET privilege_layer_code=CONCAT((SELECT c.privilege_layer_code FROM (SELECT * FROM auth_privilege) c WHERE a.PRIVILEGE_parent_ID = c. PRIVILEGE_ID ),',',privilege_id) WHERE application_id=2 AND privilege_layer_code IS NULL AND EXISTS( SELECT * FROM (SELECT * FROM auth_privilege) b WHERE a.PRIVILEGE_parent_ID = b. PRIVILEGE_ID AND b.privilege_layer_code IS NOT NULL ); /* 查看排好序的权限 */ SELECT * FROM auth_privilege WHERE application_id=2 ORDER BY privilege_layer_code; /* 以下是迁移发版的时候使用的语句 */ /* 将父key更新到PRIVILEGE_LAYER_CODE_DESCRIPTION */ UPDATE auth_privilege a SET a.PRIVILEGE_LAYER_CODE_DESCRIPTION=(SELECT c.PRIVILEGE_KEY FROM (SELECT * FROM auth_privilege) c WHERE a.PRIVILEGE_parent_ID = c. PRIVILEGE_ID ) WHERE a.application_id=2; /* 生成权限迁移的sql语句,需要借助 PRIVILEGE_LAYER_CODE_DESCRIPTION 的值*/ SELECT CONCAT("INSERT INTO auth_privilege ( APPLICATION_ID, PRIVILEGE_KEY, PRIVILEGE_NAME, PRIVILEGE_TYPE_DICT, CREATE_USER_ID, CREATE_TIME, UPDATE_USER_ID, UPDATE_TIME,PRIVILEGE_DISPLAY_ORDER,PRIVILEGE_PARENT_ID,PRIVILEGE_ICON,PRIVILEGE_URL,PRIVILEGE_STATUS ) (SELECT " ,APPLICATION_ID,",'",PRIVILEGE_KEY,"','",PRIVILEGE_NAME,"','",PRIVILEGE_TYPE_DICT,"','1',now(),'1',now(),",PRIVILEGE_DISPLAY_ORDER,",(SELECT max(PRIVILEGE_ID) FROM auth_privilege WHERE PRIVILEGE_KEY='",PRIVILEGE_LAYER_CODE_DESCRIPTION,"'),'",PRIVILEGE_ICON,"','",PRIVILEGE_URL,"','",PRIVILEGE_STATUS,"' FROM DUAL);" ) AS psql FROM auth_privilege WHERE application_id=2 AND privilege_key LIKE 'ITIM.PERSONNEL%' ORDER BY privilege_layer_code;