MySQL中UPDATE語句使用的實(shí)例教程
一、UPDATE常見用法
首先建立測試環(huán)境：
DROP TABLE IF EXISTS t_test; CREATE TABLE t_test ( bs bigint(20) NOT NULL auto_increment, username varchar(20) NOT NULL, password varchar(20) default NULL, remark varchar(200) default NULL, PRIMARY KEY (bs) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk; INSERT INTO t_test VALUES (1,'lavasoft','123456',NULL); INSERT INTO t_test VALUES (2,'hello',NULL,NULL); INSERT INTO t_test VALUES (3,'haha',zz,tt);
1、set一個(gè)字段
在表t_test中設(shè)置第二條記錄（bs為2）的password為'***'。
update t_test t set t.password = '***' where t.bs = 2;
2、set多個(gè)字段
在表t_test中設(shè)置第一條記錄（bs為1）的password為'*'、remark為'*'。
update t_test t set t.password = '*', t.remark = '*' where t.bs = 1;
3、set null值
在表t_test中設(shè)置第三條記錄（bs為3）的password為null、remark為null。
update t_test t set t.password = null, t.remark = null where t.bs = 3;
這個(gè)是按照標(biāo)準(zhǔn)語法寫的，在不同的數(shù)據(jù)庫系統(tǒng)中，update還有更多的寫法，但是標(biāo)準(zhǔn)寫法都是支持的。以上三個(gè)例子為了說明情況，每次都更新一行。在實(shí)際中，可以通過where語句約束來控制更新行數(shù)。
二、UPDATE使用中的相關(guān)性能問題以及解決方法
UPDATE的功能是更新表中的數(shù)據(jù)。這的語法和INSERT的第二種用法相似。必須提供表名以及SET表達(dá)式，在后面可以加WHERE以限制更新的記錄范圍。
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, WHERE ;
如下面的語句將users表中id等于123的記錄的age改為24
UPDATE users SET age = 24 WHERE id = 123;
同樣，可以使用UPDATE更新多個(gè)字段的值
UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
上面的UPDATE語句通過WHERE指定一個(gè)條件，否則，UPDATE將更新表中的所有記錄的值
百萬級(jí)別的數(shù)據(jù)，對于mysql應(yīng)該沒有問題。
你這個(gè)sql的問題是，相當(dāng)于修改ONE表里面所有記錄的AGE信息，而修改的過程是，對于每一條ONE里面的記錄，去TWO里面查詢，再修改。而且，期間很可能會(huì)有鎖之類的東西。
首先，這種sql不應(yīng)該出現(xiàn)在業(yè)務(wù)邏輯里面，而應(yīng)該是后臺(tái)的job里面。
如果一定要這么做，可以試著用相反的方式，如果不一樣的記錄不是特別多，那就找到ONE表里面AGE記錄跟TWO表不一樣的記錄，再修改, 例如大概象下面（可能語法不太對）：
update ONE，TWO set ONE.AGE=TWO.AGE where ONE.ID=TWO.ID AND ONE.AGE != TWO.AGE
當(dāng)我把數(shù)據(jù)調(diào)到了1000W就更新不了了，下面我來分析原因。
實(shí)例：需要根據(jù)用戶日志的ip地址計(jì)算出其地理地址
表結(jié)構(gòu)：
用戶日志表（200萬條記錄），其中address是待填充的字段：
CREATE TABLE `tmp_open_ip` ( `email` varchar(60) NOT NULL DEFAULT '', `address` varchar(50) NOT NULL DEFAULT '', `ip` int(10) unsigned NOT NULL DEFAULT '0', KEY `email` (`email`), KEY `ip` (`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
另ip地址數(shù)據(jù)庫表（44萬條記錄）
CREATE TABLE `ip` ( `s` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '開始ip', `e` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '結(jié)束ip', `a` varchar(50) NOT NULL DEFAULT '', KEY `s` (`s`), KEY `e` (`e`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
需要根據(jù)用戶日志表 tmp_open_ip 里的 ip字段到ip地址數(shù)據(jù)庫表里查詢出對應(yīng)的地理地址，將地址填充到address字段。
使用如下update語句執(zhí)行：
UPDATE tmp_open_ip AS u INNER JOIN ip ON u.ip BETWEEN ip.s AND ip.e SET u.address = ip.a
在筆者的電腦上運(yùn)行了速度非常之慢，執(zhí)行了一個(gè)多小時(shí)（4500s）都沒有完，也不知道還要多久。
實(shí)在看不過去，于是想到使用insert 是否會(huì)快一些，于是重新導(dǎo)一張表 tmp_open_log 與tmp_open_log完全一致。
創(chuàng)建一張表 tmp_open_address，是insert的目標(biāo)表，為了速度更快，沒建索引：
CREATE TABLE `tmp_open_address` ( `email` varchar(60) NOT NULL DEFAULT '', `address` varchar(50) NOT NULL DEFAULT '', `ip` int(10) unsigned NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8
執(zhí)行insert 語句
insert into tmp_open_address (email,address,ip) select l.email,ip.a,l.ip from tmp_open_log as l inner join ip on l.ip between ip.s and ip.e ; /* Affected rows: 2,543,124 Found rows: 0 Warnings: 0 Duration for 3 queries: 16.922 sec. */
不到17s！本來還想去倒杯水、稍事休息一下，結(jié)果已經(jīng)執(zhí)行完畢。
到本文寫完時(shí)，前面的update語句已經(jīng)執(zhí)行了5000s，結(jié)束仍是遙遙無期。
所以，對于大數(shù)據(jù)量執(zhí)行update時(shí)，可以考慮改用insert 語句實(shí)現(xiàn)，可能麻煩一些，但高速帶來的收益遠(yuǎn)大于麻煩！
后記：
直接殺死了update進(jìn)程，去看看update執(zhí)行了多少：運(yùn)行
SELECT * FROM `tmp_open_ip` where address!=''
結(jié)果只有 11,373 ，照這個(gè)速度，要運(yùn)行N天....
相關(guān)文章
本文主要介紹了MySQL表如何批量添加字段的方法，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
Linux中更改轉(zhuǎn)移mysql數(shù)據(jù)庫目錄的步驟
前幾天發(fā)現(xiàn)由于MySQL的數(shù)據(jù)庫太大，默認(rèn)安裝的/var盤已經(jīng)再也無法容納新增加的數(shù)據(jù)，只能想辦法轉(zhuǎn)移數(shù)據(jù)的目錄。網(wǎng)上有很多相關(guān)的文章寫到轉(zhuǎn)移數(shù)據(jù)庫目錄的文章，但轉(zhuǎn)載的過程中還會(huì)有一些錯(cuò)誤，因?yàn)榇蟛糠秩烁揪蜎]測試過，這篇文章是本文測試過整理好后分享給大家。2016-11-11
推薦沒有虛擬主機(jī)的小巧的Mysql數(shù)據(jù)庫備份腳本（PHP）
推薦沒有虛擬主機(jī)的小巧的Mysql數(shù)據(jù)庫備份腳本（PHP）...2007-07-07
這篇文章主要介紹了MySQL中UPDATE語句使用的實(shí)例教程,包括UPDATE的使用中所容易引起的性能問題的分析,需要的朋友可以參考下2015-11-11
MyBatis攔截器實(shí)現(xiàn)分頁功能的實(shí)現(xiàn)方法
這篇文章主要介紹了MyBatis攔截器實(shí)現(xiàn)分頁功能的實(shí)現(xiàn)方法的相關(guān)資料,希望通過本文大家能夠?qū)崿F(xiàn)這樣的方法，需要的朋友可以參考下2017-10-10
這篇文章主要介紹了mysql鎖定單個(gè)表的方法 ,需要的朋友可以參考下2014-05-05
MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始(sql語句)
這篇文章主要介紹了MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始，非常不錯(cuò)，具有參考借鑒價(jià)值,需要的朋友可以參考下2017-05-05
最近工作中用到了好幾次列轉(zhuǎn)行,索性做個(gè)小總結(jié),下面這篇文章主要給大家介紹了關(guān)于MYSQL如何列轉(zhuǎn)行的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
在MySQL數(shù)據(jù)庫中,經(jīng)常需要查詢某個(gè)時(shí)間段內(nèi)的數(shù)據(jù),所以本文給大家介紹了MySQL各個(gè)特殊時(shí)間段的查詢方法,并提供相應(yīng)的源代碼示例,具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2024-01-01
MySql中流程控制函數(shù)/統(tǒng)計(jì)函數(shù)/分組查詢用法解析
這篇文章主要介紹了MySql中流程控制函數(shù)/統(tǒng)計(jì)函數(shù)/分組查詢用法解析，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07