2 栏位资料型态

在建立表格时，你会帮每一个栏位指定适合的「资料型态、data type」。正确的选择栏位资料型态，除了可以帮你储存正确的资料外，还可以让资料库使用最少的记忆体与储存空间，这样会让资料库运作的效率更好一些。资料型态主要分为下列三大类：

• 数值：任何包含正、负号的整数与小数资料；另外还有位元(bit)的数值资料，它使用二进位来表示一个数字。
• 字串：包含non-binary与binary两种字串值，non-binary字串值是一些使用字元集与collat​​ion的字元(character)组合起来的；binary字串值是一些位元组(bytes )组合的资料。
• 日期与时间：包含日期、时间与日期加时间。

2.1 数值

数值资料分为整数与小数资料，下列是MySQL提供的整数型态：

型态	Byte(s)	预设长度	有号数范围	无号数范围
TINYINT[(长度)]	1	4	-128~127	0~255
SMALLINT[(长度)]	2	6	-32768~32767	0~65535
MEDIUMINT[(长度)]	3	9	-8388608~8388607	0~16777215
INT[(长度)]	4	11	-2147683648~2147683647	0~4294967295
BIGINT[(长度)]	8	20	-9223372036854775808~9223372036854775807	0~18446744073709551615

整数型态的意思就是它们不能储存小数，在建立表格的时候，如果需要一个可以储存整数资料的栏位，你可以依照整数资料的大小需求，选择一个够用又不会太浪费空间的整数形态。以下列的「cmdev.integertable」表格来说：

栏位名称	型态	范围
n	TINYINT(4)	-128~127
n2	SMALLINT(6)	-32768~32767
n3	MEDIUMINT(9)	-8388608~8388607
n4	INT(11)	-2147683648~2147683647
n5	BIGINT(20)	-9223372036854775808~9223372036854775807

整数型态的后面会在左右刮号中指定一个数字，以「SMALLING」型态来说：

当你在执行资料的新增或修改的时候，就要特别注意它们的可以储存数字的范围：

整数型态的栏位，就表示它们不可以储存小数的数值：

数值型态还有下列几种可以储存小数资料的浮点数型态：

型态	Byte(s)	预设长度	最大长度	说明
FLOAT[(长度,小数位数)]	4	注1	255, 30	单精确度浮点数(近似值)
DOUBLE[(长度,小数位数)]	8		255, 30	双精确度浮点数(近似值)
DECIMAL[(长度[,小数位数])]	注2	10, 0	65, 30	自行指定位数的精确值

注1：FLOAT与DOUBLE的预设长度会因为不同的作业系统而有不一样的长度
注2：依照指定的位数决定实际储存的空间

「FLOAT」和「DOUBLE」型态的栏位可以用来储存包含小数的数值，储存空间分别是4和8个位元组，它们是一种占用储存空间比较小，执行运算比较快的型态。不过因为它们是使用「近似值」来储存你的数值，所以如果你需要储存完全精准的数值，就不能使用这两种型态。

另外一种可以储存小数数值的「DECIMAL」型态就可以用来储存完全精准的数值，储存在这个型态中的数值，不论是查询或是运算，都不会有任何误差，不过「DECIMAL」型态占用的储存空间就比「FLOAT」和「DOUBLE」型态大。「DECIMAL」型态在MySQL还有一个一样的关键字是「NUMERIC」，这两种型态完全一样。

在MySQL中，「FLOAT」、「DOUBLE」和「DECIMAL」都可以依照自己的需要设定长度与位数：

在设定长度与小数位数的时候，要注意下列几个规则：

• 不可以超过最大长度
• 小数位数不可以超过长度
• 长度与小数位数一样的时候，表示只可以储存小数，例如「0.123」

MySQL的数值型态，包含整数与浮点数都可以设定为「只能储存正数」，以下列的「cmdev.numerictable」表格来说：

栏位名称	型态
i	TINYINT(3) UNSIGNED
i2	SMALLINT(5) UNSIGNED
i3	MEDIUMINT(8) UNSIGNED
i4	INT(10)
i5	BIGINT(20) UNSIGNED
f	FLOAT UNSIGNED
f2	DOUBLE
f3	DECIMAL(10, 0) UNSIGNED

设定为只能储存正数的栏位，就跟字面上的效果一样，任何希望储存负数的动作都会造成错误：

MySQL的数值型态都可以依照自己的需要设定长度，以下列的「cmdev.numerictable2」表格来说：

栏位名称	型态
i	TINYINT(3)
i2	SMALLINT(3)
i3	MEDIUMINT(3)
i4	INT(3)
i5	BIGINT(3)
f	FLOAT(5, 2)
f2	DOUBLE(5, 2)
f3	DECIMAL(5, 2)

