Chapter聽11.聽Column Types - MySQL 5.1参考手册中文版

涓嬮潰涓烘暟鍊煎垪绫诲瀷鐨勬�傝堪銆傝�︾粏淇℃伅鍙傝��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(2⁸鈥�1)瀛楄妭鐨�BLOB鍒椼��

路         TINYTEXT

鏈�澶ч暱搴︿负255(2⁸鈥�1)瀛楃�︾��TEXT鍒椼��

路         BLOB[(M)]

鏈�澶ч暱搴︿负65,535(2¹⁶鈥�1)瀛楄妭鐨�BLOB鍒椼��

鍙�浠ョ粰鍑鸿�ョ被鍨嬬殑鍙�閫夐暱搴�M銆傚�傛灉缁欏嚭锛屽��MySQL灏嗗垪鍒涘缓涓烘渶灏忕殑浣嗚冻浠ュ�圭��M瀛楄妭闀跨殑鍊肩殑BLOB绫诲瀷銆�

路         TEXT[(M)]

鏈�澶ч暱搴︿负65,535(2¹⁶鈥�1)瀛楃�︾��TEXT鍒椼��

鍙�浠ョ粰鍑哄彲閫夐暱搴�M銆傚垯MySQL灏嗗垪鍒涘缓涓烘渶灏忕殑浣嗚冻浠ュ�圭��M瀛楃�﹂暱鐨勫�肩殑TEXT绫诲瀷銆�

路         MEDIUMBLOB

鏈�澶ч暱搴︿负16,777,215(2²⁴鈥�1)瀛楄妭鐨�BLOB鍒椼��

路         MEDIUMTEXT

鏈�澶ч暱搴︿负16,777,215(2²⁴鈥�1)瀛楃�︾��TEXT鍒椼��

路         LONGBLOB

鏈�澶ч暱搴︿负4,294,967,295鎴�4GB(2³²鈥�1)瀛楄妭鐨�BLOB鍒椼��LONGBLOB鍒楃殑鏈�澶�鏈夋晥(鍏佽�哥��)闀垮害鍙栧喅浜庡�㈡埛绔�/鏈嶅姟鍣ㄥ崗璁�涓�閰嶇疆鏈�澶у寘澶у皬鍜屽彲鐢ㄧ殑鍐呭瓨銆�

路         LONGTEXT

鏈�澶ч暱搴︿负4,294,967,295鎴�4GB(2³²鈥�1)瀛楃�︾��TEXT鍒椼��LONGTEXT鍒楃殑鏈�澶�鏈夋晥(鍏佽�哥��)闀垮害鍙栧喅浜庡�㈡埛绔�/鏈嶅姟鍣ㄥ崗璁�涓�閰嶇疆鏈�澶у寘澶у皬鍜屽彲鐢ㄧ殑鍐呭瓨銆�

路         ENUM('value1','value2',...)

鏋氫妇绫诲瀷銆傚彧鑳芥湁涓�涓�鍊肩殑瀛楃�︿覆锛屼粠鍊煎垪'value1'锛�'value2'锛�...锛�NULL涓�鎴栫壒娈� ''閿欒��鍊间腑閫夊嚭銆�ENUM鍒楁渶澶氬彲浠ユ湁65,535涓�鎴�鐒朵笉鍚岀殑鍊笺��ENUM鍊煎湪鍐呴儴鐢ㄦ暣鏁拌〃绀恒��

路         SET('value1','value2',...)

涓�涓�璁剧疆銆傚瓧绗︿覆瀵硅薄鍙�浠ユ湁闆朵釜鎴栧�氫釜鍊硷紝姣忎釜鍊煎繀椤绘潵鑷�鍒楀��'value1'锛�'value2'锛�...SET鍒楁渶澶氬彲浠ユ湁64涓�鎴愬憳銆�SET鍊煎湪鍐呴儴鐢ㄦ暣鏁拌〃绀恒��

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> SET GLOBAL sql_mode=MAXDB锛�

mysql> SET SESSION sql_mode=MAXDB;

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);

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);

CREATE TABLE t

(

  ts1 TIMESTAMP NULL DEFAULT NULL,

  ts2 TIMESTAMP NULL DEFAULT 0,

  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE t (ts NULLDEFAULT CURRENT_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鏈�韬�瀵逛簬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涔嬮棿鐨勫樊鍒�锛�

璇锋敞鎰忎笂琛ㄤ腑鏈�鍚庝竴琛岀殑鍊煎彧閫傜敤涓嶄娇鐢ㄤ弗鏍兼ā寮�鏃讹紱濡傛灉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绫诲瀷锛屾湁鐩稿悓鐨勬渶澶ч暱搴﹀拰瀛樺偍闇�姹傘��

鍙傝��11.5鑺傦紝鈥滃垪绫诲瀷瀛樺偍闇�姹傗��銆�

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鍒楀垎閰嶅瓨鍌ㄥ紩鎿庛��