MySQL中tinytext、text、mediumtext和longtext详解

r囧r小猫 2022-02-27 08:14 135阅读 0赞

一、数字类型

<table> 
 <tbody> 
  <tr> 
   <td> <p>类型</p> </td> 
   <td> <p>范围</p> </td> 
   <td> <p>说明</p> </td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>Char(N) [ binary]</td> 
   <td>N=1~255 个字元<br> binary ：分辨大小写</td> 
   <td>固定长度</td> 
   <td>std_name cahr(32) not null</td> 
  </tr> 
  <tr> 
   <td>VarChar(N) [binary]</td> 
   <td>N=1~255 个字元<br> binary ：分辨大小写</td> 
   <td>可变长度</td> 
   <td>std_address varchar(256)</td> 
  </tr> 
  <tr> 
   <td>TinyBlob</td> 
   <td>最大长度255个字元(2^8-1)</td> 
   <td style="vertical-align:top;"> <p>Blob (Binary large objects)储存二进位资料，且有分大小写</p> </td> 
   <td style="vertical-align:top;">memo text not null</td> 
  </tr> 
  <tr> 
   <td>TinyText</td> 
   <td>最大长度255个字元(2^8-1)</td> 
  </tr> 
  <tr> 
   <td>Blob</td> 
   <td>最大长度65535个字元(2^16-1)</td> 
  </tr> 
  <tr> 
   <td><img alt="Mysql&nbsp;<wbr><wbr>:&nbsp;<wbr><wbr>tinytext,&nbsp;<wbr><wbr>text,&nbsp;<wbr><wbr>mediumtext,&nbsp;<wbr><wbr>longtext" height="8" src="http://hiphotos.baidu.com/%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD/pic/item/c65c10384c10b6ccb211c7f5.jpg" width="8">&nbsp;Text</td> 
   <td>最大长度65535个字元(2^16-1)</td> 
  </tr> 
  <tr> 
   <td>MediumBlob</td> 
   <td>最大长度 16777215 个字元(2^24-1)</td> 
  </tr> 
  <tr> 
   <td>MediumText</td> 
   <td>最大长度 16777215 个字元(2^24-1</td> 
  </tr> 
  <tr> 
   <td>LongBlob</td> 
   <td>最大长度4294967295个字元 (2^32-1)</td> 
  </tr> 
  <tr> 
   <td>LongText</td> 
   <td>最大长度4294967295个字元 (2^32-1)</td> 
  </tr> 
  <tr> 
   <td>Enum</td> 
   <td>集合最大数目为65535</td> 
   <td style="vertical-align:top;">列举(Enumeration)，Enum单选、Set复选</td> 
   <td style="vertical-align:top;">sex enum(1,0)<br> habby set(‘玩电玩’,'睡觉’,'看电影’,'听音乐’)</td> 
  </tr> 
  <tr> 
   <td>Set</td> 
   <td>集合最大数目为64</td> 
  </tr> 
 </tbody> 
</table>

辨別Null与Not Null ：Null为允许储存空值(Null)

二、数值

<table> 
 <tbody> 
  <tr> 
   <td> <p>类型</p> </td> 
   <td> <p>范围</p> </td> 
   <td> <p>说明</p> </td> 
   <td> <p>例如</p> </td> 
  </tr> 
  <tr> 
   <td>TinyInt[M] [UNSIGNED]</td> 
   <td>-128~127<br> UNSIGNED ： 0~255</td> 
   <td>&nbsp;</td> 
   <td>num tinyint unsigned</td> 
  </tr> 
  <tr> 
   <td>SmallInt[M] [UNSIGNED]</td> 
   <td>-32768~32767<br> UNSIGNED ：0~ 65535</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>MediumInt[M] [UNSIGNED]</td> 
   <td>-8388608~8388607<br> UNSIGNED ：0~16777215</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>Int[M] [UNSIGNED]</td> 
   <td>-2^31~2^31-1<br> UNSIGNED ： 0~2^32</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>BigInt[M] [UNSIGNED]</td> 
   <td>-2^63~2^63-1<br> UNSIGNED ： 0~2^64</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>Float [(M,D)]</td> 
   <td> <p>-3.4E+38~3.4E+38( 约 )</p> </td> 
   <td>注： M 为长度， D 为小数,Float 4 bytes,Double 8 bytes</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>Double [(M,D)]</td> 
   <td>-1.79E+308~1.79E+308( 约 )</td> 
  </tr> 
  <tr> 
   <td>Decimal [(M,D)]</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

辨別ZeroFill：当宣告关键字ZeroFill为自动填满0，如 000021

三、日期时间

<table> 
 <tbody> 
  <tr> 
   <td> <p>类型</p> </td> 
   <td> <p>范围</p> </td> 
   <td> <p>说明</p> </td> 
  </tr> 
  <tr> 
   <td>Date</td> 
   <td>日期(yyyy-mm-dd)</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>Time</td> 
   <td>时间(hh:mm:ss)</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>DateTime</td> 
   <td>日期与时间組合(yyyy-mm-dd hh:mm:ss)</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>TimeStamp</td> 
   <td>yyyymmddhhmmss</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>Year</td> 
   <td> <p>年份yyyy</p> </td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

参考文献：

[https://www.cnblogs.com/printN/p/7298937.html][https_www.cnblogs.com_printN_p_7298937.html]

[https_www.cnblogs.com_printN_p_7298937.html]: https://www.cnblogs.com/printN/p/7298937.html