1、MySQL常用数据类型和建库策略1. MYSQL 5数据类型,长度范围1.1数值类型MySQL支持所有标准SQL数 值数据类型.这些类型包括严格数值数据类型(INTEGER,SMALLINT,DECIMAL和NUMERIC),以及近似数值数据类型 (FLOAT,REAL和DOUBLE PRECISION).关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词.BIT 数据类型保存位字段值,并且支持MyISAM,MEMORY,InnoDB和BDB表.作为SQL标准的扩展,MySQL也支持整数类型 TINYINT,MEDIUMINT和BIGINT.下面的表显示了需要的每个整数
2、类型的存储和范围.MySQL还支持选择在该类型关键字 后面的括号内指定整数值的显示宽度(例如,INT(4).该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度.显示宽 度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示.当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替.例如,对于声明为INT(5) ZEROFILL的列,值4检索为00004.请注意如果在整数列保存超过显示宽度的一个值,当MySQL为复杂联接生成临时表时会遇到问题,因为在这些 情况下MySQL相信数据适合原列宽度.所有整数类型可以有一个可选(非标准)属性UNSIGNED.当你
3、想要在列内只允许非负数和该列需 要较大的上限数值范围时可以使用无符号值.浮点和定点类型也可以为UNSIGNED.同数类型,该属性防止负值保存到列中.然而,与整数类 型不同的是,列值的上范围保持不变.如果为一个数值列指定ZEROFILL,MySQL自动为该列添加UNSIGNED属性.对 于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节.FLOAT类型用于表示近似数值数据类型.SQL标准允许在关 键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围).MySQL还支持可选的只用于确定存储大小的精度规定.0到23的精度对应FLOAT 列的4字节单精度.24到53的精度对
4、应DOUBLE列的8字节双精度.MySQL允许使用非标准语法:FLOAT(M,D)或 REAL(M,D)或DOUBLE PRECISION(M,D).这里,(M,D)表示该值一共显示M位整数,其中D位位于小数点后面.例如,定义为FLOAT(7,4)的一个列可以 显示为-,因此如果在FLOAT(7,4)列内插入999.00009,近似结果是999.0001.MySQL 将DOUBLE视为DOUBLE PRECISION(非标准扩展)的同义词.MySQL还将REAL视为DOUBLE PRECISION(非标准扩展)的同义词,除非SQL服务器模式包括REAL_AS_FLOAT选项.为了保证最大可能的
5、可移植性,需要 使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION,不规定精度或位数.DECIMAL和NUMERIC类 型在MySQL中视为相同的类型.它们用于保存必须为确切精度的值,例如货币数据.当声明该类型的列时,可以(并且通常要)指定精度和标度;例如:salary DECIMAL(5,2)在该例子中,5是精度,2是标度.精度表示保存值的主要位数,标度表示小数点后面可以保存的位数.在MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC值.标准SQL要求salary列能够用5位整数位和两位小数保存任何值.因 此,在这种情况下可以保存在salary列的
6、值的范围是从-999.99到999.99.在标准SQL中,语法DECIMAL(M)等价于 DECIMAL(M,0).同样,语法DECIMAL等价于DECIMAL(M,0),可以通过计算确定M的值.在MySQL 5.1中支持DECIMAL和NUMERIC数据类型的变量形式.M默认值是10.DECIMAL或NUMERIC的最大位数是65,但具 体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束.如果此类列分配的值小数点后面的位数超过指定的标度允许的范围,值被转换为该 标度.(具体操作与操作系统有关,但一般结果均被截取到允许的位数).BIT数据类型可用来保存位字段值.BIT(M)
7、类型允许存储M位 值.M范围为1到64.要指定位值,可以使用bvalue符.value是一个用0和1编写的二进制值.例如,b111和 b0分别表示7和128.参见,位字段值.如果为BIT(M)列分配的值的长度小于M位,在值的左边 用0填充.例如,为BIT(6)列分配一个值b101,其效果与分配b000101相同.当要在一个数值列内保存一个超出该列允许 范围的值时,MySQL的操作取决于此时有效的SQL模式.如果模式未设置,MySQL将值裁剪到范围的相应端点,并保存裁减好的值.但是,如果模式设置 为traditional(严格模式),超出范围的值将被拒绝并提示错误,并且根据SQL标准插入会失败.
8、参见,SQL服务器模式.如 果INT列是UNSIGNED,列范围的大小相同,但其端点会变为到0和95.如果你试图保存-99和 99,以非严格模式保存到列中的值是0和96.如果在浮点或定点列中分配的值超过指定(或默认)精度和标 度规定的范围,MySQL以非严格模式保存表示范围相应端点的值.当MySQL没有工作在严格模式时,对于ALTER TABLE,LOAD DATA INFILE,UPDATE和多行INSERT语句,由于裁剪发生的转换将报告为警告.当MySQL工作在严格模式时,这些语句将失败,并且部分或全部值 不会插入或更改,取决于是否表为事务表和其它因素.TypeBytesMinimum V
9、alueMaximum Value(Signed/Unsigned)(Signed/Unsigned)TINYINT1-1281270255SMALLINT2-3276832767065535MEDIUMINT3-838860883886070INT4-4847095BIGINT8-80880700615这 里将数字类型按照分类方法分为三类:整数类,小数类和数字类.我所谓的数字类,就是指 DECIMAL 和 NUMERIC,它们是同一种类型.它严格的说不是一种数字类型,因为他们实际上是将数字以字符串形式保存的;他的值的每一位 (包括小数点) 占一个字节的存储空间,因此这种类型耗费空间比较大.但
10、是它的一个突出的优点是小数的位数固定,在运算中不会失真,所以比较适合用于价格,金 额这样对精度要求不高但准确度要求非常高的字段.小数类,即浮点数类型,根据精度的不同,有 FLOAT 和 DOUBLE 两种.它们的优势是精确度,FLOAT 可以表示绝对值非常小,小到约 1.17E-38 (0.000.0117,小数点后面有 37 个零) 的小数,而 DOUBLE 更是可以表示绝对值小到约 2.22E-308 (0.000.0222,小数点后面有 307 个零) 的小数.FLOAT 类型和 DOUBLE 类型占用存储空间分别是 4 字节和 8 字节.如果需要用到小数的字段,精度要求不高的,当然用
11、FLOAT 了.可是说句实在话,我们民用的数据,哪有要求精度那么高的呢?这两种类型至今我没有用过我还没有遇到适合于使用它们的事例.用 的最多的,最值得精打细算的,是整数类型.从只占一个字节存储空间的 TINYINT 到占 8 个字节的 BIGINT,挑选一个够用并且占用存储空间最小的类型是设计数据库时应该考虑的.TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT 占用存储空间分别为 1 字节,2 字节,3 字节,4 字节和 8 字节,就无符号的整数而言,这些类型能表示的最大整数分别为 255,65535,95 和 0615.如果用来保存用户的年龄 (举例来说,数据库
12、中保存年龄是不可取的),用 TINYINT 就够了;九城的纵横里,各项技能值,用 SMALLINT 也够了;如果要用作一个肯定不会超过 行的表的 AUTO_INCREMENT 的 IDENTIFY 字段,当然用 MEDIUMINT 不用 INT,试想,每行节约一个字节, 行可以节约 10 兆多.1.2日期和时 间类型表示时间值的DATE和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR.每个时间类型有一 个有效值范围和一个零值,当指定不合法的MySQL不能表示的值时使用零值.TIMESTAMP类型有专有的自动更新特性,将在后面描述.如 果试图插入一个不合法的日期,M
13、ySQL将给出警告或错误.可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期,例如1999-11-31.当你想要保存一个可能错误的用户已经在数据库中指定(例如,以web形式)用于 将来处理的值时很有用.在这种模式下,MySQL只验证月范围为从0到12,日范围为从0到31.这些范围可以包括零,因为MySQL允许在DATE或 DATETIME列保存日/月和日是零的日期.这在应用程序需要保存一个你不知道确切日期的生日时非常有用.在这种情况下,只需要将日期保存为 1999-00-00或1999-01-00.如果保存此类日期,DATE_SUB()或DATE_ADD等需要完
14、整日期的函数不会得到正确的结 果.(如果你不想在日期中出现零,可以使用NO_ZERO_IN_DATE SQL模式).MySQL还允许将0000-00-00保 存为伪日期(如果不使用NO_ZERO_DATE SQL模式).这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小).将 sql_mode系统变量设置为相应模式值,可以更确切你想让MySQL支持哪种日期.参见,SQL服务器模式.当使用日 期和时间类型时应记住以下几点: MySQL以标准输出格式检索给定日期或时间类型的值,但它尽力解释你指定的各种输入值格式(例如,当你指定一个分配给或与日期或时间类型进行比较的值 时).只支持下
15、面章节中描述的格式.期望你能提供有效值.如果你使用其它格式的值会发生意想不到的结果. 包含两位年值的日期会令人模糊,因为世纪不知道.MySQL使用以下规则解释两位年值:o 70-99范围的年值转换为1970-1999.o 00-69范围的年值转换为2000-2069. 尽管MySQL尝试解释几种格式的值,日期总是以年-月-日顺序(例如,98-09-04),而不是其它地方常用的月-日-年或日-月-年顺序(例 如,09-04-98,04-09-98). 如果值用于数值上下文中,MySQL自动将日期或时间类型的值转换为数字,反之亦然. 当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法
16、的值时(如本节开始所描述),它将该值转换为该类的零值.一个例外是超出范围的 TIME值被裁剪到TIME范围的相应端点.下面的表显示了各类零值的格式.请注意如果启用NO_ZERO_DATE SQL模式,使用这些值会产生警告.Data TypeZero ValueDATETIME0000-00-00 00:00:00DATE0000-00-00TIMESTAMP0000-00-00 00:00:00TIME00:00:00YEAR00001.3 String类型字符串类型指CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和 SET.该节描述了这些类型如何工作
17、以及如何在查询中使用这些类型.CHAR和VARCHAR类型类似,但它们保存和检索的方式不 同.它们的最大长度和是否尾部空格被保留等方面也不同.在存储或检索过程中不进行大小写转换.CHAR和VARCHAR类型声明的 长度表示你想要保存的最大字符数.例如,CHAR(30)可以占用30个字符.CHAR列的长度固定为创建表时声明的长度.长度可以为从0 到255的任何值.当保存CHAR值时,在它们的右边填充空格以达到指定的长度.当检索到CHAR值时,尾部的空格被删除掉.在存储或检索过程中不进行大 小写转换.VARCHAR列中的值为可变长字符串.长度可以指定为0到65,535之间的值.(VARCHAR的最
18、大有效长度由最大行大小 和使用的字符集确定.整体最大长度是65,532字节).同CHAR对比,VARCHAR值保存时只保存需要的字符数,另加一个字节来记录 长度(如果列声明的长度超过255,则使用两个字节).VARCHAR值保存时不进行填充.当值保存和检索时尾部的空格仍保留,符合标准 SQL.如果分配给CHAR或VARCHAR列的值超过列的最大长度,则对值进行裁剪以使其适合.如果被裁掉的字符不是空格,则会产生一条 警告.如果裁剪非空格字符,则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入.下面的表显示了将各种字符串值保存到 CHAR(4)和VARCHAR(4)列后的结果,说明了C
19、HAR和VARCHAR之间的差别:ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required 4 bytes1 byteabab 4 bytesab3 bytesabcdabcd4 bytesabcd5 bytesabcdefghabcd4 bytesabcd5 bytes请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格 模式,超过列长度不的值不保存,并且会出现错误.从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列 删除了尾部的空格.通过下面的例子说明该差别:mysql CREATE
20、TABLE vc (v VARCHAR(4), c CHAR(4);Query OK, 0 rows affected (0.02 sec)mysql INSERT INTO vc VALUES (ab , ab );Query OK, 1 row affected (0.00 sec)mysql SELECT CONCAT(v, +), CONCAT(c, +) FROM vc;BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串.也就是 说,它们包含字节字符串而不是字符字符串.这说明它们没有字符集,并且排序和比较基于列值字节的数
21、值值.BINARY和 VARBINARY允许的最大长度一样,如同CHAR和VARCHAR,不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度.BINARY 和VARBINARY数据类型不同于CHAR BINARY和VARCHAR BINARY数据类型.对于后一种类型,BINARY属性不会将列视为二进制字符串列.相反,它致使使用列字符集的二元 校对规则,并且列自身包含非二进制字符字符串而不是二进制字节字符串.例如CHAR(5) BINARY被视为CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin,假定默认字符集是latin1.这不同
22、于BINARY(5),它保存5字节二进制字符串,没有字符集或 校对规则.当 保存BINARY值时,在它们右边填充值以达到指定长度.填充值是0x00(零字节).插入值时在右侧添加0x00 on,并且选择时不删除尾部的字节.比较时所有字节很重要,包括ORDER BY和DISTINCT操作.比较时0x00字节和空格是不同的,0x00空格.例如:对于一个BINARY(3)列,当插入时 a 变为 a .a插入时变为a.当选择时两个插入的值均不更改.对于VARBINARY,插入时不填充字符,选 择时不裁剪字节.比较时所有字节很重要,包括ORDER BY和DISTINCT操作.比较时0x00字节和空格是不同
23、的,0x00 CREATE TABLE t (c BINARY(3);Query OK, 0 rows affected (0.01 sec)mysql INSERT INTO t SET c = a;Query OK, 1 row affected (0.01 sec)mysql SELECT HEX(c), c = a, c = a from t;+-+-+-+| HEX(c) | c = a | c = a |+-+-+-+| 610000 | 0 | 1 |+-+-+-+1 row in set (0.09 sec)如果检索的值必须与指定进行存储而没有填充的值相同,最好使用BLOB数据
24、类型.BLOB是一个二进制 大对象,可以容纳可变数量的数据.有4种BLOB类型:TINYBLOB,BLOB,MEDIUMBLOB和LONGBLOB.它们只是可容纳值的最大长 度不同.有4种TEXT类型:TINYTEXT,TEXT,MEDIUMTEXT和LONGTEXT.这些对应4种BLOB类型, 有相同的最大长度和存储需求.BLOB 列被视为二进制字符串(字节字符串).TEXT列被视为非二进制字符串(字符字符串).BLOB列没有字符集,并且排序和比较基于列值字节的数值 值.TEXT列有一个字符集,并且根据字符集的 校对规则对值进行排序和比较.在TEXT或BLOB列的存储或检索过程中,不存在大小
25、写转 换.当未运行在严格模式时,如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值,值被截取以保证适合.如果截掉的字符不是 空格,将会产生一条警告.使用严格SQL模式,会产生错误,并且值将被拒绝而不是截取并给出警告.在大多数方面,可以将BLOB列视为能够 足够大的VARBINARY列.同样,可以将TEXT列视为VARCHAR列.BLOB和TEXT在以下几个方面不同于VARBINARY和 VARCHAR: 当保存或检索BLOB和TEXT列的值时不删除尾部空格.(这与VARBINARY和VARCHAR列相同).请 注意比较时将用空格对TEXT进行扩充以适合比较的对象,正如CHAR和V
26、ARCHAR. 对于BLOB和TEXT列的索引,必须指定索引前缀的长度.对于CHAR和VARCHAR,前缀长度是可选的. BLOB和TEXT列不能有 默认值.LONG和LONG VARCHAR对应MEDIUMTEXT数据类型.这是为了保证兼容性.如果TEXT列类型使用BINARY属性,将为列分配列字符集的二元 校对规则.MySQL 连接程序/ODBC将BLOB值定义为LONGVARBINARY,将TEXT值定义为LONGVARCHAR.由于BLOB和TEXT值 可能会非常长,使用它们时可能遇到一些约束: 当排序时只使用该列的前max_sort_length个字节.max_sort_length
27、的 默认值是1024;该值可以在启动mysqld服务器时使用-max_sort_length选项进行更改.运行时增加 max_sort_length的值可以在排序或组合时使更多的字节有意义.任何客户端可以更改其会话max_sort_length变量的值:mysql SET max_sort_length = 2000;mysql SELECT id, comment FROM tbl_name- ORDER BY comment;当你想要使超过max_sort_length的字节有意义,对含长值 的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对
28、象.标准方法是使用SUBSTRING函数.例如,下面的语句对comment列的2000个字节进行排序:mysql SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name- ORDER BY SUBSTRING(comment,1,2000); BLOB或TEXT对象的最大大小由其类型确定,但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定.你可以通过更改 max_allowed_packet变量的值更改消息缓存区的大小,但必须同时修改服务器和客户端程序.例如,可以使用 mysql和mysqldump来更改客户端的max_a
29、llowed_packet值.参见,调节服务器参数,8.3 节,mysql:MySQL命令行工具和8.8节,mysqldump:数据库备份程序.每个BLOB或TEXT值分别由内部分配 的对象表示.这与其它列类型形成对比,后者是当打开表时为每1列分配存储引擎.不要以为字符类型就是 CHAR,CHAR 和 VARCHAR 的区别在于 CHAR 是固定长度,只要你定义一个字段是 CHAR(10),那么不论你存储的数据是否达到了 10 个字节,它都要占去 10 个字节的空间;而 VARCHAR 则是可变长度的,如果一个字段可能的值是不固定长度的,我们只知道它不可能超过 10 个字符,把它定义为 VAR
30、CHAR(10) 是最合算的,VARCHAR 类型的占用空间是它的值的实际长度 +1.为什么要 +1 呢?这一个字节用于保存实际使用了多大的长度.从这个 +1 中也应该看到,如果一个字段,它的可能值最长是 10 个字符,而多数情况下也就是用到了 10 个字符时,用 VARCHAR 就不合算了:因为在多数情况下,实际占用空间是 11 个字节,比用 CHAR(10) 还多占用一个字节.举 个例子,就是一个存储股票名称和代码的表,股票名称绝大部分是四个字的,即 8 个字节;股票代码,上海的是六位数字,深圳的是四位数字.这些都是固定长度的,股票名称当然要用 CHAR(8);股票代码虽然是不固定长度,但如果使用 VARCHAR(6),一个深圳的股票代码实际占用空间是 5 个字节,而一个上海的股票代码要占用 7 个字节!考虑到上海的股票数目比深圳的多,那么用 VARCHAR(6) 就不如 CHAR(6) 合算了.虽然一个 CHAR 或 VARCHAR 的最大长度可以到 255,我认为大于 20 的 CHAR 是几乎用不到的很
