数据类型基本介绍
数值类型
整形类型: tinyint,int,bigint
浮点类型: float,double
字符串类型
char 系列: char varchar
text 系列: text
blob 系列: blob
枚举类型: ENUM
集合类型: SET
时间日期型
date time datetime timestamp year
tinyint 和 int 整形测试
- mysql> create table test1(tinyint_test tinyint,int_test int);
- Query OK, 0 rows affected (0.03 sec)
- mysql> desc test1;
- +--------------+------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------------+------------+------+-----+---------+-------+
- | tinyint_test | tinyint(4) | YES | | NULL | |
- | int_test | int(11) | YES | | NULL | |
- +--------------+------------+------+-----+---------+-------+
- 2 rows in set (0.05 sec)
- mysql> insert into test1 values(111,111);
- Query OK, 1 row affected (0.01 sec)
- mysql> select * from test1;
- +--------------+----------+
- | tinyint_test | int_test |
- +--------------+----------+
- | 111 | 111 |
- +--------------+----------+
- 1 row in set (0.00 sec)
- mysql> insert into test1(tinyint_test) values(128);
- ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
- mysql> insert into test1(int_test) values(mysql> insert into test1(int_test) values(2147483647);
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into test1(int_test) values(2147483648);
- ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
整形宽度测试
- mysql> create table test2(id1 int,id2 int(8));
- mysql> desc test2;
- +-------+---------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(8) | YES | | NULL | |
- +-------+---------+------+-----+---------+-------+
- mysql> insert into test2 values(1,1);
- mysql> select * from test2;
- +------+------+
- | id1 | id2 |
- +------+------+
- | 1 | 1 |
- +------+------+
- mysql> create table test3(id1 int zerofill,id2 int(8) zerofill);
- mysql> insert into test3 values(123456789,123456789);
- mysql> select * from test3;
- +------------+-----------+
- | id1 | id2 |
- +------------+-----------+
- | 0123456789 | 123456789 |
- +------------+-----------+
总结: 整形宽度总是做填充使用, 没有实际意义
浮点数类型测试 - float
- mysql> create table test1(float_test float(5,2)); // 一共 5 位, 也就是整数 + 小数一共 5 位, 同时小数最多 2 位
- mysql> insert into test1 values(1000.123);
- ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
- mysql> insert into test1 values(999.123);
- mysql> select * from test1;
- +------------+
- | float_test |
- +------------+
- | 999.12 |
- +------------+
来源: http://www.bubuko.com/infodetail-2567327.html