【MySQL — 数据库基础】深入解析MySQL常用数据类型

---

---

常用数据类型    

---

创建完数据库之后，就要在数据库中创建表，表中存储的数据记录，一条记录由不同的列组成，每条列都需要自己的类型；并且表中的多个行对应的列的数据类型，都必须是相同的；

那么每个列需要什么要的数据类型? 

---

我们重点介绍下面这几种类型：

---

1. 位类型 

---

    BIT[(M)]     

---

• bit[(M)] : 位字段类型。
• M表示每个值的位数，M的范围 [ 1 , 64 ]，如果M被忽略，默认为1；
• 存储数值范围 [ 0 , 2^M-1 ] ；

一个字节插入测试：
mysql> insert test1 values(1,1);
Query OK, 1 row affected (0.01 sec)mysql> insert test1 values(1,0);
Query OK, 1 row affected (0.01 sec)mysql> insert test1 values(1,2);
ERROR 1406 (22001): Data too long for column 'num' at row 1

说明：当 bit 的位数位1时只能插入0/1，插入其他则报错。

测试：
mysql> select *from test1;
+------+------------+
| id   | num        |
+------+------------+
|    1 | 0x01       |
|    1 | 0x00       |
+------+------------+
2 rows in set (0.00 sec)

---

2. 整数类型

---

---

    TINTINT     

---

大小为1个字节，对应 Java 类型为 Byte ，范围是 [ -128 , +127 ]

有符号越界测试案例：
mysql> create table test1(num tinyint);
Query OK, 0 rows affected (0.02 sec)mysql> insert into test1 values(-128);
Query OK, 1 row affected (0.00 sec)mysql> insert into test1 values(127);
Query OK, 1 row affected (0.00 sec)mysql> insert into test1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

---

    说明： 

如果我们向MySQL特定的类型中插入不合法的数据，MYSQL一般都是直接拦截不让我们对应操作! 反之如果我们有数据已经插入到MySQL中，那么数据一定是合法的。简而言之，这也是对数据类型的一种约束! 

---

    SMALLTINT     

---

大小为2个字节，对应 Java 类型为 Short ，范围是 [ -32768 , +32767 ]

---

    INT     

---

大小为4个字节，对应 Java 类型为 Integer  

---

    BIGINT     

---

大小为8个字节，对应 Java 类型为 Long  

---

    注意    

---

表示整数的 TINYINT , SMALLINT , INT , BIGINT 这几个类型，在 SQL 中使用的名字和主流语言不同，主要是因为 SQL 其实是一个相当古老的编程语言； 后来随着 Java 影响力越来越大，人们越来越认可 Java 中 Byte , Short , Integer , Long 这套类型的名字。

虽然 TINYINT 和 SMALLINT 更节省空间，但是使用时很容易超出数据范围，所以还是更推荐使用 INT 或者 BIGINT；因为随着时代发展，硬件设备（内存/硬盘）成本是越来越低的，相比之下，程序猿的开发成本（时间）是更加尖锐的矛盾；

---

3. 浮点类型

---

---

    FLOAT(M,D)     

---

表示4个字节的单精度浮点数 ，M表示浮点数长度，D表示小数点位数；

---

    DOUBLE(M,D)      

---

表示8个字节的双精度浮点数，M表示浮点数长度，D表示小数点位数；

比如 double(3,1)表示数字长度是3位，小数点后是1位，后续如果针对数据库数据进行插入/修改，此时新的数据就得遵守这个规则：

如 10.0，99.5 就是合法的数据，可以插入到数据库中，但是如果是1.00，1，123.0 就是非法的数据，尝试插入数据库的时候，就会报错；

---

    注意    

---

 MYSQL 也是遵循 IEEE754 标准来表示浮点数；类似于科学计数法（二进制）的方式来表示浮点数，这种表示方法有一个比较大的缺陷:

很多时候小数是不能精确存储和表示的，这就导致在进行某些计算的时候，会出现误差；所以严禁使用两个浮点数进行比较相等；

---

不仅仅是数据库中有这样的缺陷，在C , JAVA , C++ , JS....这些主流语言也有类似的问题；如果针对浮点数，要比较相等，需要作差，看差值是否小于一定的误差范围；只要小于一定的范围就可以认为是近似相等；

---

4. 定点数类型

---

    DECIMAL(M,D) / NUMERIC(M,D)   

---

这两个类型差别不大，一般使用 decimal 类型即可；decimal 在 Java 中对应类型为 BigDecimal；

---

    注意    

---

