Mysql学习笔记(4)：数据类型

╰半夏微凉° 2022-05-23 12:08 380阅读 0赞

Mysql支持的数据类型主要有：数值数据类型、日期/时间类型、字符串类型。

![20180608145939517][]

## 数值类型    ##

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

<table> 
 <tbody> 
 <tr> 
 <th>类型</th> 
 <th>说明</th> 
 <th>存储(字节)</th> 
 <td>取值范围</td> 
 </tr> 
 </tbody> 
 <tbody> 
 <tr> 
 <td>tinyint</td> 
 <td>很小整数</td> 
 <td>1</td> 
 <td>无符号(unsigned): 0~255 有符号(signed): -128~127255=2^8-1</td> 
 </tr> 
 <tr> 
 <td align="center"> smallint</td> 
 <td> 小整数</td> 
 <td> 2</td> 
 <td>无符号(unsigned):&nbsp; 0~65535 有符号(signed): -32768~3276765535=2^16-1</td> 
 </tr> 
 </tbody> 
</table>

mediumint

中等

无符号(unsigned):  0~2^24-1

有符号(signed): \-2^23-1~2^23-1

16777215 =  2^24-1

int(integer)

普通

无符号(unsigned):  0~ 2^32-1

有符号(signed): \- 2^31-1~ 2^31-1

4294967295 = 2^32-1

bigint

大整数

无符号(unsigned):  0~2^64-1

有符号(signed): \-2^63-1~2^63-1

18446744073709551615=2^64-1

下面介绍常用的整型：

#### 1.1 int(integer)类型 ####

CREATE TABLE tab_int(
    	t1 INT,
    	t2 INT UNSIGNED 
    );

![20180608152644916][]

*  上面创建了tab\_int表，表中字段一个是有符号的int类型t1和无符号的Int类型t2.
 *  几种整型类型都是有最大最小值的，如果插入的数据超过了这个临界值，就会使用这个临界值填充。

关于表中int类型后面的数字，这个就得详细讲解一下了.

