整数数据类型smallint

整数数据类型smallint

⽂章⽬录

1.1 smallint说明

数据类型显⽰长度占⽤字节有符号⽆符号

加上unsigned/zerofill：5

不加unsigned/zerofill：6

smallint2(16bit)-32768⾄327670⾄65535

##

格式

id smallint(M) [unsigned] [zerofill]

字段名 数据类型(显⽰长度,建表时不指定) ⽆符号 ⽆符号且前导零填充

## smallint

关于最⼤数值是怎样得来的

smallint占⽤2字节,2字节占⽤16位，经过换算(2的16次⽅减1)就是65535

## unsigned

关于加上后的说明

加上unsigned后就是⽆符号(范围是0~65535的整数,因为是整数,不会有符号"-",所以就是⽆符号)

## zerofill

关于加上后的说明

zerofill会把unsigned属性也给带上,这样就是⽆符号（范围是0~65535，显⽰长度就是5），同时还会进

⾏前导零填充（没有达到显⽰长度的数值，例如：你插⼊1，显⽰的是00001）。

## unsignedzerofill

不加和的说明

字段后⾯不加上这两个属性中的任何⼀个,就表⽰是有符号(范围是-32768~32767,因为有符号"-",所有是有符号)。

1.2 测试环境说明

##

数据库版本和默认的存储引擎

mysql> lect @@version,@@default_storage_engine;

+------------+--------------------------+

| @@version | @@default_storage_engine |

+------------+--------------------------+

| 5.7.28-log | InnoDB |

+------------+--------------------------+

1 row in t (0.00 c)

## chenliang

创建库

mysql> create databa if not exists chenliang;

Query OK, 1 row affected (0.03 c)

mysql> show databas like "chenliang";

+----------------------+

| Databa (chenliang) |

+----------------------+

| chenliang |

+----------------------+

1 row in t (0.03 c)

## chenliang

进⼊库

mysql> u chenliang;

Databa changed

mysql> lect databa();

+------------+

| databa() |

+------------+

| chenliang |

+------------+

1 row in t (0.01 c)

##

查看事务是否⾃动提交

mysql> lect @@mmit;

+---------------------+

| @@mmit |

+---------------------+

| 1 |

+---------------------+

1 row in t (0.00 c)

1.3 加unsigned属性

## sql_modestrict_trans_tables

设置会话模式下中包含变量

mysql> t ssion sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREA

TE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 c)

mysql> lect @@sql_modeG

*************************** 1. row ***************************

@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_E

NGINE_SUBSTITUTION

1 row in t (0.00 c)

## test1UNSIGNED

创建测试表（这⾥指定了，也就是⽆符号）

mysql> CREATE TABLE IF NOT EXISTS test1(

-> id smallint UNSIGNED

-> )engine=innodb character t utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.03 c)

## test1idunsigned,id0~65535

表的字段指定了参数那么字段的范围就是

## smallint(5),655355

显⽰长度为因为的长度是

## test1

查看表的表结构

mysql> desc test1;

+-------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | YES | | NULL | |

+-------+----------------------+------+-----+---------+-------+

1 row in t (0.00 c)

## 0~65535

测试插⼊范围的整数和不在该范围内的整数

mysql> inrt into test1(id) values(-1); # -1,,

插⼊数值报错不在范围内

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> inrt into test1(id) values(0); # 0,,

插⼊数值正确在范围内

Query OK, 1 row affected (0.00 c)

mysql> inrt into test1(id) values(65535); # 65535,,

插⼊数值正确在范围内

Query OK, 1 row affected (0.01 c)

mysql> inrt into test1(id) values(65536); # 65536,,

插⼊数值报错不在范围内

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> lect * from test1;

+-------+

| id |

+-------+

| 0 |

| 65535 |

+-------+

2 rows in t (0.00 c)

1.3.2 SQL模式未开启严格模式

SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数

## sql_mode,strict_trans_tables

设置会话模式下中不开启严格模式即不包含变量

mysql> t ssion sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SU

BSTITUTION";

Query OK, 0 rows affected, 1 warning (0.00 c)

mysql> lect @@sql_modeG

*************************** 1. row ***************************

@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in t (0.00 c)

## test11(unsigned,)

创建表这⾥加了参数哈也就是⽆符号

mysql> create table if not exists test11(

-> id smallint unsigned

-> )engine=innodb character t utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.01 c)

## test11idunsigned,id0~65535

表的字段指定了参数那么字段的范围就是

## smallint(5),655355

显⽰长度为因为的长度是