DECIMAL(M,D) / NUMERIC(M,D) 也是用于表示小数，但是不再使用IEEE754 这一套标准，而是自己设定了一套存储格式：

• 相当于“变长的”，付出更多空间，使存储的数据更精确；
• 同时也付出了更多时间代价，拿着两个 decimal 计算的速度远远小于拿着两个 double 进行计算；

IEEE754 这套标准虽然有明显的缺陷，但是仍然成为各种主流语言表示浮点数的方法；因为这套标准对于硬件（CPU，内存......）是最友好的；运算速度也快，存储空间也小；

---

5. 无符号类型

---

在 mysql 官方文档中，明确说明不建议使用无符号类型，甚至在未来更高版本的 mysql 中，无符号类型可能会删去，因为无符号类型作用不大，但是使用可能会带来很多问题；

无符号问题的最大问题就是：两个无符号类型作减法的时候，容易出现溢出，导致结果为一个很大的正数；这样的问题可能会严重影响程序逻辑，带来非常严重的Bug。

---

6. 字符串类型

---

    VARCHAR(SIZE)     

---

---

---

 char[SIZE] 是一个定长的字符串类型，如果设定小了，容易不够用，设定大了，容易浪费空间（浪费的不是内存，而是硬盘，mysql 数据库使用硬盘存储数据）。

因此一般使用 varchar(SIZE)，表示可变长字符串（可指定最大长度），就可以解决上述问题； 

---

---

强调：

varchar(SIZE) 的单位是字符，不是字节，尤其是在 utf8 编码方式下，一个字符等于好几个字节。

---

     TEXT     

---

TEXT 也是可变长字符串，不需要指定最大长度，完全根据我们存储的数据自适应；

varchar 可以指定最大长度，TEXT则没办法指定最大长度，因此 TEXT 的大小难以预估，varchar 由于指定最大长度，程序猿很容易预估出表里面的数据量有多少，所以更推荐使用 varchar 。 

---

    MEDIUMTEXT    

---

VARCHAR(SIZE) 的大小为 0-65,535字节 ，但是在一些特定场景下，就是需要比较长的数据量，导致 varchar 不够用，就可以使用 mediumtext 类型。

---

    BLOB    

---

 前面的几个类型存储的是文本数据，blob 存储的是二进制数据；

• 计算机存储和表示的数据，都是二进制的方式（内存，硬盘，CPU....流转的数据，都是010101这样的数据）；
• 文本是一种特殊的情况，文本数据里面的二进制内容，都可以在对应的码表（gbk, utf8...）上查询到对应的合法字符；
• 区分文本数据和二进制数据，就可是否可以在码表上查询到对应的合法字符；
• 一般情况下，字符串一般都是文本数据，使用数据库存储就使用 varchar 存储；
• 图片，视频，音频，可执行文件都是二进制数据，使用数据库存储就使用 blob 存储；但是 blob 只能表示最大 64kb，所以一般不建议直接使用数据库存储图片，视频，音频等内容；
• 数据库 SQL 里面提供了丰富的功能，但是这些功能只能针对数字，字符串，时间日期才有效，存储二进制数据，上述丰富的功能就没有什么用了；
• 另一方面，数据库往往是一个系统中，执行效率比较低的环节，容易造成性能瓶颈，把二进制数据提出来，不使用数据库保存，也能有效降低数据库负担；

---

7. 时间日期类型

---

---

timestamp 表示时间戳，使用 timestamp 可以得到系统的时间戳（整数），以 1970年1月1日0时0分0秒为基准时间，计算当前时间和基准时间的秒数/毫秒数/微妙数只差；

计算机理解时间戳非常方便，但是人理解起来就比较困难，往往具体显示时间的时候，就需要计算机把时间戳转换成 年月日时分秒 这样的格式化时间；

但是大小为 4个字节版本的时间戳 timestamp 不推荐使用，因为范围是 [1970 , 2038] ，超出 2038年就会造成溢出；

当前时间戳为 17 亿左右大小的数字，而 timestamp 最大是 21 亿，到 2038年就无法存下这个数字了；

计算机发展历史上，有一个知名问题，就是千年虫问题：

---

在2000年之前，计算机表示年份是使用末尾的两位数字表示的，导致在2000年到达的时候，年份从99->00，因为 99 > 00，所以后续进行比较大小的操作就乱套了，这个问题给但是的计算机行业造成了非常大的冲击，很多程序因此产生了严重的 bug !!!!

---

所以在 2038 年，这样的剧情很可能会再次上演，这是一件细思极恐的事情；因此，在 2038 年，尽量不要随便出去坐飞机，轮船，出去探险这样的事情（bushi）。

---

---