mysql(root@localhost:test 03:19:00)>create table c ( -> id int not null, -> name char not null); Query OK, 0 rows affected (0.25 sec) mysql(root@localhost:test 03:19:34)>desc c; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | char(1) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql(root@localhost:test 03:34:53)>alter table c modify id smallint; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql(root@localhost:test 03:39:39)>desc c; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | smallint(6) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql(root@localhost:test 03:39:44)>alter table c modify id bigint; Query OK, 4 rows affected (0.23 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql(root@localhost:test 03:40:12)>desc c; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | bigint(20) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
这里我们再来看下当插入值大于数据类型的取值范围的情况：
复制代码 代码如下:
mysql(root@localhost:test 03:25:58)>insert into c values(300,'chen'); Query OK, 1 row affected, 2 warnings (0.08 sec) mysql(root@localhost:test 03:26:20)>show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'id' at row 1 | | Warning | 1265 | Data truncated for column 'name' at row 1 | +---------+------+---------------------------------------------+ 2 rows in set (0.00 sec) mysql(root@localhost:test 03:26:27)>select * from c; +------+------+ | id | name | +------+------+ | 127 | c | +------+------+ 1 row in set (0.02 sec) mysql(root@localhost:test 03:26:40)>insert into c values(320,'chen'); Query OK, 1 row affected, 2 warnings (0.05 sec) mysql(root@localhost:test 03:26:53)>select * from c; +------+------+ | id | name | +------+------+ | 127 | c | | 127 | c | +------+------+ 2 rows in set (0.00 sec)
