mysql基本数据类型详解

你的名字 2022-05-29 05:05 269阅读 0赞

#     #

MySQL与SQLServer的数据类型大同小异。因此，可以忽略与SQLServer相同的地方，在SQLServer的基础上学习。

# 一、整型的显示宽度 #

MySQL数据类型与SQLServer有些不同，其定义的基本形式如下：

整型类型(显示宽度)

注意，正确的理解，其实是显示宽度，因为整型的显示宽度，对数值大小无影响，只是当设置了zerofill的时候，在显示的时候补0而已。

![121031439463728.jpg][]

从上面的显示我们看到，设置了宽度为8，并且zerofill了，这样在查询的时候，会在前面自动补0。宽度可以不设置，因为整数数据类型都有默认的显示宽度，不设就采用默认的。

另外，即使设置了显示宽度，也能赋超过显示宽度的值，整型的显示宽度仅仅用于显示补0。

# 二、浮点型与定点型 #

MySQL中可以指定浮点数和定点数的精度

数据类型(M,D)

*  M：精度，数据的总长度；
 *  D：标度，小数点后的长度；

这种类型MySQL里有3种如：Float(6,2)、Double(6,2)、Decimal(6,2)；

其区别在于：

1.  当不指定精度时，Float、Double默认会保存实际精度，而Decimal默认是整数；
2.  当标度不够时，都会四舍五入，但Decimal会警告信息。

![121055476342315.jpg][]

# 三、日期与时间类型 #

MySQL日期时间所有种类如下：

<table style="width:700px;border:1px solid rgb(192,192,192);font-family:verdana, sans-serif;"> 
 <tbody> 
  <tr style="background-color:rgb(220,220,220);"> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">类型</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">字节数</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">取值范围</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">零值</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">YEAR</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">1</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">1901~2155</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">0000</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">DATE</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">4</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">1000-01-01~9999-12-31</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">0000:00:00</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">TIME</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">3</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">-838:59:59~838:59:59</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">00:00:00</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">DATETIME</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">8</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">1000-01-01 00:00:00~9999-12-31 23:59:59</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">0000-00-00 00:00:00</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">TIMESTAMP</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">4</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">1970-01-01 08:00:01~2038-01-19 11:14:07</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">00000000000000</td> 
  </tr> 
 </tbody> 
</table>

DATE类型前面可以加个D，D表示天即24小时，如往DATE类型插入 '2 15:20:20'；

![121134544936361.jpg][]

另外TimeStamp类型与DateTime在无输入的情况下要注意下：

1.  TimeStamp使用Current\_TimeStamp()而DateTime使用NOW(来获取当前时间)；
2.  输入NULL时，系统会输入系统当前日期与时间；
3.  无任何输入时，系统会输入系统当前日期与时间；

# 四、字符串类型 #

在MySQL中，字符串类型包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET。

1、CHAR与VARCHAR

其定义方式为：

字符串类型(M)

这个M是长度的意思，插入该列的字符串长度将不允许超过M所指定的长度。另外，由于MySQL在建库时就指定了字符集，因此就不存在nchar、nvarchar、ntext这种数据类型了。

*  char最长可取255；
 *  varchar最长可取65535；

2、TEXT

TEXT分为4种，与SQLServer不同：

3、ENUM类型

ENUM类型(枚举类型)，与C\#的概念一样，在定义时指定取值范围。

属性名 ENUM('值1','值2','值3'...'值n')

*  ENUM有NOT NULL属性，其默认值为取值列表的第一个元素；
 *  ENUM无NOT NULL，则ENUM类型将允许插入NULL，并且NULL为默认值；

![121428362289426.jpg][]

CREATE TABLE Test4(Sex ENUM('男','女'));
    　　INSERT INTO Test4 VALUES('男');
    　　INSERT INTO Test4 VALUES('爷');    --这行报错
    　　SELECT * FROM Test4;

4、SET类型

在创建表时，就指定SET类型的取值范围。

属性名 SET('值1','值2','值3'...,'值n')

它与ENUM的区别在什么地方呢？

![121442395091653.jpg][]

基本上就是多选的ENUM。

5、二进制类型

二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

