MySQL常用数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间、字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL、NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。

下面的表显示了需要的每个整数类型的存储和范围。

INT类型：

在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。这些类型有附加参数：显示宽度指示器(ZEROFILL)，可以用来指定取出数据显示的长度

浮点类型：

MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。

FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。这两个类型也有附加参数：一个显示宽度指示器和一个小数点指示器。比如：语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。

DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。

UNSIGNED

上面的数值类型我们可以存储有符号数和无符号数。

默认使用的是有符号数，那么怎么表示存储为无符号数？

以TINYINT为例：

CREATE TABLE `test` (                                  
    `id` int NOT NULL AUTO_INCREMENT,                                           
    `state` tinyint unsigned,
    PRIMARY KEY (`id`)
);

ZEROFILL

ZEROFILL的意思是零填充

在使用时，我们可以限定取出数据的格式：

tinyint(M)，这里的M并不是指存储在数据库的具体字节，只有在字段指定ZEROFILL才有效。

下面看看zerofill的效果：

CREATE TABLE `test` (                                  
    `id` int NOT NULL AUTO_INCREMENT,
    `state` tinyint(1) zerofill DEFAULT NULL,   
    `state2` tinyint(2) zerofill DEFAULT NULL,  
    `state3` tinyint(3) zerofill DEFAULT NULL,  
    `state4` tinyint(4) zerofill DEFAULT NULL, 
    PRIMARY KEY (`id`)                                   
);

insert into test (state,state2,state3,state4) values(4,4,4,4);
insert into test (state,state2,state3,state4) values(14,14,14,14);

查询结果如下：

mysql> select * from test;
+----+------+-------+--------+--------+--------+
| id | str  | state | state2 | state3 | state4 |
+----+------+-------+--------+--------+--------+
|  6 | csdn |     4 |     04 |    004 |   0004 |
|  7 | csdn |    14 |     14 |    014 |   0014 |
+----+------+-------+--------+--------+--------+
2 rows in set (0.00 sec)

• TINYINT(M)：M默认为4
• SMALLINT(M)：M默认为6
• MEDIUMINT(M)：M默认为9
• INT(M)：M默认为11
• BIGINT(M)：M默认为20

时间和日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIME、TIMESTAMP和YEAR。

date、time和 year类型：

date类型的值应该使用连字号-作为分隔符分开

time类型的值应该使用冒号:作为分隔符分开。没有冒号分隔符的 time类型值，将会被 MySQL 理解为持续的时间。

year 类型的值必须用 4 个数字存储。

MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1999 之内。

datetime 和 timestamp 类型：

MySQL 支持 datetime 和 timestamp 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。

如果我们对 timestamp 类型的字段没有明确赋值，或是被赋予了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

字符串类型

MySQL 提供了8个基本的字符串类型,分别:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET等多种字符串类型。

可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

CHAR和VARCHAR类型：

• CHAR(M)和VARCHAR(M)的M代表的是存储字符的个数。M的个数不是任意的，在不同的字符集编码下M的最大值不一样。

  字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766

  字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845

  若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。

  **注：**真正的utf-8的字符占4字节，而mysql的utf-8实际上并不是真正的utf-8。MySQL的utf8是一种“专属的编码”，它能够编码的Unicode字符并不多。

  MySQL在2010年了一个叫作“utf8mb4”的字符集，这个才是真正意义上的utf-8。所以，我们都应该改用“utf8mb4”，永远都不要再使用“utf8”。

• CHAR表示的是存储定长的字符串，不足10个就用空格补齐，超过的就会截取。
• VARCHAR表示的是变长的字符串
• CHAR尾部的空格不会保留，而VARCHAR尾部的空格会保留

文章永久链接：https://tech.souyunku.com/40729