MySQL列类型详解

快来打我* 2023-07-18 14:09 40阅读 0赞

### MySQL列类型详解 ###

*   *   *   *  数值型
             *   *  整型
                 *  浮点型/定点型
             *  字符型
             *  日期时间类型(插入时需加引号)

在MySQL中，所谓建表就是声明列的过程。数据是以文件的形式放在硬盘上,也有放在内存里的，不同的列所占的空间不同。选择列的原则是够用且不浪费。

#### 数值型 ####

##### 整型 #####

整型包括5种列类型：tinyint、smallint、mediumint、int、bigint。

**占据空间和存储范围**

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>字节</th> 
   <th>位</th> 
   <th>无符号</th> 
   <th>有符号</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>1</td> 
   <td>8</td> 
   <td>0~(2^8-1)</td> 
   <td>-2^7~ +(2^7-1)</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2</td> 
   <td>16</td> 
   <td>0~(2^16-1)</td> 
   <td>-2^15~ +(2^15-1)</td> 
  </tr> 
  <tr> 
   <td>mediumint</td> 
   <td>3</td> 
   <td>24</td> 
   <td>0~(2^24-1)</td> 
   <td>-2^23~ +(2^23-1)</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>4</td> 
   <td>32</td> 
   <td>0~(2^32-1)</td> 
   <td>-2^31~ +(2^31-1)</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>8</td> 
   <td>64</td> 
   <td>0~(2^64-1)</td> 
   <td>-2^63~ +(2^63-1)</td> 
  </tr> 
 </tbody> 
</table>

以tinyint为例，在硬盘上占据1个字节(8位)的空间，无符号存储范围为2^8-1,如果表示负数，可以用最高位来标志符号位。 -2^7~+(2^7-1)(只有后7位表示绝对值，最高位表示的是符号位)(补码)  
tinyint默认是有符号的，存储是-2^7~+(2^7-1)  
整数列的可选属性tinyint(M) unsigned zerofill;M:宽度(在0填充的时候才有意义)、unsigned:无符号类型(非负)、zerofill:0填充(默认无符号)；如果某列定义为zerofill,默认就是unsigned。

##### 浮点型/定点型 #####

浮点型：float(M,D)  
定点型：decimal(M,D)  
M:精度(总位数，不包含小数点)，D:标度(小数位)，float(6,2)为例，能存储-9999.99~9999.99范围内的数字，可取值范围与整形不同，比较特殊。float(4,2)取值范围：-99.99~99.99,float(4,2) unsigned 取值范围：0.00~99.99。

#### 字符型 ####

**占据空间和存储范围**

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
   <th>典型声明方式</th> 
   <th>范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char</td> 
   <td>定长字符串</td> 
   <td>gender char(1)</td> 
   <td>char(M),M取值范围0~2^8-1</td> 
  </tr> 
  <tr> 
   <td>varchar</td> 
   <td>变长字符串</td> 
   <td>email varchar(20)</td> 
   <td>varchar(M),M取值范围0~2^16-1,约2W~6W字符受字符集影响</td> 
  </tr> 
  <tr> 
   <td>text++</td> 
   <td>文本串</td> 
   <td>content text</td> 
   <td>约2W~6W字符受字符集影响</td> 
  </tr> 
 </tbody> 
</table>

char定长，char(M),M代表宽度，即可容纳的字符数。  
varchar变长，varchar(M),M代表宽度，即可容纳的字符数。  
**区别：**  
空间利用效率上：  
char定长M个字符，如果存的小于M个字符，实占M个字符，若不够M个字符内部用空格补齐，读时空格丢失；varchar:变长，M个字符，存的小于M个字符，设为N,N<=M，实占N个字符。  
运算速度上：定长速度快些。  
char与varchar的选择综合空间利用效率和运算速度两方面考虑。

#### 日期时间类型(插入时需加引号) ####

**占据空间和存储范围**

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>year(年)</td> 
   <td>1901~2155，如果输入两位，'00~69’表示2000~2069年，'70~00’表示1970~1999年</td> 
  </tr> 
  <tr> 
   <td>date(年-月-日)</td> 
   <td>1000-01-01~9999-12-31(公元)</td> 
  </tr> 
  <tr> 
   <td>time(时间点)</td> 
   <td>-838:59:59~+838:59:59</td> 
  </tr> 
  <tr> 
   <td>datetime(年-月-日 时:分:秒)</td> 
   <td>1000-01-01 00:00:00~9999-12-31 23:59:59(公元)</td> 
  </tr> 
 </tbody> 
</table>

很少用日期时间类型来表示一个需要精确到秒的列，因为虽然日期时间类型能精确到秒而且方便查看，但计算不便。可以用时间戳来表示一个精确到秒的列，时间戳用int来存储，是1970-01-01 00:00:00到当前的秒数。  
比如，一般存注册时间商品发布时间等，并不是用datetime存储，而是时间戳，因为datetime虽然直观，但计算不便，而用int存储时间戳，方便计算，对于显示来说，也方便格式化。