同样为数值型态设定长度，在整数和浮点数会有不一样的效果。如果你为整数型态的栏位设定长度的话，这个长度只是设定显示的长度而已，并不会影响实际储存的长度：

为浮点数型态设定长度与小数位数的时候，效果就跟整数型态不一样了：

不过在整数位数的部份，就一定会依照设定来储存，否则会造成错误：

MySQL的在数值型态的设定上，还有一个比较特别的设定，就是「ZEROFILL」，以下列的「cmdev.numerictable3」表格来说：

栏位名称	型态
i	TINYINT(3) UNSIGNED ZEROFILL
i2	SMALLINT(4) UNSIGNED ZEROFILL
i3	MEDIUMINT(5) UNSIGNED ZEROFILL
i4	INT(6) UNSIGNED ZEROFILL
i5	BIGINT(7) UNSIGNED ZEROFILL
f	FLOAT(5, 2) UNSIGNED ZEROFILL
f2	DOUBLE(7, 3) UNSIGNED ZEROFILL
f3	DECIMAL(9, 5) UNSIGNED ZEROFILL

「ZEROFILL」的设定表示在查询这些栏位的时候，回传的资料会在左侧根据长度的设定填满「0」：

注：「ZEROFILL」一定要跟「UNSIGNED」一起使用，就算你只有为栏位设定「ZEROFILL」，MySQL也会自动加入「UNSIGNED」的设定。

整数型态的部份，在补0的处理上会不太一样：

2.2 位元

「位元、BIT」型态其实也是用来储存数值用的，不过它是以二进位的型式储存资料，也就是只有0跟1两种资料。MySQL的在数值型态的设定上，还有一个比较特别的设定，就是「ZEROFILL」，以下列的「cmdev.numerictable3」表格来说：

栏位名称	型态	数字范围
n	BIT	0~1
n2	BIT(8)	0~255
n3	BIT(64)	0~18446744073709551615

你可以直接储存数字到位元型态的栏位；也可以指定一个使用二进位表示的值：

2.3 字串

MySQL把字串型态分为两大类：「非二进位制、non-binary」与「二进位制、binary」。非二进位制就是储存一般文字的字串，会有特定的字元集与collat​​ion；二进位制使用位元组储存资料，不包含字元集与collat​​ion，所以大多用来储存图片或音乐这类资料。「非二进位制、non-binary」的字串型态有下列几种：

型态	最大长度	实际储存的空间	说明
CHAR[(长度)]	255	指定的长度	固定长度的字串，预设长度为1
VARCHAR(长度)	65535	字元个数加1或2bytes	变动长度的字串
TINYTEXT	255	字元个数加1byte
TEXT	65535	字元个数加2bytes
MEDIUMTEXT	16,772,215	字元个数加3bytes
LONGTEXT	4,294,967,295	字元个数加4bytes

固定长度与变动长度的两种字串型态都可以储存字串，差异在储存的文字个数小于型态指定的长度时，变动长度实际储存的空间会小一些，以下列的「cmdev.nonbinarytable 」表格来说：

栏位名称	型态
s	CHAR(10)
s2	VARCHAR(10)

同样把长度设定为10的「CHAR」与「VARCHAR」字串型态，它们在储存字串资料的时候会不太一样：

「非二进位制、non-binary」的字串都会包含特定的字元集与collat​​ion，所以可以用来储存各种不同国家的文字。不同的字元集会占用不同的储存空间，以下列的「cmdev.nonbinarytable2」表格来说：

栏位名称	型态	字元集
s	VARCHAR(6)	latin1
s2	VARCHAR(6)	big5
s3	VARCHAR(6)	utf8

上列的表格中，三个栏位分别设定为「latin1」、「big5」与「utf8」字元集，你可以查询MySQL资料库支援的字元集特性，「MAXLEN」栏位是关于储存空间的资讯：

使用在「LENGTH」函式来查询储存在这个表格中的字串资料，就可以很明显的看出不同的字元集，在储存字元时使用的储存空间：

「LENGTH」函式会传回字串资料实际的储存长度(byte)；如果你要查询字串的字元数量的话，就要使用「CHAR_LENGTH」函式：

字元集会影响字串的储存空间，collat​​ion会影响字串排列顺序。以下列的「cmdev.nonbinarytable3」表格来说：

栏位名称	型态	字元集	Collat​​ion
s	VARCHAR(6)	latin1	latin1_general_ci
s2	VARCHAR(6)	latin1	latin1_general_cs

上列表格中栏位的字元集都指定为「latin1」，不过「s」栏位的collat​​ion设定为「latin1_general_ci」，表示排序时不区分大小写；「s2」栏位设定为「latin1_general_cs」，表示排序时会区分大小写。以下列储存在这个表格中纪录来说：

