mysql有符号和无符号,MySQL杂谈-自增主键、有无符号、字符比较、时区影响

一、整数值的那些事

1. 我常用整数类型

类型

大小(byte)

范围(有符号)

范围(无符号)

用途

TINYINT

1

(-128，127)

(0，255)

小整数值 或 true/false

INT

4

(-2 147 483 648，2 147 483 647)

(0，4 294 967 295)

大整数值

BIGINT

8

(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)

极大整数值

关键点：

在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却可以表示精确的时间，仅此而已。

四、总结

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