<table style="width:700px;border:1px solid rgb(192,192,192);font-family:verdana, sans-serif;"> 
 <tbody> 
  <tr style="background-color:rgb(220,220,220);"> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">类型</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">取值范围</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">BINARY(M)</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">字节数为M，允许长度为0~M的定长二进制字符串</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">VARBINARY(M)</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">允许长度为0~M的变长二进制字符串，字节数为值的长度加1</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">BIT(M)</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">M位二进制数据，M最大值为64</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">TINYBLOB</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">可变长二进制数据，最多255个字节</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">BLOB</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">可变长二进制数据，最多2的16次方-1个字节</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">MEDIUMBLOB</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">可变长二进制数据，最多2的24次方-1个字节</td> 
  </tr> 
  <tr> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">LONGBLOB</td> 
   <td style="border-color:rgb(192,192,192);padding:3px;border-collapse:collapse;">可变长二进制数据，最多2的32次方-1个字节</td> 
  </tr> 
 </tbody> 
</table>

1、BINARY和VARBINARY

两者唯一的差别在于BINARY当长度不够时会补\\0。

2、BIT类型

BIT类型与SQLServer里的就差别大了。 其定义方式为：

BIT(M)

其中"M"指定了该二进制的最大字节长度为M，M的最大值为64。如BIT(4)就是数据类型为BIT类型，长度为4。其能够存储的值为0-15。因为变成二进制后，15的值为1111。

在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。

![121528536183759.jpg][]

CREATE TABLE Test6(BB BIT(4));
    INSERT INTO Test6 VALUES(12);
    SELECT BIN(BB+0) FROM Test6;

上面的结果以二进制显示，最大的长度为4。

3、BLOB类型

BLOB类型是一种特殊的二进制类型。BLOB可以存储数据量很大的二进制数据，如图片，视频等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们之间的区别也只是最大长度不同。

# 五、数据类型的选择 #

1.  整型：根据要显示的最大值决定；
2.  浮点型：要显示小数。如果要精确到小数点后10位，就选择DOUBLE，而不应该选择FLOAT。DECIMAL精度较高，浮点数会出现误差，如果精度较高，则应选择定点数DECIMAL；
3.  字符串型：定长与变长的区别，CHAR类型占用空间比较大，但是处理速度比VARCHAR快，如果长度变化不大，如身份证号码那种，最好选择CHAR类型。而对于评论字符串，最好选择VARCHAR；
4.  时间：根据需要显示的类型咯，特别是TIMESTAMP,如果需要显示的时间与时区对应，就应该选择TIMESTAMP；
5.  ENUM类型和SET类型：长度不同，ENUM类型最多可以由65535个成员，而SET类型最多只能包含64个成员。且ENUM只能单选，而SET类型可以多选；
6.  TEXT类型和BLOB类型：TEXT只能存储字符数据，而BLOB可以存储二进制数据。如果是纯文本，适合TEXT。如果是图片等适合存二进制；

# 六、问题总结 #

1、存储路径的问题

MySQL中，如果路径中使用"\\"符号时，这个符号会被过滤。解决的办法是路径中用"/"或"\\\\"来代替"\\"。这样MySQL就不会自动过滤路径中的分隔符。

2、MySQL中的布尔类型

MySQL中没有Bool或Boolean类型，但是为了支持SQL标准，也可以定义Bool或Boolean类型的，但是Bool或Boolean类型最后转换成的是TinyInt(1)，也就是说,在MySQL中，布尔类型实际上是TinyInt(1)。

3、MySQL中如何存储JPG图片或MP3音乐

一般情况下，数据库中不直接存储图片和音频文件，而是存储图片或音频文件的路径，如果在特殊情况下需要在MySQL数据库中存储图片和音频文件，可以选择BLOB类型；

[121031439463728.jpg]: /images/20220529/6041d13c209b453d89d08f410f865e04.png
[121055476342315.jpg]: /images/20220529/90637e56e4364977bb48e27b201717c8.png
[121134544936361.jpg]: /images/20220529/efb8e8a7ff3948c4866815786da646db.png
[121428362289426.jpg]: /images/20220529/78c09a795c2e4884b485a494ddde1f34.png
[121442395091653.jpg]: /images/20220529/be16af9b65ef4c78865bfe60ac1f828b.png
[121528536183759.jpg]: /images/20220529/e88b873a0adc4bfa93e1af6be27aea42.png