mysql修改字段类型为smallint_MYSQL 字段类型之TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

1. tinyint: 只能存储-128到127大小的数字， 不在乎后面的定义，如tinyint(9). 需1字节存储空间。 当加有UNSIGNED后，数字可以从0增加到255大小。

dba@localhost : test 22:36:25> create table ti(id tinyint(9), no tinyint(1));

Query OK, 0 rows affected (0.03 sec)

dba@localhost : test 22:36:44> insert into ti values(129,130);

Query OK, 1 row affected, 2 warnings (0.00 sec)

dba@localhost : test 22:37:31> show warnings;

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

| Level | Code | Message |

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

| Warning | 1264 | Out of range value for column 'id' at row 1 |

| Warning | 1264 | Out of range value for column 'no' at row 1 |

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

2 rows in set (0.00 sec)

这里在插入数据时会报出警告，是因为SQL_MODE的关系。MYSQL这里默认设置为空， 允许超出范围的数据插入， 只给出一个警告。

可以查询下这个表：

dba@localhost : test 22:37:39> select * from ti;

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

| id | no |

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

| 127 | 127 |

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

1 row in set (0.00 sec)

这个时候是允许插入负数的。

dba@localhost : test 22:49:40> insert into ti values(-9,9);

Query OK, 1 row affected (0.00 sec)

dba@localhost : test 22:50:06> select * from ti;

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

| id | no |

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

| 127 | 127 |

| -9 | 9 |

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

2 rows in set (0.00 sec)

为了不让数据库产生负数的值， 我们可以修改下这个字段的类型参数：

dba@localhost : test 22:46:25> alter table ti modify no tinyint unsigned;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

dba@localhost : test 22:50:18> insert into ti values(-9,-9);

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

dba@localhost : test 22:51:20> show warnings;

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

| Level | Code | Message |

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

| Warning | 1264 | Out of range value for column 'no' at row 1 |

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

1 row in set (0.00 sec)

dba@localhost : test 22:51:27> select * from ti;

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

| id | no |

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

| 127 | 127 |

| -9 | 9 |

| -9 | 0 |

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

3 rows in set (0.00 sec)

可以看到NO字段插入-9， 直接被截成0了。

在对TIINYINT增加了UNSIGNED后， 从-128 至 127 变成了 0 至 255.

dba@localhost : test 22:51:37> insert into ti values(1,258);

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

dba@localhost : test 22:54:08> select * from ti;

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

| id | no |

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

| 127 | 127 |

| -9 | 9 |

| -9 | 0 |

| 1 | 255 |

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

4 rows in set (0.00 sec)

此类型的字段应用场景： 如果有业务字段只存储性别、状态、类型等少量值的时候，就可以使用此类型的字段。

2. smallint: 只能存储-32768到32767大小的数字， 不在乎后面的定义，如smallint(9). 需2字节存储空间。对字段加上UNSIGNED后，数字大小就可以从0增加到65535大小。

dba@localhost : test 23:00:21> create table ts(id smallint(1), no smallint(10) unsigned);

Query OK, 0 rows affected (0.01 sec)

dba@localhost : test 23:01:51> insert into ts values(-32768,32768);

Query OK, 1 row affected (0.00 sec)

dba@localhost : test 23:02:30> select * from ts;

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

| id | no |

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

| -32768 | 32768 |

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

1 row in set (0.00 sec)

从以上可以看出， 我们存储了一个带符号的值，也就是负数，长度为1，这个也超出了我们的设置。 另外， 对字段加上UNSIGNED后，数字就可以存储超过32767的值。

应用场景： 当需要的值比TINYINT要多，而又不需要超过65535的时候就可以使用这个类型。

3. mediumint: 只能存储-8388608 到 8388607大小的数字， 不在乎后面的定义，如mediumint(9). 需3字节存储空间。对字段加上UNSIGNED后，数字大小就可以从0增加到16777215大小.

dba@localhost : test 23:13:10> create table tm(id mediumint(1), no mediumint(10) unsigned);

Query OK, 0 rows affected (0.01 sec)

dba@localhost : test 23:13:21> insert into tm values(-8388608,16777216);

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

dba@localhost : test 23:14:44> select * from tm;

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

| id | no |

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

| -8388608 | 16777215 |

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

1 row in set (0.00 sec)

从以上可以看出， 字段长度为1， 而存储的值远远超过1位。字段加UNSIGNED，就可以存储0到16777215的数字。

应用场景：当需要的值比SMALLINT的值多些，而又不需要超过16777215大小的时候就可以利用这个类型。

4. int/integer: 只能存-2147483648 到 2147483647大小的数字， 不在乎后面的定义，如int(9). 需4字节存储空间。对字段加上UNSIGNED后，数字大小就可以从0增加到4294967295大小.

dba@localhost : test 23:14:52> create table tn(id int(1), no int(10) unsigned);

Query OK, 0 rows affected (0.01 sec)

dba@localhost : test 23:22:20> insert into tn values(-2147483648,4294967296);

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

dba@localhost : test 23:22:22> select * from tn;

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

| id | no |

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

| -2147483648 | 4294967295 |

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

1 row in set (0.00 sec)

从以上可以看出， 字段长度为1， 而存储的值远远超过1位。字段加UNSIGNED，就可以存储0到4294967295的数字。

应用场景：当需要的值比mediumint的值多些，而又不需要超过4294967295大小的时候就可以利用这个类型。 通常主键字段会以INT类型自增的形式。

5. bigint: 只能存-9223372036854775808 到 9223372036854775807大小的数字， 不在乎后面的定义，如bigint(9). 需8字节存储空间。对字段加上UNSIGNED后，数字大小就可以从0增加到18446744073709551615大小.

dba@localhost : test 23:22:28> create table tb(id bigint(1), no bigint(10) unsigned);

Query OK, 0 rows affected (0.01 sec)

dba@localhost : test 23:27:40> insert into tb values(-9223372036854775808,18446744073709551616);

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

dba@localhost : test 23:28:41> select * from tb;

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

| id | no |

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

| -9223372036854775808 | 18446744073709551615 |

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

1 row in set (0.00 sec)

从以上可以看出， 字段长度为1， 而存储的值远远超过1位。字段加UNSIGNED，就可以存储0到18446744073709551615的数字。

应用场景：当需要的值比int的值多些，而又不需要超过18446744073709551615大小的时候就可以利用这个类型。此类型很少用，可以存到20位数字大小。

总结：

在MYSQL里，我们要求越精短越好，因为存储的容量小了，才能从根本上减少IO的扫描，如果在此字段上建主键索引或者建辅助索引，也能有效的减少存储空间，与IO的压力。