MYSQ数据类型选择原则。

忘是亡心i 2024-03-30 15:45 79阅读 0赞

![2eb58550e4e6e7d427862a0e177d2719.jpeg][]

转自：[微点阅读][Link 1][https://www.weidianyuedu.com][Link 1]

在进行数据库设计时，如果能够选择最恰当的数据类型就可以为后期的[数据库调优][Link 2]打好最坚实的基础

**选择数据类型的原则**

更小的通常更好

例如存储订单状态字段很多时候使用0、1、2表示使用tinyint类型存储就够了，没必要搞个int、long甚至varchar来。

越小的数据类型代表着占用越小的磁盘、内存和CPU，也就代表着越快的速度。当然，不要为了追求小而不考虑实际的场景、通常选择一个不会超过范围的最小数据类型就好了

越简单越好

数据类型越简单操作所需要的CPU周期就越少，例如说ip地址通常使用int而不是varchar来存储

尽量避免NULL

包含NULL的列会使得索引、索引统计和值比较都变得复杂

各个数据类型占用的字节

<table style="table-layout:fixed;width:28px;"> 
 <tbody> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">数据类型</td> 
 <td style="width:369px;vertical-align:top;height:52px;">所占字节（byte）</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">tinyint</td> 
 <td style="width:369px;vertical-align:top;height:52px;">1</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">smallint</td> 
 <td style="width:369px;vertical-align:top;height:52px;">2</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">medumint</td> 
 <td style="width:369px;vertical-align:top;height:52px;">3</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">int</td> 
 <td style="width:369px;vertical-align:top;height:52px;">4</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">bigint</td> 
 <td style="width:369px;vertical-align:top;height:52px;">8</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">float</td> 
 <td style="width:369px;vertical-align:top;height:52px;">4</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">double</td> 
 <td style="width:369px;vertical-align:top;height:52px;">8</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">decimal(m,d）</td> 
 <td style="width:369px;vertical-align:top;height:52px;">m+2</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">date</td> 
 <td style="width:369px;vertical-align:top;height:52px;">3</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">datetime</td> 
 <td style="width:369px;vertical-align:top;height:52px;">8</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">timestamp</td> 
 <td style="width:369px;vertical-align:top;height:52px;">4</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">char（n）</td> 
 <td style="width:369px;vertical-align:top;height:52px;">1-n-255</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">varchar（n）</td> 
 <td style="width:369px;vertical-align:top;height:52px;">1-n-65532</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">tinyblob</td> 
 <td style="width:369px;vertical-align:top;height:52px;">255</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">blob</td> 
 <td style="width:369px;vertical-align:top;height:52px;">65535</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">medumblob</td> 
 <td style="width:369px;vertical-align:top;height:52px;">16777215</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">longblob</td> 
 <td style="width:369px;vertical-align:top;height:52px;">4294967295</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">tinytext</td> 
 <td style="width:369px;vertical-align:top;height:52px;">255</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">text</td> 
 <td style="width:369px;vertical-align:top;height:52px;">65535</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">medumtext</td> 
 <td style="width:369px;vertical-align:top;height:52px;">16777215</td> 
 </tr> 
 <tr> 
 <td style="width:369px;vertical-align:top;height:52px;">longtext</td> 
 <td style="width:369px;vertical-align:top;height:52px;">4294967295</td> 
 </tr> 
 </tbody> 
</table>

[2eb58550e4e6e7d427862a0e177d2719.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b6bd36969e774451a12d419b03040ca5.jpeg
[Link 1]: https://www.weidianyuedu.com/
[Link 2]: https://so.csdn.net/so/search?q=%E6%95%B0%E6%8D%AE%E5%BA%93%E8%B0%83%E4%BC%98&spm=1001.2101.3001.7020