这篇文章是和MySQL数据类型（一） 联系一起的。如有不太明白时请参考MySQL数据类型（一） 。
本人已经建好data表，此改写一下f的数据类型。
一 mysql> alter table data change f f tinyint unsigned;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from data;
+------+-------+
| f | b |
+------+-------+
| 12 | 13 |
| 14 | 19 |
| 127 | 32767 |
| 127 | 32767 |
+------+-------+
4 rows in set (0.01 sec)
mysql> insert into data values(255,65535),(256,65536);
Query OK, 2 rows affected, 3 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 3
一看有警告，便知出现问题。于是show warnings查看一下：
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'f' at row 2 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 2 |
+---------+------+-----------------------------------------------------+
3 rows in set (0.01 sec)
显然256出界，出界MySQL会用本类型最接近他的值替代他。对于任何非法数字值MySQL会用会用0取代，字符型null替代。
如下：
mysql> insert into data(f) values('womshoa');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'womshoa' for column 'f' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from data ;
+------+-------+
| f | b |
+------+-------+
| 12 | 13 |
| 14 | 19 |
| 127 | 32767 |
| 127 | 32767 |
| 255 | 32767 |
| 255 | 32767 |
| 0 | NULL |
+------+-------+
7 rows in set (0.01 sec)
最后插入f的为0，b由于未指定是null，代表不确定性。
再一个就是关于数据类型的宽度问题。我们通常指定如int（10），char（10），varchar（10）。对于指定宽度的，如果数据没达到指定宽度则默认用空格取代。对于数值型，我们可用zerofill来用0填充，注意此性质不能让此列插入负数，如插入为0，如下：
mysql> alter table data change f f int(4) zerofill;
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from data;
+------+-------+
| f | b |
+------+-------+
| 0012 | 13 |
| 0014 | 19 |
| 0127 | 32767 |
| 0127 | 32767 |
| 0255 | 32767 |
| 0255 | 32767 |
| 0000 | NULL |
+------+-------+
7 rows in set (0.00 sec）
二 对于float（m，d）， decimal（m，d），m代表宽度，d代表小数位数，decimal（m，d）适用精度要求较高的。
mysql> alter table data change f f float(10,4) ;
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> alter table data change b b decimal(14,2) ;
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> insert into data values(-18,-5.4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from data where b=-5.4;
+----------+-------+
| f | b |
+----------+-------+
| -10.0000 | -5.40 |
| -18.0000 | -5.40 |
+----------+-------+
2 rows in set (0.00 sec)
三 对于timestamp等时间的类型，我们要注意。timestamp的字段，我们插入为空，则会插入当当前时间戳；对于date，MySQL把00-69会转换为2000-2069，把70-99转换为1970-1999.对于非法值都会插入0组成的该数据类型。
mysql> create table mytime(id date);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into mytime values(20081229),('2008-12-31'),(2008-12-32);（红体字非法）
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'id' at row 3 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mytime;;
+------------+
| id |
+------------+
| 2008-12-29 |
| 2008-12-31 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)
mysql> insert into mytime values('8-12-32');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytime;
+------------+
| id |
+------------+
| 2008-12-29 |
| 2008-12-31 |
| 0000-00-00 | |
| 0000-00-00 |
+------------+
5 rows in set (0.00 sec)
mysql> insert into mytime values('2008-2-31');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytime;
+------------+
| id |
+------------+
| 2008-12-29 |
| 2008-12-31 |
| 0000-00-00 |
| 0000-00-00 | |
| 0000-00-00 |
+------------+
6 rows in set (0.01 sec)
mysql> delete from mytime;
Query OK, 6 rows affected (0.02 sec)删除一下，改一下作别的
mysql> alter table mytime change id id year;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into mytime values(0),(69),(70),(2155),（80），(2156),(71); --注意超过范围的用0替代；
Query OK, 7 rows affected, 1 warning (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'id' at row 6 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mytime;
+------+
| id |
+------+
| 0000 |
| 2069 |
| 1970 |
| 2155 |
| 1980 |
| 0000 |
| 1971 |
+------+
7 rows in set (0.00 sec)
mysql> drop table mytime ;
Query OK, 0 rows affected (0.01 sec)
mysql> create table mytime(f1 datetime, f2 timestamp) ;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into mytime values(now(),null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytime;
+---------------------+---------------------+
| f1 | f2 |
+---------------------+---------------------+
| 2008-12-18 17:32:48 | 2008-12-18 17:32:48 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> insert into mytime values('1234-12-12 11:23:45',19721232112324);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from mytime;
+---------------------+---------------------+
| f1 | f2 |
+---------------------+---------------------+
| 2008-12-18 17:32:48 | 2008-12-18 17:32:48 |
| 1234-12-12 11:23:45 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.01 sec)
now（）是一系统函数，返回类似datetime的值，如下：
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2008-12-18 17:38:17 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into mytime values('2034-12-12 11:23:45',null );
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytime;
+---------------------+---------------------+
| f1 | f2 |
+---------------------+---------------------+
| 2008-12-18 17:32:48 | 2008-12-18 17:32:48 |
| 1234-12-12 11:23:45 | 0000-00-00 00:00:00 |
| 2034-12-12 11:23:45 | 2008-12-18 17:39:37 |
+---------------------+---------------------+
3 rows in set (0.00 sec)
这个情景就到这，如有疑问，请留言。
