mysql 鑾峰彇鍏ㄥ眬鍞竴鍊_mysql

聽 聽 聽 聽 鍦ㄦ秹鍙婃暟鎹簱瀛樺偍鏁版嵁鐨勬椂鍊欙紝缁忓父浼氶亣鍒板敮涓€鍊奸棶棰橈紝鏈夌殑鏄富閿甫鏉ョ殑闄愬埗锛屾湁鐨勫垯鏄笟鍔′笂鐨勯渶瑕併€?/p>

聽 聽 聽 聽 涓嬮潰浠嬬粛鍑犵鍞竴鍊肩殑鑾峰彇鎴栬€呯敓浜ф柟娉曪細

銆€銆€鍏堝缓涓€涓祴璇曠敤鐨勮〃tbl_user,鏈変笁涓瓧娈碉細Id銆丯ame銆丄ge锛屽叾涓璉d涓轰富閿€?/p>

drop table if exists `tbl_user`;  create table `tbl_user` (    `Id` int(10),    `Name` varchar(20),    `Age` int(10),    PRIMARY KEY  (`Id`) )DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

聽 聽 聽 聽聽鎻掑叆鍑犳潯鏁版嵁

insert into tbl_user values (1000,"灏忕尗",22); insert into tbl_user values (1001,"灏忕嫍",22);  insert into tbl_user values (1002,"灏忓埡鐚?,22);  

聽 聽 聽 聽 鏌ヨ缁撴灉锛?/p>

聽 聽 聽 聽 1.鐢卞簲鐢ㄧ▼搴忔牴鎹竴瀹氱畻娉曠敓鎴愬敮涓€鍊硷細涓€鑸噰鐢ㄢ€滿D5(鏃堕棿鎴?闅忔満鏁?鈥滄垨鑰呭叾浠栫殑UUID绠楁硶锛屽熀鏈篃姣旇緝濂藉疄鐜般€傚鏋滈亣鍒板鏈哄櫒涓婂垎甯冪殑绋嬪簭璁块棶缁熶竴鏁版嵁搴撶殑琛紝鍙互鎶奍p銆佺綉鍗″彿绛変俊鎭€冭繘鏉ュ氨鍙互瑙ｅ喅浜嗭紙褰撶劧鍙互涓嶆槸绠€鍗曠殑鎷兼帴锛屼綘鍙互鏍规嵁闇€瑕佸幓鍚堥€傜殑浣嶆暟缁忚繃涓€瀹氱殑绠楁硶鍘昏幏鍙栵級銆?/p>

聽 聽 聽 聽 2.鍏堟煡璇㈣〃涓渶澶х殑鍊約elect max(id)锛屽啀鍔?鍚庝綔涓烘柊鐨勫€笺€傚緢绗ㄧ殑鏂规硶銆?/p>

select max(Id) from tbl_user;  #鏌ヨ鍒扮殑鏈€澶d涓?1002,涔嬪悗鎻掑叆 1003  insert into tbl_user values (1003,"灏忕唺",22);

聽 聽 聽 聽 姝ゆ椂琛ㄤ腑鏁版嵁涓?/p>

聽 聽 聽 聽 3.濡傛灉鏄〃绾у埆鐨勫敮涓€锛屽嵆鍦ㄥ悓涓€涓〃涓煇涓瓧娈靛敮涓€锛屽彲浠ユ妸璇ュ瓧娈佃缃负鈥滆嚜澧?AUTO_INCREMENT)鈥濈殑銆傝繖鏍蜂綘涓嶅繀璐瑰績鎬濆幓鐢熸垚杩欎釜涓嶈兘閲嶅鐨勫敮涓€鍊间簡銆備絾鏄竴鑸簲鐢ㄧ▼搴忔槸闇€瑕佽繖涓敮涓€鍊肩殑锛岃繖涓椂鍊欎綘灏卞緱鍦ㄦ煡璇竴娆″幓鑾峰彇鍒氭墠鏁版嵁搴撹嚜澧炵敓鎴愮殑Id銆傛瘮濡傚湪鐢ㄦ埛鐧诲綍鐨勬椂鍊欙紝浣犺鐢熸垚涓€涓櫥褰曚細璇滻d鎴栬€匱oken锛岃繖浜涚▼搴忎竴鑸槸闇€瑕佸緱鍒拌繖涓€艰€屼笉鏄粎浠呭瓨鍦ㄦ暟鎹簱涓€傜敓鎴愮殑鍊硷紝a.鍙互涓€鑸殑select鏉′欢鏌ヨ锛屾牴鎹潯浠舵煡璇㈠垰鎵嶆彃鍏ョ殑鏁版嵁銆俠.鐩存帴璋冪敤select @@IDENTITY 灏卞彲浠ュ緱鍒颁笂涓€娆℃彃鍏ヨ褰曟椂鑷姩浜х敓鐨処D锛堟敞鎰忔槸鍦ㄦ暟鎹簱鍚屼竴涓繛鎺ワ紙浼氳瘽锛変腑锛夛紝鐢ㄥ湪鎻掑叆鍚庣珛鍗硈elect @@IDENTITY銆?/p>

聽 聽 聽 聽 鐪嬩緥瀛愶紝鍏堝皢琛ㄤ腑鐨処d瀛楁璁剧疆涓鸿嚜澧烇紝鍐嶆彃鍏ヤ竴鏉℃暟鎹紙涓嶈鎻掑叆Id鍊硷紝璁╂暟鎹簱鑷寰楀埌鍊硷級锛宻elect @@IDENTITY鏌ヨ锛屾渶鍚庨獙璇佺湅鐪嬨€?/p>

#1.#灏咺d鏀逛负鑷锛坅uto_increment锛? ALTER TABLE tbl_user CHANGE Id Id int not null auto_increment;  #鎴栬€?鍏堝垹闄d瀛楁鍐嶆坊鍔犱竴涓狪d瀛楁 alter table tbl_user auto_increment=1000; alter table tbl_user drop column Id; alter table tbl_user add Id int not null auto_increment primary key first;  #2.鎻掑叆涓€鏉¤褰? insert tbl_user set Name='灏忕尨',Age=23;  #3.鏌ヨ鍒氭墠鐨勮嚜澧濱d鍊? select @@IDENTITY; #鍊兼槸1004

聽 聽 聽 聽聽楠岃瘉涓嬶細select * from tbl_user;寰楀埌鐨勫綋鍓嶈〃璁板綍涓?/p>

聽 聽 聽 聽聽杩囧垰鎻掑叆鐨勬暟鎹€滃皬鐚粹€漣d涓?004锛屽拰select @@IDENTITY;缁撴灉涓€鏍枫€?/p>

聽 聽 聽 聽 4.浣跨敤mysql鐨?UUID()鍑芥暟銆傚墠闈㈢殑鑷瀛楁锛坅uto_increment锛夊彧鑳界敓鎴愨€濊〃鍐呪€濈殑鍞竴鍊硷紝涓旈渶瑕佹惌閰嶄娇鍏朵负鈥濆敮涓€鐨勪富閿垨鍞竴绱㈠紩鈥濓紝瀹冪殑鍊兼槸閫愭澧為暱鐨勩€傝繖閲岀殑UUID浜х敓鐨勬槸瀛楃涓茬被鍨嬪€硷紝鍥哄畾闀垮害涓?36涓瓧绗︺€俇UID鐢熸垚鐨勬槸鍦ㄦ椂闂淬€佺┖闂翠笂閮界嫭涓€鏃犱簩鐨勫€硷紝鏄€滈殢鏈?瑙勫垯鈥濈粍鍚堣€屾垚銆?/p>

select uuid(); -- c725d905-388e-11e6-9cab-b8ca3a6f5881  select uuid(); -- cb7d3894-388e-11e6-9cab-b8ca3a6f5881  select replace(uuid(),'-','');  -- eb1347a2388e11e69cabb8ca3a6f5881

聽 聽 聽 聽聽鍙互鐪嬪埌锛屽娆¤皟鐢║UID()鍑芥暟寰楀埌鐨勫€间笉鐩稿悓锛屽畠鐢变簲閮ㄥ垎缁勬垚锛屽苟涓旀湁杩炲瓧绗︼紙-锛夐殧寮€锛屼竴鍏?6涓瓧绗︺€傚叾涓細

聽 聽 聽 聽 鍓?缁勫€兼槸鏃堕棿鎴虫崲绠楄繃鏉ョ殑锛岃В鍐斥€滄椂闂翠笂鍞竴鈥濓紱

聽 聽 聽 聽 绗?缁勫€兼槸鏆傛椂鎬т繚鎸佹椂闂存埑鐨勫敮涓€鎬э紝閲嶅惎mysql鎵嶄細鍙樺姩锛?/p>

聽 聽 聽 聽 绗?缁勬槸mac鍊艰浆杩囨潵鐨勶紝鏈夊姪浜庤В鍐斥€滅┖闂翠笂鐨勫敮涓€鈥濓紝鍚屼竴涓満鍣ㄥ瀹炰緥鐨勪竴鑸浉鍚屻€傚鏋渕ac鍊艰幏鍙栦笉鍒帮紝鍒欐槸涓€涓殢鏈哄€笺€?/p>

聽 聽 聽 聽 杩欎簺宸茬粡鍙互淇濊瘉寰楀埌鐨勫€煎湪鏃堕棿鍜岀┖闂翠笂鐨勫敮涓€銆傚綋鐒朵綘涔熷彲浠ュ幓鎺夎繛瀛楃: select replace(uuid(),’-‘,”)銆?/p>

聽 聽 聽 聽 鍦∕ySQL 5.1.*鍙婃洿楂樼増鏈湁涓€涓彉绉嶇殑UUID()鍑芥暟锛孶UID_SHORT()锛屽彲浠ョ敓鎴愪竴涓?7-64浣嶆棤绗﹀彿鐨勬暣鏁帮紝娉ㄦ剰鏄敓鎴愮殑涓€涓暣鏁帮紝鑰屽墠闈UID()鐢熸垚鐨勬槸瀛楃涓层€侻ySQL鍚姩鍚庣涓€娆℃墽琛岀殑鍊兼槸閫氳繃鏃堕棿鎴崇瓑鍒濆鍖栬繖涓€硷紝鍦ㄦ湰娆¤繍琛屼腑鍐嶆璋冪敤鐨勬椂鍊欓兘鍔?銆傝繖涓€间竴鑸瘮杈冨ぇ锛屽彲浠ヨ皟鐢╮ight(UUID_SHORT(),9)鍙栧悗闈㈢殑鑻ュ共浣嶃€傛垨鑰?浣犺繕鍙互鍐欐垚鑷畾涔夊嚱鏁?鏉ユ寜闇€鐢熸垚杩欎釜鍊笺€備妇涓緥瀛愶細

#1.璋冪敤uuid_short()鍑芥暟 SELECT UUID_SHORT(); -- 17246045196806782976 SELECT UUID_SHORT(); -- 17246045196806782977  #鎵ц涓ゆ寰楀埌鐨勫€奸€掑鐨勶細 23285634974089217  #2.鍒涘缓涓€涓嚜瀹氫箟鍑芥暟锛屾寜闇€鑾峰彇鍞竴鍊硷細 delimiter // CREATE FUNCTION `GetUuidTest`(SysId int) RETURNS int(10) DETERMINISTIC begin     declare tmpID  int;                 set tmpID = 0;                SELECT concat(SysId,right(UUID_SHORT(),8)) into  tmpID;#SysId鍜孶UID_SHORT()鍚?浣嶆暟鎷兼帴寰楀埌      return tmpID; end; //  #3.璋冪敤鑷畾涔夌殑鍑芥暟GetUuidTest(int)鍑芥暟: select GetUuidTest(1); select GetUuidTest(1); select GetUuidTest(2); select GetUuidTest(2);  #寰楀埌缁撴灉: #1+uuid_short()鍚?浣嶏紙74089233锛夌粍鎴? #1+uuid_short()鍚?浣嶏紙74089234锛夌粍鎴? #2+uuid_short()鍚?浣嶏紙74089235锛夌粍鎴? #3+uuid_short()鍚?浣嶏紙74089236锛夌粍鎴? #uuid_short()鍊奸€掑锛屽墠闈㈠湪鍔犱竴涓狪d锛屼笉鍚岀殑鏈嶅姟鍣↖dSysId涓嶅悓銆?  #4.鍦ㄤ緥瀛愪腑璋冪敤鑷畾涔夊嚱鏁癎etUuidTest(int)  鏉ユ彃鍏ヨ褰曪細杩欐椂鍊欎笉闇€瑕佹妸Id璁剧疆涓鸿嚜澧炰簡銆? insert tbl_user set Id=GetUuidTest(1),Name='灏忕唺鐚?,Age=22; insert tbl_user set Id=GetUuidTest(2),Name='灏忛腑瀛?,Age=21;

聽 聽 聽 聽 渚嬪瓙涓紝select * from tbl_user;寰楀埌鐨勬墍鏈夎褰曚负渚嬪瓙涓紝select * from tbl_user;寰楀埌鐨勬墍鏈夎褰曚负

聽

鏂囩珷鏉ユ簮锛?a href=”http://www.cnblogs.com/alylee/p/Mysql_UUID.html” target=”_blank” title=”http://www.cnblogs.com/alylee/p/Mysql_UUID.html”>http://www.cnblogs.com/alylee/p/Mysql_UUID.html