mysql 整数类型_MySQL 数据类型之整数类型

mysql>use course;

Reading table informationforcompletion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql>create table temp(id tinyint);

Query OK,0 rows affected (0.20sec)

mysql> insert into temp values(-128);

Query OK,1 row affected (0.02sec)

mysql> insert into temp values(127);

Query OK,1 row affected (0.06sec)

mysql> select * fromtemp;+------+

| id |

+------+

| -128 |

| 127 |

+------+

2 rows in set (0.00sec)

mysql> insert into temp values(128);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into temp values(0);

Query OK,1 row affected (0.11sec)

mysql> insert into temp values(-129);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into temp values(128);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> select * fromtemp;+------+

| id |

+------+

| -128 |

| 127 |

| 0 |

+------+

3 rows in set (0.00sec)

mysql>alter table temp modify id smallint;

Query OK,3 rows affected (0.33sec)

Records:3 Duplicates: 0 Warnings: 0mysql> select * fromtemp;+------+

| id |

+------+

| -128 |

| 127 |

| 0 |

+------+

3 rows in set (0.00sec)

mysql>desc temp;+-------+-------------+------+-----+---------+-------+

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

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

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

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

1 row in set (0.00sec)

mysql> insert into temp values(129);

Query OK,1 row affected (0.07sec)

mysql> select * fromtemp;+------+

| id |

+------+

| -128 |

| 127 |

| 0 |

| 129 |

+------+

4 rows in set (0.00sec)

mysql>drop table temp;

Query OK,0 rows affected (0.18sec)

mysql>create table temp(id tinyint unsigned);

Query OK,0 rows affected (0.06sec)

mysql>desc temp;+-------+---------------------+------+-----+---------+-------+

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

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

| id | tinyint(3) unsigned | YES | | NULL | |

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

1 row in set (0.01sec)

mysql> insert into temp values(-128);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into temp values(-1);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into temp values(0);

Query OK,1 row affected (0.09sec)

mysql> insert into temp values(127);

Query OK,1 row affected (0.13sec)

mysql> insert into temp values(128);

Query OK,1 row affected (0.01sec)

mysql> insert into temp values(256);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql> insert into temp values(250);

Query OK,1 row affected (0.03sec)

mysql>mysql>mysql>desc teacher;+---------+-------------+------+-----+---------+----------------+

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(64) | YES | | NULL | |

| dept_id | int(11) | NO | MUL | NULL | |

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

3 rows in set (0.01sec)

mysql>drop table temp;

Query OK,0 rows affected (0.13sec)

mysql> create table temp(id int,name varchar(5));

Query OK,0 rows affected (0.13sec)

mysql> insert into temp values(1,'aaaaa');

Query OK,1 row affected (0.10sec)

mysql> insert into temp values(1,'aaaaaa');

ERROR1406 (22001): Data too long for column 'name' at row 1mysql>drop table temp;

Query OK,0 rows affected (0.04sec)

mysql> create table temp(id int(2),id2 int(10));

Query OK,0 rows affected (0.07sec)

mysql> insert into temp values(1000000,1000000);

Query OK,1 row affected (0.09sec)

mysql> select * fromtemp;+---------+---------+

| id | id2 |

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

| 1000000 | 1000000 |

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

1 row in set (0.00sec)

mysql> alter table temp modify id int(10) zerofill;

Query OK,1 row affected (0.12sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromtemp;+------------+---------+

| id | id2 |

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

| 0001000000 | 1000000 |

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

1 row in set (0.00 sec)