è¿åé®é¢
表ç»æå¦ä¸ï¼
-- è¡¨æ ¼å¼
CREATE TABLE `student_course` (
`id` bigint NOT NULL,
`student_id` bigint NOT NULL,
`course_id` bigint NOT NULL,
`xxx_id` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- 为äºä¿è¯æ°æ®çå¯ä¸æ§ï¼å»ºäºå¯ä¸ç´¢å¼ï¼
alter table student_course add unique index
ux_student_conrse_xxx(student_id,course_id,xxx_id);
第äºå¤©æ¥çæ°æ®ï¼åç°è¯¥è¡¨ä¸ç«ç¶äº§çäºéå¤çæ°æ®ï¼
ä»ä¸å¾ä¸çåºï¼mysqlçå¯ä¸æ§çº¦æçæäºï¼éå¤æ°æ®è¢«æ¦æªäºã
æ¥ä¸æ¥ï¼æ们åæå ¥ä¸¤æ¡xxx_id为nullçæ°æ®ï¼å ¶ä¸ç¬¬ä¸æ¡æ°æ®è·ç¬¬äºæ¡æ°æ®ä¸student_idãcourse_idåxxx_idå段å¼é½ä¸æ ·.
ä»å¾ä¸çåºï¼ç«ç¶æ§è¡æåäºã
æ¢å¥è¯è¯´ï¼å¦æå¯ä¸ç´¢å¼çå段ä¸ï¼åºç°äºnullå¼ï¼åå¯ä¸æ§çº¦æä¸ä¼çæã
æç»æå ¥çæ°æ®æ åµæ¯è¿æ ·çï¼
- å½xxx_idå段ä¸ä¸ºç©ºæ¶ï¼ä¸ä¼äº§çéå¤çæ°æ®ã
- å½xxx_idå段为空æ¶ï¼ä¼çæéå¤çæ°æ®ã
æ们éè¦ç¹å«æ³¨æï¼å建å¯ä¸ç´¢å¼çå段ï¼é½ä¸è½å 许为nullï¼å¦åmysqlçå¯ä¸æ§çº¦æå¯è½ä¼å¤±æã
é»è¾å é¤è¡¨å å¯ä¸ç´¢å¼
æ们é½ç¥éå¯ä¸ç´¢å¼é常ç®å好ç¨ï¼ä½ææ¶åï¼å¨è¡¨ä¸å®å¹¶ä¸å¥½å ãä¸ä¿¡ï¼æ们ä¸èµ·å¾ä¸çãé常æ åµä¸ï¼è¦å é¤è¡¨çææ¡è®°å½çè¯ï¼å¦æç¨deleteè¯å¥æä½çè¯ãä¾å¦ï¼
delete from product where id=123;
è¿ç§deleteæä½æ¯ç©çå é¤ï¼å³è¯¥è®°å½è¢«å é¤ä¹åï¼åç»éè¿sqlè¯å¥åºæ¬æ¥ä¸åºæ¥ãï¼ä¸è¿éè¿å ¶ä»ææ¯æ段å¯ä»¥æ¾åï¼é£æ¯åè¯äºï¼è¿æå¦å¤ä¸ç§æ¯é»è¾å é¤ï¼ä¸»è¦æ¯éè¿updateè¯å¥æä½çãä¾å¦ï¼
update product set delete_status=1,edit_time=now(3) where id=123;
é»è¾å é¤éè¦å¨è¡¨ä¸é¢å¤å¢å ä¸ä¸ªå é¤ç¶æå段ï¼ç¨äºè®°å½æ°æ®æ¯å¦è¢«å é¤ãå¨ææçä¸å¡æ¥è¯¢çå°æ¹ï¼é½éè¦è¿æ»¤æå·²ç»å é¤çæ°æ®ãéè¿è¿ç§æ¹å¼å é¤æ°æ®ä¹åï¼æ°æ®ä»»ç¶è¿å¨è¡¨ä¸ï¼åªæ¯ä»é»è¾ä¸è¿æ»¤äºå é¤ç¶æçæ°æ®èå·²ã
å ¶å®å¯¹äºè¿ç§é»è¾å é¤ç表ï¼æ¯æ²¡æ³å å¯ä¸ç´¢å¼çã为ä»ä¹å¢ï¼
å设ä¹åç»åå表ä¸çnameåmodelå äºå¯ä¸ç´¢å¼ï¼å¦æç¨æ·æææ¡è®°å½å é¤äºï¼delete_status设置æ1äºãåæ¥ï¼è¯¥ç¨æ·åç°ä¸å¯¹ï¼åéæ°æ·»å äºä¸æ¨¡ä¸æ ·çååãç±äºå¯ä¸ç´¢å¼çåå¨ï¼è¯¥ç¨æ·ç¬¬äºæ¬¡æ·»å ååä¼å¤±è´¥ï¼å³ä½¿è¯¥ååå·²ç»è¢«å é¤äºï¼ä¹æ²¡æ³åæ·»å äºãè¿ä¸ªé®é¢æ¾ç¶æç¹ä¸¥éã
æ人å¯è½ä¼è¯´ï¼ænameãmodelådelete_statusä¸ä¸ªå段åæ¶åæå¯ä¸ç´¢å¼ä¸å°±è¡äºï¼
çï¼è¿æ ·åç¡®å®å¯ä»¥è§£å³ç¨æ·é»è¾å é¤äºæ个ååï¼åæ¥åéæ°æ·»å ç¸åçååæ¶ï¼æ·»å ä¸äºçé®é¢ãä½å¦æ第äºæ¬¡æ·»å çååï¼å被å é¤äºã该ç¨æ·ç¬¬ä¸æ¬¡æ·»å ç¸åçååï¼ä¸ä¹åºç°é®é¢äºï¼ç±æ¤å¯è§ï¼å¦æ表ä¸æé»è¾å é¤åè½ï¼æ¯ä¸æ¹ä¾¿å建å¯ä¸ç´¢å¼çã
ä½å¦æççæ³ç»å å«é»è¾å é¤ç表ï¼å¢å å¯ä¸ç´¢å¼ï¼è¯¥æä¹åå¢ï¼
å é¤ç¶æ+1
éè¿åé¢ç¥éï¼å¦æ表ä¸æé»è¾å é¤åè½ï¼æ¯ä¸æ¹ä¾¿å建å¯ä¸ç´¢å¼çãå ¶æ ¹æ¬åå æ¯ï¼è®°å½è¢«å é¤ä¹åï¼delete_statusä¼è¢«è®¾ç½®æ1ï¼é»è®¤æ¯0ãç¸åçè®°å½ç¬¬äºæ¬¡å é¤çæ¶åï¼delete_status被设置æ1ï¼ä½ç±äºå建äºå¯ä¸ç´¢å¼ï¼ænameãmodelådelete_statusä¸ä¸ªå段åæ¶åæå¯ä¸ç´¢å¼ï¼ï¼æ°æ®åºä¸å·²åå¨delete_status为1çè®°å½ï¼æ以è¿æ¬¡ä¼æä½å¤±è´¥ã
æ们为å¥ä¸æ¢ä¸ç§æèï¼ä¸è¦çº ç»äºdelete_status为1ï¼è¡¨ç¤ºå é¤ï¼å½delete_status为1ã2ã3ççï¼åªè¦å¤§äº1é½è¡¨ç¤ºå é¤ãè¿æ ·çè¯ï¼æ¯æ¬¡å é¤é½è·åé£æ¡ç¸åè®°å½çæ大å é¤ç¶æï¼ç¶åå 1ã
è¿æ ·æ°æ®æä½è¿ç¨åæï¼
æ·»å è®°å½aï¼delete_status=0ã
å é¤è®°å½aï¼delete_status=1ã
æ·»å è®°å½aï¼delete_status=0ã
å é¤è®°å½aï¼delete_status=2ã
æ·»å è®°å½aï¼delete_status=0ã
å é¤è®°å½aï¼delete_status=3ã
ç±äºè®°å½aï¼æ¯æ¬¡å é¤æ¶ï¼delete_statusé½ä¸ä¸æ ·ï¼æ以å¯ä»¥ä¿è¯å¯ä¸æ§ã该æ¹æ¡çä¼ç¹æ¯ï¼ä¸ç¨è°æ´å段ï¼é常ç®ååç´æ¥ã
缺ç¹æ¯ï¼å¯è½éè¦ä¿®æ¹sqlé»è¾ï¼ç¹å«æ¯æäºæ¥è¯¢sqlè¯å¥ï¼æäºä½¿ç¨delete_status=1å¤æå é¤ç¶æçï¼éè¦æ¹ædelete_status>=1ã
å¢å æ¶é´æ³å段
导è´é»è¾å é¤è¡¨ï¼ä¸å¥½å å¯ä¸ç´¢å¼ææ ¹æ¬çå°æ¹å¨é»è¾å é¤é£éãæ们为ä»ä¹ä¸å 个å段ï¼ä¸é¨å¤çé»è¾å é¤çåè½å¢ï¼
çï¼å¯ä»¥å¢å æ¶é´æ³å段ã
ænameãmodelãdelete_statusåtimeStampï¼å个å段åæ¶åæå¯ä¸ç´¢å¼å¨æ·»å æ°æ®æ¶ï¼timeStampå段åå ¥é»è®¤å¼1ã
ç¶åä¸æ¦æé»è¾å é¤æä½ï¼åèªå¨å¾è¯¥å段åå ¥æ¶é´æ³ãè¿æ ·å³ä½¿æ¯åä¸æ¡è®°å½ï¼é»è¾å é¤å¤æ¬¡ï¼æ¯æ¬¡çæçæ¶é´æ³ä¹ä¸ä¸æ ·ï¼ä¹è½ä¿è¯æ°æ®çå¯ä¸æ§ã
æ¶é´æ³ä¸è¬ç²¾ç¡®å°ç§ãé¤éå¨é£ç§æé并åçåºæ¯ä¸ï¼å¯¹åä¸æ¡è®°å½ï¼ä¸¤æ¬¡ä¸åçé»è¾å é¤æä½ï¼äº§çäºç¸åçæ¶é´æ³ãè¿æ¶å¯ä»¥å°æ¶é´æ³ç²¾ç¡®å°æ¯«ç§ã
该æ¹æ¡çä¼ç¹æ¯ï¼å¯ä»¥å¨ä¸æ¹åå·²æ代ç é»è¾çåºç¡ä¸ï¼éè¿å¢å æ°å段å®ç°äºæ°æ®çå¯ä¸æ§ã缺ç¹æ¯ï¼å¨æéçæ åµä¸ï¼å¯è½è¿æ¯ä¼äº§çéå¤æ°æ®ã
å¢å idå段
å ¶å®ï¼å¢å æ¶é´æ³å段åºæ¬å¯ä»¥è§£å³é®é¢ãä½å¨å¨æéçæ åµä¸ï¼å¯è½è¿æ¯ä¼äº§çéå¤æ°æ®ãæ没æåæ³è§£å³è¿ä¸ªé®é¢å¢ï¼
çï¼å¢å 主é®å段ï¼delete_idã
该æ¹æ¡çæè·¯è·å¢å æ¶é´æ³å段ä¸è´ï¼å³å¨æ·»å æ°æ®æ¶ç»delete_id设置é»è®¤å¼1ï¼ç¶åå¨é»è¾å é¤æ¶ï¼ç»delete_idèµå¼æå½åè®°å½ç主é®idã
ænameãmodelãdelete_statusådelete_idï¼å个å段åæ¶åæå¯ä¸ç´¢å¼ãè¿å¯è½æ¯æä¼æ¹æ¡ï¼æ éä¿®æ¹å·²æå é¤é»è¾ï¼ä¹è½ä¿è¯æ°æ®çå¯ä¸æ§ã
éå¤åå²æ°æ®å¦ä½å å¯ä¸ç´¢å¼ï¼
åé¢èè¿å¦æ表ä¸æé»è¾å é¤åè½ï¼ä¸å¤ªå¥½å å¯ä¸ç´¢å¼ï¼ä½éè¿æä¸ä»ç»çä¸ç§æ¹æ¡ï¼å¯ä»¥é¡ºå©çå ä¸å¯ä¸ç´¢å¼ã
ä½æ¥èªçµéçä¸é®ï¼å¦ææå¼ è¡¨ä¸ï¼å·²åå¨åå²éå¤æ°æ®ï¼è¯¥å¦ä½å ç´¢å¼å¢ï¼æç®åçåæ³æ¯ï¼å¢å ä¸å¼ é²é表ï¼ç¶åææ°æ®åå§åè¿å»ã
å¯ä»¥åä¸æ¡ç±»ä¼¼è¿æ ·çSQLï¼
insert into product_unqiue(id,name,category_id,unit_id,model)
select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;
è¿æ ·åå¯ä»¥æ¯å¯ä»¥ï¼ä½ä»å¤©ç主é¢æ¯ç´æ¥å¨å表ä¸å å¯ä¸ç´¢å¼ï¼ä¸ç¨é²é表ãé£ä¹ï¼è¿ä¸ªå¯ä¸ç´¢å¼è¯¥æä¹å å¢ï¼å ¶å®å¯ä»¥åé´ä¸ä¸èä¸ï¼å¢å idå段çæè·¯ãå¢å ä¸ä¸ªdelete_idå段ãä¸è¿å¨ç»product表å建å¯ä¸ç´¢å¼ä¹åï¼å è¦åæ°æ®å¤çãè·åç¸åè®°å½çæ大idï¼
select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;
ç¶åå°delete_idå段设置æ1ãç¶åå°å ¶ä»çç¸åè®°å½çdelete_idå段ï¼è®¾ç½®æå½åç主é®ãè¿æ ·å°±è½åºååå²çéå¤æ°æ®äºãå½ææçdelete_idå段é½è®¾ç½®äºå¼ä¹åï¼å°±è½ç»nameãmodelãdelete_statusådelete_idï¼å个å段å å¯ä¸ç´¢å¼äºãå®ç¾ã