软件开发培训班 >> 编程开发 >> MySQL


MySQL几种更新操作的案例分析

鏈枃灏嗛�氳繃涓�涓?鐢ㄦ埛璐︽埛閲戦鏇存柊鐨勬渚?鍒嗘瀽鍑犵鏁版嵁鏇存柊鐨勬搷浣滅殑浼樺姡銆傚笇鏈涘澶у鏈夊府鍔?🐶銆?br />

鏁版嵁搴撶増鏈?: mysql 5.7.23

妗堜緥鍒嗘瀽

鍒涘缓鏁版嵁搴撶殑DDL锛?br /> 

CREATE TABLE `hw_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

鏇存柊璐︽埛閲戦

鐩存帴鏇存柊

鏂规 1 鏌ヨ鍚庢洿鏂?br /> 

# 鏁版嵁鏌ヨ
select * from hw_account where id = 1;

# 鏁版嵁鏇存柊
update hw_account set balance = 5 where id = 1;

瀛樺湪鐨勯棶棰橈紝灏辨槸鍒嗕袱娆℃搷浣滐紝濡傛灉骞跺彂鎵ц鐨勬椂鍊欙紝鍙兘閫犳垚鏇存柊涓㈠け鐨勯棶棰?

涔愯閿佹柟妗?br />

鍒╃敤鐗堟湰鍙锋搷浣滐紝鍗冲鏁版嵁搴撳鍔犱箰瑙傞攣鐨勬柟寮忚繘琛屻�?br /> 

# 鏁版嵁鏌ヨ
select * from hw_account where id = 1;

# 鏁版嵁鏇存柊
update hw_account set balance = 5 , version = version + 1 
  where id = 1 and version = n;
  
# 鍒ゆ柇鏄惁鎴愬姛  
if row < 1 {
   鍥炴粴
}

瀛樺湪鐨勯棶棰橈紝濡傛灉璇ユ潯鏁版嵁骞跺彂鎿嶄綔鐨勬椂鍊欙紝浼氬鑷村叾浠栫殑璇锋眰澶辫触銆傚鏋滆繖涓姹傜殑鍓嶇疆閾捐矾姣旇緝闀跨殑璇濓紝 鍥炴粴鎴愭湰姣旇緝楂樸�?br />

鏃犻攣鏂规

涓嶇敤鏌ヨ锛岄噰鐢ㄦ暟鎹簱鐨勮绠楋紝涔熶笉闇�瑕佺増鏈彿鐨勬搷浣滐紝鐩存帴閫氳繃鍩熷�艰繘琛屾湁鏁堟�у垽鏂�傚叿浣撶殑 SQL 濡備笅锛?br /> 

# 鏁版嵁鏇存柊
update hw_account set balance = balance + @change_num , version = version + 1 
  where id = 1 and version = n;
  
# 鍒ゆ柇鏄惁鎴愬姛  
if row < 1 {
   鍥炴粴
}

杩欑鏂规淇敼姣旇緝绠�鍗曪紝 浣嗘槸渚濊禆浜庢暟鎹绠楋紝鎰熻涓嶆槸鐗瑰埆鍙嬪ソ銆?br />

鎺掗槦鎿嶄綔

閫氳繃 redis 鎴栬�?zk 鐨勫垎甯冨紡閿侊紝杩涜鏁版嵁璇锋眰杩涜鎺掗槦銆傜劧鍚庡湪杩涜鏁版嵁鏇存柊銆?br /> 

# 浼唬鐮?

if (鑾峰彇鍒嗗竷寮忛攣) {
  update hw_account set balance = @balance where id = 1;
} else {
  # 杩涘叆绛夊緟锛屾垨鑰呰繘琛岃嚜鏃嬭幏鍙栭攣
}

甯歌闂

濡傛灉鏁版嵁涓瓨鍦?update_time 瀛楁鍙楀奖鍝嶇殑琛屾暟鏄灏戯紵

update_time 鐨勫瓧娈靛畾涔夊涓嬶紝濡傛灉鏁版嵁涓篿d = 1, status = 1 濡傛灉鎵ц鏇存柊鏁版嵁鐨?sql 涓?br /> 

update hw_account set `status` = 1 where id = 1;

杩斿洖鐨勫彈褰卞搷鐨勮鏁颁负 0锛?br />

濡傛灉鎵ц update 鏇存柊浣嗗彈褰卞搷鐨勮鏁颁负 0 浼氬姞琛岄攣鍚楋紵

浼氱殑锛?鎵ц鏇存柊鐨勮鍙ラ兘浼氬姞琛岄攣锛堝墠鎻愶紝浜嬪姟鍐咃級

鍙傝�冭祫鏂?/h2>

mysql.com

鍒版杩欑瘒鍏充簬MySQL鍑犵鏇存柊鎿嶄綔鐨勬渚嬪垎鏋愮殑鏂囩珷灏变粙缁嶅埌杩欎簡,鏇村鐩稿叧MySQL 鏇存柊鎿嶄綔鍐呭璇锋悳绱㈣剼鏈箣瀹朵互鍓嶇殑鏂囩珷鎴栫户缁祻瑙堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у浠ュ悗澶氬鏀寔鑴氭湰涔嬪锛?/p>