Mysql 字段类型分析

mysql建表初步知识及字段类型

一、如何登录phpMyAdmin环境

　　phpMyAdmin可以对mysql数据库进行完全管理。在IE地址中输入地址，输入数据库帐号和密码，登录phpMyAdmin。


二、几个概念

1、数据库：在MySQL中，要存储数据，必须从数据库开始，因此首先要创建数据库，但由于学校的MySQL服务器对学生数据帐号有限止，学生不得创建数据库，故每个学生的帐号中已事先由信息中心为大家建立了一个名为帐号名的数据库。但我们必须了解创建数据库的语句为
CREATE DATABASE <数据库名>

2、数据表：是构成数据库的一个基本单位，在一个数据库中用户可以建立多张数据表。这是我们有权力建立的。

3、数据表结构：创建一个数据表的第一步即为创建数据表结构，在数据表结构中的内容包括：有几个字段，每个字段的名称，字段的类型，字段的长度，字段的属性。

4、字段：它是构成数据表结构的必须元素，一列数据就是一个字段。

5、字段名：用来描述字段的名称，它可以用中文、英文字母、数字等字符来描述。

6、字段类型：用来限定数据的一种方式，即每一个字段下的数据必定类型是相同的。

7、字段长度：即每一列字段中的数据位数。


三、建立第一张数据表

1、在左窗口中点击数据库名，
2、在右窗口中输入数据表的名称如number1,输入字段数6，单击执行，
3、按如下要求建立一张数据表：

字段名field	字段类型（列类型） type	属性	说明
name	Char(8)		姓名
xb	Char(2)		性别
csrq	Date		出生日期
address	Text		住址
Byxx	Char(20)		毕业学校
zip	Int(6)		邮政编码

注意其下的sql语句：

CREATE TABLE `number1` ( 
　　`name` CHAR( 8 ) NOT NULL ,
　　`xb` CHAR( 2 ) NOT NULL ,
　　`csrq` DATE NOT NULL ,
　　`address` TEXT,
　　`byxx` CHAR( 20 ) ,
　　`zip` INT( 6 ) NOT NULL 
　　);

以上命令即是建立第一张数据表的命令，注意理解。建立数据表，我们也可以直接在代码窗口中输入命令也可以建立数据表。


四、字段类型（列类型）

1、字段的大类型有三种：数值型、字符型、日期/时间型，每一种大类型中也可以分成若干个列类型。
2、每种列类型均有以下几个特性：
（1）其中可以存放什么类型的值。
（2）值要占据多少空间，以及该值是否是定长或可变长的。
（3）该类型的值是否比较和存储
（4）此类型是否可以索引


五、学习几种常用的列类型

1、数值类型

（1）INT[ (M) ]
　说明：标准大小的整数
　允许的属性：不选（可以取正负数），UNSIGNED（无符号，取值范围增大一倍），UNSIGED　ZEROFILL（在数值前自动填0），auto_increment（自动递增）
　取值范围：－2147483648 到2147483647（－231 到231－1），或者如果为UNSIGNED，则0 到4294967295（0 到232－1）
　缺省值：如果列为NULL，则为NULL；如果列为NOT FULL，则为0
　存储需求：4 字节
　同义词：INTEGER[ (M) ] 

（2）FLOAT[(M,D)]（M表示显示宽度，在D表示小数位数）
　说明：小浮点数；单精度（精度小于DOUBLE）
　允许的属性：ZEROFILL
　取值范围：最小非零值为±1.75494351E-38;最大非零值为±3.402823466E+38
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为0
　存储需求：4字节
　同义词：MySQL3.23版以前，FLOAT(4)为具有缺省M和D值的FLOAT的同义词。
　注释：在MySQL3.23以后，FLOAT(4)为真浮点类型（值存储为硬件所允许的完整精度，而不四舍五入为缺省的小数位数）。

（3）DOUBLE[(M,D)]
　说明：大浮点数；双精度（比FLOAT更精确）
　允许的属性：ZEROFILL
　取值范围：最小非零值为±2.2250738585072014E-308;最大非零值为±1。79769313-48623157E+308
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为0
　存储需求：8字节
　同义词：DOUBLEPRECISION[(M,D)]和REAL[(M,D)]为DOUBLE[(M,D)]的同义词。MySQL3.23版以前，FLOAT(8)为具有缺省M和D值的FLOAT的同义词。
　注释：在MySQL3.23以后，FLOAT(8)为真浮点类型（值存储为硬件所允许的完整精度，而不四舍五入为缺省的小数位数）。

