MySQL数据库操作补坑(一)数据类型

骑猪看日落 2022-12-17 11:56 265阅读 0赞

### 目录 ###

*  数据类型
 *   *  数据类型介绍
     *   *  1）、整数类型
         *  2）、浮点类型和定点数类型
         *  3）、日期与时间型
         *  4）、字符串类型
         *  5）、二进制类型
     *  如何选择数据类型
     *   *  1）、整数类型和浮点数类型
         *   *   *  区别及选择
         *  2）、浮点数类型和定点数类型
         *   *   *  区别及选择
         *  3）、CHAR类型和VARCHAR类型
         *   *   *  区别及选择
         *  4）、时间和日期类型
         *   *   *  区别及选择
         *  5）、ENUM类型和SET类型
         *   *   *  区别及选择
         *  6）、TEXT和BOLB类型
         *   *   *  区别及选择
     *  问题补充：
     *   *   *  \*\*什么数据类型能够存储路径\*\*
             *  MYSQL中如何使用布尔型
             *  MYSQL中如何存储JPG图片和MP3音乐

疫情期间数据库学的一直不是很好，后边发现别人都比自己会的多，现在来补补坑。

--------------------

# 数据类型 #

## 数据类型介绍 ##

### 1）、整数类型 ###

<table> 
 <thead> 
  <tr> 
   <th>整数类型</th> 
   <th>字节数</th> 
   <th>无符合的取值范围</th> 
   <th>有符合的取值范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>1</td> 
   <td>0~255</td> 
   <td>-128~127</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2</td> 
   <td>0~65535</td> 
   <td>-32768~32767</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3</td> 
   <td>0~16777215</td> 
   <td>-8388608~8388607</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4</td> 
   <td>0~4294967295</td> 
   <td>-2147483648~2147483647</td> 
  </tr> 
  <tr> 
   <td>INTEGER</td> 
   <td>4</td> 
   <td>0~4294967295</td> 
   <td>-2147483648~2147483647</td> 
  </tr> 
  <tr> 
   <td>BIGNT</td> 
   <td>8</td> 
   <td>0~18446744071709551615</td> 
   <td>-9223372036854775808~9223372036854775807</td> 
  </tr> 
 </tbody> 
</table>

基本形式：**数据类型(显示宽度)**

> 计算无符合数的取值范围

*  取值范围  
    一个字节 = 8bit。由此可推算出，TINYINT类型数据无符号的取值范围为 28-1= 255；有符号的取值范围27-1=127。
 *  zerofill  
    搭配zerofill使用参数使用时，数字不足的显示空间由0填补。使用该参数时，MYSQL会自动加上UNSIGNED属性，只能表示无符号数，其显示宽度比默认宽度小1。
 *  显示宽度  
    设置为int(11)的数据当输入111111时也可以显示，此时设置的显示宽度在显示该记录时将失效，但数据的显示宽度不能大于数据的默认宽度。如果大于了其最大值，将是不可插入的。

### 2）、浮点类型和定点数类型 ###

<table> 
 <thead> 
  <tr> 
   <th>整数类型</th> 
   <th>字节数</th> 
   <th>负数的取值范围</th> 
   <th>非负数的取值范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>FLOAT</td> 
   <td>4</td> 
   <td>-3.4028223466E+38 ~ -1.175494351E-38</td> 
   <td>0和1.175494351E-38 ~ 3.402823466E+38</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8</td> 
   <td>-1.7976931348623157E+308~ -2.2250738585072014E-308</td> 
   <td>0和2.2250738585072014E ~ 1.7976931348623157E+308</td> 
  </tr> 
  <tr> 
   <td>DECIMAL</td> 
   <td>M+2</td> 
   <td>同DOUBLE类型</td> 
   <td>同DOUBLE类型</td> 
  </tr> 
 </tbody> 
</table>

基本形式：**数据类型(M,D)**

> M参数为精度，小数点不占位置  
> D参数称为标度，是指小数点后的长度为D

