MySQL杂谈

2024-05-27 12:05| 来源: 网络整理| 查看: 265

一、整数值的那些事 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却可以表示精确的时间，仅此而已。

四、总结

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

【本文地址】

MySQL杂谈

MySQL杂谈

今日新闻

推荐新闻