mysql zerofill_Mysql 数据类型之zerofill使用（一）

用过数据库的朋友都知道，要想将数据存入表中，需要将表建立多个字段，对于这些字段要有合理的类型来匹配，因为是初学Mysql，所以在此做些小的总结与记录，以便自己以后学习。

数值类型

严格(INTEGER，SMALLINT，DECIMAL，MUMERIC)

近似(FLOAT，REAL，DOUBLE，PRECISION)

扩展(TINYINT，MEDIUMINT，BIGINT)

zerofill 使用说明，例如 int(5)表示当数值宽度小于 5 位的时候在数字前面加’0’填满宽度,如果不显示指定宽度则默认为 int(11)，zerofill默认为int(10)。注:当使用zerofill 时，默认会自动加unsigned(无符号)属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0~256。

(1)连接测试数据库mydata

[mysql@suzzy ~]$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.22-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database           |

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

| information_schema |

| mydata             |

| mysql              |

| performance_schema |

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

4 rows in set (0.00 sec)

mysql> use mydata;

Reading table information for completion of table and column names

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

Database changed

(2)创建测试表T1

mysql> create table t1 (id1 int,id2 int(5));

Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;

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

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

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

| id1   | int(11) | YES  |     | NULL    |       |

| id2   | int(5)  | YES  |     | NULL    |       |

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

2 rows in set (0.01 sec)

(3)插入数据直接查询，未能看到两个字段的不同

mysql> insert into t1 values (1,1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

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

| id1  | id2  |

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

|    1 |    1 |

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

1 row in set (0.00 sec)

(4)改变id1,id2两个字段为zerofill，id1为默认长度’10’，id2为长度为’5’

mysql> alter table t1 modify id1 int zerofill;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify id2 int(5) zerofill;

Query OK, 1 row affected (0.02 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t1;

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

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

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

| id1   |int(10) unsigned zerofill | YES  |     | NULL    |       |

| id2   |int(5) unsigned zerofill | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)

(5)查询两列数据，数值前面用’0’被全位数

mysql> select * from t1;

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

| id1        | id2   |

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

| 0000000001 | 00001 |

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

1 row in set (0.00 sec)

(6)插入超出给定范围的值，可以正常存入

mysql> insert into t1 values (1,1234567);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

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

| id1        | id2     |

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

| 0000000001 |   00001 |

| 0000000001 | 1234567 |

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

2 rows in set (0.00 sec)