mysql+创建sa_MySQL 数据类型学习笔记

MySQL的数值类型有7种，下面的表显示了需要的每个整数类型的存储和范围：

类型

字节

最小值

最大值

(带符号的/无符号的)

(带符号的/无符号的)

TINYINT

1

-128

127

0

255

SMALLINT

2

-32768

32767

0

65535

MEDIUMINT

3

-8388608

8388607

0

16777215

INT

4

-2147483648

2147483647

0

4294967295

BIGINT

8

-9223372036854775808

9223372036854775807

0

18446744073709551615

数值类型存储需求：

列类型

存储需求

TINYINT

1个字节

SMALLINT

2个字节

MEDIUMINT

3个字节

INT, INTEGER

4个字节

BIGINT

8个字节

FLOAT(p)

如果0 <= p <= 24为4个字节, 如果25 <= p <= 53为8个字节

FLOAT

4个字节

DOUBLE [PRECISION], item REAL

8个字节

DECIMAL(M,D), NUMERIC(M,D)

变长；参见下面的讨论

BIT(M)

大约(M+7)/8个字节

BIT[(M)]位字段类型。M表示每个值的位数，范围为从1到64。如果M被省略， 默认为1。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]很小的整数。带符号的范围是-128到127。无符号的范围是0到255。

SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。

INT[(M)] [UNSIGNED] [ZEROFILL] 普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。

MySQL默认是有符号的,Player表其中的三个字段,类型分别是int(2),int(4),int(16),插入4条数据,第3条和第4条QuestId字段定义是显示宽度是16,实际上只显示了10个宽度,而且值都是相同的,不要感到诧异，因为int类型最多支持10位数,而且带符号的最大值是2147483647,所以int(1)与int(10)都一样,没有什么区别。

mysql> CREATE TABLE `Player` (

-> `Id` int(2) NOT NULL AUTO_INCREMENT,

-> `PlayerId` int(4) NOT NULL DEFAULT '0',

-> `QuestId` int(16) NOT NULL,

-> `FinishTime` timestamp NULL DEFAULT NULL,

-> PRIMARY KEY (`Id`)

-> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='[玩家]完成任务表';

Query OK, 0 rows affected (0.06 sec)

mysql> insert into Player values (" "," "," "," ");

Query OK, 1 row affected, 4 warnings (0.01 sec)

mysql> insert into Player values ("2","3","4","2010-05-20 23:59:59");

Query OK, 1 row affected (0.00 sec)

mysql> insert into Player values ("50","3000","4000000000","2010-05-20 23:59:59");

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from Player;

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

| Id | PlayerId | QuestId| FinishTime          |

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

| 1 |0 |          0 | 0000-00-00 00:00:00 |

| 2 |3 |          4 | 2010-05-20 23:59:59 |

| 50 |3000 | 2147483647 | 2010-05-20 23:59:59 |

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

3 rows in set (0.00 sec)

mysql> insert into Player values ("20","3000","4567890456789045","2010-05-20 23:59:59");

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from Player;

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

| Id | PlayerId | QuestId| FinishTime          |

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

| 1 |0 |          0 | 0000-00-00 00:00:00 |

| 2 |3 |          4 | 2010-05-20 23:59:59 |

| 20 |3000 | 2147483647 | 2010-05-20 23:59:59 |

| 50 |3000 | 2147483647 | 2010-05-20 23:59:59 |

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

4 rows in set (0.01 sec)

Player2表是无符号的(UNSIGNED),Player2表其中的三个字段,类型分别是int(2),int(4),int(16),插入3条数据,其中第3条数据是16位的,QuestId字段只显示10位,而且值与插入的也不相符,请不要诧异，因为int最多支持10位数,无符号的最大值是4294967295,还是一样,int(1)与int(10)都一样,没有什么区别.

mysql> CREATE TABLE `Player2` (

-> `Id` int(2) UNSIGNED NOT NULL AUTO_INCREMENT,

-> `PlayerId` int(4) UNSIGNED NOT NULL DEFAULT '0',

-> `QuestId` int(16) UNSIGNED NOT NULL,

-> `FinishTime` timestamp NULL DEFAULT NULL,

-> PRIMARY KEY (`Id`)

-> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='[玩家]完成任务表';

Query OK, 0 rows affected (0.01 sec)

mysql> insert into Player2 values (" "," "," "," ");

Query OK, 1 row affected, 4 warnings (0.00 sec)

mysql> insert into Player2 values ("2","3","4","2010-05-20 23:59:59");

Query OK, 1 row affected (0.00 sec)

mysql> insert into Player2 values ("20","3000","4567890456789045","2010-05-20 23:59:59");

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from Player2;

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

| Id | PlayerId | QuestId| FinishTime          |

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

| 1 |0 |          0 | 0000-00-00 00:00:00 |

| 2 |3 |          4 | 2010-05-20 23:59:59 |

| 20 |3000 | 4294967295 | 2010-05-20 23:59:59 |

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

3 rows in set (0.00 sec)

Player3表,个人理解:当加上ZEROFILL属性时,多出的位数用O填充上.

MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。

显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。

当结合可选扩展属性ZEROFILL使用时，默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004。请注意如果在整数列保存超过显示宽度的一个值，当MySQL为复杂联接生成临时表时会遇到问题，因为在这些情况下MySQL相信数据适合原列宽度。

所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。

浮点和定点类型也可以为UNSIGNED。同数类型，该属性防止负值保存到列中。然而，与整数类型不同的是，列值的上范围保持不变。如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性。

mysql> CREATE TABLE `Player3` (

-> `Id` int(2) zerofill NOT NULL AUTO_INCREMENT,

-> `PlayerId` int(4) zerofill NOT NULL DEFAULT '0',

-> `QuestId` int(16) zerofill NOT NULL,

-> `FinishTime` timestamp NULL DEFAULT NULL,

-> PRIMARY KEY (`Id`)

-> ) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='[玩家]完成任务表';

Query OK, 0 rows affected (0.03 sec)

mysql> insert into Player3 values (" "," "," "," ");

insert into Player3 values ("2","3","4","2010-05-20 23:59:59");

Query OK, 1 row affected, 4 warnings (0.03 sec)

mysql> insert into Player3 values ("2","3","4","2010-05-20 23:59:59");

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> insert into Player3 values ("20","3000","4567890456789045","2010-05-20 23:59:59");

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from Player3;

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

| Id | PlayerId | QuestId| FinishTime          |

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

| 01 |0000 | 0000000000000000 | 0000-00-00 00:00:00 |

| 02 |0003 | 0000000000000004 | 2010-05-20 23:59:59 |

| 20 |3000 | 0000004294967295 | 2010-05-20 23:59:59 |

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

3 rows in set (0.00 sec)

注：M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。