Collat​​ion设定中的「latin1_general_ci」，最后的「ci」表示「case insensitive」，是不分大小写的意思。在这样的设定下，MySQL会把字串「ABC」和「abc」当成是一样的；「latin1_general_cs」，最后的「cs」表示「case sensitive」，是区分大小写的意思。在这样的设定下，MySQL就会把字串「ABC」和「abc」当成是不一样的字串。

是否区分大小写的collat​​ion设定会影响排序的结果：

另外一个影响是条件的判断：

「二进位制、binary」的字串型态是使用位元组(byte)为单位来储存字串资料，跟非二进位制的字串类似，它也提供许多应用在不同长度的型态：

型态	最大长度(byte)	实际储存的空间(byte)	说明
BINARY[(长度)]	255	指定的长度	固定长度的字串，预设长度为1
VARBINARY(长度)	65535	长度加1或2bytes	变动长度的字串
TINYBLOB	255	byte数加1byte
BLOB	65535	byte数加2bytes
MEDIUMBLOB	16,772,215	byte数加3bytes
LONGBLOB	4,294,967,295	byte数加4bytes

「BINARY」与「VARBINARY」两种型态的差异，与「CHAR」和「VARCHAR」的差异一样。在一般的情况下，使用「VARBINARY」会比「BINARY」节省一点储存空间。你也可以使用「二进位制、binary」型态储存文字资料，只不过MySQL都是以位元组来储存所有的资料，也就是0到255的数字：

所有「二进位制、binary」的字串型态都不可以指定字元集与collat​​ion，不过你可以使用它们来储存任何语言的文字，也可以储存类似音乐或图片资料，因为MySQL都是一个一个byte的把资料储存到资料库中；所以在执行查询时的排序和条件设定，都是以使用位元组为单位来判断。

2.4 列举与集合

列举(ENUM)与集合(SET)是一种特殊的「非二进位制、non-binary」字串型态，所以它们也可以指定字元集与collat​​ion。下列是这两种型态的说明：

型态	最大个数	储存空间	说明
ENUM(字串值[,...])	65535	1byte(255个)2bytes(256到65535个)	包含一组合法的字串值(单一值)
SET(字串值[,...])	64	1byte(8个)2bytes(16个)3bytes(24个)4bytes(32个)8bytes(64个)	包含一组合法的字串值(多个值)

列举(enumeration)的资料在资料库中的应用很常见，例如服装的大小就会以S、M与L来表示小、中与大。你可以使用字串来储存这类资料，不过这类的资料也很适合使用「ENUM」型态来储存。以下列的「cmdev.enumtable」表格来说：

在储存资料的时候，「ENUM」型态看起来似乎与「VARCHAR」完全一样：

可是列举型态在资料的正确性方面，就会比单纯的字串型态好多了。例如下列错误示范：

列举型态栏位除了可以直接使用字串值来新增与更新资料外，还可以使用数值资料的编号来代替，任何一个列举型态中的成员，MySQL都会帮它们编一个号码：

了解列举型态中成员的编号以后，你可以选择字串值或数值来管理列举型态栏位储存的资料：

虽然在查询列举型态栏位资料的时候，所得到的结果都是成员的字串值；不过真正储存在资料库中的资料却是成员的编号，所以指定列举型态栏位为排序栏位的时候，资料库会使用编号来排序，而不是以成员的字串值：

在指定列举型态栏位的查询条件时，可以使用成员的字串值或编号：

集合(SET)型态同样可以设定一组成员，不过它可以储存多个成员资料。例如星期的成员总共有七个，而需要工作的星期就会有一个以上了，类似这样的需求就应该使用集合型态。以下列的「cmdev.settable」表格来说：

栏位名称	型态
workingday	SET('MON','TUE','WED','THU','FRI','SAT','SUN')

你可以使用一个字串值来管理集合型态栏位，在这个字串值中，使用逗号来隔开不同的成员字串：

集合型态栏位与列举型态栏位同样具有检查资料是否正确的能力：

列举型态栏位的成员编号使用简单的连续数字；集合型态栏位会比较复杂一些：

了解集合型态栏位的成员所代表的数字后，你就可以使用数值来管理储存的资料：

要使用数值来代表多个成员的时候，你只要把所有成员的数字加总起来就可以了：

列举与集合型态都可以设定需要的字元集与collat​​ion，以下列的「cmdev.estable」表格来说：

栏位名称	型态	字元集	Collat​​ion
enumsize	enum('XS', …)	latin1	latin1_general_ci
enumsize2	enum('XS', …)	latin1	latin1_general_cs
workingday	set('MON', …)	latin1	latin1_general_ci
workingday2	set('MON', …)	latin1	latin1_general_cs