2、串类型
　　MySQL的串类型常用来存储文本，它不但是通用的而且还能存储任何数据。可用串类型来存储最大长度可变的值，而且可以选择在处理值时是否区分大小写。

（1）CHAR(M)
　说明：0到M字节长的定长字符串。在MySQL3.23版以前，M应该为一个1到255之间的整数。而MySQL3.23版以后，M应该为一个0到255之间的整数。短于M个字符的串存储进右边补空格。长于M个字符的串存储时剪断为长度是M的串。在检索值时，去掉后跟的空格。
　允许的属性：BINARY
　允许的长度：0到M字节
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“”
　存储需求：M字节
　比较：不区分大小写（如果具有BINARY属性，则区分大小写）

（2）VARCHAR(M)
　说明：0到M字节长的可变长字符串。M应该为1到255之间的一个整数，或者自MySQL3.23后为0到255之间的一个整数。存储时后跟的空格被去掉。存储时，大于M个字符的串剪断为M个字符。
　允许的属性：BINARY
　允许的长度：0到M字节
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“”
　存储需求：值的长度，加上1字节用来记录长度
　比较：不区分大小写（如果具有BINARY属性，则区分大小写）

（3）TEXT
　说明：常规大小的TEXT值
　允许的属性：除通用属性外无其他属性
　允许的长度：0到65535（0到216－1）字节
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“”
　存储需求：值的长度加上用于记录长度的2个字节
　比较：不区分大小写

（4）ENUM(“value1”,“value2”,...)
　说明：枚举，列值可赋予值列表中的某个成员
　允许的属性：除通用属性外无其他属性
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为第一个枚举值
　存储需求：对1到255个成员的枚举1个字节，对255到65535个成员的枚举2个字节
　比较：不区分大小写（MySQL3.22.1版以前区分大小写）

（5）SET(“value1”,“value2”,...)
　说明：集合，列值可赋予值列表中的零个或多个成员
　允许的属性：除通用属性外无其他属性
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“”（空集）
　存储需求：1字节（1到8个成员的集合），2字节（9到16个成员的集合），3字节（17到24个成员的集合），4字节（25到32个成员的集合）或8字节（33到64个成员的集合）
　比较：不区分大小写（MySQL3.22.1版以前区分大小写）

3、日期和时间类型
　　MySQL提供以各种形式表示时间数据的类型。这些数据有日期和时间类型。有一种特殊的时间类型，它在记录更改时自动更新。还有一种用于存储年份的类型，在不需要完全的日期时使用。

（1）DATE
　说明：“YYYY-MM-DD”格式的日期
　允许的属性：除通用属性外无其他属性
　取值范围：“1000-01-01”到“9999-12-31”
　零值：“0000-00-00”
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“0000-00-00”
　存储需求：3字节（MySQL3.22版以前为4字节）

（2）TIME
　说明：“HH:MM:SS”格式的时间（负值为“-HH:MM:SS”）；表示占用的时间，但也可
　以作为日常时间
　允许的属性：除通用属性外无其他属性
　取值范围：“-838:59:59”到“838:59:59”
　零值：“00:00:00”
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“00:00:00”
　存储需求：3字节
　注释：虽然在非法值插入TIME列时，“00:00:00”用作零值，但它也作为一个合法的值出现在正常的取值范围内

（3）YEAR
　说明：YYYY格式表示的年份
　允许的属性：除通用属性外无其他属性
　取值范围：1900到2155
　零值：0000
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为0000
　存储需求：1字节

（4）DATETIME
　说明：以“YYYY-MM-DDhh:mm:ss”格式表示的日期和时间（两个部分都需要）
　允许的属性：除通用属性外无其他属性
　取值范围：“1000-01-0100:00:00”到“9999-12-3123:59:59”
　零值：“0000-00-0000:00:00”
　缺省值：如果列可为NULL，则为NULL；如果列为NOTNULL，则为“0000-00-00 00:00:00”
　存储需求：8字节