## test11

查看表的表结构

mysql> desc test11;

+-------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | YES | | NULL | |

+-------+----------------------+------+-----+---------+-------+

1 row in t (0.00 c)

## 0~65535

测试插⼊范围的整数和不在该范围内的整数

mysql> inrt into test11(id) values(-1);

Query OK, 1 row affected, 1 warning (0.01 c)

## ,(sql_mode)

不在范围内插⼊未报错因为中没有开启严格模式

## -1,0

插⼊到表中的数据不是⽽是

mysql> inrt into test11(id) values(0);

Query OK, 1 row affected (0.00 c)

## ,,

在范围内插⼊未报错插⼊的是多少就是多少

mysql> inrt into test11(id) values(65535);

Query OK, 1 row affected (0.00 c)

## ,,

在范围内插⼊未报错插⼊的是多少就是多少

mysql> inrt into test11(id) values(65536);

Query OK, 1 row affected, 1 warning (0.00 c)

## ,(sql_mode)

不在范围内插⼊未报错因为中没有开启严格模式

## 65536,65535

插⼊到表中的数据不是⽽是

mysql> lect * from test11;

+-------+

| id |

+-------+

| 0 |

| 0 |

| 65535 |

| 65535 |

+-------+

4 rows in t (0.00 c)

1.4 加zerofill属性

1.4.1 SQL模式开启严格模式

SQL_MODE中开启了严格模式,即SQL_MODE参数中包含STRICT_TRANS_TABLES参数

## sql_modestrict_trans_tables

设置会话模式下中包含变量

mysql> t ssion sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREA

TE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 c)

mysql> lect @@sql_modeG

*************************** 1. row ***************************

@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_E

NGINE_SUBSTITUTION

1 row in t (0.00 c)

## test2zerofill,

创建表，（这⾥指定了也就是前导零填充）

mysql> create table if not exists test2(

-> id smallint zerofill

-> )engine=innodb character t utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.12 c)

## idzerofill,unsigned,id0~65535;

字段指定了参数它会把参数也带上那么字段的范围

## smallint(5),655355;

显⽰长度是因为的长度是

## test2

查看表的表结构

mysql> desc test2;

+-------+-------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------------------------+------+-----+---------+-------+

| id | smallint(5) unsigned zerofill | YES | | NULL | |

+-------+-------------------------------+------+-----+---------+-------+

1 row in t (0.00 c)

## 0~65535

测试插⼊范围的整数和不在该范围内的整数

mysql> inrt into test2(id) values(-1); # -1,,

插⼊数值报错不在范围内

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> inrt into test2(id) values(0); # 0,,

插⼊数值正确在范围内

Query OK, 1 row affected (0.00 c)

mysql> inrt into test2(id) values(65535); # 65535,,

插⼊数值正确在范围内

Query OK, 1 row affected (0.01 c)

mysql> inrt into test2(id) values(65536); # 65536,,

插⼊数值报错不在范围内

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> lect * from test2;

+-------+

| id |

+-------+

| 00000 |

| 65535 |

+-------+

2 rows in t (0.00 c)

1.4.2 SQL模式未开启严格模式

SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数

## sql_mode,strict_trans_tables

设置会话模式下中不开启严格模式即不包含变量

mysql> t ssion sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SU

BSTITUTION";

Query OK, 0 rows affected, 1 warning (0.00 c)

mysql> lect @@sql_modeG

*************************** 1. row ***************************

@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in t (0.00 c)

## test22(zerofill,,unsigned)

创建表这⾥加上参数前导零填充同时也会把参数也带上

mysql> create table if not exists test22(

-> id smallint zerofill

-> )engine=innodb character t utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.01 c)

## idzerofill,unsigned,id0~65535;

字段指定了参数它会把参数也带上那么字段的范围

## smallint(5),655355;

显⽰长度是因为的长度是

## test22

查看表的表结构

mysql> desc test22;

+-------+-------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------------------------+------+-----+---------+-------+

| id | smallint(5) unsigned zerofill | YES | | NULL | |

+-------+-------------------------------+------+-----+---------+-------+

1 row in t (0.01 c)

## 0~65535

测试插⼊范围的整数和不在该范围内的整数

mysql> inrt into test22(id) values(-1);

Query OK, 1 row affected, 1 warning (0.00 c)

## ,sql_mode

不在范围内插⼊未报错（因为中未开启严格模式）

## -1,0,,zerofill

插⼊到表中的数据不是⽽是但显⽰的时候会前导零填充因为有参数