字元集的设定可以决定可以储存字串资料的编码，而collat​​ion的设定会决定字串值是否区分大小写：

如果指定字串值的时候违反collat​​ion设定的大小写规则，就会发生错误：

2.5 日期与时间

MySQL提供下列几个可以储存日期与时间资料的栏位型态：

型态	Byte(s)	说明	范围
DATE	3	日期	'1000-01-01′~'9999-12-31′
TIME	3	时间	'-838:59:59′~'838:59:59′
DATETIME	8	日期与时间	'1000-01-01 00:00:00′~'9999-12-31 23:59:59′
YEAR[(4|2)]	1	西元年	1901~2155[YEAR(4)]1970~2069[YEAR(2)]
TIMESTAMP	4	日期与时间	'1970-01-01 00:00:00′~2037

日期(DATE)型态栏位可以储存年、月、日的资料，范围从「1000-01-01」到「9999-12-31」，你的日期资料不可以超过「9999-12-31」 ，可是你可以储存「1000-01-01」以前的日期，不过MySQL建议你最好不要这么作，不然可能会造成一些奇怪的问题。

因为日期中的西元年份可以使用四个或两个数字，使用两个数字的时候，「70」到「99」表示「1970」到「1999」；如果是「00」到「69」就是「2000 」到「2069」。所以要注意下列的情况：

另一个日期资料会变成这样：

时间(TIME)型态可以储存时、分、秒的资料，范围从「-838:59:59」到「838:59:59」。这个储存时间资料的范围可能会跟你想的不太一样。一般来说，时间资料指的是从「00:00:00」到「23:59:59」，也就是一天的时间。MySQL的时间型态栏位可以让你储存类似「经过的时间」这样的资料：

在指定一个时间资料的时候，你可以省略秒或分，省略的部份，MySQL都会帮你设定为「0」：

日期与时间(DATETIME)型态可以储存完整的年、月、日与时、分、秒资料，范围从「1000-01-01 00:00:00」到「9999-12-31 23:59: 59」。在表示一个日期与时间资料的时候，日期与时间之间，至少要使用一个空白隔开。时间部份的时、分、秒都可以省略，省略的部份，MySQL都会帮你设定为「0」：

如果只需要储存年份资料的话，你可以使用西元年(YEAR)型态，这样会节省很多储存空间。你可以视需要把西元年型态设定为两位或四位数字，四位数字可以储存的范围从「1901」到「2155」；两位数字的范围从「00」到「99」，实际的西元年份是「1970」到「2069」。

在指定一个年份资料给西元年型态栏位的时候，可以使用字串值或数值来表示西元年份，不同个数的资料会有不同的储存效果：

如果西元年型态栏位的值是「0」的话，MySQL会把它当成是一个不正确的西元年资料，所以你应该不会指定这样的资料，不过指定不同的资料也会有不同的储存结果：

「TIMESTAMP」型态的格式与「DATETIME」一样，都包含完整的年、月、日与时、分、秒资料，不过它使用的储存空间只有4bytes，是「DATETIME」型态的一半。

「TIMESTAMP」也是MySQL日期与时间型态中具有「时区」特性的型态。它可以储存从「1970-01-01 00:00:00」到目前经过的秒数。这个起始日期与时间使用「Coordinated Universal Time、UTC」世界标准时间为储存资料的依据，它与「Greenwich Mean Time、GMT」格林威治标准时间是一样的。

全世界分为许多不同时区(time zone)，所有时区都使用跟标准时间的差异来当作自己的标准时间。以台湾来说，你会在安装Windows平台的电脑中，经由控制台里的日期和时间，看到这个关于时区的设定：

MySQL资料库采用与作业系统同样的时区设定，所以在储存「TIMESTAMP」型态栏位的资料时，过程中会有一些计算的动作：

而查询「TIMESTAMP」型态栏位资料的时候，也会有这样的情况：

了解时区设定与「TIMESTAMP」型态的关系后，你就可以知道下列的动作为什么会发生错误了：

你可以使用查询叙述取得MySQL资料库伺服器关于时区的设定：

如果想要设定其它的时区，可以使用「+时时:分分」或「-时时:分分」的格式。例如日本东京时区比格林威治标准时间晚九小时，你可以设定为「+09:00」；而美国旧金山比格林威治标准时间早七小时，可以设定为「-07:00」：

设定新的时区以后，使用下列的范例测试「DATETIME」和「TIMESTAMP」两种型态，可以看出在储存日期时间资料上的差异：

因为「TIMESTAMP」型态储存的是格林威治标准时间，所以在修改时区后，查询得到的日期时间资料就会有差异了：