*  精度问题  
    在定义时不建议此形式，其不是标准用法，可能影响数据库的迁移  
    如果插入值的的精度高于实际定义的精度，系统会自动进行四舍五入处理。FLOAT和DOUBLE类型在四舍五入时不会报错，而DECIMAL型会有警告。  
    如果不指定精度，浮点数和定点数尤其默认的精度。FLOAT型和DOUBLE型默认保存实际精度(与操作系统有关)。DECIMAL型默认为整数。

### 3）、日期与时间型 ###

<table> 
 <thead> 
  <tr> 
   <th>整数类型</th> 
   <th>字节数</th> 
   <th>取值范围</th> 
   <th>0值表示</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>YYYY</td> 
   <td>0000</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>4</td> 
   <td>YYYY-MM-DD</td> 
   <td>0000-00-00</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
   <td>HH:MM:SS</td> 
   <td>00:00:00</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>0000-00-00 00:00:00</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>00000000000000</td> 
  </tr> 
 </tbody> 
</table>

插入的值如果超过了范围，系统就会报错，并插入零值，不同的日期与时间类型有不同的零值。

*  YEAR类型  
    **输入格式**为YYYY，例如2020、2021…当超出其范围时会插入0值  
    **用两位数表示**：  
    ‘00’ ~ '69’ 转换为2000~2069  
    ‘70’ ~ ‘99’ 转换为1970~1999  
    ‘35’ 转换为2035  
    ‘90’ 转换为1990  
    ‘0’ 和 '00’ 效果一样
 *  TIME  
    输入格式：HH:MM:SS  
    HH表示时，MM表示分，SS表示秒，取值范围为0~59  
    **TIME类型的取值范围**可以从 ‘-838:59:59’ ~ ‘838:59:59’，小时的范围是 ‘0 ~ 23’。但是为了表示某种特殊需要的时间间隔，将TIME类型的范围扩大了。而且，还支持了负值。  
    **TIME类型字段的表示方式**：`'D HH:MM:SS'`格式化的字符串表示。D表示天数，取值范围是 ‘0~34’ 保存时，小时值等于(D\*24+HH)；也支持`'HH:MM'/'D HH:MM'/'D HH'/'SS'`等形式。345454的输入会装换为34:45:54。输入0和‘0’，那么TIME类型会转为0000:00:00。  
    **使用CURRENT\_YIME或NOW()输入当前系统时间**如果TIME超出范围，会截取为最接近的端点。  
    **TIme类型是专门用来存储时间数据的**，而且只占了3个字节，如果只需要存储时间数据，选择TIME类型。
 *  DATA  
    以`YYYY-MM-DD`的形式显示  
    **赋值方法**：`‘YYYY-MM-DD’`或‘YYYYMMDD’格式的字符串。还支持一些不严格的语法格式，任何标点都可以作为间隔符：`‘YYYY(/@.等)MM(/@.等)DD’`
    
    > ‘89@3@8’ 转换为 1989-03-08
    
    ‘00’~‘69’ 转换为 2000 ~ 2069  
    ‘70’ ~ ‘99’ 转换为1970 ~ 1999  
    DATE类型只占用4个字节，最适合用来存日期。
 *  DATETIME  
    **DATETIME类型使用8个字节来表示日期和时间**。MySQL中以‘YYYY-MM-DD HH:MM:SS’表示  
    **同DATE类型一样，也可用标点作为间隔符**，而且时间部分也可以用间隔符分开:：2020@10@25 08*08*08。支持YYYYMMDDHHMMSS的形式。  
    使用NOW()来输入当前系统日期和时间。  
    DATETIME类型的作用等价于DATE类型和TIME类型的组合
 *  TIMESTAMP  
    使用4个字节来表示日期和时间。TIMESTAMP类型范围比较小没有DATETIME类型的范围大。  
    TIMESTAMP类型的几种与DATATIME类型的不同的形式  
    1）使用CURRENT\_TIMESTAMP来输入系统当前日期与实践。  
    2）输入NULL时，系统会输入系统当前日期与时间。  
    3）无任何输入时，系统会输入系统当前日期与时间。  
    4）TIMESTAMP类型是按照时区来显示的。  
    需要显示日期与时间，TIMESTAMP类型能够根据不同地区的时区来转换时间。但是，TIMESTAMP类型的范围太小。（max = 2038-01-19 11:14:07）。因此，若需要的时间范围比较大，还是选择DATETIME类型比较安全

