mysql数据类型

ゞ浴缸里的玫瑰 2022-04-22 00:28 224阅读 0赞

[开发十年，就只剩下这套Java开发体系了 >>> ][Java_ _]  ![hot3.png][]

一、数字类型

<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> <p>Blob (Binary large objects)储存二进位资料，且有分大小写</p> </td> 
   <td>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>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>列举(Enumeration)，Enum单选、Set复选</td> 
   <td>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>

[![223916\_bL9y\_2663968.jpg][223916_bL9y_2663968.jpg]][223916_bL9y_2663968.jpg_223916_bL9y_2663968.jpg]

[Java_ _]: https://my.oschina.net/u/3779583/blog/1862418
[hot3.png]: /images/20220422/b00d5c489d4a45ca950c00fc21c0e806.png
[223916_bL9y_2663968.jpg]: https://static.oschina.net/uploads/space/2018/1026/223916_bL9y_2663968.jpg
[223916_bL9y_2663968.jpg_223916_bL9y_2663968.jpg]: /images/20220422/0469b8cf4e8446e09dddb941a98b0cdb.png