mysql常见的数据类型有哪些 MySql数据类型有哪些
MySQL\u652f\u6301\u7684\u6570\u636e\u7c7b\u578b\u6709\u54ea\u4e9bMysql\u652f\u6301\u7684\u591a\u79cd\u6570\u636e\u7c7b\u578b\u4e3b\u8981\u6709\uff1a\u6570\u503c\u6570\u636e\u7c7b\u578b\u3001\u65e5\u671f/\u65f6\u95f4\u7c7b\u578b\u3001\u5b57\u7b26\u4e32\u7c7b\u578b\u3002
1.\u6574\u6570\u6570\u636e\u7c7b\u578b\u53ca\u5176\u53d6\u503c\u8303\u56f4\uff1a
\u7c7b\u578b
\u8bf4\u660e
\u5b58\u50a8\u9700\u6c42\uff08\u53d6\u503c\u8303\u56f4\uff09
tinyint \u5f88\u5c0f\u6574\u6570 1\u5b57\u8282\uff08[0~255]\u3001[-128~127]\uff09; 255=2^8-1;127=2^7-1
smallint \u5c0f\u6574\u6570 2\u5b57\u8282\uff080~65535\u3001-32768~32767\uff09 ;65535=2^16-1
mediumint \u4e2d\u7b49 3\u5b57\u8282\uff080~16777215\uff09 ;16777215=2^24-1
int(integer) \u666e\u901a 4\u5b57\u8282\uff080~4294967295\uff09 ;4294967295=2^32-1
bigint \u5927\u6574\u6570 8\u5b57\u8282\uff080~18446744073709551615\uff09;18446744073709551615=2^64-1
\u6d6e\u70b9\u6570&\u5b9a\u70b9\u6570\uff1a
\u7c7b\u578b\u540d\u79f0
\u8bf4\u660e
\u5b58\u50a8\u9700\u6c42
float \u5355\u7cbe\u5ea6\u6d6e\u70b9\u6570 4\u5b57\u8282
double \u53cc\u7cbe\u5ea6\u6d6e\u70b9\u6570 8\u5b57\u8282
decimal \u538b\u7f29\u7684\u201c\u4e25\u683c\u201d\u5b9a\u70b9\u6570 M+2\u5b57\u8282
Mysql\u652f\u6301\u7684\u591a\u79cd\u6570\u636e\u7c7b\u578b\u4e3b\u8981\u6709\uff1a\u6570\u503c\u6570\u636e\u7c7b\u578b\u3001\u65e5\u671f/\u65f6\u95f4\u7c7b\u578b\u3001\u5b57\u7b26\u4e32\u7c7b\u578b\u3002 1.\u6574\u6570\u6570\u636e\u7c7b\u578b\u53ca\u5176\u53d6\u503c\u8303\u56f4\uff1a
\u7c7b\u578b
\u8bf4\u660e
\u5b58\u50a8\u9700\u6c42\uff08\u53d6\u503c\u8303\u56f4\uff09
tinyint \u5f88\u5c0f\u6574\u6570 1\u5b57\u8282\uff08[0~255]\u3001[-128~127]\uff09; 255=2^8-1;127=2^7-1
smallint \u5c0f\u6574\u6570 2\u5b57\u8282\uff080~65535\u3001-32768~32767\uff09 ;65535=2^16-1
mediumint \u4e2d\u7b49 3\u5b57\u8282\uff080~16777215\uff09 ;16777215=2^24-1
int(integer) \u666e\u901a 4\u5b57\u8282\uff080~4294967295\uff09 ;4294967295=2^32-1
bigint \u5927\u6574\u6570 8\u5b57\u8282\uff080~18446744073709551615\uff09;18446744073709551615=2^64-1
\u6d6e\u70b9\u6570&\u5b9a\u70b9\u6570\uff1a
\u7c7b\u578b\u540d\u79f0
\u8bf4\u660e
\u5b58\u50a8\u9700\u6c42
float \u5355\u7cbe\u5ea6\u6d6e\u70b9\u6570 4\u5b57\u8282
double \u53cc\u7cbe\u5ea6\u6d6e\u70b9\u6570 8\u5b57\u8282
decimal \u538b\u7f29\u7684\u201c\u4e25\u683c\u201d\u5b9a\u70b9\u6570 M+2\u5b57\u8282
\u6ce8\uff1a\u5b9a\u70b9\u6570\u4ee5\u5b57\u7b26\u4e32\u5f62\u5f0f\u5b58\u50a8\uff0c\u5bf9\u7cbe\u5ea6\u8981\u6c42\u9ad8\u65f6\u4f7f\u7528decimal\u8f83\u597d\uff1b\u5c3d\u91cf\u907f\u514d\u5bf9\u6d6e\u70b9\u6570\u8fdb\u884c\u51cf\u6cd5\u548c\u6bd4\u8f83\u8fd0\u7b97\u3002 2.\u65f6\u95f4/\u65e5\u671f\u7c7b\u578b\uff1a year\u8303\u56f4\uff1a1901~2155; time\u683c\u5f0f\uff1a\u2018HH:MM:SS\u2019\uff08\u5982\u679c\u7701\u7565\u5199\uff0c\u5e76\u4e14\u6ca1\u6709\u5192\u53f7\uff0c\u5219\u9ed8\u8ba4\u6700\u53f3\u8d772\u4f4d\u4e3a\u79d2\uff0c\u518d\u5230\u5206\uff0c\u6700\u540e\u5230\u65f6\uff09\uff1b \u63d2\u5165\u7cfb\u7edf\u5f53\u524d\u65f6\u95f4\uff1ainsert into \u8868\u540d values(current_date()),(now()); date\u7c7b\u578b\uff1a\u2018YYYY-MM-DD\u2019\uff1b datetime\uff08\u65e5\u671f+\u65f6\u95f4\uff09\uff1a\u2018YYYY-MM-DD HH:MM:SS\u2019\u6216\u2018YYYYMMDDHHMMSS\u2019\uff0c\u53d6\u503c\u8303\u56f4\uff1a\u20181000-01-01 00:00:00\u2019~\u20189999-12-31 23:59:59\u2019\uff1b timestamp\u683c\u5f0f\u540cdatetime\uff0c\u4f46\u5728\u5b58\u50a8\u65f6\u9700\u89814\u4e2a\u5b57\u8282(datetime\u9700\u89818\u5b57\u8282)\uff0c\u5e76\u4e14\u4ee5UTC\uff08\u4e16\u754c\u6807\u51c6\u65f6\u95f4\uff09\u8fdb\u884c\u5b58\u50a8\uff08\u5373timestamp\u4f1a\u968f\u8bbe\u7f6e\u7684\u65f6\u533a\u800c\u53d8\u5316\uff0c\u800cdatetime\u5b58\u50a8\u7684\u7edd\u4e0d\u4f1a\u53d8\u5316\uff09\uff1btimestamp\u7684\u8303\u56f4\uff1a1970-2037\u3002 3.\u5b57\u7b26\u4e32\u7c7b\u578b\uff1a text\u7c7b\u578b\uff1atinytext\u3001text\u3001mediumtext\u3001longtext;
\u7c7b\u578b
\u8303\u56f4
tinytext 255=2^8-1
text 65535=2^16-1
mediumtext 16777215=2^24-1
longtext 4294967295=4GB=2^32-1
char\u7684\u5b58\u50a8\u9700\u6c42\u662f\u5b9a\u4e49\u65f6\u6307\u5b9a\u7684\u56fa\u5b9a\u957f\u5ea6\uff1bvarchar\u7684\u5b58\u50a8\u9700\u6c42\u662f\u53d6\u51b3\u4e8e\u5b9e\u9645\u503c\u957f\u5ea6\u3002 set\u7c7b\u578b\u683c\u5f0f\uff1aset\uff08\u2019\u503c1\u2019,\u2019\u503c2\u2019\u2026) \u2014\u2014\u53ef\u4ee5\u67090\u6216\u8005\u591a\u4e2a\u503c\uff0c\u5bf9\u4e8eset\u800c\u8a00\uff0c\u82e5\u63d2\u5165\u7684\u503c\u4e3a\u91cd\u590d\u7684\uff0c\u5219\u53ea\u5a36\u4e00\u4e2a\u3002\u63d2\u5165\u7684\u503c\u4e71\u5e8f\uff0c\u5219\u81ea\u52a8\u6309\u987a\u5e8f\u63d2\u5165\u6392\u5217\u3002\u63d2\u5165\u4e0d\u6b63\u5e38\u503c\uff0c\u5219\u5ffd\u7565\u3002 \u4e8c\u8fdb\u5236\u7c7b\u578b\uff1a bit\uff08M\uff09\u2014\u2014\u4fdd\u5b58\u4f4d\u5b57\u6bb5\u503c\uff08\u4f4d\u5b57\u6bb5\u7c7b\u578b\uff09\uff0cM\u8868\u793a\u503c\u7684\u4f4d\u6570\uff1b eg:select BIN(b+0) from \u8868\u540d\uff1b\u2014\u2013b\u4e3a\u5217\u540d;b+0\u8868\u793a\u5c06\u4e8c\u8fdb\u5236\u7684\u7ed3\u679c\u8f6c\u6362\u4e3a\u5bf9\u5e94\u7684\u6570\u5b57\u7684\u503c\uff0cBIN\uff08\uff09\u51fd\u6570\u5c06\u6570\u5b57\u8f6c\u6362\u4e3a\u4e8c\u8fdb\u5236\u3002 blog\u2014\u2014-\u4e8c\u8fdb\u5236\u5927\u5bf9\u8c61\uff0c\u7528\u6765\u5b58\u50a8\u53ef\u53d8\u6570\u91cf\u7684\u6570\u636e\u3002
\u6570\u636e\u7c7b\u578b
\u5b58\u50a8\u8303\u56f4(\u5b57\u8282)
tinyblog \u6700\u591a255=2^8-1 \u5b57\u8282
bolg \u6700\u591a65535=2^16-1 \u5b57\u8282
mediumblog \u6700\u591a16777215=2^24-1 \u5b57\u8282
longblog \u6700\u591a4294967295=4GB=2^32-1 \u5b57\u8282
整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等
绛旓細璇存槑锛氱敤鏉ヤ繚瀛MySQL涓緝鐭殑瀛楃涓 璇存槑锛氱被浼间簬char鍜寁archar锛屼笉鍚岀殑鏄畠浠寘鍚簩杩涘埗瀛楃涓茶屼笉鍖呭惈闈炰簩杩涘埗瀛楃涓 璇存槑:鍙堢О涓烘灇涓绫诲瀷鍝︼紝瑕佹眰鎻掑叆鐨勫煎繀椤诲睘浜庡垪琛ㄤ腑鎸囧畾鐨勫间箣涓銆 濡傛灉鍒楄〃鎴愬憳涓1~255锛屽垯闇瑕1涓瓧鑺傚瓨鍌 濡傛灉鍒楄〃鎴愬憳涓255~65535锛屽垯闇瑕2涓瓧鑺傚瓨鍌 鏈澶氶渶瑕65535涓垚鍛橈紒璇...
绛旓細MySQL 鏁版嵁绫诲瀷缁嗗垎涓嬫潵锛屽ぇ姒傛湁浠ヤ笅鍑犵被锛氭暟鍊硷紝鍏稿瀷浠ｈ〃涓 tinyint,int,bigint娴偣/瀹氱偣锛屽吀鍨嬩唬琛ㄤ负 float,double,decimal 浠ュ強鐩稿叧鐨勫悓涔夎瘝瀛楃涓诧紝鍏稿瀷浠ｈ〃涓 char,varchar鏃堕棿鏃ユ湡锛屽吀鍨嬩唬琛ㄤ负 date,datetime,time,timestamp浜岃繘鍒讹紝鍏稿瀷浠ｈ〃涓 binary,varbinary浣嶇被鍨嬫灇涓剧被鍨嬮泦鍚堢被鍨 ...
绛旓細MySQL涓殑text銆乴ongtext銆乵ediumtext鏄敤鏉ュ瓨鍌ㄥ瓧绗︿覆鏁版嵁绫诲瀷鐨勩備粬浠箣闂寸殑涓昏鍖哄埆鍦ㄤ簬鍙瓨鍌ㄧ殑瀛楃鏁扮殑涓嶅悓銆1. TextText鏁版嵁绫诲瀷鍙互瀛樺偍鏈澶ч暱搴︿负65,535涓瓧绗︾殑瀛楃涓叉暟鎹傚湪瀹為檯搴旂敤涓竴鑸敤浜庡瓨鍌ㄨ緝鐭殑鏂囧瓧銆佸娉ㄣ佸崥瀹佹枃绔犵瓑銆俆ext鐨勪紭鐐规槸鍗犵敤瀛樺偍绌洪棿杈冨皬锛岀己鐐规槸涓嶉傚悎瀛樺偍杩囧ぇ鐨勬暟鎹銆2...
绛旓細D 閫傜敤浜庢诞鐐圭被鍨嬪苟涓旀寚鍑鸿窡闅忓湪鍗佽繘鍒跺皬鏁扮偣鍚庣殑鏁扮爜鐨勬暟閲忋傛渶澶у彲鑳界殑鍊兼槸30锛屼絾鏄簲璇ヤ笉澶т簬M-2銆傛柟鎷彿(鈥淸鈥濆拰鈥淽鈥)鎸囧嚭鍙鐨勭被鍨淇グ绗︾殑閮ㄥ垎銆傛敞鎰忥紝濡傛灉浣犳寚瀹氫竴涓簡涓篫EROFILL锛MySQL灏嗕负璇ュ垪鑷姩鍦板鍔燯NSIGNED灞炴с俆INYINT[(M)] [UNSIGNED] [ZEROFILL]涓涓緢灏忕殑鏁存暟銆傛湁绗﹀彿鐨...
绛旓細鍦MySQL涓紝CHAR鍜孷ARCHAR鏄袱绉嶇敤浜庡瓨鍌ㄦ枃鏈暟鎹鐨勬暟鎹被鍨锛屽畠浠殑鍖哄埆锛氬瓨鍌ㄦ柟寮忎笉鍚屻佸瓨鍌ㄧ┖闂翠笉鍚屻佹ц兘涓嶅悓銆侀傜敤鍦烘櫙涓嶅悓銆1銆佸瓨鍌ㄦ柟寮忎笉鍚 CHAR锛欳HAR鏄浐瀹氶暱搴︾殑瀛楃绫诲瀷锛屽畠浼氬湪瀛樺偍鏃惰嚜鍔ㄥ～鍏呯┖鏍间互杈惧埌鎸囧畾鐨勯暱搴︺備緥濡傦紝濡傛灉瀹氫箟涓涓狢HAR锛10锛夌被鍨嬬殑瀛楁锛屽苟瀛樺偍鈥淗ello鈥濓紝鍒欎細鍦ㄥ悗闈㈠～鍏6...
绛旓細mysql鏁版嵁搴撴椂闂寸敤浠涔堢被鍨嬬殑鏁版嵁 Mysql涓粡甯哥敤鏉ュ瓨鍌ㄦ棩鏈鐨勬暟鎹被鍨嬫湁涓夌锛欴ate銆丏atetime銆乀imestamp銆侱ate鏁版嵁绫诲瀷锛氱敤鏉ュ瓨鍌ㄦ病鏈夋椂闂寸殑鏃ユ湡銆侻ysql鑾峰彇鍜屾樉绀鸿繖涓被鍨嬬殑鏍煎紡涓衡淵YYY-MM-DD鈥濄傛敮鎸佺殑鏃堕棿鑼冨洿涓衡1000-00-00鈥濆埌鈥9999-12-31鈥濄侱atetime绫诲瀷锛氬瓨鍌ㄦ棦鏈夋棩鏈熷張鏈夋椂闂寸殑鏁版嵁銆傚瓨鍌ㄥ拰鏄剧ず...
绛旓細鐒惰岋紝涔熸湁涓浜涢檺鍒堕渶瑕佹敞鎰忋傞鍏堬紝瀛樺偍澶ч噺鐨勪簩杩涘埗鏁版嵁鍙兘浼氭秷鑰楀ぇ閲忕殑鏁版嵁搴撳瓨鍌ㄧ┖闂淬傚叾娆★紝鏌ヨ鍜屾搷浣滀簩杩涘埗鏁版嵁鍙兘姣斿鐞嗘枃鏈暟鎹洿澶嶆潅鍜岃楁椂銆傚洜姝わ紝鍦ㄥ疄闄呭簲鐢ㄤ腑闇瑕佹牴鎹暟鎹殑鐗规у拰闇姹傛潵閫夋嫨鍚堥鐨勬暟鎹被鍨銆傛讳箣锛孧ediumBlob鏄MySQL涓敤浜庡瓨鍌ㄤ簩杩涘埗澶у璞＄殑涓绉嶆暟鎹被鍨嬶紝閫傜敤浜庡瓨鍌ㄤ腑绛夊ぇ灏忕殑...
绛旓細Number鍦∣racle涓槸鏁板煎瀷鐨勭粺绉帮紝鍙互琛ㄧずOracle涓殑鎵鏈夋暟鍊笺傝孧ySQL娌℃湁鏁板肩殑缁熺О,鍙湁鍒嗗埆鐨勬暟鎹被鍨嬶紝渚嬪 INT銆丗LOAT銆丏OUBLE绛夈MySQL鐨勬暟鍊肩被鍨嬫湁濡備笅鍑犵 锛
绛旓細decimal鏄MySQL涓瓨鍦ㄧ殑绮惧噯鏁版嵁绫诲瀷銆侻ySQL涓鐨勬暟鎹被鍨嬫湁锛歠loat,double绛夐潪绮惧噯鏁版嵁绫诲瀷鍜宒ecimal杩欑绮惧噯銆傚尯鍒細float锛宒ouble绛夐潪绮惧噯绫诲瀷锛屽湪DB涓繚瀛樼殑鏄繎浼煎笺侱ecimal鍒欎互瀛楃涓茬殑褰㈠紡淇濆瓨绮剧‘鐨勫師濮嬫暟鍊笺俤ecimal浠嬬粛锛歞ecimal(a,b)鍏朵腑锛歛鎸囧畾鎸囧畾灏忔暟鐐瑰乏杈瑰拰鍙宠竟鍙互瀛樺偍鐨勫崄杩涘埗鏁板瓧鐨勬渶澶т釜鏁...
绛旓細鍦ㄥ瓨鍌ㄤ汉姘戝竵鐩稿叧鐨勯噾棰濈殑鏃跺欙紝鍒欏彧鑳藉瓨鍌ㄥ埌9闀垮害鐨勪汉姘戝竵锛屼篃灏辨槸璇达紝鏈澶у彧鑳藉瓨鍌999999999锛屼笉鍒10浜跨殑鏁板硷紝濡傛灉涓氬姟澧為暱寰堝揩鐨勮瘽锛屽氨浼氱粰鑷繁鐣欎笅闅愭偅銆侱ecimal锛欴ecimal涓轰笓闂ㄤ负璐㈠姟鐩稿叧闂璁捐鐨勬暟鎹被鍨銆侱ECIMAL浠MySQL 5.1寮曞叆锛屽垪鐨勫０鏄庤娉曟槸DECIMAL(M,D)銆傚湪MySQL 5.1涓紝鍙傞噺鐨勫彇鍊艰寖鍥...
