MySQL中字符串、数字、日期类型详解

落日映苍穹つ 2022-11-25 13:25 71阅读 0赞

### **一、字符串类型** ###

<table> 
 <tbody> 
  <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>&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>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

### **二、数字/数值类型** ###

<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> 
 </tbody> 
</table>

### **三、日期时间类型** ###

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