1。整数类型
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT或INTEGER 4字节
BIGINT 8字节
整数类型后面圆括号中的数字代表该整型字段的显示宽度，如果数字位不足就自动用空格填充，但这不会影响该字段的大小和可存储的值的范围。
UNSIGNED修饰符规定字段只保存正值；ZEROFILL修饰符规定用0来代替空格用于填补输出值，使用它的字段也不能存储负值。
-> (
-> num1 tinyint,
-> num2 tinyint(3),
-> num3 tinyint unsigned,
-> num4 tinyint(3) zerofill
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> desc int_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | tinyint(4) | YES | | NULL | |
| num2 | tinyint(3) | YES | | NULL | |
| num3 | tinyint(3) unsigned | YES | | NULL | |
| num4 | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> insert into int_test values(1,1,1,1);
Query OK, 1 row affected (0.43 sec)
mysql> insert into int_test values(-1,-1,-1,-1);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into int_test values(-1,-1,1,-1);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into int_test values(127,127,127,127);
Query OK, 1 row affected (0.59 sec)
mysql> insert into int_test values(128,128,128,128);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> insert into int_test values(127,127,128,127);
Query OK, 1 row affected (0.04 sec)
mysql> select * from int_test;
+------+------+------+------+
| num1 | num2 | num3 | num4 |
+------+------+------+------+
| 1 | 1 | 1 | 001 |
| 127 | 127 | 127 | 127 |
| 127 | 127 | 128 | 127 |
+------+------+------+------+
3 rows in set (0.03 sec)
2.浮点类型
FLOAT 4字节
DOUBLE或REAL DOUBLE PRECISION 8字节
DECIMAL或DEC NUMERIC 对DECIMAL(M,D)，如果M>D，为M+2，否则为D+2。
浮点类型后面圆括号中的两个数字分别为一个显示宽度指示器和一个小数点指示器。
UNSIGNED和ZEROFILL修饰符同样可以用于浮点类型，效果与用于整数类型时类似。
MySQL以二进制格式保存DECIMAL数据类型，用于保存必须为确切精度的值。
mysql> create table float_test
-> (
-> num1 float,
-> num2 float(5,2),
-> num3 float unsigned,
-> num4 float(5,2) zerofill,
-> num5 decimal,
-> num6 decimal(5,2)
-> );
Query OK, 0 rows affected (0.58 sec)
mysql> desc float_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | float | YES | | NULL | |
| num2 | float(5,2) | YES | | NULL | |
| num3 | float unsigned | YES | | NULL | |
| num4 | float(5,2) unsigned zerofill | YES | | NULL | |
| num5 | decimal(10,0) | YES | | NULL | |
| num6 | decimal(5,2) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
6 rows in set (0.05 sec)
mysql> insert into float_test values (123.321, 123.321, 123.321, 123.321, 123.32
1, 123.321);
Query OK, 1 row affected, 2 warnings (0.04 sec)
mysql> insert into float_test values (-123.3, -123.3, -123.3, -123.3, -123.3, -1
23.3);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, -123.3, -123.3, -12
3.3);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, 123.3, -123.3, -123
.3);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> insert into float_test values (123.456, 123.456, 123.456, 123.456, 123.45
6, 123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)
mysql> insert into float_test values (-123.456, -123.456, 123.456, 123.456, -123
.null