六、输入记录
　　单击右窗口中的插入按钮，在出现的记录输入界面下输入数据，并选择“插入新的一行”，再单击“执行”。即可连续输入多条记录，直到选择“后退到上一页”，再单击“执行”，则结束记录的输入。

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

　MySQL 字段数据类型/长度

　　1、数值类型

　　列类型 需要的存储量

　　TINYINT 1 字节

　　SMALLINT 2 个字节

　　MEDIUMINT 3 个字节

　　INT 4 个字节

　　INTEGER 4 个字节

　　BIGINT 8 个字节

　　FLOAT(X) 4 如果 X < = 24 或 8 如果 25 < = X < = 53

　　FLOAT 4 个字节

　　DOUBLE 8 个字节

　　DOUBLE PRECISION 8 个字节

　　REAL 8 个字节

　　DECIMAL(M,D) M字节(D+2 , 如果M < D)

　　NUMERIC(M,D) M字节(D+2 , 如果M < D)

　　MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。

　　表列出了各种数值类型以及它们的允许范围和占用的内存空间。

　　类型 大小 范围(有符号) 范围(无符号) 用途

　　TINYINT 1 字节 (-128，127) (0，255) 小整数值

　　SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值

　　MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值

　　INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

　　BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值

　　FLOAT 4 字节 (-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) 单精度

　　浮点数值

　　DOUBLE 8 字节 (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的值 小数值

　　INT 类型

　　在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。

　　MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

　　万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。

　　UNSIGNED(未签署) 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。

　　ZEROFILL(零填充) 修饰符规定 0(不是空格)可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。

　　FLOAT、DOUBLE 和 DECIMAL 类型

　　MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。

　　与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

　　对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。

　　DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。

　　忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。

　　UNSIGNED(未签署) 和 ZEROFILL(零填充) 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。

　　2、日期和时间类型

　　列类型 需要的存储量

　　DATE 3 个字节

　　DATETIME 8 个字节

　　TIMESTAMP 4 个字节

　　TIME 3 个字节

　　YEAR 1 字节

　　在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式

　　类型 大小

　　(字节) 范围 格式 用途

　　DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值

　　TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间

　　YEAR 1 1901/2155 YYYY 年份值

　　DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值

　　TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

　　DATE、TIME 和 TEAR 类型

　　MySQL 用 DATE 和 TEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。

　　需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

　　MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。

　　DATETIME 和 TIMESTAMP 类型

　　除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

　　如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

　　3、字符串类型

　　列类型 需要的存储量

　　CHAR(M) M字节，1 <= M <= 255

　　VARCHAR(M) L+1 字节， 在此L <= M和1 <= M <= 255

　　TINYBLOB, TINYTEXT L+1 字节， 在此L< 2 ^ 8

　　BLOB, TEXT L+2 字节， 在此L< 2 ^ 16

　　MEDIUMBLOB, MEDIUMTEXT L+3 字节， 在此L< 2 ^ 24

　　LONGBLOB, LONGTEXT L+4 字节， 在此L< 2 ^ 32

　　ENUM('value1','value2',…) 1 或 2 个字节， 取决于枚举值的数目(最大值65535)

　　SET('value1','value2',…) 1，2，3，4或8个字节， 取决于集合成员的数量(最多64个成员)

　　MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

　　类型 大小 用途

　　CHAR 0-255字节 定长字符串

　　VARCHAR 0-255字节 变长字符串

　　TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串

　　TINYTEXT 0-255字节 短文本字符串

　　BLOB 0-65 535字节 二进制形式的长文本数据

　　TEXT 0-65 535字节 长文本数据

　　MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据

　　MEDIUMTEXT 0-16 777 215字节 中等长度文本数据

　　LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据

　　LONGTEXT 0-4 294 967 295字节 极大文本数据

　　CHAR 和 VARCHAR 类型

　　CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

　　CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。

　　CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MuSQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度(增加一个额外字节来存储字符串本身的长度)来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

　　因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。

　　VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

　　TEXT 和 BLOB 类型

　　对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

　　TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。

　　3、复合类型

　　MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值;而 SET 类型允许从一个集合中取得任意多个值。

　　ENUM 类型

　　ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。

　　ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。

　　MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。

　　SET 类型

　　SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。

　　一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。

　　希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

原文出自【比特网】，转载请保留原文链接：http://soft.chinabyte.com/database/171/11878171.shtml