mysql哪个数据类型包含整型又包含小数,MySQL数据类型 · Issue #I1YP5T · mdmbct/Blog

## 数值型

### 整型

| 整数类型 | 字节 | 范围 |

| :----------: | :--: | :--------------------------------: |

| tinyint | 1 | 有符号:-128~127 有符号:0~255 |

| smallint | 2 | 有符号:-32768~32767 无符号:0~65535 |

| mediumint | 3 | 反正很大 |

| int、integer | 4 | 反正很大 |

| bigint | 8 | 反正很大 |

**注**

1、如果不设置无符号还是有符号，**默认是有符号**，如果想设置无符号，需要添加unsigned关键字；

2、如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值；

3、如果不设置长度(M)，会有默认的长度长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配`zerofill`使用。

```sql

CREATE TABLE tab_int(

t1 INT(7) ZEROFILL,

t2 INT(7) ZEROFILL

);

```

### 小数

| 浮点数类型 | 字节 | 范围 |

| :------------------------: | :---: | :----------------------------------------------------------: |

| float(M, D) | 4 | 反正很大 |

| double(M, D) | 8 | 反正很大 |

| 定点数类型 | 字节 | 范围 |

| DEC(M, D) DECIMAL(M, D) | M + 2 | 最大取值范围与double相同，给定decimal的有效取值范围由M和D决定 |

**注**

1、M：整数部位+小数部位，D：小数部位。如果超过范围，则插入临界值；

2、M和D都可以省略，如果是decimal，则M默认为10，D默认为0。如果是float和double，则会根据插入的数值的精度来决定精度；

3、定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用。

### 位类型

bit(M)

M的范围1~64，默认是1。

如果存入一个BIT类型的值，位数少于M值，则左补0。

如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式。

如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。

MySQL 中的位模式字面量一般使用 `b'val'` 或 `0bval` 语法，`val` 是只包含 `0` 和 `1` 的二进制值，字符 `b` 之前有无 `0` 都无关紧要，但要注意的是 `0b` 是区分大小写的，`0b` 不能写为 `0B` ，但 `b` 由不限大小写。`b'001'` 和 `B'001'` 是一个意思。

```sql

# 以下这些是合法的位类型字面量

b'01'

B'01'

0b01

```

### 原则

所选择的类型越简单越好，能保存数值的类型越小越好。

## 字符型

### char与varchar

char和varchar类型声明的长度表示你想要保存的最大字符数。例如，char(30)可以占用30个字符。默认长度都为255。

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

所以当char类型的字段为唯一值时，添加的值是否已经存在以不包含末尾空格(可能有多个空格)的值确定，比较时会在末尾补满空格后与现已存在的值比较。

**varchar列中的值为可变长字符串**。长度可以指定为0到65,535之间的值(实际可指定的最大长度与编码和其他字段有关，比如，MySQL使用UTF-8编码格式，大小为标准格式大小的2倍，仅有一个varchar字段时实测最大值仅21844，如果添加一个char(3)，则最大取值减少3。整体最大长度是65,532字节)。

同char对比，varchar值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

varchar值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

如果分配给char或varchar列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

### binary与varbinary

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

