文章目录
1.1 tinyint类型说明1.2 实践环境说明1.3 加unsigned属性1.3.1 SQL模式开启严格模式1.3.3 SQL模式未开启严格模式1.4 加zerofill属性1.4.1 SQL模式开启严格模式1.4.2 SQL模式未开启严格模式1.5 不加unsigned和zerofill属性1.5.1 SQL模式开启严格模式1.5.2 SQL模式未开启严格模式PS：mysql整数数据类型规范整理
1.1 tinyint类型说明
#### 格式id tinyint(M) [unsigned][zerofill]字段名 数据类型(显示长度,建表时不指定)无符号无符号且前导零填充#### 关于tinyint最大数值是怎样得来的tinyint占用1字节,1字节占用8位，经过换算(2的8次方减1)就是255;#### 关于加上unsigned后的说明加上unsigned属性后就是无符号(范围是0~255的整数,因为是整数,不会有符号"-",所以就是无符号)#### 关于加上zerofill后的说明zerofill属性会把unsigned属性也给带上,这样就是无符号（范围是0~255，显示长度就是3），同时还会进行前导零填充（没有达到显示长度的数值，例如：你插入1，显示的是001）。#### 不加unsigned和zerofill的说明字段后面不加上这两个属性中的任何一个,就表示是有符号(范围是-128~127,因为有符号"-",所有是有符号)。
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_tablesmysql> 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 tinyint UNSIGNED-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.06 sec)## id字段类型是tinyint unsigned,范围就是0~255,其长度是3;因为255的长度是3;#### 查看test1表的表结构mysql> desc test1;+-------+---------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| id | tinyint(3) unsigned | YES || NULL | |+-------+---------------------+------+-----+---------+-------+1 row in set (0.03 sec)#### 测试插入范围0~255范围整数和不在该范围内的整数mysql> insert into test1 values(-1); # 插入数值-1,错误(不在0~255范围内)ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> insert into test1 values(0); # 插入数值0,正常(在0~255范围内)Query OK, 1 row affected (0.06 sec)mysql> insert into test1 values(255); # 插入数值255,正常(在0~255范围内)Query OK, 1 row affected (0.05 sec)mysql> insert into test1 values(256); # 插入数值256,错误(不在0~255范围内)ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> select * from test1;+------+| id |+------+| 0 || 255 |+------+2 rows in set (0.00 sec)
1.3.3 SQL模式未开启严格模式
SQL_MODE未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数
#### 设置会话模式下sql_mode中不包含strit_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 tinyint unsigned-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.00 sec)## id字段类型是tinyint unsigned,范围就是0~255,其长度是3;因为255的长度是3;#### 查看test11表的表结构mysql> desc test11;+-------+---------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| id | tinyint(3) unsigned | YES || NULL | |+-------+---------------------+------+-----+---------+-------+1 row in set (0.00 sec)#### 测试插入范围0~255范围整数和不在该范围内的整数mysql> insert into test11(id) values(-1);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围内,插入没有报错(因为sql_mode中没有开启严格模式)## 插入到表中的数据不是-1,而是0mysql> insert into test11(id) values(0);Query OK, 1 row affected (0.01 sec)## 没报错,因为在范围内,插入到表中的也是0mysql> insert into test11(id) values(255);Query OK, 1 row affected (0.01 sec)## 没报错,因为在范围内,插入到表中的也是255mysql> insert into test11(id) values(256);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围内,插入没有报错(因为sql_mode中没有开启严格模式)## 插入到表中的数据不是256,而是255mysql> select * from test11;+------+| id |+------+| 0 || 0 || 255 || 255 |+------+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_tablesmysql> 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,会前导零填充,同时还会带上unsigned）mysql> CREATE TABLE IF NOT EXISTS test2(-> id tinyint zerofill-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.01 sec)## id字段类型是tinyint zerofill,范围就是0~255,其长度是3;因为255的长度是3;## 查看test2表的表结构mysql> desc test2;+-------+------------------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+------------------------------+------+-----+---------+-------+| id | tinyint(3) unsigned zerofill | YES || NULL | |+-------+------------------------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入范围0~255范围整数和不在该范围内的整数mysql> insert into test2 values(-1); # 插入数值-1,错误(不在0~255范围内)ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> insert into test2 values(0); # 插入数值0,正常(在0~255范围内)Query OK, 1 row affected (0.06 sec)mysql> insert into test2 values(255); # 插入数值255,正常(在0~255范围内)Query OK, 1 row affected (0.05 sec)mysql> insert into test2 values(256); # 插入数值256,错误(不在0~255范围内)ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> select * from test2;+------+| id |+------+| 000 || 255 |+------+2 rows in set (0.00 sec)
1.4.2 SQL模式未开启严格模式
SQL_MODE未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数
## 设置会话模式下sql_mode中不包含strit_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 tinyint zerofill-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.00 sec)## id字段类型是tinyint unsigned,范围就是0~255,其长度是3;因为255的长度是3;## 查看test22表的表结构mysql> desc test22;+-------+------------------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+------------------------------+------+-----+---------+-------+| id | tinyint(3) unsigned zerofill | YES || NULL | |+-------+------------------------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入范围0~255范围整数和不在该范围内的整数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.01 sec)## 没报错,因为在范围内,插入到表中的也是0mysql> insert into test22(id) values(255);Query OK, 1 row affected (0.01 sec)## 没报错,因为在范围内,插入到表中的也是255mysql> insert into test22(id) values(256); Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围,插入没报错（sql_mode中没有开启严格模式）## 但到表中的数据不是256,而是255mysql> select * from test22;+------+| id |+------+| 000 || 000 || 255 || 255 |+------+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_tablesmysql> 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 test3(-> id tinyint-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.06 sec)## id字段的类型为tinyint,其范围是-128至127,其显示长度是4,因为要显示符号("-")## 查看test3表的表结构mysql> desc test3;+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id | tinyint(4) | YES || NULL | |+-------+------------+------+-----+---------+-------+1 row in set (0.01 sec)## 测试插入-128~127范围的整数和不在该范围内的整数mysql> insert into test3(id) values(-129); # 插入数值-129,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> insert into test3(id) values(-128); # 插入数值-128,正确,在范围内Query OK, 1 row affected (0.00 sec)mysql> insert into test3(id) values(127); # 插入数值127,正确,在范围内Query OK, 1 row affected (0.01 sec)mysql> insert into test3(id) values(128); # 插入数值128,错误,不在范围内ERROR 1264 (2): Out of range value for column 'id' at row 1mysql> select * from test3;+------+| id |+------+| -128 || 127 |+------+2 rows in set (0.00 sec)
1.5.2 SQL模式未开启严格模式
SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数
## 设置会话模式下sql_mode中不包含strit_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 test33(-> id tinyint-> )engine=innodb character set utf8 collate utf8_general_ci;Query OK, 0 rows affected (0.00 sec)## id字段的类型为tinyint,其范围是-128至127,其显示长度是4,因为要显示符号("-")## 查看test33表的表结构mysql> desc test33;+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id | tinyint(4) | YES || NULL | |+-------+------------+------+-----+---------+-------+1 row in set (0.00 sec)## 测试插入-128~127范围的整数和不在该范围内的整数mysql> insert into test33(id) values(-129);Query OK, 1 row affected, 1 warning (0.00 sec)## 不在范围内,插入未报错,因为sql_mode中没有开启严格模式## 插入到表中的不是-129,而是-128;mysql> insert into test33(id) values(-128);Query OK, 1 row affected (0.01 sec)## 在范围内,插入不报错,插入的是多少就是多少mysql> insert into test33(id) values(127);Query OK, 1 row affected (0.00 sec)## 在范围内,插入不报错,插入的是多少就是多少mysql> insert into test33(id) values(128);Query OK, 1 row affected, 1 warning (0.01 sec)## 不在范围内,插入未报错,因为sql_mode中没有开启严格模式## 插入到表中的不是128,而是127;mysql> select * from test33;+------+| id |+------+| -128 || -128 || 127 || 127 |+------+4 rows in set (0.00 sec)
