文章目录
1.1 mediumint说明1.2 测试环境说明1.3 加unsigned属性1.3.1 SQL模式开启严格模式1.3.2 SQL模式未开启严格模式 1.4 加zerofill属性1.4.1 SQL模式开启严格模式1.4.2 SQL模式未开启严格模式 1.5 不加unsigned和zerofill属性1.5.1 SQL模式开启严格模式1.5.2 SQL模式未开启严格模式1.1 mediumint说明
## 格式id mediumint(M)[unsigned][zerofill]字段名 数据类型(显示长度,建表时不指定) 无符号 无符号且前导零填充## 关于mediumint最大数值是怎样得来的mediumint占用3字节,3字节占用24位，经过换算(2的24次方减1)就是16777215## 关于加上unsigned后的说明加上unsigned后就是无符号(范围是0~16777215的整数,因为是整数,不会有符号"-",就是无符号)## 关于加上zerofill后的说明zerofill会把unsigned属性也给带上,这样就是无符号（范围是0~16777215，显示长度就是8），同时还会进行前导零填充（没有达到显示长度的数值，例如：你插入1,显示的是00000001）。## 不加unsigned和zerofill的说明字段后面不加上这两个属性中的任何一个,就表示是有符号(范围是-8388608~8388607,因为有符号"-",所有是有符号)。
1.2 测试环境说明
## 数据库版本和默认的存储引擎mysql> select @@version,@@default_storage_engine;+------------+--------------------------+| @@version | @@default_storage_engine |+------------+--------------------------+| 5.7.28-log | InnoDB |+------------+--------------------------+1 row in set (0.00 sec)## 创建chenliang库mysql> create database if not exists chenliang;Query OK, 1 row affected (0.03 sec)mysql> show databases like "chenliang";+----------------------+| Database (chenliang) |+----------------------+| chenliang |+----------------------+1 row in set (0.03 sec)## 进入chenliang库mysql> use chenliang;Database changedmysql> select database();+------------+| database() |+------------+| chenliang |+------------+1 row in set (0.01 sec)## 查看事务是否自动提交mysql> select @@global.autocommit;+---------------------+| @@global.autocommit |+---------------------+| 1 |+---------------------+1 row in set (0.00 sec)
1.3 加unsigned属性
1.3.1 SQL模式开启严格模式
SQL_MODE中开启了严格模式,即SQL_MODE参数中包含STRICT_TRANS_TABLES参数
# 设置会话模式下sql_mode中包含strict_trans_tables变量mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)## 创建test1测试表（这里指定了UNSIGNED，也就是无符号）mysql> create table if not exists test1(-> id mediumint unsigned-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.19 sec)## 表test1的id字段加了unsigned参数，那么id字段的范围就是0~16777215;## 显示显示应该是mediumint(8),因为16777215的长度是8;## 查看test1表的表结构mysql> desc test1;+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id | mediumint(8) unsigned | YES || NULL | |+-------+-----------------------+------+-----+---------+-------+1 row in set (0.10 sec)## 测试插入0~16777215范围内的整数和不在该范围内的整数mysql> insert into test1(id) values(-1); # 插入数值-1,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> insert into test1(id) values(0); # 插入数值0,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test1(id) values(16777215); # 插入数值16777215,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test1(id) values(16777216); # 插入数值16777216,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> select * from test1;+----------+| id |+----------+| 0 || 16777215 |+----------+2 rows in set (0.00 sec)
1.3.2 SQL模式未开启严格模式
SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数
## 设置会话模式下sql_mode中不开启严格模式,即不包含strict_trans_tables变量mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)## 创建test11表（这里加上unsigned，也就是无符号）mysql> create table if not exists test11(-> id mediumint unsigned-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.01 sec)## 表test1的id字段加了unsigned参数，那么id字段的范围就是0~16777215;## 显示显示应该是mediumint(8),因为16777215的长度是8;## 查看test11表的表结构mysql> desc test11;+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id | mediumint(8) unsigned | YES || NULL | |+-------+-----------------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入0~16777215范围内的整数和不在该范围内的整数mysql> insert into test11(id) values(-1);Query OK, 1 row affected, 1 warning (0.01 sec)## 不在范围内,插入时未报错(因为sql_mode中未开启严格模式)## 插入到表中的数据不是-1,而是0mysql> insert into test11(id) values(0);Query OK, 1 row affected (0.00 sec)## 在范围内,插入时不报错,插入的是什么就是什么mysql> insert into test11(id) values(16777215);Query OK, 1 row affected (0.01 sec)## 在范围内,插入时不报错,插入的是什么就是什么mysql> insert into test11(id) values(16777216);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围内,插入时未报错(因为sql_mode中未开启严格模式)## 插入到表中的数据不是16777216,而是16777215mysql> select * from test11;+----------+| id |+----------+| 0 || 0 || 16777215 || 16777215 |+----------+4 rows in set (0.00 sec)
1.4 加zerofill属性
1.4.1 SQL模式开启严格模式
SQL_MODE中开启了严格模式,即SQL_MODE参数中包含STRICT_TRANS_TABLES参数
# 设置会话模式下sql_mode中包含strict_trans_tables变量mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)## 创建test2表（这里指定了zerofill,也就是前导零填充）mysql> create table if not exists test2(-> id mediumint zerofill-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.03 sec)## id字段加了zerofile参数,它会把unsigned参数也带上,且会前导零填充;## 范围就是0~16777215, 因为16777215的长度是8, 显示长度是mediumint(8);## 查看test2表的表结构mysql> desc test2;+-------+--------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------------------------+------+-----+---------+-------+| id | mediumint(8) unsigned zerofill | YES || NULL | |+-------+--------------------------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入0~16777215范围内的整数和不在该范围内的整数mysql> insert into test2(id) values(-1); # 插入数值-1,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> insert into test2(id) values(0); # 插入数值0,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test2(id) values(16777215); # 插入数值16777215,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test2(id) values(16777216); # 插入数值16777216,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> select * from test2;+----------+| id |+----------+| 00000000 || 16777215 |+----------+2 rows in set (0.00 sec)
1.4.2 SQL模式未开启严格模式
SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数
## 设置会话模式下sql_mode中不开启严格模式,即不包含strict_trans_tables变量mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)## 创建test22表(加上zerofill参数,它会前导零填充,同时也会把unsigned带上)mysql> create table if not exists test22(-> id mediumint zerofill-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.01 sec)## id字段加了zerofile参数,它会把unsigned参数也带上,且会前导零填充;## 范围就是0~16777215, 因为16777215的长度是8, 显示长度是mediumint(8);## 查看test22表的表结构mysql> desc test22;+-------+--------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------------------------+------+-----+---------+-------+| id | mediumint(8) unsigned zerofill | YES || NULL | |+-------+--------------------------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入0~16777215范围内的整数和不在该范围内的整数mysql> insert into test22(id) values(-1);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围,插入未报错(因为sql_mode中未开启严格模式)## 插入的数据不是-1,而是0,但显示时会前导零填充,因为加了zerofill参数mysql> insert into test22(id) values(0);Query OK, 1 row affected (0.00 sec)## 在范围内,不报错,插入是多少就是多少,但显示时会前导零填充,因为加了zerofill参数mysql> insert into test22(id) values(16777215);Query OK, 1 row affected (0.00 sec)## 在范围内,不报错,插入是多少就是多少mysql> insert into test22(id) values(16777216);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围,插入未报错(因为sql_mode中未开启严格模式)## 插入的数据不是16777216,而是16777215mysql> select * from test22;+----------+| id |+----------+| 00000000 || 00000000 || 16777215 || 16777215 |+----------+4 rows in set (0.00 sec)
1.5 不加unsigned和zerofill属性
1.5.1 SQL模式开启严格模式
SQL_MODE中开启了严格模式,即SQL_MODE参数中包含STRICT_TRANS_TABLES参数
## 设置会话模式下sql_mode中包含strict_trans_tables变量mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)## 创建test3表（不加unsigned和zerofill）mysql> create table if not exists test3(-> id mediumint-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.05 sec)## 表test3的id字段没加unsigned和zerofile参数,那么id字段的范围就是-8388608~8388607## 显示长度是9,因为-8388608和8388607的长度是8,但要显示符号("-"),所显示长度是9## 查看test3表的表结构mysql> desc test3;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | mediumint(9) | YES || NULL | |+-------+--------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入-8388608~8388607范围内的整数和不在该范围内的整数mysql> insert into test3(id) values(-8388609); # 插入数值-8388609,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> insert into test3(id) values(-8388608); # 插入数值-8388608,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test3(id) values(8388607); # 插入数值8388607,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test3(id) values(8388608); # 插入数值8388608,错误,在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> select * from test3;+----------+| id |+----------+| -8388608 || 8388607 |+----------+2 rows in set (0.00 sec)
1.5.2 SQL模式未开启严格模式
SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数
## 设置会话模式下sql_mode中不开启严格模式,即不包含strict_trans_tables变量mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)## 创建test33表（不加unsigned和zerofill参数）mysql> create table if not exists test33(-> id mediumint-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.01 sec)## 表test33的id字段没加unsigned和zerofile参数,那么id字段的范围就是-8388608~8388607## 显示长度是9,因为-8388608和8388607的长度是8,但要显示符号("-"),所显示长度是9## 查看test33表的表结构mysql> desc test33;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | mediumint(9) | YES || NULL | |+-------+--------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入-8388608~8388607范围内的整数和不在该范围内的整数mysql> insert into test33(id) values(-8388609);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围内,插入未报错（因为sql_mode中未开启严格模式）## 插入的数据不是-8388609,而是-8388608mysql> insert into test33(id) values(-8388608);Query OK, 1 row affected (0.00 sec)## 在范围内,未报错,插入是多少就是多少mysql> insert into test33(id) values(8388607);Query OK, 1 row affected (0.00 sec)## 在范围内,未报错,插入是多少就是多少mysql> insert into test33(id) values(8388608);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围内,插入未报错（因为sql_mode中未开启严格模式）## 插入的数据不是8388608,而是8388607mysql> select * from test33;+----------+| id |+----------+| -8388608 || -8388608 || 8388607 || 8388607 |+----------+4 rows in set (0.00 sec)