当保存binary值时，在它们右边填充0x00(零字节)值以达到指定长度。取值时不删除尾部的字节。比较时所有字节很重要(因为空格和0x00是不同的，0x00

对于varbinary，插入时不填充字符，选择时不裁剪字节。比较时所有字节很重要。

当类型为binary的字段为主键时，应考虑上面介绍的存储方式。

### blob与text

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

有4种text类型：tinytext、text、mediumtext和longtext。这些对应4种blob类型，有相同的最大长度和存储需求。

**blob列被视为二进制字符串**，**text列被视为字符字符串**，类似char和binary。

在text或blob列的存储或检索过程中，不存在大小写转换。

未运行在严格模式时，如果你为blob或text列分配一个超过该列类型的最大长度的值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

在大多数方面，可以将blob列视为能够足够大的varbinary列。同样，可以将text列视为varchar列。

blob和text在以下几个方面不同于varbinary和varchar：

- 当保存或检索blob和text列的值时不删除尾部空格。(这与varbinary和varchar列相同)。

- 比较时将用空格对text进行扩充以适合比较的对象，正如char和varchar。

- 对于blob和text列的索引，必须指定索引前缀的长度。对于char和varchar，前缀长度是可选的。

- blob和text列不能有默认值。

MySQL Connector/ODBC将blob值定义为longvarbinary，将text值定义为longvarchar。

blob或text对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

每个blob或text值分别由内部分配的对象表示。

它们(text和blob同)的长度：

- tiny：最大长度255个字符(2^8-1)

- blob或text：最大长度65535个字符(2^16-1)

- medium：最大长度16777215个字符(2^24-1)

- longtext 最大长度4294967295个字符(2^32-1)

实际长度与编码有关，比如UTF-8的会减半。

### enum

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

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

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

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

值的索引规则如下：

- 来自列规定的允许的值列中的值从1开始编号。

- 空字符串错误值的索引值是0。所以，可以使用下面的SELECT语句来找出分配了非法enum值的行：`SELECT * FROM tbl_name WHERE enum_col=0;`

- NULL值的索引是NULL。

enum最多可以有65,535个元素。当创建表时，enum成员值的尾部空格将自动被删除。

如果将一个数字保存到enum列，数字被视为索引，并且保存的值是该索引对应的枚举成员(这不适合LOAD DATA，它将所有输入视为字符串)。**不建议使用类似数字的枚举值来定义一个enum列**，因为这很容易引起混淆。

enum值根据索引编号进行排序。例如，对于enum('a'，'b')，'a'排在'b'前面，但对于enum(' b'，'a')，' b'排在'a'前面。空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果，按字母顺序规定enum列。还可以使用`GROUP BY CAST(col AS CHAR)`或`GROUP BY CONCAT(col)`来确保按照词汇对列进行排序而不是用索引数字。

### set

set是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号(‘,’)间隔开。例如，指定为set('one', 'two') NOT NULL的列可以有下面的任何值：

- ''

- 'one'

- 'two'

- 'one,two'

set最多可以设置64个值。创建表时，set成员值的尾部空格将自动被删除。检索时，保存在set列的值使用列定义中所使用的大小写来显示。

MySQL用数字保存set值，所保存值的低阶位对应第1个set成员。如果在数值上下文中检索一个set值，检索的值的位设置对应组成列值的set成员。

对于包含多个set元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。

set值按数字顺序排序。NULL值排在非NULL set值的前面

## 日期时间类型

### date, datetime, 和timestamp

date只保存日期，MySQL用'YYYY-MM-DD'格式检索和显示date值。支持的范围是'1000-01-01'到 '9999-12-31'。

datetime保存日期+时间，MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示datetime值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'，不受时区影响。

timestamp保存日期+时间，范围从'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC，受时区影响。

可以使用任何常见格式指定datetime、date和timestamp值：

- 'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，'98-12-31’ 、‘11:30:45'、'98.12.31’ 、‘11+30+45'、'98/12/31’ 、‘11\*30\*45'和'98@12@31’ 、‘11\^30\^45'是等价的。

- 'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。这里也允许使用“不严格的”语法。例如，'98-12-31'、'98.12.31'、'98/12/31'和'98@12@31'是等价的。

- YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，'19970523091528'和'970523091528'被解释为'1997-05-23 09:15:28'，但'971122129015'是不合法的(它有一个没有意义的分钟部分)，将变为'0000-00-00 00:00:00'。

- 'YYYYMMDD'或'YYMMDD'格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，'19970523'和'970523'被解释为 '1997-05-23'，但'971332'是不合法的(它有一个没有意义的月和日部分)，将变为'0000-00-00'。

- YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为 '1983-09-05 13:28:00'。

- YYYYMMDD或YYMMDD格式的数字，假定数字对于日期类型是有意义的。例如，19830905和830905被解释为'1983-09-05'。

- 函数返回的结果，其值适合datetime、date或者timestamp上下文，例如NOW()或CURRENT_date。

对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需要指定两位数。

'1979-6-9'与'1979-06-09'是相同的。

同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。

'1979-10-30 1:2:3'与'1979-10-30 01:02:03'相同。

可以将一个日期类型的值分配给一个不同的日期类型。但是，值可能会更改或丢失一些信息：

- 如果你为一个datetime或timestamp对象分配一个date值，结果值的时间部分被设置为'00:00:00'，因为date值未包含时间信息。

- 如果你为一个date对象分配一个datetime或timestamp值，结果值的时间部分被删除，因为date值未包含时间信息。

- 记住尽管可以使用相同的格式指定datetime、date和timestamp值，不同类型的值的范围却不同。例如，timestamp值不能早于1970或晚于2037。这说明一个日期，例如'1968-01-01'，虽然对于datetime或date值是有效的，但对于timestamp值却无效，如果分配给这样一个对象将被转换为0。

当指定日期值时请注意某些缺陷：

- 指定为字符串的值允许的非严格格式可能会欺骗。例如，值'10:11:12'由于‘:’间割符看上去可能象时间值，但如果用于日期上下文值则被解释为年'2010-11-12'。值'10:45:15'被转换为'0000-00-00'因为'45'不是合法月。

- 在非严格模式，MySQL服务器只对日期的合法性进行基本检查：年、月和日的范围分别是1000到9999、00到12和00到31。任何包含超出这些范围的部分的日期被转换成'0000-00-00'。请注意仍然允许你保存非法日期，例如'2002-04-31'。要想确保不使用严格模式时日期有效，应检查应用程序。在严格模式，非法日期不被接受，并且不转换。

- 包含两位年值的日期会令人模糊，因为世纪不知道。MySQL使用以下规则解释两位年值： o 00-69范围的年值转换为2000-2069。 o 70-99范围的年值转换为1970-1999。

### time

MySQL以'HH:MM:SS'格式检索和显示time值(或对于大的小时值采用'HHH:MM:SS'格式)。

time值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是time类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为**某个事件过去的时间或两个事件之间的时间间隔**(可以大于24小时，或者甚至为负)。

你可以用各种格式指定time值：

- 'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法：'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0到34之间的值。请注意MySQL还不保存分数。

- 'HHMMSS'格式的没有间割符的字符串，假定是有意义的时间。例如，'101112'被理解为'10:11:12'，但'109712'是不合法的(它有一个没有意义的分钟部分)，将变为'00:00:00'。

- HHMMSS格式的数值，假定是有意义的时间。例如，101112被理解为'10:11:12'。下面格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL还不保存分数。

- 函数返回的结果，其值适合time上下文，例如CURRENT_time。

对于指定为包括时间部分间割符的字符串的time值，如果时、分或者秒值小于10，则不需要指定两位数。'8:3:2'与'08:03:02'相同。

为time列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释time值为过去的时间而不是当天的时间)。例如，你可能认为'1112'和1112表示'11:12:00'(11点过12分)，但MySQL将它们解释为'00:11:12'(11分，12 秒)。同样，'12'和12 被解释为 '00:00:12'。相反，time值中使用冒号则肯定被看作当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。

超出time范围但合法的值被裁为范围最接近的端点。例如，'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。

无效time值被转换为'00:00:00'。请注意由于'00:00:00'本身是一个合法time值，只从表内保存的一个'00:00:00'值还不能说出原来的值是 '00:00:00'还是不合法的值。

### year

year类型是一个单字节类型用于表示年。

MySQL以YYYY格式检索和显示year值。范围是1901到2155。

可以指定各种格式的YEAR值：

- 四位字符串，范围为'1901'到'2155'。

- 四位数字，范围为1901到2155。

- year

- 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的year值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。

- 函数返回的结果，其值适合year上下文，例如NOW()。

非法YEAR值被转换为0000。