mysql> inrt into test22(id) values(0);

Query OK, 1 row affected (0.01 c)

## ,,,

在范围内插⼊未报错插⼊是什么就是什么

## zerofill,

但因为有参数显⽰时就会前导零填充

mysql> inrt into test22(id) values(65535);

Query OK, 1 row affected (0.00 c)

## ,,

在范围内插⼊未报错插⼊是什么就是什么

mysql> inrt into test22(id) values(65536);

Query OK, 1 row affected, 1 warning (0.00 c)

## ,sql_mode

不在范围内插⼊未报错（因为中未开启严格模式）

## 65536,65535

插⼊到表中的数据不是⽽是

mysql> lect * from test22;

+-------+

| id |

+-------+

| 00000 |

| 00000 |

| 65535 |

| 65535 |

+-------+

4 rows in t (0.00 c)

1.5 不加unsigned和zerofill属性

1.5.1 SQL模式开启严格模式

SQL_MODE中开启了严格模式,即SQL_MODE参数中包含STRICT_TRANS_TABLES参数

## sql_modestrict_trans_tables

设置会话模式下中包含变量

mysql> t ssion sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREA

TE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 c)

mysql> lect @@sql_modeG

*************************** 1. row ***************************

@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_E

NGINE_SUBSTITUTION

1 row in t (0.00 c)

## test3unsignedzerofill

创建表（不加和）

mysql> create table if not exists test3(

-> id smallint

-> )engine=innodb character t utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.08 c)

## test3idunsignedzerofill,id-32768~32767

表的字段没指定和参数那么字段范围就是

## ("-"),smallint(6)

因为要显⽰符号可以显⽰长度是

## test3

查看表的表结构

mysql> desc test3;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | smallint(6) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

1 row in t (0.01 c)

## -32768~32767

测试插⼊范围内的整数和不在该范围内的整数

mysql> inrt into test3(id) values(-32769); #-32769,,

插⼊数值错误不在范围内

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> inrt into test3(id) values(-32768); #-32768,,

插⼊数值正确在范围内

Query OK, 1 row affected (0.01 c)

mysql> inrt into test3(id) values(32767); #32767,,

插⼊数值正确在范围内

Query OK, 1 row affected (0.00 c)

mysql> inrt into test3(id) values(32768); #32768,,

插⼊数值错误不在范围内

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> lect * from test3;

+--------+

| id |

+--------+

| -32768 |

| 32767 |

+--------+

2 rows in t (0.00 c)

1.5.2 SQL模式未开启严格模式

SQL_MODE中未开启严格模式,即SQL_MODE参数中不包含STRICT_TRANS_TABLES参数

## sql_mode,strict_trans_tables

设置会话模式下中不开启严格模式即不包含变量

mysql> t ssion sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SU

BSTITUTION";

Query OK, 0 rows affected, 1 warning (0.00 c)

mysql> lect @@sql_modeG

*************************** 1. row ***************************

@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in t (0.00 c)

## test33(zerofillunsigned)

创建表不加和参数

mysql> create table if not exists test33(

-> id smallint

-> )engine=innodb character t utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.00 c)

## test33idunsignedzerofill,id-32768~32767

表的字段没指定和参数那么字段范围就是

## ("-"),smallint(6)

因为要显⽰符号可以显⽰长度是

## test33

查看表的表结构

mysql> desc test33;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | smallint(6) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

1 row in t (0.00 c)

## -32768~32767

测试插⼊范围内的整数和不在该范围内的整数

mysql> inrt into test33(id) values(-32769);

Query OK, 1 row affected, 1 warning (0.00 c)

## ,(sql_mode)

不在范围内插⼊未报错因为中没有开启严格格式

## -32769,-32768

插⼊的数据不是⽽是

mysql> inrt into test33(id) values(-32768);

Query OK, 1 row affected (0.01 c)

## ,,

在范围内插⼊不会报错插⼊的是什么就是什么

mysql> inrt into test33(id) values(32767);

Query OK, 1 row affected (0.00 c)

## ,,

在范围内插⼊不会报错插⼊的是什么就是什么

mysql> inrt into test33(id) values(32768);

Query OK, 1 row affected, 1 warning (0.00 c)

## ,(sql_mode)

不在范围内插⼊未报错因为中没有开启严格格式

## 32768,32767

插⼊的数据不是⽽是

mysql> lect * from test33;

+--------+

| id |

+--------+

| -32768 |

| -32768 |

| 32767 |

| 32767 |

+--------+

4 rows in t (0.00 c)