**关于int(\#)**

int后边的(\#)内的数字\#与int类型的数字范围无关，不会因为里边是11或者4，就表示11位或者4位的int型数据，这个\#字只表示数字显示出来的宽度。数据显示的宽度有俩种情况：

1.  如果没有在数据库设置0填充，那么其实不管设置显示的宽度为多少，显示就是数据库内的数据，不会在数据左侧自动填充0来补足显示的宽度；
2.  设置了0填充，在查询结果时候显示出来的数据宽度不够设置的显示宽度时候，就会在数据左侧填充0直到达到设置的显示宽度，但是有一个问题就是设置了0填充，数据会自动变成unsigned类型的；如果数据宽度超过设置的显示宽度，也不会将数据只显示指定的显示宽度。
3.  字段默认是不使用0填充的。如果使用的话使用 zerofill 关键字。

\----------

### 2. 浮点数和定点数类型    ###

<table style="border-collapse:collapse;border-spacing:0px;width:852px;text-align:center;margin-bottom:24px;color:rgb(51,51,51);font-family:'-apple-system', 'SF UI Text', Arial, 'PingFang SC', 'Hiragino Sans GB', 'Microsoft YaHei', 'WenQuanYi Micro Hei', sans-serif, SimHei, SimSun;font-size:14px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:22px;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:rgb(255,255,255);"> 
 <thead> 
 <tr style="border-width:1px 0px 0px;border-top-style:solid;border-top-color:rgb(221,221,221);background-color:rgb(255,255,255);"> 
 <th style="padding:8px;margin:0px;font-weight:700;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;background-color:rgb(239,243,245);">类型名称</th> 
 <th style="padding:8px;margin:0px;font-weight:700;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;background-color:rgb(239,243,245);" align="center">说明</th> 
 <th style="padding:8px;margin:0px;font-weight:700;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;background-color:rgb(239,243,245);">存储需求</th> 
 </tr> 
 </thead> 
 <tbody style="border:0px;"> 
 <tr style="border-width:1px 0px 0px;border-top-style:solid;border-top-color:rgb(221,221,221);background-color:rgb(255,255,255);"> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;">float</td> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;" align="center">单精度浮点数</td> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;">4字节</td> 
 </tr> 
 <tr style="border-width:1px 0px 0px;border-top-style:solid;border-top-color:rgb(221,221,221);background-color:rgb(247,247,247);"> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;">double</td> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;" align="center">双精度浮点数</td> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;">8字节</td> 
 </tr> 
 <tr style="border-width:1px 0px 0px;border-top-style:solid;border-top-color:rgb(221,221,221);background-color:rgb(255,255,255);"> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;">DEC(M,D) DECIMAL(M,D)</td> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;" align="center">压缩的“严格”定点数</td> 
 <td style="padding:8px;margin:0px;font-weight:normal;text-align:left;font-size:14px;color:rgb(79,79,79);line-height:22px;border:1px solid rgb(221,221,221);vertical-align:middle;">M+2字节</td> 
 </tr> 
 </tbody> 
</table>

注：定点数以字符串形式存储，对精度要求高时使用decimal较好；尽量避免对浮点数进行减法和比较运算。

#### 2.1 定点数    ####

DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值。

使用方式如下：

salary DECIMAL(5,2)

我们看到其中有两个参数，即DECIMAL(M,D)，其中M表示十进制数字总的个数(注意是所有的个数)，D表示小数点后面数字的位数，上例中的取值范围为\-999.99~999.99。

如果存储时，整数部分超出了范围（如上面的例子中，添加数值为1000.01），MySql就会报错，不允许存这样的值。

如果存储时，小数点部分若超出范围，就分以下情况：

*   若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。如999.994实际被保存为999.99。
 *  若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如999.995和\-999.995都会报错。

M的默认取值为10，D默认取值为0。如果创建表时，某字段定义为decimal类型不带任何参数，等同于decimal(10,0)。带一个参数时，D取默认值。

* M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。
 * D的取值范围为0~30，而且必须<=M，超出范围会报错。
 * 所以，很显然，当M=65，D=0时，可以取得最大和最小值。

#### 2.2 浮点数 ####

####  ####

浮点数是用来表示实数的一种方法，它用 M(尾数) \* B( 基数)的E(指数）次方来表示实数，相对于定点数来说，在长度一定的情况下，具有表示数据范围大的特点。但同时也存在误差问题。

如果希望保证值比较准确(在记录money相关使一般就使用定点数类型)，推荐使用定点数数据类型。

MySql中的浮点类型有float，double和real。他们定义方式为：FLOAT(M,D) 、 REAL(M,D) 、 DOUBLE PRECISION(M,D)。

REAL就是DOUBLE ，如果SQL服务器模式包括REAL\_AS\_FLOAT选项，REAL是FLOAT的同义词而不是DOUBLE的同义词。

“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为\-999.9999。MySQL保存值时如果小数位数超过定义的长度，进行四舍五入。因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

FLOAT和DOUBLE中的M和D的取值默认都为0，即除了最大最小值，不限制位数。允许的值理论上是\-1.7976931348623157E+308~-2.2250738585072014E-308、0和2.2250738585072014E-308~1.7976931348623157E+308。M、D范围如下：

*  M取值范围为0~255。FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M<=6时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。
 * D取值范围为0~30，同时必须<=M。double只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M<=16时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。

FLOAT和DOUBLE中，若M的定义分别超出7和17，则多出的有效数字部分，取值是不定的，通常数值上会发生错误。因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。

内存中，FLOAT占4-byte（1位符号位 8位表示指数 23位表示尾数），DOUBLE占8-byte（1位符号位 11位表示指数 52位表示尾数）。IEEE754标准还对尾数的格式做了规范：d.dddddd...，小数点左面只有1位且不能为零，计算机内部是二进制，因此，尾数小数点左面部分总是1。显然，这个1可以省去，以提高尾数的精度。由上可知，单精度浮点数的尾数是用24bit表示的，双精度浮点数的尾数是用53bit表示的。所以就能算出取值范围和准确的有效位数了，但MySql中其实略有不同。

\----------

### 3. 日期/时间类型    ###

"0"值如下:

![20180608160423184][]

year ：范围 1901~2155;

time ：格式 'HH:MM:SS'（如果省略写，并且没有冒号，则默认最右起2位为秒，再到分，最后到时）；

date类型：格式 'YYYY-MM-DD',支持的范围是'1000-01-01'到 '9999-12-31'；

datetime（日期\+时间）：格式‘YYYY-MM-DDHH:MM:SS’或’YYYYMMDDHHMMSS’，取值范围：’1000-01-01 00:00:00’ ~’9999-12-31 23:59:59’；

timestamp（日期\+时间） ：格式‘YYYY-MM-DDHH:MM:SS’，但在存储时需要4个字节(datetime需要8字节)，并且以UTC（世界标准时间）进行存储（即timestamp会随设置的时区而变化，而datetime存储的绝不会变化）；范围从'1970-01-01 00:00:01' UTC 到'2038-01-1903:14:07' UTC。

**注意：上面使用分隔符的可以使用别的标识符比如'@','^'等代替。存入数据库时会自动解析。**

上面几种类型都可以使用时间函数插入当前时间：

INSERT INTO tab_time VALUES(CURRENT_DATE(),NOW());

\----------

### 4.字符串类型    ###

#### 4.1 CHAR和VARCHAR类型 ####

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值,如果不指定则默认为 1 。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉，所以，我们在存储时字符串右边不能有空格，即使有，查询出来后也会被删除。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值（实际可指定的最大长度与编码有关）。数据保存时不进行填充，插入时是什么样保存进去还是什么样。当值保存和检索时尾部的空格仍保留，符合标准SQL。在创建表时，VARCHAR类型的列必须指定最大长度。当插入的数据长度超过指定的长度，报错。

#### 4.2 BINARY和VARBINARY类型 ####

BINARY和VARBINARY类型类似于CHAR和VARCHAR类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。

#### 4.3 BLOB和TEXT类型 ####

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

**TEXT** 是一个字符串大对象，有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

BLOB和TEXT相比类似于CHAR和BINARY相比。

在TEXT或BLOB列的存储或检索过程中，区分大小写。

在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。

它们（TEXT和BLOB相同）的长度：

*  Tiny：最大长度255个字符(2^8-1)
 *  BLOB或TEXT：最大长度65535个字符(2^16-1)
 *  Medium：最大长度16777215个字符(2^24-1)
 *  LongText 最大长度4294967295个字符(2^32-1)

----------

#### 4.4 ENUM枚举类型 ####

MySql中的ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。

可以插入空字符串""和NULL：

如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。

如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。

============================

更加详细的说明可以参考：

[https://www.cnblogs.com/Caveolae/p/7058890.html][https_www.cnblogs.com_Caveolae_p_7058890.html]

[20180608145939517]: /images/20220523/2ea57aae59eb4e44a588b15ff2100bdb.png
[20180608152644916]: /images/20220523/e0e1651991674d4bb0942b1a38ca1ea2.png
[20180608160423184]: /images/20220523/4a9baa092a7f4d1e9579a847c43df3d9.png
[https_www.cnblogs.com_Caveolae_p_7058890.html]: https://www.cnblogs.com/Caveolae/p/7058890.html