ãã以åä¸ç´é½è®¤ä¸ºæ两个åèæ¥è®°å½é¿åº¦ï¼é¿åº¦å°ä¹å¯ä»¥ç¨ä¸ä¸ªåèè®°å½ï¼ï¼æ以è¿ä¸ªé®é¢å½æ¶è§å¾å°±æºæ èçä¸è¿åæ¥ç¾¤éæ人ç»äºè§£éï¼çªç¶æåç°åæ¥äºæ
ä¸æ¯è¿ä¹ç®åMYSQL COMPACTæ ¼å¼ï¼æ¯æ¡è®°å½æä¸ä¸ªåèæ¥è¡¨ç¤ºNULLå段åå¸ï¼å¦æ表ä¸æå段å
许为空ï¼åæ大åªè½å®å°65532ï¼å¦æ没æå段å
许为空ï¼åé£ä¸ªåèå¯ä»¥èçï¼æ大å¯ä»¥å®ä¹å°65533ï¼ä¸ç¥éæ¯ä¸æ¯è¿ä¸ªåå äºæ¯ä¸ç½çäºäºèµæï¼åå¨æ¬å°åäºäºå®éªï¼åæ¥vacharçæ大é¿åº¦ççæ¯ä¸å®çï¼æ ¹æ®æ¯å¦æé空å段æ¥å³å®ï¼å¨æ¬å°åäºä¸å®éªï¼innodb+latinçç¯å¢
ããå¤å¶ä»£ç 代ç å¦ä¸:
ãã-- success
ããdrop table if exists test;
ããcreate table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1-- too large
ããdrop table if exists test;
ããcreate table test(name varchar(65533))engine=innodb DEFAULT CHARSET=latin1对äºç¬¬äºç§æ
åµï¼å
许空å段çæ¶åæ¯ä¸è½å å°65533çé¿åº¦çï¼æ大åªè½å°65532ï¼å°åºåºè¯¥æ¯å¼æçé£ç§è¯´æ³ã
ããç½ä¸ä¹æ人åäºç±»ä¼¼çå®éªï¼åè
http://stackoverflow.com/questions/8295131/best-practise-for-sql-varchar-column-lengthå¤å¶ä»£ç 代ç å¦ä¸:
ããname varchar(100) not null will be 1 byte (length) + up to 100 chars (latin1)name varchar(500) not null will be 2 bytes (length) + up to 500 chars (latin1)name varchar(65533) not null will be 2 bytes (length) + up to 65533 chars (latin1)name varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byteæ»ç»ä¸ä¸ï¼åæ¥mysqlçvacharå段çç±»åè½ç¶æ大é¿åº¦æ¯65535ï¼ä½æ¯å¹¶ä¸æ¯è½åè¿ä¹å¤æ°æ®ï¼æ大å¯ä»¥å°65533ï¼ä¸å
许é空å段çæ¶åï¼ï¼å½å
许é空å段çæ¶ååªè½å°65532ã
ãã以ä¸æ¯å
¶å®ç½åçè¡¥å
说æï¼
ããè¿ä¸æ¯ä¸ä¸ªåºå®çæ°åãæ¬æç®è¦è¯´æä¸ä¸éå¶è§åã
ããstrlen 计ç®å符串é¿åº¦ï¼ä¸ä¸ªä¸æå½2å符
ããmb_strlenæ ¹æ®å®çå符ç¼ç 模å¼ï¼ç»è®¡å符quotcount计ç®æ°ç»ä¸çå
ç´ æ°ç®æ对象ä¸çå±æ§ä¸ªæ°å¤å¶ä»£ç 代ç å¦ä¸:
ãã<?php
ããheader('Content-Type:text/html;charset=UTF-8');$string1="è°¢æ¥ä¸";//å®ä¹ä¸æå符åé
ãã$string2="xcy";//å®ä¹è±æå符åé
ãã//ç´æ¥è¾åºççä»ä»¬çé¿åº¦
ããecho strlen($string1);
ããecho "</br>";
ããecho strlen($string2);
ããecho "</br>";
ãã//ç¨ php å¤åèæ©å±å½æ° mb_strlenè¯è¯ç
ããecho mb_strlen($string1,'utf8');
ããecho "</br>";
ããecho mb_strlen($string2,'utf8');
ããecho "</br>";
ãã?>
ããè¾åºç»ææ¯ï¼
ãã9
ãã3
ãã3
ãã3
ãã1ãéå¶è§å
ããå段çéå¶å¨å段å®ä¹çæ¶åæ以ä¸è§åï¼
ããa) åå¨éå¶
ããvarchar å段æ¯å°å®é
å
容åç¬åå¨å¨èç°ç´¢å¼ä¹å¤ï¼å
容å¼å¤´ç¨1å°2个åè表示å®é
é¿åº¦ï¼é¿åº¦è¶
è¿255æ¶éè¦2个åèï¼ï¼å æ¤æ大é¿åº¦ä¸è½è¶
è¿65535ã
ããb) ç¼ç é¿åº¦éå¶
ããå符类åè¥ä¸ºgbkï¼æ¯ä¸ªå符æå¤å 2个åèï¼æ大é¿åº¦ä¸è½è¶
è¿32766;å符类åè¥ä¸ºutf8ï¼æ¯ä¸ªå符æå¤å 3个åèï¼æ大é¿åº¦ä¸è½è¶
è¿21845ã
ãã对äºè±ææ¯è¾å¤ç论å ï¼ä½¿ç¨GBKåæ¯ä¸ªå符å ç¨2个åèï¼è使ç¨UTFï¼8è±æå´åªå ä¸ä¸ªåèã
ããè¥å®ä¹çæ¶åè¶
è¿ä¸è¿°éå¶ï¼åvarcharå段ä¼è¢«å¼ºè¡è½¬ä¸ºtextç±»åï¼å¹¶äº§çwarningã
ããc) è¡é¿åº¦éå¶
ãã导è´å®é
åºç¨ä¸varcharé¿åº¦éå¶çæ¯ä¸ä¸ªè¡å®ä¹çé¿åº¦ã MySQLè¦æ±ä¸ä¸ªè¡çå®ä¹é¿åº¦ä¸è½è¶
è¿65535ãè¥å®ä¹ç表é¿åº¦è¶
è¿è¿ä¸ªå¼ï¼åæ示ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to ãããchange some columns to TEXT or BLOBsã
ãã2ã计ç®ä¾å
ãã举两个ä¾è¯´æä¸ä¸å®é
é¿åº¦ç计ç®ã
ããa) è¥ä¸ä¸ªè¡¨åªæä¸ä¸ªvarcharç±»åï¼å¦å®ä¹ä¸º
ããcreate table t4(c varchar(N)) charset=gbk;åæ¤å¤Nçæ大å¼ä¸º(65535-1-2)/2= 32766ã
ããå1çåå æ¯å®é
è¡åå¨ä»ç¬¬äºä¸ªåèå¼å§';
ããå2çåå æ¯varchar头é¨ç2个åè表示é¿åº¦;
ããé¤2çåå æ¯å符ç¼ç æ¯gbkã
ããb) è¥ä¸ä¸ªè¡¨å®ä¹ä¸º
ããcreate table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;åæ¤å¤Nçæ大å¼ä¸º (65535-1-2-4-30*3)/3=21812å1åå2ä¸ä¸ä¾ç¸å;
ããå4çåå æ¯intç±»åçcå 4个åè;
ããå30*3çåå æ¯char(30)å ç¨90个åèï¼ç¼ç æ¯utf8ã
ããå¦æ被varcharè¶
è¿ä¸è¿°çbè§åï¼è¢«å¼ºè½¬ætextç±»åï¼åæ¯ä¸ªå段å ç¨å®ä¹é¿åº¦ä¸º11åèï¼å½ç¶è¿å·²ç»ä¸æ¯âvarcharâäºã