MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型.
数值类型(整型)
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。类型大小范围(有符号)范围(无符号)用途
TINYINT1 字节(-128,127)(0,255)小整数值
SMALLINT2 字节(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 字节(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 字节(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度
浮点数值
DOUBLE8 字节(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度
浮点数值
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值
测试整型(tinyint)
mysql> create table t2 (
-> num tinyint
-> )
-> ;
Query OK, 0 rows affected (0.01 sec)
为什么不是0-255而是-128-127呢?
mysql> insert into t2 values (255);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (-128);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values (127);
Query OK, 1 row affected (0.01 sec)
注释:插入-128-127就可以,为什么0-255就失败了呢?
数据类型的属性MySQL关键字含义
NULL数据列可包含NULL值
NOT NULL数据列不允许包含NULL值
DEFAULT默认值
PRIMARY KEY主键
AUTO_INCREMENT自动递增,适用于整数类型
UNSIGNED无符号
CHARACTER SET name指定一个字符集
数据类型(unsignde)
unsgned 无符号,列的值从0开始,不为负.
在t2表中增加anum列,使用属性unsigned参数.
mysql> alter table t2 add anum tinyint unsigned;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
在t2表anum列插入数字255,测试成功.
mysql> insert into t2 (anum) values (255);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+------+------+
| num | unum | anum |
+------+------+------+
| -128 | NULL | NULL |
| 127 | NULL | NULL |
| NULL | NULL | 255 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> insert into t2 (anum) values (3);
Query OK, 1 row affected (0.01 sec)
插入负数则会报错.
mysql> insert into t2 (anum) values (-23);
ERROR 1264 (22003): Out of range value for column 'anum' at row 1
整数属性(zerofill和M)
那这个int[M]中M是什么意义喃,在定义数值型数据类型的时候,可以在关键字括号内指定整数值(如:int(M),M的最大值为255)显示最大显示宽度,显示宽度M与数据所占用空间,数值的范围无关。 如果在定义字段的时候指定zerofill,那么当数值的显示宽度小于指定的列宽度时候,则默认补充的空格用0代替。
zerofill:适合用于学号,编码,固定宽度的数字,可以用0填充至固定宽度.
学号-->1--->0001
学号-->123-->0123
在t2表中创建一个新的列:
mysql> alter table t2 add sn tinyint(5) zerofill;
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t2 values (4,null,123,2)
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+------+------+-------+
| num | unum | anum | sn |
+------+------+------+-------+
| -128 | NULL | NULL | NULL |
| 127 | NULL | NULL | NULL |
| NULL | NULL | 255 | NULL |
| NULL | NULL | 3 | NULL |
| 4 | NULL | 123 | 00002 |
+------+------+------+-------+
注意:插入的数字2变为00002.
使用zerofill属性,默认属性unsigned,数值从正数开始不包含负数.
mysql> desc t2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num | tinyint(4) | YES | | NULL | |
| unum | tinyint(4) | YES | | NULL | |
| anum | tinyint(3) unsigned | YES | | NULL | |
| sn |tinyint(5) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
注意:m要和zerofill一起使用,很多人有个误区,就是我想使用0-9的数字,是不是直接使用M就可以了?
mysql> alter table t2 add sx tinyint(1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t2 (sx) values (11),(111);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+------+------+------+-------+------+
| num | unum | anum | sn | sx |
+------+------+------+-------+------+
| -128 | NULL | NULL | NULL | NULL |
| 127 | NULL | NULL | NULL | NULL |
| NULL | NULL | 255 | NULL | NULL |
| NULL | NULL | 3 | NULL | NULL |
| 4 | NULL | 123 | 00002 | NULL |
| NULL | NULL | NULL | NULL | 11 |
| NULL | NULL | NULL | NULL | 111 |
+------+------+------+-------+------+
7 rows in set (0.00 sec)
注释:如果只是单独的使用M参数,tinyint(M)将没有任何意义。zerfill和M配合使用,用0填充,填充至多宽。