MySQL常用数据类型和建库策略.docx
《MySQL常用数据类型和建库策略.docx》由会员分享，可在线阅读，更多相关《MySQL常用数据类型和建库策略.docx（18页珍藏版）》请在冰豆网上搜索。
MySQL常用数据类型和建库策略
1.MYSQL5数据类型,长度范围
1.1数值类型
MySQL支持所有标准SQL数值数据类型.这些类型包括严格数值数据类型（INTEGER,SMALLINT,DECIMAL和NUMERIC）,以及近似数值数据类型（FLOAT,REAL和DOUBLEPRECISION）.关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词.
BIT数据类型保存位字段值,并且支持MyISAM,MEMORY,InnoDB和BDB表.作为SQL标准的扩展,MySQL也支持整数类型TINYINT,MEDIUMINT和BIGINT.下面的表显示了需要的每个整数类型的存储和范围.
MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度（例如,INT（4））.该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度.
显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示.
当结合可选扩展属性ZEROFILL使用时,默认补充的空格用零代替.例如,对于声明为INT（5）ZEROFILL的列,值4检索为00004.请注意如果在整数列保存超过显示宽度的一个值,当MySQL为复杂联接生成临时表时会遇到问题,因为在这些情况下MySQL相信数据适合原列宽度.
所有整数类型可以有一个可选（非标准）属性UNSIGNED.当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值.
浮点和定点类型也可以为UNSIGNED.同数类型,该属性防止负值保存到列中.然而,与整数类型不同的是,列值的上范围保持不变.
如果为一个数值列指定ZEROFILL,MySQL自动为该列添加UNSIGNED属性.
对于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节.
FLOAT类型用于表示近似数值数据类型.SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度（但不能为指数范围）.MySQL还支持可选的只用于确定存储大小的精度规定.0到23的精度对应FLOAT列的4字节单精度.24到53的精度对应DOUBLE列的8字节双精度.
MySQL允许使用非标准语法:
FLOAT（M,D）或REAL（M,D）或DOUBLEPRECISION（M,D）.这里,"（M,D）"表示该值一共显示M位整数,其中D位位于小数点后面.例如,定义为FLOAT（7,4）的一个列可以显示为-,因此如果在FLOAT（7,4）列内插入999.00009,近似结果是999.0001.
MySQL将DOUBLE视为DOUBLEPRECISION（非标准扩展）的同义词.MySQL还将REAL视为DOUBLEPRECISION（非标准扩展）的同义词,除非SQL服务器模式包括REAL_AS_FLOAT选项.
为了保证最大可能的可移植性,需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLEPRECISION,不规定精度或位数.
DECIMAL和NUMERIC类型在MySQL中视为相同的类型.它们用于保存必须为确切精度的值,例如货币数据.当声明该类型的列时,可以（并且通常要）指定精度和标度;例如:
salaryDECIMAL（5,2）
在该例子中,5是精度,2是标度.精度表示保存值的主要位数,标度表示小数点后面可以保存的位数.
在MySQL5.1中以二进制格式保存DECIMAL和NUMERIC值.
标准SQL要求salary列能够用5位整数位和两位小数保存任何值.因此,在这种情况下可以保存在salary列的值的范围是从-999.99到999.99.
在标准SQL中,语法DECIMAL（M）等价于DECIMAL（M,0）.同样,语法DECIMAL等价于DECIMAL（M,0）,可以通过计算确定M的值.在MySQL5.1中支持DECIMAL和NUMERIC数据类型的变量形式.M默认值是10.
DECIMAL或NUMERIC的最大位数是65,但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束.如果此类列分配的值小数点后面的位数超过指定的标度允许的范围,值被转换为该标度.（具体操作与操作系统有关,但一般结果均被截取到允许的位数）.
BIT数据类型可用来保存位字段值.BIT（M）类型允许存储M位值.M范围为1到64.
要指定位值,可以使用b'value'符.value是一个用0和1编写的二进制值.例如,b'111'和b'0'分别表示7和128.参见,"位字段值".
如果为BIT（M）列分配的值的长度小于M位,在值的左边用0填充.例如,为BIT（6）列分配一个值b'101',其效果与分配b'000101'相同.
当要在一个数值列内保存一个超出该列允许范围的值时,MySQL的操作取决于此时有效的SQL模式.如果模式未设置,MySQL将值裁剪到范围的相应端点,并保存裁减好的值.但是,如果模式设置为traditional（"严格模式"）,超出范围的值将被拒绝并提示错误,并且根据SQL标准插入会失败.参见,"SQL服务器模式".
如果INT列是UNSIGNED,列范围的大小相同,但其端点会变为到0和95.如果你试图保存-99和99,以非严格模式保存到列中的值是0和96.
如果在浮点或定点列中分配的值超过指定（或默认）精度和标度规定的范围,MySQL以非严格模式保存表示范围相应端点的值.
当MySQL没有工作在严格模式时,对于ALTERTABLE,LOADDATAINFILE,UPDATE和多行INSERT语句,由于裁剪发生的转换将报告为警告.当MySQL工作在严格模式时,这些语句将失败,并且部分或全部值不会插入或更改,取决于是否表为事务表和其它因素.
Type
Bytes
MinimumValue
MaximumValue
（Signed/Unsigned）
（Signed/Unsigned）
TINYINT
1
-128
127
0
255
SMALLINT
2
-32768
32767
0
65535
MEDIUMINT
3
-8388608
8388607
0
INT
4
-48
47
0
95
BIGINT
8
-808
807
0
0615
这里将数字类型按照分类方法分为三类:
整数类,小数类和数字类.
我所谓的"数字类",就是指DECIMAL和NUMERIC,它们是同一种类型.它严格的说不是一种数字类型,因为他们实际上是将数字以字符串形式保存的;他的值的每一位（包括小数点）占一个字节的存储空间,因此这种类型耗费空间比较大.但是它的一个突出的优点是小数的位数固定,在运算中不会"失真",所以比较适合用于"价格","金额"这样对精度要求不高但准确度要求非常高的字段.
小数类,即浮点数类型,根据精度的不同,有FLOAT和DOUBLE两种.它们的优势是精确度,FLOAT可以表示绝对值非常小,小到约1.17E-38（0.000...0117,小数点后面有37个零）的小数,而DOUBLE更是可以表示绝对值小到约2.22E-308（0.000...0222,小数点后面有307个零）的小数.FLOAT类型和DOUBLE类型占用存储空间分别是4字节和8字节.如果需要用到小数的字段,精度要求不高的,当然用FLOAT了.可是说句实在话,我们"民用"的数据,哪有要求精度那么高的呢?
这两种类型至今我没有用过――我还没有遇到适合于使用它们的事例.
用的最多的,最值得精打细算的,是整数类型.从只占一个字节存储空间的TINYINT到占8个字节的BIGINT,挑选一个"够用"并且占用存储空间最小的类型是设计数据库时应该考虑的.TINYINT,SMALLINT,MEDIUMINT,INT和BIGINT占用存储空间分别为1字节,2字节,3字节,4字节和8字节,就无符号的整数而言,这些类型能表示的最大整数分别为255,65535,,95和0615.如果用来保存用户的年龄（举例来说,数据库中保存年龄是不可取的）,用TINYINT就够了;九城的《纵横》里,各项技能值,用SMALLINT也够了;如果要用作一个肯定不会超过行的表的AUTO_INCREMENT的IDENTIFY字段,当然用MEDIUMINT不用INT,试想,每行节约一个字节,行可以节约10兆多.
1.2日期和时间类型
表示时间值的DATE和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR.每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值.TIMESTAMP类型有专有的自动更新特性,将在后面描述.
如果试图插入一个不合法的日期,MySQL将给出警告或错误.可以使用ALLOW_INVALID_DATESSQL模式让MySQL接受某些日期,例如'1999-11-31'.当你想要保存一个"可能错误的"用户已经在数据库中指定（例如,以web形式）用于将来处理的值时很有用.在这种模式下,MySQL只验证月范围为从0到12,日范围为从0到31.这些范围可以包括零,因为MySQL允许在DATE或DATETIME列保存日/月和日是零的日期.这在应用程序需要保存一个你不知道确切日期的生日时非常有用.在这种情况下,只需要将日期保存为'1999-00-00'或'1999-01-00'.如果保存此类日期,DATE_SUB（）或DATE_ADD等需要完整日期的函数不会得到正确的结果.（如果你不想在日期中出现零,可以使用NO_ZERO_IN_DATESQL模式）.
MySQL还允许将'0000-00-00'保存为"伪日期"（如果不使用NO_ZERO_DATESQL模式）.这在某些情况下比使用NULL值更方便（并且数据和索引占用的空间更小）.
将sql_mode系统变量设置为相应模式值,可以更确切你想让MySQL支持哪种日期.参见,"SQL服务器模式".
当使用日期和时间类型时应记住以下几点:
·MySQL以标准输出格式检索给定日期或时间类型的值,但它尽力解释你指定的各种输入值格式（例如,当你指定一个分配给或与日期或时间类型进行比较的值时）.只支持下面章节中描述的格式.期望你能提供有效值.如果你使用其它格式的值会发生意想不到的结果.
·包含两位年值的日期会令人模糊,因为世纪不知道.MySQL使用以下规则解释两位年值:
o70-99范围的年值转换为1970-1999.
o00-69范围的年值转换为2000-2069.
·尽管MySQL尝试解释几种格式的值,日期总是以年-月-日顺序（例如,'98-09-04'）,而不是其它地方常用的月-日-年或日-月-年顺序（例如,'09-04-98','04-09-98'）.
·如果值用于数值上下文中,MySQL自动将日期或时间类型的值转换为数字,反之亦然.
·当MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时（如本节开始所描述）,它将该值转换为该类的"零"值.一个例外是超出范围的TIME值被裁剪到TIME范围的相应端点.
下面的表显示了各类"零"值的格式.请注意如果启用NO_ZERO_DATESQL模式,使用这些值会产生警告.
DataType
"Zero"Value
DATETIME
'0000-00-0000:
00:
00'
DATE
'0000-00-00'
TIMESTAMP
'0000-00-0000:
00:
00'
TIME
'00:
00:
00'
YEAR
0000
1.3String类型
字符串类型指CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和SET.该节描述了这些类型如何工作以及如何在查询中使用这些类型.
CHAR和VARCHAR类型类似,但它们保存和检索的方式不同.它们的最大长度和是否尾部空格被保留等方面也不同.在存储或检索过程中不进行大小写转换.
CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数.例如,CHAR（30）可以占用30个字符.
CHAR列的长度固定为创建表时声明的长度.长度可以为从0到255的任何值.当保存CHAR值时,在它们的右边填充空格以达到指定的长度.当检索到CHAR值时,尾部的空格被删除掉.在存储或检索过程中不进行大小写转换.
VARCHAR列中的值为可变长字符串.长度可以指定为0到65,535之间的值.（VARCHAR的最大有效长度由最大行大小和使用的字符集确定.整体最大长度是65,532字节）.
同CHAR对比,VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度（如果列声明的长度超过255,则使用两个字节）.
VARCHAR值保存时不进行填充.当值保存和检索时尾部的空格仍保留,符合标准SQL.
如果分配给CHAR或VARCHAR列的值超过列的最大长度,则对值进行裁剪以使其适合.如果被裁掉的字符不是空格,则会产生一条警告.如果裁剪非空格字符,则会造成错误（而不是警告）并通过使用严格SQL模式禁用值的插入.
下面的表显示了将各种字符串值保存到CHAR（4）和VARCHAR（4）列后的结果,说明了CHAR和VARCHAR之间的差别:
Value
CHAR（4）
StorageRequired
VARCHAR（4）
StorageRequired
''
''
4bytes
''
1byte
'ab'
'ab'
4bytes
'ab'
3bytes
'abcd'
'abcd'
4bytes
'abcd'
5bytes
'abcdefgh'
'abcd'
4bytes
'abcd'
5bytes
请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值不保存,并且会出现错误.
从CHAR（4）和VARCHAR（4）列检索的值并不总是相同,因为检索时从CHAR列删除了尾部的空格.通过下面的例子说明该差别:
mysql>CREATETABLEvc（vVARCHAR（4）,cCHAR（4））;
QueryOK,0rowsaffected（0.02sec）
mysql>INSERTINTOvcVALUES（'ab','ab'）;
QueryOK,1rowaffected（0.00sec）
mysql>SELECTCONCAT（v,'+'）,CONCAT（c,'+'）FROMvc;
BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串.也就是说,它们包含字节字符串而不是字符字符串.这说明它们没有字符集,并且排序和比较基于列值字节的数值值.
BINARY和VARBINARY允许的最大长度一样,如同CHAR和VARCHAR,不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度.
BINARY和VARBINARY数据类型不同于CHARBINARY和VARCHARBINARY数据类型.对于后一种类型,BINARY属性不会将列视为二进制字符串列.相反,它致使使用列字符集的二元校对规则,并且列自身包含非二进制字符字符串而不是二进制字节字符串.例如CHAR（5）BINARY被视为CHAR（5）CHARACTERSETlatin1COLLATElatin1_bin,假定默认字符集是latin1.这不同于BINARY（5）,它保存5字节二进制字符串,没有字符集或校对规则.
当保存BINARY值时,在它们右边填充值以达到指定长度.填充值是0x00（零字节）.插入值时在右侧添加0x00on,并且选择时不删除尾部的字节.比较时所有字节很重要,包括ORDERBY和DISTINCT操作.比较时0x00字节和空格是不同的,0x00<空格.
例如:
对于一个BINARY（3）列,当插入时'a'变为'a�'.'a�'插入时变为'a��'.当选择时两个插入的值均不更改.
对于VARBINARY,插入时不填充字符,选择时不裁剪字节.比较时所有字节很重要,包括ORDERBY和DISTINCT操作.比较时0x00字节和空格是不同的,0x00<空格.
对于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形,如果列的索引需要唯一的值,在列内插入一个只是填充字符数不同的值将会造成复制键值错误.
如果你计划使用这些数据类型来保存二进制数据并且需要检索的值与保存的值完全相同,应考虑前面所述的填充和裁剪特征.下面的例子说明了用0x00填充的BINARY值如何影响列值比较:
mysql>CREATETABLEt（cBINARY（3））;
QueryOK,0rowsaffected（0.01sec）
mysql>INSERTINTOtSETc='a';
QueryOK,1rowaffected（0.01sec）
mysql>SELECTHEX（c）,c='a',c='a��'fromt;
+--------+---------+-------------+
|HEX（c）|c='a'|c='a��'|
+--------+---------+-------------+
|610000|0|1|
+--------+---------+-------------+
1rowinset（0.09sec）
如果检索的值必须与指定进行存储而没有填充的值相同,最好使用BLOB数据类型.
BLOB是一个二进制大对象,可以容纳可变数量的数据.有4种BLOB类型:
TINYBLOB,BLOB,MEDIUMBLOB和LONGBLOB.它们只是可容纳值的最大长度不同.
有4种TEXT类型:
TINYTEXT,TEXT,MEDIUMTEXT和LONGTEXT.这些对应4种BLOB类型,有相同的最大长度和存储需求.
BLOB列被视为二进制字符串（字节字符串）.TEXT列被视为非二进制字符串（字符字符串）.BLOB列没有字符集,并且排序和比较基于列值字节的数值值.TEXT列有一个字符集,并且根据字符集的校对规则对值进行排序和比较.
在TEXT或BLOB列的存储或检索过程中,不存在大小写转换.
当未运行在严格模式时,如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值,值被截取以保证适合.如果截掉的字符不是空格,将会产生一条警告.使用严格SQL模式,会产生错误,并且值将被拒绝而不是截取并给出警告.
在大多数方面,可以将BLOB列视为能够足够大的VARBINARY列.同样,可以将TEXT列视为VARCHAR列.BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR:
·当保存或检索BLOB和TEXT列的值时不删除尾部空格.（这与VARBINARY和VARCHAR列相同）.
请注意比较时将用空格对TEXT进行扩充以适合比较的对象,正如CHAR和VARCHAR.
·对于BLOB和TEXT列的索引,必须指定索引前缀的长度.对于CHAR和VARCHAR,前缀长度是可选的.
·BLOB和TEXT列不能有默认值.
LONG和LONGVARCHAR对应MEDIUMTEXT数据类型.这是为了保证兼容性.如果TEXT列类型使用BINARY属性,将为列分配列字符集的二元校对规则.
MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY,将TEXT值定义为LONGVARCHAR.
由于BLOB和TEXT值可能会非常长,使用它们时可能遇到一些约束:
·当排序时只使用该列的前max_sort_length个字节.max_sort_length的默认值是1024;该值可以在启动mysqld服务器时使用--max_sort_length选项进行更改.
运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义.任何客户端可以更改其会话max_sort_length变量的值:
mysql>SETmax_sort_length=2000;
mysql>SELECTid,commentFROMtbl_name
->ORDERBYcomment;
当你想要使超过max_sort_length的字节有意义,对含长值的BLOB或TEXT列使用GROUPBY或ORDERBY的另一种方式是将列值转换为固定长度的对象.标准方法是使用SUBSTRING函数.例如,下面的语句对comment列的2000个字节进行排序:
mysql>SELECTid,SUBSTRING（comment,1,2000）FROMtbl_name
->ORDERBYSUBSTRING（comment,1,2000）;
·BLOB或TEXT对象的最大大小由其类型确定,但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定.你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小,但必须同时修改服务器和客户端程序.例如,可以使用mysql和mysqldump来更改客户端的max_allowed_packet值.参见,"调节服务器参数",8.3节,"mysql:
MySQL命令行工具"和8.8节,"mysqldump:
数据库备份程序".
每个BLOB或TEXT值分别由内部分配的对象表示.这与其它列类型形成对比,后者是当打开表时为每1列分配存储引擎.
不要以为字符类型就是CHAR,CHAR和VARCHAR的区别在于CHAR是固定长度,只要你定义一个字段是CHAR（10）,那么不论你存储的数据是否达到了10个字节,它都要占去10个字节的空间;而VARCHAR则是可变长度的,如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为VARCHAR（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是几乎用不到的――很
升级会员 