Chapter聽11.聽Column Types - MySQL 5.1参考手册中文版
鐩綍
MySQL鏀寔澶氱鍒楃被鍨嬶細鏁板肩被鍨嬨佹棩鏈/鏃堕棿绫诲瀷鍜屽瓧绗︿覆(瀛楃)绫诲瀷銆傛湰绔犻鍏堝杩欎簺鍒楃被鍨嬭繘琛屼簡姒傝堪锛岀劧鍚庢洿鍔犺缁嗗湴鎻忚堪浜嗗悇绉嶅垪鐨勭被鍨嬶紝浠ュ強鍒楃被鍨嬪瓨鍌ㄩ渶姹傜殑鎬荤粨銆傛杩板緢绠鍗曘傚叧浜庡叿浣撳垪绫诲瀷鐨勮缁嗕俊鎭簲鏌ラ槄璇︾粏鐨勬弿杩帮紝渚嬪鎸囧畾鍊兼椂鍏佽浣跨敤鐨勬牸寮忋
MySQL鏀寔澶勭悊绌洪棿鏁版嵁鐨勬墿灞曞悕銆傚叧浜庣┖闂寸被鍨嬬殑淇℃伅鍙傝绗19绔狅細MySQL涓殑绌洪棿鎵╁睍銆
鍑犵鍒楃被鍨嬫弿杩颁娇鐢ㄤ簡涓嬭堪鎯緥锛
路 M
琛ㄧず鏈澶ф樉绀哄搴︺傛渶澶ф湁鏁堟樉绀哄搴︽槸255銆
路 D
閫傜敤浜庢诞鐐瑰拰瀹氱偣绫诲瀷锛屽苟琛ㄧず灏忔暟鐐瑰悗闈㈢殑浣嶆暟銆傛渶澶у彲鑳界殑鍊兼槸30锛屼絾涓嶅簲澶т簬M-2銆
路 鏂规嫭鍙(鈥[鈥欏拰鈥]鈥)琛ㄧず鍙夐儴鍒嗐
涓嬮潰涓烘暟鍊煎垪绫诲瀷鐨勬杩般傝缁嗕俊鎭弬瑙11.2鑺傦紝鈥滄暟鍊肩被鍨嬧銆傚垪瀛樺偍闇姹傚弬瑙11.5鑺傦紝鈥滃垪绫诲瀷瀛樺偍闇姹傗銆
M鎸囩ず鏈澶ф樉绀哄搴︺傛渶澶ф湁鏁堟樉绀哄搴︽槸255銆傛樉绀哄搴︿笌瀛樺偍澶у皬鎴栫被鍨嬪寘鍚殑鍊肩殑鑼冨洿鏃犲叧锛岀浉鍏虫弿杩拌11.2鑺傦紝鈥滄暟鍊肩被鍨嬧銆
濡傛灉涓轰竴涓暟鍊煎垪鎸囧畾ZEROFILL锛MySQL鑷姩涓鸿鍒楁坊鍔UNSIGNED灞炴с
SERIAL鏄BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE鐨勪竴涓埆鍚嶃
鍦ㄦ暣鏁板垪瀹氫箟涓紝SERIAL DEFAULT VALUE鏄NOT NULL AUTO_INCREMENT UNIQUE鐨勪竴涓埆鍚嶃
璀﹀憡锛搴斿綋娓呮锛屽綋浣跨敤鍦ㄦ暣鏁板硷紙鍏朵腑涓涓槸UNSIGNED绫诲瀷锛変箣闂翠娇鐢ㄥ噺鍙锋椂锛岀粨鏋滄槸鏃犵鍙枫傚弬瑙12.8鑺傦紝鈥淐ast鍑芥暟鍜屾搷浣滅鈥銆
路 BIT[(M)]
浣嶅瓧娈电被鍨嬨M琛ㄧず姣忎釜鍊肩殑浣嶆暟锛岃寖鍥翠负浠1鍒64銆傚鏋M琚渷鐣ワ紝 榛樿涓1銆
路 TINYINT[(M)] [UNSIGNED] [ZEROFILL]
寰堝皬鐨勬暣鏁般傚甫绗﹀彿鐨勮寖鍥存槸-128鍒127銆傛棤绗﹀彿鐨勮寖鍥存槸0鍒255銆
路 BOOL锛BOOLEAN
鏄TINYINT(1)鐨勫悓涔夎瘝銆zero鍊艰瑙嗕负鍋囥傞潪zero鍊艰涓虹湡銆
鍦ㄥ皢鏉ワ紝灏嗘牴鎹爣鍑SQL寮曞叆瀹屽叏甯冨皵绫诲瀷鐨勫鐞嗐
路 SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
灏忕殑鏁存暟銆傚甫绗﹀彿鐨勮寖鍥存槸-32768鍒32767銆傛棤绗﹀彿鐨勮寖鍥存槸0鍒65535銆
路 MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
涓瓑澶у皬鐨勬暣鏁般傚甫绗﹀彿鐨勮寖鍥存槸-8388608鍒8388607銆傛棤绗﹀彿鐨勮寖鍥存槸0鍒16777215銆
路 INT[(M)] [UNSIGNED] [ZEROFILL]
鏅氬ぇ灏忕殑鏁存暟銆傚甫绗﹀彿鐨勮寖鍥存槸-2147483648鍒2147483647銆傛棤绗﹀彿鐨勮寖鍥存槸0鍒4294967295銆
路 INTEGER[(M)] [UNSIGNED] [ZEROFILL]
杩欐槸INT鐨勫悓涔夎瘝銆
路 BIGINT[(M)] [UNSIGNED] [ZEROFILL]
澶ф暣鏁般傚甫绗﹀彿鐨勮寖鍥存槸-9223372036854775808鍒9223372036854775807銆傛棤绗﹀彿鐨勮寖鍥存槸0鍒18446744073709551615銆
搴旀竻妤BIGINT鍒楃殑涓嬭堪鍐呭锛
o 浣跨敤甯︾鍙风殑BIGINT鎴DOUBLE鍊艰繘琛屾墍鏈夌畻娉曪紝鍥犳闄や簡浣嶅嚱鏁帮紝涓嶅簲浣跨敤澶т簬9223372036854775807(63浣)鐨勬棤绗﹀彿鐨勫ぇ鏁存暟! 濡傛灉杩欐牱鍋氾紝缁撴灉涓殑鏈鍚庡嚑浣嶅彲鑳藉嚭閿欙紝杩欐槸鐢变簬灏BIGINT鍊艰浆鎹负DOUBLE杩涜鍥涜垗浜斿叆鏃堕犳垚鐨勯敊璇
MySQL鍙互鍦ㄤ互涓嬫儏鍐典笅澶勭悊BIGINT锛
搂 褰撲娇鐢ㄦ暣鏁板湪涓涓BIGINT鍒椾繚瀛樺ぇ鐨勬棤绗﹀彿鐨勫兼椂銆
搂 鍦MIN(col_name)鎴MAX(col_name)涓紝鍏朵腑col_name鎸BIGINT鍒椼
搂 浣跨敤鎿嶄綔绗(+锛-锛*绛夌瓑)骞朵笖涓や釜鎿嶄綔鏁板潎涓烘暣鏁版椂銆
o 鎬绘槸鍙互浣跨敤涓涓瓧绗︿覆鍦BIGINT鍒椾腑淇濆瓨涓ユ牸鏁存暟鍊笺傚湪杩欑鎯呭喌涓嬶紝MySQL鎵ц瀛楃涓-鏁板瓧杞崲锛屽叾闂翠笉瀛樺湪鍙岀簿搴﹁〃绀恒
o 褰撲袱涓搷浣滄暟鍧囦负鏁存暟鍊兼椂锛-銆+鍜* 鎿嶄綔绗︿娇鐢BIGINT绠楁硶銆傝繖璇存槑濡傛灉涔樹袱涓ぇ鏁存暟(鎴栨潵鑷繑鍥炴暣鏁扮殑鍑芥暟)锛屽綋缁撴灉澶т簬9223372036854775807鏃讹紝浼氬緱鍒版剰鎯充笉鍒扮殑缁撴灉銆
路 FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
灏(鍗曠簿搴)娴偣鏁般傚厑璁哥殑鍊兼槸-3.402823466E+38鍒-1.175494351E-38銆0鍜1.175494351E-38鍒3.402823466E+38銆傝繖浜涙槸鐞嗚闄愬埗锛屽熀浜IEEE鏍囧噯銆傚疄闄呯殑鑼冨洿鏍规嵁纭欢鎴栨搷浣滅郴缁熺殑涓嶅悓鍙兘绋嶅井灏忎簺銆
M鏄皬鏁扮旱浣嶆暟锛D鏄皬鏁扮偣鍚庨潰鐨勪綅鏁般傚鏋M鍜D琚渷鐣ワ紝鏍规嵁纭欢鍏佽鐨勯檺鍒舵潵淇濆瓨鍊笺傚崟绮惧害娴偣鏁扮簿纭埌澶х害7浣嶅皬鏁颁綅銆
濡傛灉鎸囧畾UNSIGNED锛屼笉鍏佽璐熷笺
浣跨敤娴偣鏁板彲鑳戒細閬囧埌鎰忔兂涓嶅埌鐨勯棶棰橈紝鍥犱负鍦MySQL涓殑鎵鏈夎绠楃敤鍙岀簿搴﹀畬鎴愩傚弬瑙A.5.7鑺傦紝鈥滆В鍐充笌涓嶅尮閰嶈鏈夊叧鐨勯棶棰樷銆
路 DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
鏅氬ぇ灏(鍙岀簿搴)娴偣鏁般傚厑璁哥殑鍊兼槸-1.7976931348623157E+308鍒-2.2250738585072014E-308銆0鍜2.2250738585072014E-308鍒 1.7976931348623157E+308銆傝繖浜涙槸鐞嗚闄愬埗锛屽熀浜IEEE鏍囧噯銆傚疄闄呯殑鑼冨洿鏍规嵁纭欢鎴栨搷浣滅郴缁熺殑涓嶅悓鍙兘绋嶅井灏忎簺銆
M鏄皬鏁版讳綅鏁帮紝D鏄皬鏁扮偣鍚庨潰鐨勪綅鏁般傚鏋M鍜D琚渷鐣ワ紝鏍规嵁纭欢鍏佽鐨勯檺鍒舵潵淇濆瓨鍊笺傚弻绮惧害娴偣鏁扮簿纭埌澶х害15浣嶅皬鏁颁綅銆
濡傛灉鎸囧畾UNSIGNED锛屼笉鍏佽璐熷笺
路 DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
涓DOUBLE鐨勫悓涔夎瘝銆傞櫎浜嗭細濡傛灉SQL鏈嶅姟鍣ㄦā寮忓寘鎷REAL_AS_FLOAT閫夐」锛REAL鏄FLOAT鐨勫悓涔夎瘝鑰屼笉鏄DOUBLE鐨勫悓涔夎瘝銆
路 FLOAT(p) [UNSIGNED] [ZEROFILL]
娴偣鏁般p琛ㄧず绮惧害锛堜互浣嶆暟琛ㄧず锛夛紝浣MySQL鍙娇鐢ㄨ鍊兼潵纭畾鏄惁缁撴灉鍒楃殑鏁版嵁绫诲瀷涓FLOAT鎴DOUBLE銆傚鏋p涓轰粠0鍒24锛屾暟鎹被鍨嬪彉涓烘病鏈M鎴D鍊肩殑FLOAT銆傚鏋p涓轰粠25鍒53锛屾暟鎹被鍨嬪彉涓烘病鏈M鎴D鍊肩殑DOUBLE銆傜粨鏋滃垪鑼冨洿涓庢湰鑺傚墠闈㈡弿杩扮殑鍗曠簿搴FLOAT鎴栧弻绮惧害DOUBLE鏁版嵁绫诲瀷鐩稿悓銆
FLOAT(p)璇硶涓ODBC鍏煎銆
路 DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
鍘嬬缉鐨勨涓ユ牸鈥濆畾鐐规暟銆M鏄皬鏁颁綅鏁(绮惧害)鐨勬绘暟锛D鏄皬鏁扮偣(鏍囧害)鍚庨潰鐨勪綅鏁般傚皬鏁扮偣鍜(璐熸暟)鐨勨-鈥欑鍙蜂笉鍖呮嫭鍦M涓傚鏋D鏄0锛屽垯鍊兼病鏈夊皬鏁扮偣鎴栧垎鏁伴儴鍒嗐DECIMAL鏁存暟鏈澶т綅鏁(M)涓65銆傛敮鎸佺殑鍗佽繘鍒舵暟鐨勬渶澶т綅鏁(D)鏄30銆傚鏋D琚渷鐣ワ紝 榛樿鏄0銆傚鏋M琚渷鐣ワ紝 榛樿鏄10銆
濡傛灉鎸囧畾UNSIGNED锛屼笉鍏佽璐熷笺
鎵鏈DECIMAL鍒楃殑鍩烘湰璁＄畻(+锛-锛*锛/)鐢65浣嶇簿搴﹀畬鎴愩
路 DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
鏄DECIMAL鐨勫悓涔夎瘝銆FIXED鍚屼箟璇嶉傜敤浜庝笌鍏跺畠鏈嶅姟鍣ㄧ殑鍏煎鎬с
鏈妭缁煎悎璁ㄨ浜嗕复鏃跺垪绫诲瀷銆傝缁嗕俊鎭紝鍙傝11.3鑺傦紝鈥滄棩鏈熷拰鏃堕棿绫诲瀷鈥銆傚垪瀛樺偍闇姹傚弬瑙11.5鑺傦紝鈥滃垪绫诲瀷瀛樺偍闇姹傗銆
路 DATE
鏃ユ湡銆傛敮鎸佺殑鑼冨洿涓'1000-01-01'鍒'9999-12-31'銆MySQL浠'YYYY-MM-DD'鏍煎紡鏄剧ずDATE鍊硷紝浣嗗厑璁镐娇鐢ㄥ瓧绗︿覆鎴栨暟瀛椾负DATE鍒楀垎閰嶅笺
路 DATETIME
鏃ユ湡鍜屾椂闂寸殑缁勫悎銆傛敮鎸佺殑鑼冨洿鏄'1000-01-01 00:00:00'鍒'9999-12-31 23:59:59'銆MySQL浠'YYYY-MM-DD HH:MM:SS'鏍煎紡鏄剧ずDATETIME鍊硷紝浣嗗厑璁镐娇鐢ㄥ瓧绗︿覆鎴栨暟瀛椾负DATETIME鍒楀垎閰嶅笺
路 TIMESTAMP[(M)]
鏃堕棿鎴炽傝寖鍥存槸'1970-01-01 00:00:00'鍒2037骞淬
TIMESTAMP鍒楃敤浜INSERT鎴UPDATE鎿嶄綔鏃惰褰曟棩鏈熷拰鏃堕棿銆傚鏋滀綘涓嶅垎閰嶄竴涓硷紝琛ㄤ腑鐨勭涓涓TIMESTAMP鍒楄嚜鍔ㄨ缃负鏈杩戞搷浣滅殑鏃ユ湡鍜屾椂闂淬備篃鍙互閫氳繃鍒嗛厤涓涓NULL鍊硷紝灏TIMESTAMP鍒楄缃负褰撳墠鐨勬棩鏈熷拰鏃堕棿銆
TIMESTAMP鍊艰繑鍥炲悗鏄剧ず涓'YYYY-MM-DD HH:MM:SS'鏍煎紡鐨勫瓧绗︿覆锛屾樉绀哄搴﹀浐瀹氫负19涓瓧绗︺傚鏋滄兂瑕佽幏寰楁暟瀛楀硷紝搴斿湪TIMESTAMP 鍒楁坊鍔+0銆
娉ㄩ噴锛MySQL 4.1浠ュ墠浣跨敤鐨TIMESTAMP鏍煎紡鍦MySQL 5.1涓笉鏀寔锛涘叧浜庢棫鏍煎紡鐨勪俊鎭弬瑙MySQL 4.1 鍙傝冩墜鍐銆
路 TIME
鏃堕棿銆傝寖鍥存槸'-838:59:59'鍒'838:59:59'銆MySQL浠'HH:MM:SS'鏍煎紡鏄剧ずTIME鍊硷紝浣嗗厑璁镐娇鐢ㄥ瓧绗︿覆鎴栨暟瀛椾负TIME鍒楀垎閰嶅笺
路 YEAR[(2|4)]
涓や綅鎴栧洓浣嶆牸寮忕殑骞淬傞粯璁ゆ槸鍥涗綅鏍煎紡銆傚湪鍥涗綅鏍煎紡涓紝鍏佽鐨勫兼槸1901鍒2155鍜0000銆傚湪涓や綅鏍煎紡涓紝鍏佽鐨勫兼槸70鍒69锛岃〃绀轰粠1970骞村埌2069骞淬MySQL浠YYYY 鏍煎紡鏄剧ずYEAR鍊硷紝浣嗗厑璁镐娇鐢ㄥ瓧绗︿覆鎴栨暟瀛椾负YEAR鍒楀垎閰嶅笺
鏈妭缁煎悎璁ㄨ浜嗗瓧绗︿覆鍒楃被鍨嬨傝缁嗕俊鎭弬瑙11.4鑺傦紝鈥淪tring绫诲瀷鈥銆傚垪瀛樺偍闇姹傚弬瑙11.5鑺傦紝鈥滃垪绫诲瀷瀛樺偍闇姹傗銆
鍦ㄦ煇浜涙儏鍐典腑锛MySQL鍙互灏嗕竴涓瓧绗︿覆鍒楁洿鏀逛负涓嶅悓浜CREATE TABLE鎴ALTER TABLE璇彞涓墍缁欏嚭鐨勭被鍨嬨傚弬瑙13.1.5.1鑺傦紝鈥滄矇瀵傜殑鍒楄鏍煎彉鏇粹銆
MySQL 5.1瀛楃涓叉暟鎹被鍨嬪寘鎷儴鍒嗗湪MySQL 4.1涔嬪墠鐨勭増鏈腑娌℃湁鐨勭壒鎬э細
路 璁稿瀛楃涓叉暟鎹被鍨嬬殑鍒楀畾涔夊彲浠ュ寘鎷寚瀹氬瓧绗﹂泦鐨CHARACTER SET灞炴э紝涔熷彲鑳藉寘鎷牎瀵硅鍒欍(CHARSET鏄CHARACTER SET鐨勪竴涓悓涔夎瘝锛銆杩欎簺灞炴ч傜敤浜CHAR銆VARCHAR銆TEXT绫诲瀷銆ENUM鍜SET銆備緥濡傦細
路 CREATE TABLE t
路 (
路 c1 CHAR(20) CHARACTER SET utf8,
路 c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
路 );
璇ヨ〃瀹氫箟鍒涘缓浜嗕竴涓悕涓c1鐨勫垪锛屽叿鏈変竴涓utf8瀛楃闆嗗拰璇ュ瓧绗﹂泦鐨勯粯璁 鏍″瑙勫垯锛屽拰涓涓悕涓c2鐨勫垪浠ュ強latin1瀛楃闆嗗拰璇ュ瓧绗﹂泦鐨勪簩鍏 鏍″瑙勫垯銆備簩鍏冩牎瀵硅鍒欏澶у皬鍐欎笉鏁忔劅銆
路 MySQL 5.1鐢ㄥ瓧绗﹀崟浣嶈В閲婂湪瀛楃鍒楀畾涔変腑鐨勯暱搴﹁鑼冦(浠ュ墠鐨勪竴浜MySQL鐗堟湰浠ュ瓧鑺傝В閲婇暱搴︼級銆
路 瀵逛簬CHAR銆VARCHAR鍜TEXT绫诲瀷锛BINARY灞炴у彲浠ヤ负鍒楀垎閰嶈鍒楀瓧绗﹂泦鐨 鏍″瑙勫垯銆
路 瀛楃鍒楃殑鎺掑簭鍜屾瘮杈冨熀浜庡垎閰嶇粰鍒楃殑瀛楃闆嗐傚湪浠ュ墠鐨勭増鏈腑锛屾帓搴忓拰姣旇緝鍩轰簬鏈嶅姟鍣ㄥ瓧绗﹂泦鐨勬牎瀵硅鍒欍傚浜CHAR鍜VARCHAR 鍒楋紝浣犲彲浠ョ敤BINARY灞炴у０鏄庡垪璁╂帓搴忓拰 鏍″瑙勫垯浣跨敤褰撳墠鐨勫瓧绗︿唬鐮佸艰屼笉鏄瘝姹囬『搴忋
鍏充簬MySQL 5.1涓瓧绗﹂泦鐨勬敮鎸侊紝鍙傝绗10绔狅細瀛楃闆嗘敮鎸銆
路 [NATIONAL] CHAR(M) [BINARY| ASCII | UNICODE]
鍥哄畾闀垮害瀛楃涓诧紝褰撲繚瀛樻椂鍦ㄥ彸渚у～鍏呯┖鏍间互杈惧埌鎸囧畾鐨勯暱搴︺M琛ㄧず鍒楅暱搴︺M鐨勮寖鍥存槸0鍒255涓瓧绗︺
娉ㄩ噴锛褰撴绱CHAR鍊兼椂灏鹃儴绌烘牸琚垹闄ゃ
濡傛灉鎯宠灏嗘煇涓CHAR鐨勯暱搴﹁涓哄ぇ浜255锛屾墽琛岀殑CREATE TABLE鎴ALTER TABLE璇彞灏嗗け璐ュ苟鎻愮ず閿欒锛
mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500));
ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead
mysql> SHOW CREATE TABLE c1;
ERROR 1146 (42S02): Table 'test.c1' doesn't exist
CHAR鏄CHARACTER鐨勭畝鍐欍NATIONAL CHAR(鎴栧叾绛夋晥鐭舰寮NCHAR)鏄爣鍑嗙殑瀹氫箟CHAR鍒楀簲浣跨敤 榛樿瀛楃闆嗙殑SQL鏂规硶銆傝繖鍦MySQL涓负榛樿鍊笺
BINARY灞炴ф槸鎸囧畾鍒楀瓧绗﹂泦鐨勪簩鍏 鏍″瑙勫垯鐨勭畝鍐欍傛帓搴忓拰姣旇緝鍩轰簬鏁板煎瓧绗﹀笺
鍒楃被鍨CHAR BYTE鏄CHAR BINARY鐨勪竴涓埆鍚嶃傝繖鏄负浜嗕繚璇佸吋瀹规с
鍙互涓CHAR鎸囧畾ASCII灞炴с傚畠鍒嗛厤latin1瀛楃闆嗐
鍙互涓CHAR鎸囧畾UNICODE灞炴с傚畠鍒嗛厤ucs2瀛楃闆嗐
MySQL鍏佽鍒涘缓绫诲瀷CHAR(0)鐨勫垪銆傝繖涓昏鐢ㄤ簬蹇呴』鏈変竴涓垪浣嗗疄闄呬笂涓嶄娇鐢ㄥ肩殑鏃х増鏈殑搴旂敤绋嬪簭鐩稿吋瀹广傚綋浣犻渶瑕佸彧鑳藉彇涓や釜鍊肩殑鍒楁椂涔熷緢濂斤細娌℃湁瀹氫箟涓NOT NULL鐨勪竴涓CHAR(0)鍒楀彧鍗犵敤涓浣嶏紝鍙彲浠ュ彇鍊NULL鍜''(绌哄瓧绗︿覆)銆
路 CHAR
杩欐槸CHAR(1)鐨勫悓涔夎瘝銆
路 [NATIONAL] VARCHAR(M) [BINARY]
鍙橀暱瀛楃涓层M 琛ㄧず鏈澶у垪闀垮害銆M鐨勮寖鍥存槸0鍒65,535銆(VARCHAR鐨勬渶澶у疄闄呴暱搴︾敱鏈闀跨殑琛岀殑澶у皬鍜屼娇鐢ㄧ殑瀛楃闆嗙‘瀹氥傛渶澶鏈夋晥闀垮害鏄65,532瀛楄妭锛銆
娉ㄩ噴锛MySQL 5.1閬典粠鏍囧噯SQL瑙勮寖锛屽苟涓斾笉鍒犻櫎VARCHAR鍊肩殑灏鹃儴绌烘牸銆
VARCHAR鏄瓧绗VARYING鐨勭畝鍐欍
BINARY灞炴ф槸鎸囧畾鍒楃殑瀛楃闆嗙殑浜屽厓 鏍″瑙勫垯鐨勭畝鍐欍傛帓搴忓拰姣旇緝鍩轰簬鏁板煎瓧绗﹀笺
VARCHAR淇濆瓨鏃剁敤涓涓瓧鑺傛垨涓や釜瀛楄妭闀跨殑鍓嶇紑+鏁版嵁銆傚鏋VARCHAR鍒楀０鏄庣殑闀垮害澶т簬255锛岄暱搴﹀墠缂鏄袱涓瓧鑺傘
路 BINARY(M)
BINARY绫诲瀷绫讳技浜CHAR绫诲瀷锛屼絾淇濆瓨浜岃繘鍒跺瓧鑺傚瓧绗︿覆鑰屼笉鏄潪浜岃繘鍒跺瓧绗︿覆銆
路 VARBINARY(M)
VARBINARY绫诲瀷绫讳技浜VARCHAR绫诲瀷锛屼絾淇濆瓨浜岃繘鍒跺瓧鑺傚瓧绗︿覆鑰屼笉鏄潪浜岃繘鍒跺瓧绗︿覆銆
路 TINYBLOB
鏈澶ч暱搴︿负255(28鈥1)瀛楄妭鐨BLOB鍒椼
路 TINYTEXT
鏈澶ч暱搴︿负255(28鈥1)瀛楃鐨TEXT鍒椼
路 BLOB[(M)]
鏈澶ч暱搴︿负65,535(216鈥1)瀛楄妭鐨BLOB鍒椼
鍙互缁欏嚭璇ョ被鍨嬬殑鍙夐暱搴M銆傚鏋滅粰鍑猴紝鍒MySQL灏嗗垪鍒涘缓涓烘渶灏忕殑浣嗚冻浠ュ绾M瀛楄妭闀跨殑鍊肩殑BLOB绫诲瀷銆
路 TEXT[(M)]
鏈澶ч暱搴︿负65,535(216鈥1)瀛楃鐨TEXT鍒椼
鍙互缁欏嚭鍙夐暱搴M銆傚垯MySQL灏嗗垪鍒涘缓涓烘渶灏忕殑浣嗚冻浠ュ绾M瀛楃闀跨殑鍊肩殑TEXT绫诲瀷銆
路 MEDIUMBLOB
鏈澶ч暱搴︿负16,777,215(224鈥1)瀛楄妭鐨BLOB鍒椼
路 MEDIUMTEXT
鏈澶ч暱搴︿负16,777,215(224鈥1)瀛楃鐨TEXT鍒椼
路 LONGBLOB
鏈澶ч暱搴︿负4,294,967,295鎴4GB(232鈥1)瀛楄妭鐨BLOB鍒椼LONGBLOB鍒楃殑鏈澶鏈夋晥(鍏佽鐨)闀垮害鍙栧喅浜庡鎴风/鏈嶅姟鍣ㄥ崗璁腑閰嶇疆鏈澶у寘澶у皬鍜屽彲鐢ㄧ殑鍐呭瓨銆
路 LONGTEXT
鏈澶ч暱搴︿负4,294,967,295鎴4GB(232鈥1)瀛楃鐨TEXT鍒椼LONGTEXT鍒楃殑鏈澶鏈夋晥(鍏佽鐨)闀垮害鍙栧喅浜庡鎴风/鏈嶅姟鍣ㄥ崗璁腑閰嶇疆鏈澶у寘澶у皬鍜屽彲鐢ㄧ殑鍐呭瓨銆
路 ENUM('value1','value2',...)
鏋氫妇绫诲瀷銆傚彧鑳芥湁涓涓肩殑瀛楃涓诧紝浠庡煎垪'value1'锛'value2'锛...锛NULL涓垨鐗规畩 ''閿欒鍊间腑閫夊嚭銆ENUM鍒楁渶澶氬彲浠ユ湁65,535涓埅鐒朵笉鍚岀殑鍊笺ENUM鍊煎湪鍐呴儴鐢ㄦ暣鏁拌〃绀恒
路 SET('value1','value2',...)
涓涓缃傚瓧绗︿覆瀵硅薄鍙互鏈夐浂涓垨澶氫釜鍊硷紝姣忎釜鍊煎繀椤绘潵鑷垪鍊'value1'锛'value2'锛...SET鍒楁渶澶氬彲浠ユ湁64涓垚鍛樸SET鍊煎湪鍐呴儴鐢ㄦ暣鏁拌〃绀恒
MySQL鏀寔鎵鏈夋爣鍑SQL鏁板兼暟鎹被鍨嬨傝繖浜涚被鍨嬪寘鎷弗鏍兼暟鍊兼暟鎹被鍨(INTEGER銆SMALLINT銆DECIMAL鍜NUMERIC)锛屼互鍙婅繎浼兼暟鍊兼暟鎹被鍨(FLOAT銆REAL鍜DOUBLE PRECISION)銆傚叧閿瓧INT鏄INTEGER鐨勫悓涔夎瘝锛屽叧閿瓧DEC鏄DECIMAL鐨勫悓涔夎瘝銆
BIT鏁版嵁绫诲瀷淇濆瓨浣嶅瓧娈靛硷紝骞朵笖鏀寔MyISAM銆MEMORY銆InnoDB鍜BDB琛ㄣ
浣滀负SQL鏍囧噯鐨勬墿灞曪紝MySQL涔熸敮鎸佹暣鏁扮被鍨TINYINT銆MEDIUMINT鍜BIGINT銆備笅闈㈢殑琛ㄦ樉绀轰簡闇瑕佺殑姣忎釜鏁存暟绫诲瀷鐨勫瓨鍌ㄥ拰鑼冨洿銆
|
绫诲瀷 |
瀛楄妭 |
鏈灏忓 |
鏈澶у |
|
|
|
(甯︾鍙风殑/鏃犵鍙风殑) |
(甯︾鍙风殑/鏃犵鍙风殑) |
|
TINYINT |
1 |
-128 |
127 |
|
|
|
0 |
255 |
|
SMALLINT |
2 |
-32768 |
32767 |
|
|
|
0 |
65535 |
|
MEDIUMINT |
3 |
-8388608 |
8388607 |
|
|
|
0 |
16777215 |
|
INT |
4 |
-2147483648 |
2147483647 |
|
|
|
0 |
4294967295 |
|
BIGINT |
8 |
-9223372036854775808 |
9223372036854775807 |
|
|
|
0 |
18446744073709551615 |
MySQL杩樻敮鎸侀夋嫨鍦ㄨ绫诲瀷鍏抽敭瀛楀悗闈㈢殑鎷彿鍐呮寚瀹氭暣鏁板肩殑鏄剧ず瀹藉害(渚嬪锛INT(4))銆傝鍙夋樉绀哄搴﹁瀹氱敤浜庢樉绀哄搴﹀皬浜庢寚瀹氱殑鍒楀搴︾殑鍊兼椂浠庡乏渚у～婊″搴︺
鏄剧ず瀹藉害骞朵笉闄愬埗鍙互鍦ㄥ垪鍐呬繚瀛樼殑鍊肩殑鑼冨洿锛屼篃涓嶉檺鍒惰秴杩囧垪鐨勬寚瀹氬搴︾殑鍊肩殑鏄剧ず銆
褰撶粨鍚堝彲閫夋墿灞曞睘鎬ZEROFILL浣跨敤鏃讹紝 榛樿琛ュ厖鐨勭┖鏍肩敤闆朵唬鏇裤備緥濡傦紝瀵逛簬澹版槑涓INT(5) ZEROFILL鐨勫垪锛屽4妫绱负00004銆傝娉ㄦ剰濡傛灉鍦ㄦ暣鏁板垪淇濆瓨瓒呰繃鏄剧ず瀹藉害鐨勪竴涓硷紝褰MySQL涓哄鏉傝仈鎺ョ敓鎴愪复鏃惰〃鏃朵細閬囧埌闂锛屽洜涓哄湪杩欎簺鎯呭喌涓MySQL鐩镐俊鏁版嵁閫傚悎鍘熷垪瀹藉害銆
鎵鏈夋暣鏁扮被鍨嬪彲浠ユ湁涓涓彲閫(闈炴爣鍑)灞炴UNSIGNED銆傚綋浣犳兂瑕佸湪鍒楀唴鍙厑璁搁潪璐熸暟鍜岃鍒楅渶瑕佽緝澶х殑涓婇檺鏁板艰寖鍥存椂鍙互浣跨敤鏃犵鍙峰笺
娴偣鍜屽畾鐐圭被鍨嬩篃鍙互涓UNSIGNED銆傚悓鏁扮被鍨嬶紝璇ュ睘鎬ч槻姝㈣礋鍊间繚瀛樺埌鍒椾腑銆傜劧鑰岋紝涓庢暣鏁扮被鍨嬩笉鍚岀殑鏄紝鍒楀肩殑涓婅寖鍥翠繚鎸佷笉鍙樸
濡傛灉涓轰竴涓暟鍊煎垪鎸囧畾ZEROFILL锛MySQL鑷姩涓鸿鍒楁坊鍔UNSIGNED灞炴с
瀵逛簬娴偣鍒楃被鍨嬶紝鍦MySQL涓崟绮惧害鍊间娇鐢4涓瓧鑺傦紝鍙岀簿搴﹀间娇鐢8涓瓧鑺傘
FLOAT绫诲瀷鐢ㄤ簬琛ㄧず杩戜技鏁板兼暟鎹被鍨嬨SQL鏍囧噯鍏佽鍦ㄥ叧閿瓧FLOAT鍚庨潰鐨勬嫭鍙峰唴閫夋嫨鐢ㄤ綅鎸囧畾绮惧害(浣嗕笉鑳戒负鎸囨暟鑼冨洿)銆MySQL杩樻敮鎸佸彲閫夌殑鍙敤浜庣‘瀹氬瓨鍌ㄥぇ灏忕殑绮惧害瑙勫畾銆0鍒23鐨勭簿搴﹀搴FLOAT鍒楃殑4瀛楄妭鍗曠簿搴︺24鍒53鐨勭簿搴﹀搴DOUBLE鍒楃殑8瀛楄妭鍙岀簿搴︺
MySQL鍏佽浣跨敤闈炴爣鍑嗚娉曪細FLOAT(M,D)鎴REAL(M,D)鎴DOUBLE PRECISION(M,D)銆傝繖閲岋紝鈥(M,D)鈥濊〃绀鸿鍊间竴鍏辨樉绀M浣嶆暣鏁帮紝鍏朵腑D浣嶄綅浜庡皬鏁扮偣鍚庨潰銆備緥濡傦紝瀹氫箟涓FLOAT(7,4)鐨勪竴涓垪鍙互鏄剧ず涓-999.9999銆MySQL淇濆瓨鍊兼椂杩涜鍥涜垗浜斿叆锛屽洜姝ゅ鏋滃湪FLOAT(7,4)鍒楀唴鎻掑叆999.00009锛岃繎浼肩粨鏋滄槸999.0001銆
MySQL灏DOUBLE瑙嗕负DOUBLE PRECISION(闈炴爣鍑嗘墿灞)鐨勫悓涔夎瘝銆MySQL杩樺皢REAL瑙嗕负DOUBLE PRECISION(闈炴爣鍑嗘墿灞)鐨勫悓涔夎瘝锛岄櫎闈SQL鏈嶅姟鍣ㄦā寮忓寘鎷REAL_AS_FLOAT閫夐」銆
涓轰簡淇濊瘉鏈澶у彲鑳界殑鍙Щ妞嶆э紝闇瑕佷娇鐢ㄨ繎浼兼暟鍊兼暟鎹煎瓨鍌ㄧ殑浠ｇ爜搴斾娇鐢FLOAT鎴DOUBLE PRECISION锛屼笉瑙勫畾绮惧害鎴栦綅鏁般
DECIMAL鍜NUMERIC绫诲瀷鍦MySQL涓涓虹浉鍚岀殑绫诲瀷銆傚畠浠敤浜庝繚瀛樺繀椤讳负纭垏绮惧害鐨勫硷紝渚嬪璐у竵鏁版嵁銆傚綋澹版槑璇ョ被鍨嬬殑鍒楁椂锛屽彲浠(骞朵笖閫氬父瑕)鎸囧畾绮惧害鍜屾爣搴︼紱渚嬪锛
salary DECIMAL(5,2)
鍦ㄨ渚嬪瓙涓紝5鏄簿搴︼紝2鏄爣搴︺傜簿搴﹁〃绀轰繚瀛樺肩殑涓昏浣嶆暟锛屾爣搴﹁〃绀哄皬鏁扮偣鍚庨潰鍙互淇濆瓨鐨勪綅鏁般
鍦MySQL 5.1涓互浜岃繘鍒舵牸寮忎繚瀛DECIMAL鍜NUMERIC鍊笺
鏍囧噯SQL瑕佹眰salary鍒楄兘澶熺敤5浣嶆暣鏁颁綅鍜屼袱浣嶅皬鏁颁繚瀛樹换浣曞笺傚洜姝わ紝鍦ㄨ繖绉嶆儏鍐典笅鍙互淇濆瓨鍦salary鍒楃殑鍊肩殑鑼冨洿鏄粠-999.99鍒999.99銆
鍦ㄦ爣鍑SQL涓紝璇硶DECIMAL(M)绛変环浜DECIMAL(M,0)銆傚悓鏍凤紝璇硶DECIMAL绛変环浜DECIMAL(M,0)锛屽彲浠ラ氳繃璁＄畻纭畾M鐨勫笺傚湪MySQL 5.1涓敮鎸DECIMAL鍜NUMERIC鏁版嵁绫诲瀷鐨勫彉閲忓舰寮忋M榛樿鍊兼槸10銆
DECIMAL鎴NUMERIC鐨勬渶澶т綅鏁版槸65锛屼絾鍏蜂綋鐨DECIMAL鎴NUMERIC鍒楃殑瀹為檯鑼冨洿鍙楀叿浣撳垪鐨勭簿搴︽垨鏍囧害绾︽潫銆傚鏋滄绫诲垪鍒嗛厤鐨勫煎皬鏁扮偣鍚庨潰鐨勪綅鏁拌秴杩囨寚瀹氱殑鏍囧害鍏佽鐨勮寖鍥达紝鍊艰杞崲涓鸿鏍囧害銆(鍏蜂綋鎿嶄綔涓庢搷浣滅郴缁熸湁鍏筹紝浣嗕竴鑸粨鏋滃潎琚埅鍙栧埌鍏佽鐨勪綅鏁帮級銆
BIT鏁版嵁绫诲瀷鍙敤鏉ヤ繚瀛樹綅瀛楁鍊笺BIT(M)绫诲瀷鍏佽瀛樺偍M浣嶅笺M鑼冨洿涓1鍒64銆
瑕佹寚瀹氫綅鍊硷紝鍙互浣跨敤b'value'绗︺value鏄竴涓敤0鍜1缂栧啓鐨勪簩杩涘埗鍊笺備緥濡傦紝b'111'鍜b'100000000'鍒嗗埆琛ㄧず7鍜128銆傚弬瑙9.1.5鑺傦紝鈥滀綅瀛楁鍊尖銆
濡傛灉涓BIT(M)鍒楀垎閰嶇殑鍊肩殑闀垮害灏忎簬M浣嶏紝鍦ㄥ肩殑宸﹁竟鐢0濉厖銆備緥濡傦紝涓BIT(6)鍒楀垎閰嶄竴涓b'101'锛屽叾鏁堟灉涓庡垎閰b'000101'鐩稿悓銆
褰撹鍦ㄤ竴涓暟鍊煎垪鍐呬繚瀛樹竴涓秴鍑鸿鍒楀厑璁歌寖鍥寸殑鍊兼椂锛MySQL鐨勬搷浣滃彇鍐充簬姝ゆ椂鏈夋晥鐨SQL妯″紡銆傚鏋滄ā寮忔湭璁剧疆锛MySQL灏嗗艰鍓埌鑼冨洿鐨勭浉搴旂鐐癸紝骞朵繚瀛樿鍑忓ソ鐨勫笺備絾鏄紝濡傛灉妯″紡璁剧疆涓traditional(鈥滀弗鏍妯″紡鈥)锛岃秴鍑鸿寖鍥寸殑鍊煎皢琚嫆缁濆苟鎻愮ず閿欒锛屽苟涓旀牴鎹SQL鏍囧噯鎻掑叆浼氬け璐ャ傚弬瑙5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊銆
濡傛灉INT鍒楁槸UNSIGNED锛屽垪鑼冨洿鐨勫ぇ灏忕浉鍚岋紝浣嗗叾绔偣浼氬彉涓哄埌0鍜4294967295銆傚鏋滀綘璇曞浘淇濆瓨-9999999999鍜9999999999锛屼互闈炰弗鏍兼ā寮忎繚瀛樺埌鍒椾腑鐨勫兼槸0鍜4294967296銆
濡傛灉鍦ㄦ诞鐐规垨瀹氱偣鍒椾腑鍒嗛厤鐨勫艰秴杩囨寚瀹(鎴栭粯璁)绮惧害鍜屾爣搴﹁瀹氱殑鑼冨洿锛MySQL浠ラ潪涓ユ牸妯″紡淇濆瓨琛ㄧず鑼冨洿鐩稿簲绔偣鐨勫笺
褰MySQL娌℃湁宸ヤ綔鍦ㄤ弗鏍兼ā寮忔椂锛屽浜ALTER TABLE銆LOAD DATA INFILE銆UPDATE鍜屽琛INSERT璇彞锛岀敱浜庤鍓彂鐢熺殑杞崲灏嗘姤鍛婁负璀﹀憡銆傚綋MySQL宸ヤ綔鍦ㄤ弗鏍兼ā寮忔椂锛岃繖浜涜鍙ュ皢澶辫触锛屽苟涓旈儴鍒嗘垨鍏ㄩ儴鍊间笉浼氭彃鍏ユ垨鏇存敼锛屽彇鍐充簬鏄惁琛ㄤ负浜嬪姟琛ㄥ拰鍏跺畠鍥犵礌銆傝鎯呭弬瑙5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊銆
琛ㄧず鏃堕棿鍊肩殑DATE鍜屾椂闂寸被鍨嬩负DATETIME銆DATE銆TIMESTAMP銆TIME鍜YEAR銆傛瘡涓椂闂寸被鍨嬫湁涓涓湁鏁堝艰寖鍥村拰涓涓闆鈥濆硷紝褰撴寚瀹氫笉鍚堟硶鐨MySQL涓嶈兘琛ㄧず鐨勫兼椂浣跨敤鈥闆鈥濆笺TIMESTAMP绫诲瀷鏈変笓鏈夌殑鑷姩鏇存柊鐗规э紝灏嗗湪鍚庨潰鎻忚堪銆
濡傛灉璇曞浘鎻掑叆涓涓笉鍚堟硶鐨勬棩鏈燂紝MySQL灏嗙粰鍑鸿鍛婃垨閿欒銆傚彲浠ヤ娇鐢ALLOW_INVALID_DATES SQL妯″紡璁MySQL鎺ュ彈鏌愪簺鏃ユ湡锛屼緥濡'1999-11-31'銆傚綋浣犳兂瑕佷繚瀛樹竴涓鍙兘閿欒鐨鈥濈敤鎴峰凡缁忓湪鏁版嵁搴撲腑鎸囧畾(渚嬪锛屼互web褰㈠紡)鐢ㄤ簬灏嗘潵澶勭悊鐨勫兼椂寰堟湁鐢ㄣ傚湪杩欑妯″紡涓嬶紝MySQL鍙獙璇佹湀鑼冨洿涓轰粠0鍒12锛屾棩鑼冨洿涓轰粠0鍒31銆傝繖浜涜寖鍥村彲浠ュ寘鎷浂锛屽洜涓MySQL鍏佽鍦DATE鎴DATETIME鍒椾繚瀛樻棩/鏈堝拰鏃ユ槸闆剁殑鏃ユ湡銆傝繖鍦ㄥ簲鐢ㄧ▼搴忛渶瑕佷繚瀛樹竴涓綘涓嶇煡閬撶‘鍒囨棩鏈熺殑鐢熸棩鏃堕潪甯告湁鐢ㄣ傚湪杩欑鎯呭喌涓嬶紝鍙渶瑕佸皢鏃ユ湡淇濆瓨涓'1999-00-00'鎴'1999-01-00'銆傚鏋滀繚瀛樻绫绘棩鏈燂紝DATE_SUB()鎴DATE_ADD绛夐渶瑕佸畬鏁存棩鏈熺殑鍑芥暟涓嶄細寰楀埌姝ｇ‘鐨勭粨鏋溿(濡傛灉浣犱笉鎯冲湪鏃ユ湡涓嚭鐜伴浂锛屽彲浠ヤ娇鐢NO_ZERO_IN_DATE SQL妯″紡)銆
MySQL杩樺厑璁稿皢'0000-00-00'淇濆瓨涓衡滀吉鏃ユ湡鈥(濡傛灉涓嶄娇鐢NO_ZERO_DATE SQL妯″紡)銆傝繖鍦ㄦ煇浜涙儏鍐典笅姣斾娇鐢NULL鍊兼洿鏂逛究(骞朵笖鏁版嵁鍜岀储寮曞崰鐢ㄧ殑绌洪棿鏇村皬)銆
灏sql_mode绯荤粺鍙橀噺璁剧疆涓虹浉搴旀ā寮忓硷紝鍙互鏇寸‘鍒囦綘鎯宠MySQL鏀寔鍝鏃ユ湡銆傚弬瑙5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊銆
褰撲娇鐢ㄦ棩鏈熷拰鏃堕棿绫诲瀷鏃跺簲璁颁綇浠ヤ笅鍑犵偣锛
路 MySQL浠ユ爣鍑嗚緭鍑烘牸寮忔绱㈢粰瀹氭棩鏈熸垨鏃堕棿绫诲瀷鐨勫硷紝浣嗗畠灏藉姏瑙ｉ噴浣犳寚瀹氱殑鍚勭杈撳叆鍊兼牸寮(渚嬪锛屽綋浣犳寚瀹氫竴涓垎閰嶇粰鎴栦笌鏃ユ湡鎴栨椂闂寸被鍨嬭繘琛屾瘮杈冪殑鍊兼椂)銆傚彧鏀寔涓嬮潰绔犺妭涓弿杩扮殑鏍煎紡銆傛湡鏈涗綘鑳芥彁渚涙湁鏁堝笺傚鏋滀綘浣跨敤鍏跺畠鏍煎紡鐨勫间細鍙戠敓鎰忔兂涓嶅埌鐨勭粨鏋溿
路 鍖呭惈涓や綅骞村肩殑鏃ユ湡浼氫护浜烘ā绯婏紝鍥犱负涓栫邯涓嶇煡閬撱MySQL浣跨敤浠ヤ笅瑙勫垯瑙ｉ噴涓や綅骞村硷細
o 70-99鑼冨洿鐨勫勾鍊艰浆鎹负1970-1999銆
o 00-69鑼冨洿鐨勫勾鍊艰浆鎹负2000-2069銆
路 灏界MySQL灏濊瘯瑙ｉ噴鍑犵鏍煎紡鐨勫硷紝鏃ユ湡鎬绘槸浠ュ勾-鏈-鏃ラ『搴(渚嬪锛'98-09-04')锛岃屼笉鏄叾瀹冨湴鏂瑰父鐢ㄧ殑鏈-鏃-骞存垨鏃-鏈-骞撮『搴(渚嬪锛'09-04-98'锛'04-09-98')銆
路 濡傛灉鍊肩敤浜庢暟鍊间笂涓嬫枃涓紝MySQL鑷姩灏嗘棩鏈熸垨鏃堕棿绫诲瀷鐨勫艰浆鎹负鏁板瓧锛屽弽涔嬩害鐒躲
路 褰 MySQL閬囧埌涓涓棩鏈熸垨鏃堕棿绫诲瀷鐨勮秴鍑鸿寖鍥存垨瀵逛簬璇ョ被鍨嬩笉鍚堟硶鐨勫兼椂(濡傛湰鑺傚紑濮嬫墍鎻忚堪)锛屽畠灏嗚鍊艰浆鎹负璇ョ被鐨勨闆鈥濆笺備竴涓緥澶栨槸瓒呭嚭鑼冨洿鐨TIME鍊艰瑁佸壀鍒TIME鑼冨洿鐨勭浉搴旂鐐广
涓嬮潰鐨勮〃鏄剧ず浜嗗悇绫烩闆鈥濆肩殑鏍煎紡銆傝娉ㄦ剰濡傛灉鍚敤NO_ZERO_DATE SQL妯″紡锛屼娇鐢ㄨ繖浜涘间細浜х敓璀﹀憡銆
|
鍒楃被鍨 |
鈥闆鈥濆 |
|
DATETIME |
'0000-00-00 00:00:00' |
|
DATE |
'0000-00-00' |
|
TIMESTAMP |
00000000000000 |
|
TIME |
'00:00:00' |
|
YEAR |
0000 |
路 鈥闆鈥濆兼槸鐗规畩鍊硷紝浣嗕綘鍙互浣跨敤琛ㄥ唴鏄剧ず鐨勫兼樉寮忎繚瀛樻垨寮曠敤瀹冧滑銆備綘涔熷彲浠ヤ娇鐢ㄥ'0'鎴0鏉ヤ繚瀛樻垨寮曠敤锛屽啓璧锋潵鏇村鏄撱
路 MyODBC涓娇鐢ㄧ殑鈥闆鈥濇棩鏈熸垨鏃堕棿鍊煎湪MyODBC 2.50.12鍜屼互涓婄増鏈腑琚嚜鍔ㄨ浆鎹负NULL锛屽洜涓ODBC涓嶈兘澶勭悊姝ょ被鍊笺
DATETIME銆DATE鍜TIMESTAMP绫诲瀷鏄浉鍏崇殑銆傝鑺傛弿杩颁簡瀹冧滑鐨勭壒寰侊紝瀹冧滑鐨勭浉浼肩偣鍜屼笉鍚岀偣銆
褰撲綘闇瑕佸悓鏃跺寘鍚棩鏈熷拰鏃堕棿淇℃伅鐨勫兼椂鍒欎娇鐢DATETIME绫诲瀷銆MySQL浠'YYYY-MM-DD HH:MM:SS'鏍煎紡妫绱㈠拰鏄剧ずDATETIME鍊笺傛敮鎸佺殑鑼冨洿涓'1000-01-01 00:00:00'鍒'9999-12-31 23:59:59'銆(鈥鏀寔鈥濊〃绀哄敖绠″厛鍓嶇殑鍊煎彲鑳藉伐浣滐紝浣嗘病鏈変繚璇)銆
褰撲綘鍙渶瑕佹棩鏈熷艰屼笉闇瑕佹椂闂撮儴鍒嗘椂搴斾娇鐢DATE绫诲瀷銆MySQL鐢'YYYY-MM-DD'鏍煎紡妫绱㈠拰鏄剧ずDATE鍊笺傛敮鎸佺殑鑼冨洿鏄'1000-01-01'鍒 '9999-12-31'銆
TIMESTAMP鍒楃被鍨嬬殑灞炴т笉鍥哄畾锛屽彇鍐充簬MySQL鐗堟湰鍜屾湇鍔″櫒杩愯鐨SQL妯″紡銆傝繖浜涘睘鎬у皢鍦ㄦ湰鑺傚悗闈㈡弿杩般
鍙互浣跨敤浠讳綍甯歌鏍煎紡鎸囧畾DATETIME銆DATE鍜TIMESTAMP鍊硷細
路 'YYYY-MM-DD HH:MM:SS'鎴'YY-MM-DD HH:MM:SS'鏍煎紡鐨勫瓧绗︿覆銆傚厑璁糕涓嶄弗鏍鈥濊娉曪細浠讳綍鏍囩偣绗﹂兘鍙互鐢ㄥ仛鏃ユ湡閮ㄥ垎鎴栨椂闂撮儴鍒嗕箣闂寸殑闂村壊绗︺備緥濡傦紝'98-12-31 11:30:45'銆'98.12.31 11+30+45'銆'98/12/31 11*30*45'鍜'98@12@31 11^30^45'鏄瓑浠风殑銆
路 'YYYY-MM-DD'鎴'YY-MM-DD'鏍煎紡鐨勫瓧绗︿覆銆傝繖閲屼篃鍏佽浣跨敤鈥滀笉涓ユ牸鐨勨濊娉曘備緥濡傦紝'98-12-31'銆'98.12.31'銆'98/12/31'鍜'98@12@31'鏄瓑浠风殑銆
路 'YYYYMMDDHHMMSS'鎴'YYMMDDHHMMSS'鏍煎紡鐨勬病鏈夐棿鍓茬鐨勫瓧绗︿覆锛屽亣瀹氬瓧绗︿覆瀵逛簬鏃ユ湡绫诲瀷鏄湁鎰忎箟鐨勩備緥濡傦紝'19970523091528'鍜'970523091528'琚В閲婁负'1997-05-23 09:15:28'锛屼絾'971122129015'鏄笉鍚堟硶鐨(瀹冩湁涓涓病鏈夋剰涔夌殑鍒嗛挓閮ㄥ垎)锛屽皢鍙樹负'0000-00-00 00:00:00'銆
路 'YYYYMMDD'鎴'YYMMDD'鏍煎紡鐨勬病鏈夐棿鍓茬鐨勫瓧绗︿覆锛屽亣瀹氬瓧绗︿覆瀵逛簬鏃ユ湡绫诲瀷鏄湁鎰忎箟鐨勩備緥濡傦紝'19970523'鍜'970523'琚В閲婁负 '1997-05-23'锛屼絾'971332'鏄笉鍚堟硶鐨(瀹冩湁涓涓病鏈夋剰涔夌殑鏈堝拰鏃ラ儴鍒)锛屽皢鍙樹负'0000-00-00'銆
路 YYYYMMDDHHMMSS鎴YYMMDDHHMMSS鏍煎紡鐨勬暟瀛楋紝鍋囧畾鏁板瓧瀵逛簬鏃ユ湡绫诲瀷鏄湁鎰忎箟鐨勩備緥濡傦紝19830905132800鍜830905132800琚В閲婁负 '1983-09-05 13:28:00'銆
路 YYYYMMDD鎴YYMMDD鏍煎紡鐨勬暟瀛楋紝鍋囧畾鏁板瓧瀵逛簬鏃ユ湡绫诲瀷鏄湁鎰忎箟鐨勩備緥濡傦紝19830905鍜830905琚В閲婁负'1983-09-05'銆
路 鍑芥暟杩斿洖鐨勭粨鏋滐紝鍏跺奸傚悎DATETIME銆DATE鎴栬TIMESTAMP涓婁笅鏂囷紝渚嬪NOW()鎴CURRENT_DATE銆
鏃犳晥DATETIME銆DATE鎴栬TIMESTAMP鍊艰杞崲涓虹浉搴旂被鍨嬬殑鈥闆鈥濆('0000-00-00 00:00:00'銆'0000-00-00'鎴栬00000000000000)銆
瀵逛簬鍖呮嫭鏃ユ湡閮ㄥ垎闂村壊绗︾殑瀛楃涓插硷紝濡傛灉鏃ュ拰鏈堢殑鍊煎皬浜10锛屼笉闇瑕佹寚瀹氫袱浣嶆暟銆'1979-6-9'涓'1979-06-09'鏄浉鍚岀殑銆傚悓鏍凤紝瀵逛簬鍖呮嫭鏃堕棿閮ㄥ垎闂村壊绗︾殑瀛楃涓插硷紝濡傛灉鏃躲佸垎鍜岀鐨勫煎皬浜10锛屼笉闇瑕佹寚瀹氫袱浣嶆暟銆'1979-10-30 1:2:3'涓'1979-10-30 01:02:03'鐩稿悓銆
鏁板瓧鍊煎簲涓6銆8銆12鎴栬14浣嶉暱銆傚鏋滀竴涓暟鍊兼槸8鎴14浣嶉暱锛屽垯鍋囧畾涓YYYYMMDD鎴YYYYMMDDHHMMSS鏍煎紡锛屽墠4浣嶆暟琛ㄧず骞淬傚鏋滄暟瀛 鏄6鎴12浣嶉暱锛屽垯鍋囧畾涓YYMMDD鎴YYMMDDHHMMSS鏍煎紡锛屽墠2浣嶆暟琛ㄧず骞淬傚叾瀹冩暟瀛楄瑙ｉ噴涓轰豢浣涚敤闆跺～鍏呭埌浜嗘渶杩戠殑闀垮害銆
鎸囧畾涓洪潪闄愬畾绗﹀瓧绗︿覆鐨勫间娇鐢ㄧ粰瀹氱殑闀垮害杩涜瑙ｉ噴銆傚鏋滃瓧绗︿覆涓8鎴14瀛楃闀匡紝鍓4浣嶆暟琛ㄧず骞淬傚惁鍒欙紝鍓2浣嶆暟琛ㄧず骞淬備粠宸﹀悜鍙宠В閲婂瓧绗︿覆鍐呭嚭鐜扮殑鍚勯儴鍒嗭紝浠ュ彂鐜板勾銆佹湀銆佹棩銆佸皬鏃躲佸垎鍜岀鍊笺傝繖璇存槑涓嶅簲浣跨敤灏戜簬6瀛楃鐨勫瓧绗︿覆銆備緥濡傦紝濡傛灉浣犳寚瀹'9903'锛岃涓哄畠琛ㄧず1999骞3鏈堬紝MySQL灏嗗湪浣犵殑琛ㄥ唴鎻掑叆涓涓闆鈥濇棩鏈熷笺傝繖鏄洜涓哄勾鍜屾湀鍊兼槸99鍜03锛屼絾鏃ラ儴鍒嗗畬鍏ㄤ涪澶憋紝鍥犳璇ュ间笉鏄竴涓悎娉曠殑鏃ユ湡銆備絾鏄紝鍙互鏄庢樉鎸囧畾涓涓浂鍊兼潵浠ｈ〃缂哄皯鐨勬湀鎴栨棩閮ㄥ垎銆備緥濡傦紝鍙互浣跨敤'990300'鏉ユ彃鍏ュ'1999-03-00'銆
鍦ㄤ竴瀹氱▼搴︿笂锛屽彲浠ュ皢涓涓棩鏈熺被鍨嬬殑鍊煎垎閰嶇粰涓涓笉鍚岀殑鏃ユ湡绫诲瀷銆備絾鏄紝鍊煎彲鑳戒細鏇存敼鎴栦涪澶变竴浜涗俊鎭細
路 濡傛灉浣犱负涓涓DATETIME鎴TIMESTAMP瀵硅薄鍒嗛厤涓涓DATE鍊硷紝缁撴灉鍊肩殑鏃堕棿閮ㄥ垎琚缃负'00:00:00'锛屽洜涓DATE鍊兼湭鍖呭惈鏃堕棿淇℃伅銆
路 濡傛灉浣犱负涓涓DATE瀵硅薄鍒嗛厤涓涓DATETIME鎴TIMESTAMP鍊硷紝缁撴灉鍊肩殑鏃堕棿閮ㄥ垎琚垹闄わ紝鍥犱负DATE鍊兼湭鍖呭惈鏃堕棿淇℃伅銆
路 璁颁綇灏界鍙互浣跨敤鐩稿悓鐨勬牸寮忔寚瀹DATETIME銆DATE鍜TIMESTAMP鍊硷紝涓嶅悓绫诲瀷鐨勫肩殑鑼冨洿鍗翠笉鍚屻備緥濡傦紝TIMESTAMP鍊间笉鑳芥棭浜1970鎴栨櫄浜2037銆傝繖璇存槑涓涓棩鏈燂紝渚嬪'1968-01-01'锛岃櫧鐒跺浜DATETIME鎴DATE鍊兼槸鏈夋晥鐨勶紝浣嗗浜TIMESTAMP鍊煎嵈鏃犳晥锛屽鏋滃垎閰嶇粰杩欐牱涓涓璞″皢琚浆鎹负0銆
褰撴寚瀹氭棩鏈熷兼椂璇锋敞鎰忔煇浜涚己闄凤細
路 鎸囧畾涓哄瓧绗︿覆鐨勫煎厑璁哥殑闈炰弗鏍兼牸寮忓彲鑳戒細娆洪獥銆備緥濡傦紝鍊'10:11:12'鐢变簬鈥:鈥欓棿鍓茬鐪嬩笂鍘诲彲鑳借薄鏃堕棿鍊硷紝浣嗗鏋滅敤浜庢棩鏈熶笂涓嬫枃鍊煎垯琚В閲婁负骞'2010-11-12'銆傚'10:45:15'琚浆鎹负'0000-00-00'鍥犱负'45'涓嶆槸鍚堟硶鏈堛
路 鍦ㄩ潪涓ユ牸妯″紡锛MySQL鏈嶅姟鍣ㄥ彧瀵规棩鏈熺殑鍚堟硶鎬ц繘琛屽熀鏈鏌ワ細骞淬佹湀鍜屾棩鐨勮寖鍥村垎鍒槸1000鍒9999銆00鍒12鍜00鍒31銆備换浣曞寘鍚秴鍑鸿繖浜涜寖鍥寸殑閮ㄥ垎鐨勬棩鏈熻杞崲鎴'0000-00-00'銆傝娉ㄦ剰浠嶇劧鍏佽浣犱繚瀛橀潪娉曟棩鏈燂紝渚嬪'2002-04-31'銆傝鎯崇‘淇濅笉浣跨敤涓ユ牸妯″紡鏃舵棩鏈熸湁鏁堬紝搴旀鏌ュ簲鐢ㄧ▼搴忋
鍦ㄤ弗鏍兼ā寮忥紝闈炴硶鏃ユ湡涓嶈鎺ュ彈锛屽苟涓斾笉杞崲銆
璇︾粏淇℃伅鍙傝5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊銆
路 鍖呭惈涓や綅骞村肩殑鏃ユ湡浼氫护浜烘ā绯婏紝鍥犱负涓栫邯涓嶇煡閬撱MySQL浣跨敤浠ヤ笅瑙勫垯瑙ｉ噴涓や綅骞村硷細
o 00-69鑼冨洿鐨勫勾鍊艰浆鎹负2000-2069銆
o 70-99鑼冨洿鐨勫勾鍊艰浆鎹负1970-1999銆
娉ㄩ噴锛鍦鏃х増鏈殑MySQL涓(4.1涔嬪墠)锛TIMESTAMP鍒楃被鍨嬬殑灞炴у湪璁稿鏂归潰浜庢湰鑺傛墍鎻忚堪鐨勫ぇ澶т笉鍚屻傚鏋滀綘闇瑕佸鏃х殑TIMESTAMP鏁版嵁杩涜杞寲浠ヤ究鍦MySQL 5.1涓伐浣滐紝璇︽儏璇峰弬瑙MySQL 4.1 鍙傝冩墜鍐銆
TIMESTAMP鍒楃殑鏄剧ず鏍煎紡涓DATETIME鍒楃浉鍚屻傛崲鍙ヨ瘽璇达紝鏄剧ず瀹藉害鍥哄畾鍦19瀛楃锛屽苟涓旀牸寮忎负YYYY-MM-DD HH:MM:SS銆
MySQL鏈嶅姟鍣ㄤ篃鍙互浠MAXDB妯″紡杩愯銆傚綋鏈嶅姟鍣ㄤ互璇ユā寮忚繍琛屾椂锛TIMESTAMP涓DATETIME鐩哥瓑銆備篃灏辨槸璇达紝濡傛灉鍒涘缓琛ㄦ椂鏈嶅姟鍣ㄤ互MAXDB妯″紡杩愯锛TIMESTAMP鍒楀垱寤轰负DATETIME鍒椼傜粨鏋滄槸锛岃鍒椾娇鐢DATETIME鏄剧ず鏍煎紡锛屾湁鐩稿悓鐨勫艰寖鍥达紝骞朵笖娌℃湁鑷姩瀵瑰綋鍓嶇殑鏃ユ湡鍜屾椂闂磋繘琛屽垵濮嬪寲鎴栨洿鏂般
瑕佹兂鍚敤MAXDB妯″紡锛屽湪鍚姩鏈嶅姟鍣ㄦ椂浣跨敤--sql-mode=MAXDB鏈嶅姟鍣ㄩ夐」鎴栧湪杩愯鏃堕氳繃璁剧疆鍏ㄥ眬sql_mode鍙橀噺灏SQL鏈嶅姟鍣ㄦā寮忚缃负MAXDB锛
mysql> SET GLOBAL sql_mode=MAXDB锛
瀹㈡埛绔彲浠ユ寜鐓т笅闈㈡柟娉曡鏈嶅姟鍣ㄤ负瀹冪殑杩炴帴浠MAXDB妯″紡杩愯锛
mysql> SET SESSION sql_mode=MAXDB;
MySQL涓嶆帴鍙楀湪鏃ユ垨鏈堝垪鍖呮嫭涓涓浂鎴栧寘鍚潪娉曟棩鏈熷肩殑鏃堕棿鎴冲笺傝瑙勫垯鐨勫敮涓渚嬪鏄壒娈婂'0000-00-00 00:00:00'銆
浣犲彲浠ラ潪甯哥伒渚垮湴纭畾浠涔堟椂鍊欏垵濮嬪寲鍜屾洿鏂TIMESTAMP鍜屽鍝簺鍒楄繘琛屽垵濮嬪寲鍜屾洿鏂帮細
路 浣犲彲浠ュ皢褰撳墠鐨勬椂闂存埑鎸囧畾涓洪粯璁ゅ煎拰鑷姩鏇存柊鐨勫笺備絾鍙兘閫夋嫨涓涓紝鎴栬呬袱鑰呴兘涓嶉夈(涓嶅彲鑳戒竴涓垪閫夋嫨涓涓涓鸿屽彟涓涓垪閫夋嫨鍙︿竴涓涓猴級銆
路 浣犲彲浠ユ寚瀹氬摢涓TIMESTAMP鍒楄嚜鍔ㄥ垵濮嬪寲鎴栨洿鏂颁负褰撳墠鐨勬棩鏈熷拰鏃堕棿銆備笉鍐嶉渶瑕佷负绗1涓TIMESTAMP鍒椼
璇锋敞鎰忎笅闈㈣璁烘墍淇℃伅鍙傜敤浜庡垱寤烘椂鏈惎鐢MAXDB妯″紡鐨勮〃鐨TIMESTAMP鍒椼(濡備笂鎵杩帮紝MAXDB妯″紡浣垮垪鍒涘缓涓DATETIME鍒楋級銆鎺у埗TIMESTAMP鍒楃殑鍒濆鍖栧拰鏇存柊鐨勮鍒欏涓嬫墍绀猴細
路 濡傛灉涓涓〃鍐呯殑绗1涓TIMESTAMP鍒楁寚瀹氫负涓涓DEFAULT鍊硷紝鍒欎笉鑳藉拷鐣ャ 榛樿鍊煎彲浠ヤ负CURRENT_TIMESTAMP鎴栧父閲忔棩鏈熷拰鏃堕棿鍊笺
路 DEFAULT NULL涓庣1涓TIMESTAMP 鍒楃殑DEFAULT CURRENT_TIMESTAMP鐩稿悓銆傚浜庡叾瀹TIMESTAMP鍒楋紝DEFAULT NULL琚涓DEFAULT 0銆
路 琛ㄥ唴鐨勪换浣曚竴涓TIMESTAMP鍒楀彲浠ヨ缃负鑷姩鍒濆鍖栦负褰撳墠鏃堕棿鎴冲拰/鎴栨洿鏂般
路 鍦CREATE TABLE璇彞涓紝鍙互鐢ㄤ笅闈㈢殑浠讳綍涓绉嶆柟寮忓０鏄庣1涓TIMESTAMP鍒楋細
o 鐢DEFAULT CURRENT_TIMESTAMP鍜ON UPDATE CURRENT_TIMESTAMP瀛愬彞锛屽垪涓洪粯璁ゅ间娇鐢ㄥ綋鍓嶇殑鏃堕棿鎴筹紝骞朵笖鑷姩鏇存柊銆
o 涓嶄娇鐢DEFAULT鎴ON UPDATE瀛愬彞锛屼笌DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP鐩稿悓銆
o 鐢DEFAULT CURRENT_TIMESTAMP瀛愬彞涓嶇敤ON UPDATE瀛愬彞锛屽垪涓洪粯璁ゅ间娇鐢ㄥ綋鍓嶇殑鏃堕棿鎴充絾鏄笉鑷姩鏇存柊銆
o 涓嶇敤DEFAULT瀛愬彞浣嗙敤ON UPDATE CURRENT_TIMESTAMP瀛愬彞锛屽垪鏈夐粯璁ゅ0骞惰嚜鍔ㄦ洿鏂般
o 鐢ㄥ父閲DEFAULT鍊硷紝鍒楁湁缁欏嚭鐨 榛樿鍊笺傚鏋滃垪鏈変竴涓ON UPDATE CURRENT_TIMESTAMP瀛愬彞锛屽畠鑷姩鏇存柊锛屽惁鍒欎笉銆
鎹㈠彞璇濊锛屼綘鍙互涓哄垵濮嬪煎拰鑷姩鏇存柊鐨勫间娇鐢ㄥ綋鍓嶇殑鏃堕棿鎴筹紝鎴栬呭叾涓竴涓娇鐢紝鎴栬呬袱涓殕涓嶄娇鐢ㄣ(渚嬪锛屼綘鍙互鎸囧畾ON UPDATE鏉ュ惎鐢ㄨ嚜鍔ㄦ洿鏂拌屼笉璁╁垪鑷姩鍒濆鍖栵級銆
路 鍦DEFAULT鍜ON UPDATE瀛愬彞涓彲浠ヤ娇鐢CURRENT_TIMESTAMP銆CURRENT_TIMESTAMP()鎴栬NOW()銆傚畠浠潎鍏锋湁鐩稿悓鐨勬晥鏋溿
涓や釜灞炴х殑椤哄簭骞朵笉閲嶈銆傚鏋滀竴涓TIMESTAMP鍒楀悓鏃舵寚瀹氫簡DEFAULT鍜ON UPDATE锛屼换浣曚竴涓彲浠ュ湪鍙︿竴涓殑鍓嶉潰銆
渚嬪瓙锛屼笅闈㈣繖浜涜鍙ユ槸绛夋晥鐨勶細
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
路 瑕佷负TIMESTAMP鍒楄屼笉鏄1鍒楁寚瀹氳嚜鍔ㄩ粯璁ゆ垨鏇存柊锛屽繀椤婚氳繃灏嗙1涓TIMESTAMP鍒楁樉寮忓垎閰嶄竴涓父閲DEFAULT鍊兼潵绂佺敤鑷姩鍒濆鍖栧拰鏇存柊銆(渚嬪锛DEFAULT 0鎴DEFAULT'2003-01-01 00:00:00')銆傜劧鍚庯紝瀵逛簬鍏跺畠TIMESTAMP鍒楋紝瑙勫垯涓庣1涓TIMESTAMP鍒楃浉鍚岋紝渚嬪鎯呭喌鏄笉鑳藉拷鐣DEFAULT鍜ON UPDATE瀛愬彞銆傚鏋滆繖鏍峰仛锛屽垯涓嶄細鑷姩杩涜鍒濆鍖栨垨鏇存柊銆
渚嬪锛氫笅闈㈣繖浜涜鍙ユ槸绛夋晥鐨勶細
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
鍙互瀵规瘡涓繛鎺ヨ缃綋鍓嶇殑鏃跺尯锛岀浉鍏虫弿杩板弬瑙5.10.8鑺傦紝鈥淢ySQL鏈嶅姟鍣ㄦ椂鍖烘敮鎸佲銆TIMESTAMP鍊间互UTC鏍煎紡淇濆瓨锛屽瓨鍌ㄦ椂瀵瑰綋鍓嶇殑鏃跺尯杩涜杞崲锛屾绱㈡椂鍐嶈浆鎹㈠洖褰撳墠鐨勬椂鍖恒傚彧瑕佹椂鍖鸿瀹氬间负甯搁噺锛屼究鍙互寰楀埌淇濆瓨鏃剁殑鍊笺傚鏋滀繚瀛樹竴涓TIMESTAMP鍊硷紝搴旀洿鏀规椂鍖虹劧鍚庢绱㈣鍊硷紝瀹冧笌浣犱繚瀛樼殑鍊间笉鍚屻傝繖鏄洜涓哄湪涓や釜鏂瑰悜鐨勮浆鎹腑娌℃湁浣跨敤鐩稿悓鐨勬椂鍖恒傚綋鍓嶇殑鏃跺尯鍙互鐢ㄤ綔time_zone绯荤粺鍙橀噺鐨勫笺
鍙互鍦TIMESTAMP鍒楃殑瀹氫箟涓寘鎷NULL灞炴т互鍏佽鍒楀寘鍚NULL鍊笺備緥濡傦細
CREATE TABLE t
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
濡傛灉鏈寚瀹NULL灞炴э紝灏嗗垪璁剧疆涓NULL璁剧疆鍒欎細灏嗗畠璁剧疆涓哄綋鍓嶇殑鏃堕棿鎴炽傝娉ㄦ剰鍏佽NULL鍊肩殑TIMESTAMP鍒椾笉浼氶噰鐢ㄥ綋鍓嶇殑鏃堕棿鎴筹紝闄ら潪瑕佷箞鍏 榛樿鍊煎畾涔変负CURRENT_TIMESTAMP锛屾垨鑰NOW()鎴CURRENT_TIMESTAMP琚彃鍏ュ埌璇ュ垪鍐呫傛崲鍙ヨ瘽璇达紝鍙湁浣跨敤濡備笅瀹氫箟鍒涘缓锛屽畾涔変负 NULL鐨TIMESTAMP鍒楁墠浼氳嚜鍔ㄦ洿鏂帮細
CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP)锛
鍚﹀垯-涔熷氨鏄锛屽鏋滀娇鐢NULL鑰屼笉鏄DEFAULT TIMESTAMP鏉ュ畾涔TIMESTAMP鍒楋紝濡備笅鎵绀...
CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');
...鍒欏繀椤绘樉寮忔彃鍏ヤ竴涓搴斿綋鍓嶆棩鏈熷拰鏃堕棿鐨勫笺備緥濡傦細
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
MySQL浠'HH:MM:SS'鏍煎紡妫绱㈠拰鏄剧ずTIME鍊(鎴栧浜庡ぇ鐨勫皬鏃跺奸噰鐢'HHH:MM:SS'鏍煎紡)銆TIME鍊肩殑鑼冨洿鍙互浠'-838:59:59'鍒'838:59:59'銆傚皬鏃堕儴鍒嗕細鍥犳澶х殑鍘熷洜鏄TIME绫诲瀷涓嶄粎鍙互鐢ㄤ簬琛ㄧず涓澶╃殑鏃堕棿(蹇呴』灏忎簬24灏忔椂)锛岃繕鍙兘涓烘煇涓簨浠惰繃鍘荤殑鏃堕棿鎴栦袱涓簨浠朵箣闂寸殑鏃堕棿闂撮殧(鍙互澶т簬24灏忔椂锛屾垨鑰呯敋鑷充负璐)銆
浣犲彲浠ョ敤鍚勭鏍煎紡鎸囧畾TIME鍊硷細
路 'D HH:MM:SS.fraction'鏍煎紡鐨勫瓧绗︿覆銆傝繕鍙互浣跨敤涓嬮潰浠讳綍涓绉嶁闈炰弗鏍鈥濊娉曪細'HH:MM:SS.fraction'銆'HH:MM:SS'銆'HH:MM'銆'D HH:MM:SS'銆'D HH:MM'銆'D HH'鎴'SS'銆傝繖閲D琛ㄧず鏃ワ紝鍙互鍙0鍒34涔嬮棿鐨勫笺傝娉ㄦ剰MySQL杩樹笉淇濆瓨鍒嗘暟銆
路 'HHMMSS'鏍煎紡鐨勬病鏈夐棿鍓茬鐨勫瓧绗︿覆锛屽亣瀹氭槸鏈夋剰涔夌殑鏃堕棿銆備緥濡傦紝'101112'琚悊瑙ｄ负'10:11:12'锛屼絾'109712'鏄笉鍚堟硶鐨(瀹冩湁涓涓病鏈夋剰涔夌殑鍒嗛挓閮ㄥ垎)锛屽皢鍙樹负'00:00:00'銆
路 HHMMSS鏍煎紡鐨勬暟鍊硷紝鍋囧畾鏄湁鎰忎箟鐨勬椂闂淬備緥濡傦紝101112琚悊瑙ｄ负'10:11:12'銆備笅闈㈡牸寮忎篃鍙互鐞嗚В锛SS銆MMSS銆HHMMSS銆HHMMSS.fraction銆傝娉ㄦ剰MySQL杩樹笉淇濆瓨鍒嗘暟銆
路 鍑芥暟杩斿洖鐨勭粨鏋滐紝鍏跺奸傚悎TIME涓婁笅鏂囷紝渚嬪CURRENT_TIME銆
瀵逛簬鎸囧畾涓哄寘鎷椂闂撮儴鍒嗛棿鍓茬鐨勫瓧绗︿覆鐨TIME鍊硷紝濡傛灉鏃躲佸垎鎴栬呯鍊煎皬浜10锛屽垯涓嶉渶瑕佹寚瀹氫袱浣嶆暟銆'8:3:2'涓'08:03:02'鐩稿悓銆
涓TIME鍒楀垎閰嶇畝鍐欏兼椂搴旀敞鎰忋傛病鏈夊啋鍙凤紝MySQL瑙ｉ噴鍊兼椂鍋囧畾鏈鍙宠竟鐨勪袱浣嶈〃绀虹銆(MySQL瑙ｉ噴TIME鍊间负杩囧幓鐨勬椂闂磋屼笉鏄綋澶╃殑鏃堕棿锛銆渚嬪锛屼綘鍙兘璁や负'1112'鍜1112琛ㄧず'11:12:00'(11鐐硅繃12鍒)锛屼絾MySQL灏嗗畠浠В閲婁负'00:11:12'(11鍒嗭紝12 绉)銆傚悓鏍凤紝'12'鍜12 琚В閲婁负 '00:00:12'銆傜浉鍙嶏紝TIME鍊间腑浣跨敤鍐掑彿鍒欒偗瀹氳鐪嬩綔褰撳ぉ鐨勬椂闂淬備篃灏辨槸璇达紝'11:12'琛ㄧず'11:12:00'锛岃屼笉鏄'00:11:12'銆
瓒呭嚭TIME鑼冨洿浣嗗悎娉曠殑鍊艰瑁佷负鑼冨洿鏈鎺ヨ繎鐨勭鐐广備緥濡傦紝'-850:00:00'鍜'850:00:00'琚浆鎹负'-838:59:59'鍜'838:59:59'銆
鏃犳晥TIME鍊艰杞崲涓'00:00:00'銆傝娉ㄦ剰鐢变簬'00:00:00'鏈韩鏄竴涓悎娉TIME鍊硷紝鍙粠琛ㄥ唴淇濆瓨鐨勪竴涓'00:00:00'鍊艰繕涓嶈兘璇村嚭鍘熸潵鐨勫兼槸 '00:00:00'杩樻槸涓嶅悎娉曠殑鍊笺
11.3.3. YEAR绫诲瀷
YEAR绫诲瀷鏄竴涓崟瀛楄妭绫诲瀷鐢ㄤ簬琛ㄧず骞淬
MySQL浠YYYY鏍煎紡妫绱㈠拰鏄剧ずYEAR鍊笺傝寖鍥存槸1901鍒2155銆
鍙互鎸囧畾鍚勭鏍煎紡鐨YEAR鍊硷細
路 鍥涗綅瀛楃涓诧紝鑼冨洿涓'1901'鍒'2155'銆
路 鍥涗綅鏁板瓧锛岃寖鍥翠负1901鍒2155銆
路 涓や綅瀛楃涓诧紝鑼冨洿涓'00'鍒'99'銆'00'鍒'69'鍜'70'鍒'99'鑼冨洿鐨勫艰杞崲涓2000鍒2069鍜1970鍒1999鑼冨洿鐨YEAR鍊笺
路 涓や綅鏁存暟锛岃寖鍥翠负1鍒99銆1鍒69鍜70鍒99鑼冨洿鐨勫艰杞崲涓2001鍒2069鍜1970鍒1999鑼冨洿鐨YEAR鍊笺傝娉ㄦ剰涓や綅鏁存暟鑼冨洿涓庝袱浣嶅瓧绗︿覆鑼冨洿绋嶆湁涓嶅悓锛屽洜涓轰綘涓嶈兘鐩存帴灏嗛浂鎸囧畾涓烘暟瀛楀苟灏嗗畠瑙ｉ噴涓2000銆備綘蹇呴』灏嗗畠鎸囧畾涓轰竴涓瓧绗︿覆'0'鎴'00'鎴栧畠琚В閲婁负0000銆
路 鍑芥暟杩斿洖鐨勭粨鏋滐紝鍏跺奸傚悎YEAR涓婁笅鏂囷紝渚嬪NOW()銆
闈炴硶YEAR鍊艰杞崲涓0000銆
11.3.4. Y2K浜嬪疁鍜屾棩鏈熺被鍨
MySQL鏈韩瀵逛簬2000骞(Y2K)鏄畨鍏ㄧ殑(鍙傝1.4.5鑺傦紝鈥2000骞村吋瀹规р)锛屼絾杈撳叆缁MySQL鐨勫煎彲鑳戒笉瀹夊叏銆備换浣曞寘鍚袱浣嶅勾鍊肩殑杈撳叆閮戒細浠や汉妯＄硦锛屽洜涓轰笘绾笉鐭ラ亾銆傝繖浜涘煎繀椤昏В閲婁负鍥涗綅褰㈠紡锛屽洜涓MySQL鍐呴儴浣跨敤鍥涗綅鏉ヤ繚瀛樺勾銆
瀵逛簬DATETIME銆DATE銆TIMESTAMP鍜YEAR绫诲瀷锛MySQL浣跨敤浠ヤ笅瑙勫垯瑙ｉ噴鍚ā绯婂勾鍊肩殑鏃ユ湡锛
路 00-69鑼冨洿鐨勫勾鍊艰浆鎹负2000-2069銆
路 70-99鑼冨洿鐨勫勾鍊艰浆鎹负1970-1999銆
璇疯浣忚繖浜涜鍒欏彧鏄悎鐞嗙寽娴嬫暟鎹艰〃绀轰粈涔堛傚鏋MySQL浣跨敤鐨勫惎鍙戜笉鑳戒骇鐢熸纭殑鍊硷紝浣犲簲鎻愪緵鍖呭惈鍥涗綅骞村肩殑纭垏杈撳叆銆
ORDER BY鍙互姝ｇ‘鎺掑簭鏈変袱浣嶅勾鐨TIMESTAMP鎴YEAR鍊笺
閮ㄥ垎鍑芥暟濡MIN()鍜MAX()灏TIMESTAMP鎴YEAR杞崲涓轰竴涓暟瀛椼傝繖璇存槑浣跨敤鏈変袱浣嶅勾鍊肩殑鍊硷紝杩欎簺鍑芥暟涓嶈兘宸ヤ綔姝ｇ‘銆傚湪杩欑鎯呭喌涓嬬殑淇鏂规硶鏄皢TIMESTAMP鎴YEAR杞崲涓哄洓浣嶅勾鏍煎紡鎴栦娇鐢MIN(DATE_ADD(TIMESTAMP,INTERVAL 0 DAYS))銆
CHAR鍜VARCHAR绫诲瀷绫讳技锛屼絾瀹冧滑淇濆瓨鍜屾绱㈢殑鏂瑰紡涓嶅悓銆傚畠浠殑鏈澶ч暱搴﹀拰鏄惁灏鹃儴绌烘牸琚繚鐣欑瓑鏂归潰涔熶笉鍚屻傚湪瀛樺偍鎴栨绱㈣繃绋嬩腑涓嶈繘琛屽ぇ灏忓啓杞崲銆
CHAR鍜VARCHAR绫诲瀷澹版槑鐨勯暱搴﹁〃绀轰綘鎯宠淇濆瓨鐨勬渶澶у瓧绗︽暟銆備緥濡傦紝CHAR(30)鍙互鍗犵敤30涓瓧绗︺
CHAR鍒楃殑闀垮害鍥哄畾涓哄垱寤鸿〃鏃跺０鏄庣殑闀垮害銆傞暱搴﹀彲浠ヤ负浠0鍒255鐨勪换浣曞笺傚綋淇濆瓨CHAR鍊兼椂锛屽湪瀹冧滑鐨勫彸杈瑰～鍏呯┖鏍间互杈惧埌鎸囧畾鐨勯暱搴︺傚綋妫绱㈠埌CHAR鍊兼椂锛屽熬閮ㄧ殑绌烘牸琚垹闄ゆ帀銆傚湪瀛樺偍鎴栨绱㈣繃绋嬩腑涓嶈繘琛屽ぇ灏忓啓杞崲銆
VARCHAR鍒椾腑鐨勫间负鍙彉闀垮瓧绗︿覆銆傞暱搴﹀彲浠ユ寚瀹氫负0鍒65,535涔嬮棿鐨勫笺(VARCHAR鐨勬渶澶ф湁鏁堥暱搴︾敱鏈澶ц澶у皬鍜屼娇鐢ㄧ殑瀛楃闆嗙‘瀹氥傛暣浣撴渶澶ч暱搴︽槸65,532瀛楄妭锛銆
鍚CHAR瀵规瘮锛VARCHAR鍊间繚瀛樻椂鍙繚瀛橀渶瑕佺殑瀛楃鏁帮紝鍙﹀姞涓涓瓧鑺傛潵璁板綍闀垮害(濡傛灉鍒楀０鏄庣殑闀垮害瓒呰繃255锛屽垯浣跨敤涓や釜瀛楄妭)銆
VARCHAR鍊间繚瀛樻椂涓嶈繘琛屽～鍏呫傚綋鍊间繚瀛樺拰妫绱㈡椂灏鹃儴鐨勭┖鏍间粛淇濈暀锛岀鍚堟爣鍑SQL銆
濡傛灉鍒嗛厤缁CHAR鎴VARCHAR鍒楃殑鍊艰秴杩囧垪鐨勬渶澶ч暱搴︼紝鍒欏鍊艰繘琛岃鍓互浣垮叾閫傚悎銆傚鏋滆瑁佹帀鐨勫瓧绗︿笉鏄┖鏍硷紝鍒欎細浜х敓涓鏉¤鍛娿傚鏋滆鍓潪绌烘牸瀛楃锛屽垯浼氶犳垚閿欒(鑰屼笉鏄鍛)骞堕氳繃浣跨敤涓ユ牸SQL妯″紡绂佺敤鍊肩殑鎻掑叆銆傚弬瑙5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊銆
涓嬮潰鐨勮〃鏄剧ず浜嗗皢鍚勭瀛楃涓插间繚瀛樺埌CHAR(4)鍜VARCHAR(4)鍒楀悗鐨勭粨鏋滐紝璇存槑浜CHAR鍜VARCHAR涔嬮棿鐨勫樊鍒細
|
鍊 |
CHAR(4) |
瀛樺偍闇姹 |
VARCHAR(4) |
瀛樺偍闇姹 |
|
'' |
' ' |
4涓瓧鑺 |
'' |
1涓瓧鑺 |
|
'ab' |
'ab ' |
4涓瓧鑺 |
'ab ' |
3涓瓧鑺 |
|
'abcd' |
'abcd' |
4涓瓧鑺 |
'abcd' |
5涓瓧鑺 |
|
'abcdefgh' |
'abcd' |
4涓瓧鑺 |
'abcd' |
5涓瓧鑺 |
璇锋敞鎰忎笂琛ㄤ腑鏈鍚庝竴琛岀殑鍊煎彧閫傜敤涓嶄娇鐢ㄤ弗鏍兼ā寮鏃讹紱濡傛灉MySQL杩愯鍦ㄤ弗鏍兼ā寮忥紝瓒呰繃鍒楅暱搴︿笉鐨勫涓淇濆瓨锛屽苟涓斾細鍑虹幇閿欒銆
浠CHAR(4)鍜VARCHAR(4)鍒楁绱㈢殑鍊煎苟涓嶆绘槸鐩稿悓锛屽洜涓烘绱㈡椂浠CHAR鍒楀垹闄や簡灏鹃儴鐨勭┖鏍笺傞氳繃涓嬮潰鐨勪緥瀛愯鏄庤宸埆锛
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
+----------------+----------------+
| CONCAT(v, '+') | CONCAT(c, '+') |
+----------------+----------------+
| ab + | ab+ |
+----------------+----------------+
1 row in set (0.00 sec)
鏍规嵁鍒嗛厤缁欏垪鐨勫瓧绗﹂泦鏍″瑙勫垯瀵CHAR鍜VARCHAR鍒椾腑鐨勫艰繘琛屾帓搴忓拰姣旇緝銆
璇锋敞鎰忔墍鏈MySQL鏍″瑙勫垯灞炰簬PADSPACE绫汇傝繖璇存槑鍦MySQL涓殑鎵鏈CHAR鍜VARCHAR鍊兼瘮杈冩椂涓嶉渶瑕佽冭檻浠讳綍灏鹃儴绌烘牸銆備緥濡傦細
mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;
+--------------------+----------------------+
| myname = 'Monty ' | yourname = 'Monty ' |
+--------------------+----------------------+
| 1 | 1 |
+--------------------+----------------------+
1 row in set (0.00 sec)
璇锋敞鎰忔墍鏈MySQL鐗堟湰鍧囧姝わ紝骞朵笖瀹冧笉鍙SQL鏈嶅姟鍣ㄦā寮忕殑褰卞搷銆
瀵逛簬灏鹃儴濉厖瀛楃琚鍓帀鎴栨瘮杈冩椂灏嗗畠浠拷瑙嗘帀鐨勬儏褰紝濡傛灉鍒楃殑绱㈠紩闇瑕佸敮涓鐨勫硷紝鍦ㄥ垪鍐呮彃鍏ヤ竴涓彧鏄～鍏呭瓧绗︽暟涓嶅悓鐨勫煎皢浼氶犳垚澶嶅埗閿奸敊璇
CHAR BYTE鏄CHAR BINARY鐨勫埆鍚嶃傝繖鏄负浜嗕繚璇佸吋瀹规с
ASCII灞炴т负CHAR鍒楀垎閰latin1瀛楃闆嗐UNICODE灞炴у垎閰ucs2瀛楃闆嗐
BINARY鍜VARBINARY绫荤被浼间簬CHAR鍜VARCHAR锛屼笉鍚岀殑鏄畠浠寘鍚簩杩涘埗瀛楃涓茶屼笉瑕侀潪浜岃繘鍒跺瓧绗︿覆銆備篃灏辨槸璇达紝瀹冧滑鍖呭惈瀛楄妭瀛楃涓茶屼笉鏄瓧绗﹀瓧绗︿覆銆傝繖璇存槑瀹冧滑娌℃湁瀛楃闆嗭紝骞朵笖鎺掑簭鍜屾瘮杈冨熀浜庡垪鍊煎瓧鑺傜殑鏁板煎笺
BINARY鍜VARBINARY鍏佽鐨勬渶澶ч暱搴︿竴鏍凤紝濡傚悓CHAR鍜VARCHAR锛屼笉鍚岀殑鏄BINARY鍜VARBINARY鐨勯暱搴︽槸瀛楄妭闀垮害鑰屼笉鏄瓧绗﹂暱搴︺
BINARY鍜VARBINARY鏁版嵁绫诲瀷涓嶅悓浜CHAR BINARY鍜VARCHAR BINARY鏁版嵁绫诲瀷銆傚浜庡悗涓绉嶇被鍨嬶紝BINARY灞炴т笉浼氬皢鍒楄涓轰簩杩涘埗瀛楃涓插垪銆傜浉鍙嶏紝瀹冭嚧浣夸娇鐢ㄥ垪瀛楃闆嗙殑浜屽厓 鏍″瑙勫垯锛屽苟涓斿垪鑷韩鍖呭惈闈炰簩杩涘埗瀛楃瀛楃涓茶屼笉鏄簩杩涘埗瀛楄妭瀛楃涓层備緥濡CHAR(5) BINARY琚涓CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin锛屽亣瀹氶粯璁ゅ瓧绗﹂泦鏄latin1銆傝繖涓嶅悓浜BINARY(5)锛屽畠淇濆瓨5瀛楄妭浜岃繘鍒跺瓧绗︿覆锛屾病鏈夊瓧绗﹂泦鎴 鏍″瑙勫垯銆
褰撲繚瀛BINARY鍊兼椂锛屽湪瀹冧滑鍙宠竟濉厖鍊间互杈惧埌鎸囧畾闀垮害銆傚～鍏呭兼槸0x00(闆跺瓧鑺)銆傛彃鍏ュ兼椂鍦ㄥ彸渚ф坊鍔0x00 on锛屽苟涓旈夋嫨鏃朵笉鍒犻櫎灏鹃儴鐨勫瓧鑺傘傛瘮杈冩椂鎵鏈夊瓧鑺傚緢閲嶈锛屽寘鎷ORDER BY鍜DISTINCT鎿嶄綔銆傛瘮杈冩椂0x00瀛楄妭鍜岀┖鏍兼槸涓嶅悓鐨勶紝0x00<绌烘牸銆
渚嬪锛氬浜庝竴涓BINARY(3)鍒楋紝褰撴彃鍏ユ椂 'a' 鍙樹负 'a \0'銆'a\0'鎻掑叆鏃跺彉涓'a\0\0'銆傚綋閫夋嫨鏃朵袱涓彃鍏ョ殑鍊煎潎涓嶆洿鏀广
瀵逛簬VARBINARY锛屾彃鍏ユ椂涓嶅～鍏呭瓧绗︼紝閫夋嫨鏃朵笉瑁佸壀瀛楄妭銆傛瘮杈冩椂鎵鏈夊瓧鑺傚緢閲嶈锛屽寘鎷ORDER BY鍜DISTINCT鎿嶄綔銆傛瘮杈冩椂0x00瀛楄妭鍜岀┖鏍兼槸涓嶅悓鐨勶紝0x00<绌烘牸銆
瀵逛簬灏鹃儴濉厖瀛楃琚鍓帀鎴栨瘮杈冩椂灏嗗畠浠拷瑙嗘帀鐨勬儏褰紝濡傛灉鍒楃殑绱㈠紩闇瑕佸敮涓鐨勫硷紝鍦ㄥ垪鍐呮彃鍏ヤ竴涓彧鏄～鍏呭瓧绗︽暟涓嶅悓鐨勫煎皢浼氶犳垚澶嶅埗閿奸敊璇
濡傛灉浣犺鍒掍娇鐢ㄨ繖浜涙暟鎹被鍨嬫潵淇濆瓨浜岃繘鍒舵暟鎹苟涓旈渶瑕佹绱㈢殑鍊间笌淇濆瓨鐨勫煎畬鍏ㄧ浉鍚岋紝搴旇冭檻鍓嶉潰鎵杩扮殑濉厖鍜岃鍓壒寰併備笅闈㈢殑渚嬪瓙璇存槑浜嗙敤0x00濉厖鐨BINARY鍊煎浣曞奖鍝嶅垪鍊兼瘮杈冿細
mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 | 0 | 1 |
+--------+---------+-------------+
1 row in set (0.09 sec)
濡傛灉妫绱㈢殑鍊煎繀椤讳笌鎸囧畾杩涜瀛樺偍鑰屾病鏈夊～鍏呯殑鍊肩浉鍚岋紝鏈濂戒娇鐢BLOB鏁版嵁绫诲瀷銆
鍒涘缓琛ㄦ椂锛MySQL鍙互榛橀粯鏇存敼BINARY鎴VARBINARY鍒楃殑绫诲瀷銆傚弬瑙13.1.5.1鑺傦紝鈥滄矇瀵傜殑鍒楄鏍煎彉鏇粹銆
BLOB鏄竴涓簩杩涘埗澶у璞★紝鍙互瀹圭撼鍙彉鏁伴噺鐨勬暟鎹傛湁4绉BLOB绫诲瀷锛TINYBLOB銆BLOB銆MEDIUMBLOB鍜LONGBLOB銆傚畠浠彧鏄彲瀹圭撼鍊肩殑鏈澶ч暱搴︿笉鍚屻
鏈4绉TEXT绫诲瀷锛TINYTEXT銆TEXT銆MEDIUMTEXT鍜LONGTEXT銆傝繖浜涘搴4绉BLOB绫诲瀷锛屾湁鐩稿悓鐨勬渶澶ч暱搴﹀拰瀛樺偍闇姹傘
BLOB 鍒楄瑙嗕负浜岃繘鍒跺瓧绗︿覆(瀛楄妭瀛楃涓)銆TEXT鍒楄瑙嗕负闈炰簩杩涘埗瀛楃涓(瀛楃瀛楃涓)銆BLOB鍒楁病鏈夊瓧绗﹂泦锛屽苟涓旀帓搴忓拰姣旇緝鍩轰簬鍒楀煎瓧鑺傜殑鏁板煎笺TEXT鍒楁湁涓涓瓧绗﹂泦锛屽苟涓旀牴鎹瓧绗﹂泦鐨 鏍″瑙勫垯瀵瑰艰繘琛屾帓搴忓拰姣旇緝銆
鍦TEXT鎴BLOB鍒楃殑瀛樺偍鎴栨绱㈣繃绋嬩腑锛屼笉瀛樺湪澶у皬鍐欒浆鎹€
褰撴湭杩愯鍦ㄤ弗鏍兼ā寮忔椂锛屽鏋滀綘涓BLOB鎴TEXT鍒楀垎閰嶄竴涓秴杩囪鍒楃被鍨嬬殑鏈澶ч暱搴︾殑鍊煎硷紝鍊艰鎴彇浠ヤ繚璇侀傚悎銆傚鏋滄埅鎺夌殑瀛楃涓嶆槸绌烘牸锛屽皢浼氫骇鐢熶竴鏉¤鍛娿備娇鐢ㄤ弗鏍SQL妯″紡锛屼細浜х敓閿欒锛屽苟涓斿煎皢琚嫆缁濊屼笉鏄埅鍙栧苟缁欏嚭璀﹀憡銆傚弬瑙5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊銆
鍦ㄥぇ澶氭暟鏂归潰锛屽彲浠ュ皢BLOB鍒楄涓鸿兘澶熻冻澶熷ぇ鐨VARBINARY鍒椼傚悓鏍凤紝鍙互灏TEXT鍒楄涓VARCHAR鍒椼BLOB鍜TEXT鍦ㄤ互涓嬪嚑涓柟闈笉鍚屼簬VARBINARY鍜VARCHAR锛
路 褰撲繚瀛樻垨妫绱BLOB鍜TEXT鍒楃殑鍊兼椂涓嶅垹闄ゅ熬閮ㄧ┖鏍笺(杩欎笌VARBINARY鍜VARCHAR鍒楃浉鍚岋級銆
璇锋敞鎰忔瘮杈冩椂灏嗙敤绌烘牸瀵TEXT杩涜鎵╁厖浠ラ傚悎姣旇緝鐨勫璞★紝姝ｅCHAR鍜VARCHAR銆
路 瀵逛簬BLOB鍜TEXT鍒楃殑绱㈠紩锛屽繀椤绘寚瀹氱储寮曞墠缂鐨勯暱搴︺傚浜CHAR鍜VARCHAR锛屽墠缂闀垮害鏄彲閫夌殑銆傚弬瑙7.4.3鑺傦紝鈥滃垪绱㈠紩鈥銆
路 BLOB鍜TEXT鍒椾笉鑳芥湁 榛樿鍊笺
LONG鍜LONG VARCHAR瀵瑰簲MEDIUMTEXT鏁版嵁绫诲瀷銆傝繖鏄负浜嗕繚璇佸吋瀹规с傚鏋TEXT鍒楃被鍨嬩娇鐢BINARY灞炴э紝灏嗕负鍒楀垎閰嶅垪瀛楃闆嗙殑浜屽厓 鏍″瑙勫垯銆
MySQL杩炴帴绋嬪簭/ODBC灏BLOB鍊煎畾涔変负LONGVARBINARY锛屽皢TEXT鍊煎畾涔変负LONGVARCHAR銆
鐢变簬BLOB鍜TEXT鍊煎彲鑳戒細闈炲父闀匡紝浣跨敤瀹冧滑鏃跺彲鑳介亣鍒颁竴浜涚害鏉燂細
路 褰撴帓搴忔椂鍙娇鐢ㄨ鍒楃殑鍓max_sort_length涓瓧鑺傘max_sort_length鐨 榛樿鍊兼槸1024锛涜鍊煎彲浠ュ湪鍚姩mysqld鏈嶅姟鍣ㄦ椂浣跨敤--max_sort_length閫夐」杩涜鏇存敼銆傚弬瑙5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥銆
杩愯鏃跺鍔max_sort_length鐨勫煎彲浠ュ湪鎺掑簭鎴栫粍鍚堟椂浣挎洿澶氱殑瀛楄妭鏈夋剰涔夈備换浣曞鎴风鍙互鏇存敼鍏朵細璇max_sort_length鍙橀噺鐨勫硷細
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
-> ORDER BY comment;
褰撲綘鎯宠浣胯秴杩max_sort_length鐨勫瓧鑺傛湁鎰忎箟锛屽鍚暱鍊肩殑BLOB鎴TEXT鍒椾娇鐢GROUP BY鎴ORDER BY鐨勫彟涓绉嶆柟寮忔槸灏嗗垪鍊艰浆鎹负鍥哄畾闀垮害鐨勫璞°傛爣鍑嗘柟娉曟槸浣跨敤SUBSTRING鍑芥暟銆備緥濡傦紝涓嬮潰鐨勮鍙ュcomment鍒楃殑2000涓瓧鑺傝繘琛屾帓搴忥細
mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
-> ORDER BY SUBSTRING(comment,1,2000);
路 BLOB鎴TEXT瀵硅薄鐨勬渶澶уぇ灏忕敱鍏剁被鍨嬬‘瀹氾紝浣嗗湪瀹㈡埛绔拰鏈嶅姟鍣ㄤ箣闂村疄闄呭彲浠ヤ紶閫掔殑鏈澶у肩敱鍙敤鍐呭瓨鏁伴噺鍜岄氫俊缂撳瓨鍖哄ぇ灏忕‘瀹氥備綘鍙互閫氳繃鏇存敼max_allowed_packet鍙橀噺鐨勫兼洿鏀规秷鎭紦瀛樺尯鐨勫ぇ灏忥紝浣嗗繀椤诲悓鏃朵慨鏀规湇鍔″櫒鍜屽鎴风绋嬪簭銆備緥濡傦紝鍙互浣跨敤 mysql鍜mysqldump鏉ユ洿鏀瑰鎴风鐨max_allowed_packet鍊笺傚弬瑙7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥銆8.3鑺傦紝鈥渕ysql锛歁ySQL鍛戒护琛屽伐鍏封鍜8.8鑺傦紝鈥渕ysqldump锛氭暟鎹簱澶囦唤绋嬪簭鈥銆
姣忎釜BLOB鎴TEXT鍊煎垎鍒敱鍐呴儴鍒嗛厤鐨勫璞¤〃绀恒傝繖涓庡叾瀹冨垪绫诲瀷褰㈡垚瀵规瘮锛屽悗鑰呮槸褰撴墦寮琛ㄦ椂涓烘瘡1鍒楀垎閰嶅瓨鍌ㄥ紩鎿庛
ENUM鏄竴涓瓧绗︿覆瀵硅薄锛屽叾鍊兼潵鑷〃鍒涘缓鏃跺湪鍒楄瀹氫腑鏄惧紡鏋氫妇鐨勪竴鍒楀笺
鍦ㄦ煇浜涙儏鍐典笅锛ENUM鍊间篃鍙互涓虹┖瀛楃涓('')鎴NULL锛
路 濡傛灉浣犲皢涓涓潪娉曞兼彃鍏ENUM(涔熷氨鏄锛屽厑璁哥殑鍊煎垪涔嬪鐨勫瓧绗︿覆)锛屽皢鎻掑叆绌哄瓧绗︿覆浠ヤ綔涓虹壒娈婇敊璇笺傝瀛楃涓蹭笌鈥滄櫘閫氣濈┖瀛楃涓蹭笉鍚岋紝璇ュ瓧绗︿覆鏈夋暟鍊煎0銆傚悗闈㈡湁璇︾粏璁ㄨ銆
路 濡傛灉灏ENUM鍒楀０鏄庝负鍏佽NULL锛NULL鍊煎垯涓鸿鍒楃殑涓涓湁鏁堝硷紝骞朵笖 榛樿鍊间负NULL銆傚鏋ENUM鍒楄澹版槑涓NOT NULL锛屽叾榛樿鍊间负鍏佽鐨勫煎垪鐨勭1涓厓绱犮
姣忎釜鏋氫妇鍊兼湁涓涓储寮曪細
路 鏉ヨ嚜鍒楄瀹氱殑鍏佽鐨勫煎垪涓殑鍊间粠1寮濮嬬紪鍙枫
路 绌哄瓧绗︿覆閿欒鍊肩殑绱㈠紩鍊兼槸0銆傝繖璇存槑浣犲彲浠ヤ娇鐢ㄤ笅闈㈢殑SELECT璇彞鏉ユ壘鍑哄垎閰嶄簡闈炴硶ENUM鍊肩殑琛岋細
路 mysql> SELECT * FROM tbl_name WHERE enum_col=0;
路 NULL鍊肩殑绱㈠紩鏄NULL銆
渚嬪锛屽畾涔変负ENUM鐨勫垪('one'锛'two'锛'three')鍙互鏈変笅闈㈡墍绀轰换浣曞笺傝繕鏄剧ず浜嗘瘡涓肩殑绱㈠紩锛
|
鍊 |
绱㈠紩 |
|
NULL |
NULL |
|
'' |
0 |
|
'one' |
1 |
|
'two' |
2 |
|
'three' |
3 |
鏋氫妇鏈澶氬彲浠ユ湁65,535涓厓绱犮
褰撳垱寤鸿〃鏃讹紝ENUM鎴愬憳鍊肩殑灏鹃儴绌烘牸灏嗚嚜鍔ㄨ鍒犻櫎銆
褰撴绱㈡椂锛屼繚瀛樺湪ENUM鍒楃殑鍊间娇鐢ㄥ垪瀹氫箟涓墍浣跨敤鐨勫ぇ灏忓啓鏉ユ樉绀恒傝娉ㄦ剰鍙互涓ENUM鍒楀垎閰嶅瓧绗﹂泦鍜 鏍″瑙勫垯銆傚浜庝簩杩涘埗鎴栧ぇ灏忓啓鏁忔劅鐨勬牎瀵硅鍒欙紝褰撲负鍒楀垎閰嶅兼椂搴旇冭檻澶у皬鍐欍
濡傛灉鍦ㄦ暟鍊间笂涓嬫枃涓绱竴涓ENUM鍊硷紝灏嗚繑鍥炲垪鍊肩殑绱㈠紩銆備緥濡傦紝浣犲彲浠ヨ繖鏍蜂粠ENUM鍒楁悳绱㈡暟鍊煎硷細
mysql> SELECT enum_col+0 FROM tbl_name;
濡傛灉灏嗕竴涓暟瀛椾繚瀛樺埌ENUM鍒楋紝鏁板瓧琚涓虹储寮曪紝骞朵笖淇濆瓨鐨勫兼槸璇ョ储寮曞搴旂殑鏋氫妇鎴愬憳銆(浣嗘槸锛岃繖涓嶉傚悎LOAD DATA锛屽畠灏嗘墍鏈夎緭鍏ヨ涓哄瓧绗︿覆锛銆涓嶅缓璁娇鐢ㄧ被浼兼暟瀛楃殑鏋氫妇鍊兼潵瀹氫箟涓涓ENUM鍒楋紝鍥犱负杩欏緢瀹规槗寮曡捣娣锋穯銆備緥濡傦紝涓嬮潰鐨勫垪鍚湁瀛楃涓插'0'銆'1'鍜'2'鐨勬灇涓炬垚鍛橈紝浣嗘暟鍊肩储寮曞间负1銆2鍜3锛
numbers ENUM('0','1','2')
鏍规嵁鏋氫妇鎴愬憳鍦ㄥ垪瀹氫箟涓垪鍑虹殑椤哄簭瀵ENUM鍊艰繘琛屾帓搴忋(鎹㈠彞璇濊锛ENUM鍊兼牴鎹储寮曠紪鍙疯繘琛屾帓搴忥級銆渚嬪锛屽浜ENUM('a'锛'b')锛'a'鎺掑湪'b'鍓嶉潰锛屼絾瀵逛簬ENUM('b'锛'a')锛'b'鎺掑湪'a'鍓嶉潰銆傜┖瀛楃涓叉帓鍦ㄩ潪绌哄瓧绗︿覆鍓嶉潰锛屽苟涓NULL鍊兼帓鍦ㄦ墍鏈夊叾瀹冩灇涓惧煎墠闈€傝鎯抽槻姝㈡剰鎯充笉鍒扮殑缁撴灉锛屾寜瀛楁瘝椤哄簭瑙勫畾ENUM鍒椼傝繕鍙互浣跨敤GROUP BY CAST(col AS CHAR)鎴GROUP BY CONCAT(col)鏉ョ‘淇濇寜鐓ц瘝姹囧鍒楄繘琛屾帓搴忚屼笉鏄敤绱㈠紩鏁板瓧銆
濡傛灉浣犳兂瑕佺‘瀹氫竴涓ENUM鍒楃殑鎵鏈夊彲鑳界殑鍊硷紝浣跨敤SHOW COLUMNS FROM tbl_name LIKE enum_col锛屽苟瑙ｆ瀽杈撳嚭涓2鍒楃殑ENUM瀹氫箟銆
11.4.5. SET绫诲瀷
SET鏄竴涓瓧绗︿覆瀵硅薄锛屽彲浠ユ湁闆舵垨澶氫釜鍊硷紝鍏跺兼潵鑷〃鍒涘缓鏃惰瀹氱殑鍏佽鐨勪竴鍒楀笺傛寚瀹氬寘鎷涓SET鎴愬憳鐨SET鍒楀兼椂鍚勬垚鍛樹箣闂寸敤閫楀彿(鈥,鈥)闂撮殧寮銆傝繖鏍SET鎴愬憳鍊兼湰韬笉鑳藉寘鍚楀彿銆
渚嬪锛屾寚瀹氫负SET('one', 'two') NOT NULL鐨勫垪鍙互鏈変笅闈㈢殑浠讳綍鍊硷細
''
'one'
'two'
'one,two'
SET鏈澶氬彲浠ユ湁64涓笉鍚岀殑鎴愬憳銆
褰撳垱寤鸿〃鏃讹紝SET鎴愬憳鍊肩殑灏鹃儴绌烘牸灏嗚嚜鍔ㄨ鍒犻櫎銆
褰撴绱㈡椂锛屼繚瀛樺湪SET鍒楃殑鍊间娇鐢ㄥ垪瀹氫箟涓墍浣跨敤鐨勫ぇ灏忓啓鏉ユ樉绀恒傝娉ㄦ剰鍙互涓SET鍒楀垎閰嶅瓧绗﹂泦鍜 鏍″瑙勫垯銆傚浜庝簩杩涘埗鎴栧ぇ灏忓啓鏁忔劅鐨勬牎瀵硅鍒欙紝褰撲负鍒楀垎閰嶅兼椂搴旇冭檻澶у皬鍐欍
MySQL鐢ㄦ暟瀛椾繚瀛SET鍊硷紝鎵淇濆瓨鍊肩殑浣庨樁浣嶅搴旂1涓SET鎴愬憳銆傚鏋滃湪鏁板间笂涓嬫枃涓绱竴涓SET鍊硷紝妫绱㈢殑鍊肩殑浣嶈缃搴旂粍鎴愬垪鍊肩殑SET鎴愬憳銆備緥濡傦紝浣犲彲浠ヨ繖鏍蜂粠涓涓SET鍒楁绱㈡暟鍊煎硷細
mysql> SELECT set_col+0 FROM tbl_name;
濡傛灉灏嗕竴涓暟瀛椾繚瀛樺埌SET鍒椾腑锛屾暟瀛椾腑浜岃繘鍒惰〃绀轰腑鐨勪綅纭畾浜嗗垪鍊间腑鐨SET鎴愬憳銆傚浜庢寚瀹氫负SET('a','b','c','d')鐨勫垪锛屾垚鍛樻湁涓嬮潰鐨勫崄杩涘埗鍜屼簩杩涘埗鍊硷細
|
SET鎴愬憳 |
鍗佽繘鍒跺 |
浜岃繘鍒跺 |
|
'a' |
1 |
0001 |
|
'b' |
2 |
0010 |
|
'c' |
4 |
0100 |
|
'd' |
8 |
1000 |
濡傛灉浣犱负璇ュ垪鍒嗛厤涓涓9锛屽叾浜岃繘鍒跺舰寮忎负1001锛屽洜姝ょ1涓拰绗4涓SET鍊兼垚鍛'a'鍜'd'琚夋嫨锛岀粨鏋滃间负 'a,d'銆
瀵逛簬鍖呭惈澶氫釜SET鍏冪礌鐨勫硷紝褰撴彃鍏ュ兼椂鍏冪礌鎵鍒楃殑椤哄簭骞朵笉閲嶈銆傚湪鍊间腑涓涓粰瀹氱殑鍏冪礌鍒椾簡澶氬皯娆′篃涓嶉噸瑕併傚綋浠ュ悗妫绱㈣鍊兼椂锛屽间腑鐨勬瘡涓厓绱犲嚭鐜颁竴娆★紝鏍规嵁琛ㄥ垱寤烘椂鎸囧畾鐨勯『搴忓垪鍑哄厓绱犮備緥濡傦紝鍋囧畾鏌愪釜鍒楁寚瀹氫负SET('a','b','c','d')锛
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
鎻掑叆鍊'a,d'銆'd,a'銆'a,d,d'銆'a,d,a'鍜'd,a,d':
mysql> INSERT INTO myset (col) VALUES
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
褰撴绱㈡椂鎵鏈夎繖浜涘兼樉绀轰负 'a,d'锛
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)
濡傛灉灏SET鍒楄缃负涓涓笉鏀寔鐨勫硷紝鍒欒鍊艰蹇界暐骞跺彂鍑鸿鍛婏細
mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
6 rows in set (0.01 sec)
SET鍊兼寜鏁板瓧椤哄簭鎺掑簭銆NULL鍊兼帓鍦ㄩ潪NULL SET鍊肩殑鍓嶉潰銆
閫氬父鎯呭喌锛屽彲浠ヤ娇鐢FIND_IN_SET()鍑芥暟鎴LIKE鎿嶄綔绗︽悳绱SET鍊硷細
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
绗1涓鍙ユ壘鍑SET_col鍖呭惈value set鎴愬憳鐨勮銆傜2涓被浼硷紝浣嗘湁鎵涓嶅悓锛氬畠鍦ㄥ叾瀹冨湴鏂规壘鍑set_col鍖呭惈value鐨勮锛岀敋鑷虫槸鍦ㄥ彟涓涓SET鎴愬憳鐨勫瓙瀛楃涓蹭腑銆
涓嬮潰鐨勮鍙ヤ篃鏄悎娉曠殑锛
mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
绗1涓鍙ュ鎵惧寘鍚1涓set鎴愬憳鐨勫笺傜2涓鍙ュ鎵句竴涓‘鍒囧尮閰嶇殑鍊笺傚簲娉ㄦ剰绗2绫荤殑姣旇緝銆傚皢set鍊间笌'val1,val2'姣旇緝杩斿洖鐨勭粨鏋滀笌鍚'val2,val1'姣旇緝杩斿洖鐨勭粨鏋滀笉鍚屻傛寚瀹氬兼椂鐨勯『搴忓簲涓庡湪鍒楀畾涔変腑鎵鍒楃殑椤哄簭鐩稿悓銆
濡傛灉鎯宠涓SET鍒楃‘瀹氭墍鏈夊彲鑳界殑鍊硷紝浣跨敤SHOW COLUMNS FROM tbl_name LIKE set_col骞惰В鏋愯緭鍑轰腑绗2鍒楃殑SET瀹氫箟銆
11.5. 鍒楃被鍨嬪瓨鍌ㄩ渶姹
鏍规嵁绫诲埆鍒楀嚭浜MySQL鏀寔鐨勬瘡涓垪绫诲瀷鐨勫瓨鍌ㄩ渶姹傘
MyISAM琛ㄤ腑琛岀殑鏈澶уぇ灏忎负65,534瀛楄妭銆傛瘡涓BLOB鍜TEXT鍒 璐︽埛鍙崰鍏朵腑鐨5鑷9涓瓧鑺傘
濡傛灉MyISAM琛ㄥ寘鎷彉闀垮垪绫诲瀷锛岃褰曟牸寮忎篃鏄彲鍙橀暱搴︺傚綋鍒涘缓琛ㄦ椂锛屽湪鏌愪簺鏉′欢涓嬶紝MySQL鍙互灏嗕竴涓垪浠庡彉闀跨被鍨嬫敼涓哄浐瀹氶暱搴︾殑绫诲瀷鎴栧弽涔嬩害鐒躲傝缁嗕俊鎭弬瑙13.1.5.1鑺傦紝鈥滄矇瀵傜殑鍒楄鏍煎彉鏇粹銆
鏁板肩被鍨嬪瓨鍌ㄩ渶姹
|
鍒楃被鍨 |
瀛樺偍闇姹 |
|
TINYINT |
1涓瓧鑺 |
|
SMALLINT |
2涓瓧鑺 |
|
MEDIUMINT |
3涓瓧鑺 |
|
INT, INTEGER |
4涓瓧鑺 |
|
BIGINT |
8涓瓧鑺 |
|
FLOAT(p) |
濡傛灉0 <= p <= 24涓4涓瓧鑺, 濡傛灉25 <= p <= 53涓8涓瓧鑺 |
|
FLOAT |
4涓瓧鑺 |
|
DOUBLE [PRECISION], item REAL |
8涓瓧鑺 |
|
DECIMAL(M,D), NUMERIC(M,D) |
鍙橀暱锛涘弬瑙佷笅闈㈢殑璁ㄨ |
|
BIT(M) |
澶х害(M+7)/8涓瓧鑺 |
DECIMAL(鍜NUMERIC)鐨勫瓨鍌ㄩ渶姹備笌鍏蜂綋鐗堟湰鏈夊叧锛
浣跨敤浜岃繘鍒舵牸寮忓皢9涓崄杩涘埗(鍩轰簬10)鏁板帇缂╀负4涓瓧鑺傛潵琛ㄧずDECIMAL鍒楀笺傛瘡涓肩殑鏁存暟鍜屽垎鏁伴儴鍒嗙殑瀛樺偍鍒嗗埆纭畾銆傛瘡涓9浣嶆暟鐨勫嶆暟闇瑕4涓瓧鑺傦紝骞朵笖鈥滃墿浣欑殑鈥濅綅闇瑕4涓瓧鑺傜殑涓閮ㄥ垎銆備笅琛ㄧ粰鍑轰簡瓒呭嚭浣嶆暟鐨勫瓨鍌ㄩ渶姹傦細
|
鍓╀綑鐨 |
瀛楄妭 |
|
浣嶆暟 |
鏁扮洰 |
|
0 |
0 |
|
1 |
1 |
|
2 |
1 |
|
3 |
2 |
|
4 |
2 |
|
5 |
3 |
|
6 |
3 |
|
7 |
4 |
|
8 |
4 |
|
9 |
4 |
鏃ユ湡鍜屾椂闂寸被鍨嬬殑瀛樺偍闇姹
|
鍒楃被鍨 |
瀛樺偍闇姹 |
|
DATE |
3涓瓧鑺 |
|
DATETIME |
8涓瓧鑺 |
|
TIMESTAMP |
4涓瓧鑺 |
|
TIME |
3涓瓧鑺 |
|
YEAR |
1涓瓧鑺 |
瀛楃涓茬被鍨嬬殑瀛樺偍闇姹
|
鍒楃被鍨 |
瀛樺偍闇姹 |
|
CHAR(M) |
M涓瓧鑺傦紝0 <= M <= 255 |
|
VARCHAR(M) |
L+1涓瓧鑺傦紝鍏朵腑L <= M 涓0 <= M <= 65535(鍙傝涓嬮潰鐨勬敞閲) |
|
BINARY(M) |
M涓瓧鑺傦紝0 <= M <= 255 |
|
VARBINARY(M) |
L+1涓瓧鑺傦紝鍏朵腑L <= M 涓0 <= M <= 255 |
|
TINYBLOB, TINYTEXT |
L+1涓瓧鑺傦紝鍏朵腑L < 28 |
|
BLOB, TEXT |
L+2涓瓧鑺傦紝鍏朵腑L < 216 |
|
MEDIUMBLOB, MEDIUMTEXT |
L+3涓瓧鑺傦紝鍏朵腑L < 224 |
|
LONGBLOB, LONGTEXT |
L+4涓瓧鑺傦紝鍏朵腑L < 232 |
|
ENUM('value1','value2',...) |
1鎴2涓瓧鑺傦紝鍙栧喅浜庢灇涓惧肩殑涓暟(鏈澶65,535涓) |
|
SET('value1','value2',...) |
1銆2銆3銆4鎴栬8涓瓧鑺傦紝鍙栧喅浜set鎴愬憳鐨勬暟鐩(鏈澶64涓垚鍛) |
VARCHAR銆BLOB鍜TEXT绫绘槸鍙橀暱绫诲瀷銆傛瘡涓被鍨嬬殑瀛樺偍闇姹傚彇鍐充簬鍒楀肩殑瀹為檯闀垮害(鐢ㄥ墠闈㈢殑琛ㄤ腑鐨L琛ㄧず)锛岃屼笉鏄绫诲瀷鐨勬渶澶у彲鑳界殑澶у皬銆備緥濡傦紝VARCHAR(10)鍒楀彲浠ュ绾虫渶澶ч暱搴︿负10鐨勫瓧绗︿覆銆傚疄闄呭瓨鍌ㄩ渶姹傛槸瀛楃涓(L)鐨勯暱搴︼紝鍔犱笂涓涓褰曞瓧绗︿覆闀垮害鐨勫瓧鑺傘傚浜庡瓧绗︿覆'abcd'锛L鏄4锛屽瓨鍌ㄩ渶瑕5涓瓧鑺傘
瀵逛簬CHAR銆VARCHAR鍜TEXT绫诲瀷锛屽墠闈㈢殑琛ㄤ腑鐨勫L鍜M搴旇В閲婁负瀛楃鏁扮洰锛屽苟涓斿垪瀹氫箟涓殑杩欎簺绫诲瀷鐨勯暱搴﹁〃绀哄瓧绗︽暟鐩備緥濡傦紝瑕佹兂淇濆瓨涓涓TINYTEXT鍊奸渶瑕L瀛楃+ 1涓瓧鑺傘
瑕佹兂璁＄畻鐢ㄤ簬淇濆瓨鍏蜂綋CHAR銆VARCHAR鎴栬TEXT鍒楀肩殑瀛楄妭鏁帮紝闇瑕佽冭檻璇ュ垪浣跨敤鐨勫瓧绗﹂泦銆傚湪鍏蜂綋鎯呭喌涓紝褰撲娇鐢Unicode鏃讹紝蹇呴』璁颁綇鎵鏈Unicode瀛楃浣跨敤鐩稿悓鐨勫瓧鑺傛暟銆備负浜嗙粏鍒嗙敤浜庝笉鍚岀被Unicode瀛楃浣跨敤鐨勫瓨鍌紝鍙傝10.5鑺傦紝鈥淯nicode鏀寔鈥銆
娉ㄩ噴锛VARCHAR鍒楃殑鏈夋晥鏈澶ч暱搴︿负65,532瀛楃銆
NDBCLUSTER寮曟搸鍙敮鎸佸浐瀹氬搴︾殑鍒椼傝繖璇存槑MySQL绨囦腑鐨勮〃涓殑VARCHAR鍒楃殑琛屼负濡傚悓绫诲瀷CHAR(涓嶅悓鐨勬槸姣忎釜璁板綍浠嶇劧鏈変竴涓澶栧瓧鑺傜┖闂)銆備緥濡傦紝鍦Cluster琛ㄤ腑锛屽０鏄庝负VARCHAR(100)鐨勫垪涓殑姣忎釜璁板綍瀛樺偍鏃跺皢鍗犵敤101涓瓧鑺傦紝鏃犺瀹為檯瀛樺偍鐨勮褰曚腑鐨勫瓧绗︿覆鐨勯暱搴︿负澶氬皯銆
BLOB鍜TEXT绫婚渶瑕
1銆2銆3鎴栬4涓瓧鑺傛潵璁板綍鍒楀肩殑闀垮害锛屽彇鍐充簬璇ョ被鐨勬渶澶у彲鑳界殑闀垮害銆傚弬瑙11.4.3鑺傦紝鈥淏LOB鍜孴EXT绫诲瀷鈥銆
鍦NDB Cluster瀛樺偍寮曟搸涓紝TEXT鍜BLOB鍒楃殑瀹炴柦鏄笉鍚岀殑锛屽叾涓TEXT鍒椾腑鐨勬瘡涓褰曠敱涓や釜鍗曠嫭閮ㄥ垎缁勬垚銆備竴涓槸鍥哄畾澶у皬(256瀛楄妭)锛屽苟涓斿疄闄呬笂淇濆瓨鍦ㄥ師琛ㄤ腑銆傚彟涓涓寘鎷秴鍑256瀛楄妭鐨勪换浣曟暟鎹紝淇濆瓨鍦ㄩ殣鍚殑琛ㄤ腑銆傜2涓〃涓殑璁板綍鎬绘槸2,000瀛楄妭闀裤傝繖璇存槑濡傛灉size<= 256锛TEXT鍒楃殑澶у皬涓256(鍏朵腑size琛ㄧず璁板綍鐨勫ぇ灏)锛涘惁鍒欙紝澶у皬鏄256 +size+(2000鈥(size鈥256)%2000)銆
ENUM瀵硅薄鐨勫ぇ灏忕敱涓嶅悓鐨勬灇涓惧肩殑鏁扮洰纭畾銆傛灇涓剧敤涓涓瓧鑺傦紝鍙互鏈255涓彲鑳界殑鍊笺傚綋鏋氫妇鐨勫间綅浜256鍜65,535涔嬮棿鏃讹紝鐢ㄤ袱涓瓧鑺傘傚弬瑙11.4.4鑺傦紝鈥淓NUM绫诲瀷鈥銆
SET瀵硅薄鐨勫ぇ灏忕敱涓嶅悓鐨set鎴愬憳鐨勬暟閲忕‘瀹氥傚鏋set澶у皬鏄N锛屽璞″崰(N+7)/8涓瓧鑺傦紝鍥涜垗浜斿叆鍒1銆2銆3銆4鎴栬8涓瓧鑺傘SET鏈澶氬彲浠ユ湁64涓垚鍛樸傚弬瑙11.4.5鑺傦紝鈥淪ET绫诲瀷鈥銆
涓轰簡浼樺寲瀛樺偍锛屽湪浠讳綍鎯呭喌涓嬪潎搴斾娇鐢ㄦ渶绮剧‘鐨勭被鍨嬨備緥濡傦紝濡傛灉鍒楃殑鍊肩殑鑼冨洿涓轰粠1鍒99999锛岃嫢浣跨敤鏁存暟锛屽垯MEDIUMINT UNSIGNED鏄ソ鐨勭被鍨嬨傚湪鎵鏈夊彲浠ヨ〃绀鸿鍒楀肩殑绫诲瀷涓紝璇ョ被鍨嬩娇鐢ㄧ殑瀛樺偍鏈灏戙
鐢ㄧ簿搴︿负65浣嶅崄杩涘埗鏁(鍩轰簬10)瀵DECIMAL 鍒楄繘琛屾墍鏈夊熀鏈绠(+銆-銆*銆/)銆傚弬瑙11.1.1鑺傦紝鈥滄暟鍊肩被鍨嬫杩扳銆
浣跨敤鍙岀簿搴︽搷浣滃DECIMAL鍊艰繘琛岃绠椼傚鏋滃噯纭害涓嶆槸澶噸瑕佹垨濡傛灉閫熷害涓烘渶楂樹紭鍏堢骇锛DOUBLE绫诲瀷鍗宠冻澶熶簡銆備负浜嗚揪鍒伴珮绮惧害锛屽彲浠ヨ浆鎹㈠埌淇濆瓨鍦BIGINT涓殑瀹氱偣绫诲瀷銆傝繖鏍峰彲浠ョ敤64浣嶆暣鏁拌繘琛屾墍鏈夎绠楋紝鏍规嵁闇瑕佸皢缁撴灉杞崲鍥炴诞鐐瑰笺
涓轰簡浣跨敤鐢卞叾瀹冨崠鏂圭紪鍐欑殑SQL鎵ц浠ｇ爜锛MySQL鎸夌収涓嬭〃鎵绀哄鍒楃被鍨嬭繘琛屾槧灏勩傞氳繃杩欎簺鏄犲皠锛屽彲浠ュ緢瀹规槗鍦颁粠鍏跺畠鏁版嵁搴撳紩鎿庡皢琛ㄥ畾涔夊鍏ュ埌MySQL涓細
|
鍏跺畠鍗栨柟绫诲瀷 |
MySQL绫诲瀷 |
|
BOOL, |
TINYINT |
|
BOOLEAN |
TINYINT |
|
CHAR VARYING(M) |
VARCHAR(M) |
|
DEC |
DECIMAL |
|
FIXED |
DECIMAL |
|
FLOAT4 |
FLOAT |
|
FLOAT8 |
DOUBLE |
|
INT1 |
TINYINT |
|
INT2 |
SMALLINT |
|
INT3 |
MEDIUMINT |
|
INT4 |
INT |
|
INT8 |
BIGINT |
|
LONG VARBINARY |
MEDIUMBLOB |
|
LONG VARCHAR |
MEDIUMTEXT |
|
LONG |
MEDIUMTEXT |
|
MIDDLEINT |
MEDIUMINT |
|
NUMERIC |
DECIMAL |
鍦ㄥ垱寤鸿〃鏃跺鍒楃被鍨嬭繘琛屾槧灏勶紝鐒跺悗鍘熸潵鐨勭被鍨嬪畾涔夎涓㈠純銆傚鏋滀綘浣跨敤鍏跺畠鍗栨柟鐨勭被鍨嬪垱寤轰竴涓〃锛岀劧鍚庢墽琛DESCRIBE tbl_name璇彞锛MySQL浣跨敤绛夋晥鐨MySQL绫诲瀷鏉ユ姤鍛婅〃鐨勭粨鏋勩備緥濡傦細
mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG, d NUMERIC);
Query OK, 0 rows affected (0.08 sec)
mysql> DESCRIBE t;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | double | YES | | NULL | |
| c | mediumtext | YES | | NULL | |
| d | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
杩欐槸MySQL鍙傝冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝冩墜鍐岋紝璇疯闂dev.mysql.com銆 鍘熷鍙傝冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