一、整数值的那些事

1. 我常用整数类型

关键点：

• 在MySQL中的 true 和 false ，就是用 1 和 0 来表示。
• 无符号整数，在正数值的表示范围是原来的两倍，但不能表示负数。

2. 整数值有无符号的坑

关于INT和BIGINT，有符号和无符号其实还是有一些比较有趣的故事。

首先创建一张数据表

create table test_unsigned ( 
a int UNSIGNED,
b int UNSIGNED
);

在这个数据表中，a和b都是无符号的整数，我尝试新增进入这张表两个数据。

insert into test_unsigned values (1,2);
insert into test_unsigned values (1,1);

此时数据表中有两行数据，1，1 和 1，2。

那么我们可以预测一下下列的sql的执行结果是什么？

mysql> select b-a from test_unsigned;
+------+
| b-a  |
+------+
|    1 |
|    0 |
+------+
2 rows in set (0.00 sec)

结果显示是正常的，1-1是0，2-1是1.也就是说无符号的字段是可以表示0的。

在看一个SQL，预测一下执行结果是什么？

mysql> select a-b from test_unsigned;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`mall`.`test_unsigned`.`a` - `mall`.`test_unsigned`.`b`)'

报错了，由于a和b是无符号的值，如果结果为负数，超出了其表达的范围就会报错。

整数值的有无符号结论：

无符号：字段值只能出现正数不能出现负数可以出现0，且正数的表示范围会 * 2（多了一位）。
有符号：可以出现负数，正常的表示范围。
故我们在选用无符号的时候需要考虑到这个问题。

3.整数值的宽度限制

日常开发中我们通常会看到如下语句，声明int(n),int(n) 中的 n 仅仅指的是显示宽度。且限制这个字节后如果不跟上ZEROFILL其实是没有意义的。

create table test_int_n(
a int(4) ZEROFILL
);

尝试向表中新增两条数据.

insert into test_int_n values (1);
insert into test_int_n values (11);
insert into test_int_n values (1111);
insert into test_int_n values (12345);

查询一下该表的全部数据吧

mysql> select * from test_int_n;
+-------+
| a     |
+-------+
| 12345 |
|  0001 |
|  0011 |
|  1111 |
+-------+
4 rows in set (0.00 sec)

不满足4个的就补0，满足4个的就正常显示。

被设置ZEROFILL的字段默认是无符号的

mysql> insert into test_int_n values (-1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

该案例即可证明，该字段无法表示负值。

4.趣味的自增主键

率先创建一张自增ID的数据表,并插入第一组数据。

create table test_auto_increment(
a int auto_increment primary key
);

尝试新增第一组数据，空和非空不等。

mysql> insert into test_auto_increment values (null),(5),(null),(15),(null),(10),(null);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

查询第一组数据的插入结果

mysql> select * from test_auto_increment;
+----+
| a  |
+----+
|  1 |
|  5 |
|  6 |
| 10 |
| 15 |
| 16 |
| 17 |
+----+
7 rows in set (0.00 sec)

可以看出，为null的就走自增了，而我们指定的主键会迫使主键自增序列同步，也就是自增序列为当前字段的最大值。若下一个值为null就从字段最大值开始向上递增。

插入第二组数据并查看结果

插入 -1 和 0 值。

mysql> insert into test_auto_increment values (-1),(0);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看结果并对比之前的结果。

mysql> select * from test_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  5 |
|  6 |
| 10 |
| 15 |
| 16 |
| 17 |
| 19 |
+----+
9 rows in set (0.00 sec)

-1值可以正常的插入进去，该字段并没有标明是无符号的，所以可以表示负值。
****关键在于 “0” ，自增主键为“0”时并没有真正意义上的插入“0”，而是走了自增序列的下一个值，变成了19.****
很奇怪，虽然不明白为什么，这确实是一个点，以前开发过程中并没发现这个事情。

二、字符类型的那些事

1.'a'是否等于'A'

执行查询语句查看结果

mysql> select 'a' = 'A'; 
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

可以看到，查询结果是'1'，目前的情况下A是等于a的。

建立数据表再次验证这个问题

创建数据表并新增两条数据。

create table test_ci (
a varchar(10),key(a)
);
insert into test_ci values ('a'),('A');
mysql> select * from test_ci;
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

数据已经填充进去了，再次执行验证的查询SQL。

mysql> select * from test_ci where a = 'a';
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

结果是 a 依旧等于 A ，这个究竟是什么问题？

排序规则，bin 二进制区分大小写，ci（case insensitive） 忽略大小写。

修改排序规则再次验证

mysql> set names utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' = 'A'; 
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

更改排序规则为二进制之后就会区分大小写了。

三、时间类型的时区

1.timestamp 和 datetime

建立数据表新增数据进行测试

create table test_time (
a timestamp,
b datetime
);
insert into test_time  values (now(),now());

查询结果

mysql> select * from test_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2020-07-08 10:46:56 | 2020-07-08 10:46:56 |
+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到，新增进去的时间不论是时间戳还是datetime都是可以精确表示当前时间的。
执行完修改时区的语句后，这个查询结果就会变了。

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2020-07-08 02:46:56 | 2020-07-08 10:46:56 |
+---------------------+---------------------+
1 row in set (0.00 sec)

时间戳受到时区的影响，查询出来的结果发生了改变，但是datetime并没有发生改变。
MySQL默认时区为东八区， default-time-zone='+08:00' 。
所以在选用时间类型的时候需要注意到这些内容。

时区修改

****永久有效方法****
修改 my.cnf 文件，加入如下2行：

# 数据表默认时区，即设置MySQL默认时区为东八区。
default-time-zone='+08:00'          

****仅当前会话有效方法****
并不是每个时候我们都需要一致的时区，如果要在当前回话中设置独特的时区，
可以进入MySQL命令行，用命令设置当前回话的时区：
SET time_zone = "+08:00";

2.date 和 datetime 的区别

区别就很简单，一句话描述，date只能表示日期 yyyy-MM-dd。datetime却可以表示精确的时间，仅此而已。

四、总结

关于数据类型在使用的时候有很多需要注意的地方，可能有一些我还没有通过什么渠道去发现，但是在使用的过程中，但凡可以考虑到的都尽可能的去考虑一下，避免因为这些细小的问题影响到自己开发的软件的正确性。