### 4）、字符串类型 ###

CHAR、VARCHAR、BOLD、TEXT、ENUM和SET。

*  char类型与varchar类型  
    基本形式：字符串类型(M)  
    CHAR类型的长度是固定的，在创建表示就指定了。  
    VARCHAR类型的长度是可变的，在创建表时指定了最大长度。VARCHAR(100)的最大长度是100。但是，不是每条记录都要占用100个字节，而是**在这个最大范围内，使用多少分配多少**。VARCHAR类型实际占用的空间位字符串的实际长度+1，可以有效节约空间。

> char在存储的时候会将右侧空格进行剔除，保留左侧空格。  
> varchar在存储的时候保留所有空格，不进行任何删除  
> varchar和char在查询的时候都只会根据where条件中的左侧空格进行判断，右侧末尾的空格会忽略

*  TEXT

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>允许的长度</th> 
   <th>存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0~255字节</td> 
   <td>值的长度+2个字节</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0~65535字节</td> 
   <td>值的长度+2个字节</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0~167772150字节</td> 
   <td>值的长度+3个字节</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>0~4294967295字节</td> 
   <td>值的长度+4个字节</td> 
  </tr> 
 </tbody> 
</table>

各种TEXT类型区别仅在于允许的长度和存储的空间不同。根据需要选择即可。

*  ENUM  
    又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式决定了。  
    基本形式：属性名 ENUM(‘值1’,‘值2’,…‘值n’)其中，属性名参数指字段的名称，这些值末尾的空格会被系统直接删除。  
    ENUM类型的值只能取列表中的一个元素。其取值列表最多能有65535个值。ENUM类型的值只能取列表中的一个元素。MYSQL中存入的是列表中顺序排列的编号，而不是其值。  
    当ENUM类型加上NOT NULL属性时，其默认值为列表的第一个元素。不加的话会允许插入NULL，并且默认值是NULL。  
    当只能选取列表中的一个值，选择ENUM类型；如果需要选取列表中的多个值的组合，则需要选择SET类型。
 *  SET  
    在创建表时，SET类型的取值范围就以列表的形式指定了。  
    基本形式：属性名 SET(‘值1’，‘值2’，…‘值n’)。其中，属性名参数指字段的名称，这些值末尾的空格会被系统直接删除。  
    SET类型的值可以去列表中的一个元素或者过个元素的组合。取多个元素时，不同元素之间用逗号分隔开。SET类型的值最多只能是有64个元素构成的组合。  
    同ENUM类型一样，列表中的每一个值都有一个顺序排列的编号。MYSQL存入的是这个编号，而不是类表中的值。  
    插入记录时，SET字段中的元素顺序无关紧要。存入MYSQL中会自动按照定义时的顺序显示。
    
    > 举例 : (‘A’，‘B’，‘C’，‘D’，‘E’)。 插入(‘C’，‘B’，‘D’)结果显示为(‘B’，‘C’，‘D’)

### 5）、二进制类型 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>取值范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BINARY(M)</td> 
   <td>字节数为M，允许长度为0~M的定长二进制字符串</td> 
  </tr> 
  <tr> 
   <td>VARBINARY(M)</td> 
   <td>允许长度为0~M的边长二进制字符串，字节数为值的长度加1</td> 
  </tr> 
  <tr> 
   <td>BIT(M)</td> 
   <td>M位二进制数据，M最大值为64</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>可变长二进制数据，最多255个字节</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>可变长二进制数据，最多(2<sup>16-1</sup>)个字节</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>可变长二进制数据，最多(2<sup>24-1</sup>个字节)</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>可变长二进制数据，最多(2<sup>32-1</sup>个字节)</td> 
  </tr> 
 </tbody> 
</table>

*  BINARY和VARBINARY类型
 *  BINARY类型和VARCHARY类型都是在创建表时指定了最大长度，其基本形式如下：  
    字符串类型(M)  
    参数指定了数据类型为BINARY类型还是VARBINARY类型；M参数指定了该二进制数的最大字节长度为M。例如BINARY(10)就是制定BINARY数据类型为BINARY类型，最大长度为10。  
    BINARY类型的长度是固定的，在创建表时就指定了。不足最大长度的空间由‘\\0’补全。  
    VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大长度以后，其长度可以在0到最大长度之间。在这个最大长度的范围以内，使用多少就分配多少。该类形实际占用的空间为实际长度加1。这样可以有效地节约系统的空间。
 *  BIT  
    创建时指定了最大长度，基本形式：BIT(M)  
    其中M指定了该二进制数的最大字节长度为M，M的最大值为64。  
    若该字段的类型BIT(4)，存储的数据是从0~15。因为变成二进制后，15的值为1111，长度为4。插入的值为16二进制为10000。长度为5，就超过了最大长度。  
    在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。
 *  BLOB  
    是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，包括图片。该类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种BLOB类型最大的区别就是能够保存的最大长度不同。  
    BLOB类型用来存储二进制数据，而TEXT类型是文本模式进行比较和排序的。
    
    > BOLB类型主要用来存储图片，PDF文档等二进制文件。通常情况下将图片、PDF文档都可以存储在文件系统中，然后再数据库中存储这些文件的路径，该种方法简单，但是访问速度比在数据库中存储慢。

## 如何选择数据类型 ##

### 1）、整数类型和浮点数类型 ###

##### 区别及选择 #####

1.  整数类型不能表示小数。浮点类型可以表示小数。
2.  不同的整数类型取值范围不同。字段值不超过255，推荐TINYINT类型
3.  DOUBLE类型的精度比FLOAT类型高。如果需要精确到小数点后十位以上，选择DOUBLE类型

### 2）、浮点数类型和定点数类型 ###

##### 区别及选择 #####

1.  当插入精度高于实际定义的精度，系统自动四舍五入时，浮点型不会报警，定点型会报警。
2.  在未指定精度的情况下，浮点型和定点性都有其默认的精度。FLOAT类型和DOUBLE类型默认会保存实际精度（精度与操作系统和硬件的精度有关）DECIMAL型默认整数。
3.  在MYSQL中，定点数精度比浮点数要高。而且浮点数会出现误差。当对数据精度要求比较高时，选择定点数。

### 3）、CHAR类型和VARCHAR类型 ###

##### 区别及选择 #####

1.  CHAR类型长度固定，VARCHAR类型在给定的最大长度内使用多少分配多少，占用的空间比VARCHAR类型小。对于长度变化较大的字符串类型最好是选择VARCHAR类型。
2.  CHAR类型的处理速度比VARCHAR类型快。对于长度变化不大和查询速度要求高的字符串类型，最好选择CHAR类型

### 4）、时间和日期类型 ###

##### 区别及选择 #####

1.  YEAR类型只会表示年份。TIME类型只表示时间。DATA类型只表示日期
2.  只需要记录日期和时间，选择DATATIME类型，和TIMESTAMP类型。当时间范围大时，选择DATATIME类型。当需要根据时区显示时，选择TIMESTAMP类型。

### 5）、ENUM类型和SET类型 ###

##### 区别及选择 #####

1.  ENUM类型最多可以有65535个成员，而SET类型最多只能包含64个成员。ENUM类型只能从成员中选择一个，SET类型可以选择多个。所以，对于多个值选择一个的，可以选择ENUM类型，对于可以选取多个值的字段，选择SET类型。

### 6）、TEXT和BOLB类型 ###

##### 区别及选择 #####

1.  TEXT类型只能存储字符数据。BOLD类型可以存储二进制数据。对于纯文本等数据，选择TEXT，对于图片等二进制的数据，选择BLOB类型。
2.  TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。四者的最大区别是内容的长度不同。BLOB类型也是如此。