目录
1 表格与储存引擎
表格(table)是数据库中用来储存纪录的基本单位，在建立一个新的数据库以后，你必须为这个数据库建立一些储存资料的表格：
每一个数据库都会使用一个资料夹，这些数据库资料夹用来储存所有数据库各自需要的档案：
“Storage engine、储存引擎”是MySQL用来储存资料的技术，为了数据库多样化的应用，你可以在建立表格的时候，依照自己的需求指定一种储存引擎，不同的储存引擎会有不同的资料储存方式与运作的特色。MySQL提供许多储存引擎让你选择，下列是主要的三种储存引擎的简介：
- MyISAM：MySQL默认的储存引擎，虽然它支援的功能并没有像一般的数据库那么多(例如交易、transaction)；不过也因为它比较简单，所以运作的效率相对也比较好
- InnoDB：这种储存引擎所提供的功能已经跟大型的商用数据库软件一样了，像是交易(transaction)、纪录锁定(row-level locking) 与自动回复(auto-recovery)。
- MEMORY：这是一个比较特殊的储存引擎，它把资料储存在纪忆体中，所以运作的效率是最快的；不过只要MySQL服务器关闭后，储存的资料就全部不见了。
1.1 MyISAM
“MyISAM”是MySQL默认的储存引擎，“默认”的意思是如果你在建立表格的时候没有指定一种储存引擎，MySQL会帮你建立的新表格指定为“MyISAM”储存引擎。以下列一个使用MyISAM储存引擎的数据库来说，在数据库资料夹中的档案会像这样：
当你建立一个表格以后，“MyISAM”储存引擎会建立以表格名称为档案名称的三个档案，以“city”表格来说：
使用“MyISAM”储存引擎的数据库具有“可携性、portable”的特色，你可以很容易的把一个数据库复制到另外一台电脑的MySQL服务器中：
注：使用“MyISAM”储存引擎时，MySQL并不会限制一个数据库中可以包含的表格数量。不过一个表格会在档案系统中建立三个档案，如果超过作业系统对于档案数量或容量的限制，你就不能再建立任何新的表格。
1.2 InnoDB
MySQL数据库服务器从3.23.49版本开始把“InnoDB”储存引擎列为正式支援的功能，所以从这个版本开始，MySQL也提供与大型商用数据库软件一样的功能。最主要的功能是支援“交易、transaction”，在比较复杂的数据库应用系统中，很常遇到这样的情况：
在顺利的情况下，当然不会有任何问题。可是如果发生下列的情况：
这样的情况是一定要避免，否则数据库中储存的资料就会出现很大的问题了。所以一般的大型商用数据库都会使用交易的功能来处理这样的情况：
“InnoDB”储存引擎除了提供许多功能外，与“MyISAM”储存引擎最大的差异是档案的储存方式：
“InnoDB”储存引擎实际储存在档案系统中的档案会像这样：
注：因为使用“InnoDB”储存引擎的表格会使用同一个储存空间，所以不同数据库的表格资料也会储存在一起。“InnoDB”储存引擎限制在这个共用的储存空间中不能超过两百万个表格。
1.3 MEMORY
“MEMORY”储存引擎与其它储存引擎有一个主要的差异，就是它会把纪录与索引资料储存在内存中。所以使用“MEMORY”储存引擎的表格，不论在查询或维护资料时的效率都是很好的。在档案系统中储存的档案只有“frm”档，也就是储存表格结构资讯的档案：
注：因为“MEMORY”储存引擎会把纪录与索引资料储存在内存中，所以只要MySQL服务器关闭、重新启动、当机，所有使用“MEMORY”储存引擎的表格资料都会全部消失，只剩下表格结构；它也不适合储存大量资料的表格，会耗用太多内存
1.4 储存引擎与作业系统
虽然MySQL数据库是一个独立运作的软件，不过它还是得安装在某一个作业系统中，例如Windows或Linux。而由作业系统控制的档案系统可能会有许多限制，例如档案的数量和档案的大小。如果MySQL数据库软件在建立或使用数据库档案的时候，超过作业系统的限制，就会发生错误。
如果以支援的功能来决定储存引擎的话，那就会比较明确。如果要以作业系统的限制来决定储存引擎的话，你可以参考下列的作法：
- 使用“MyISAM”储存引擎可以避免违反档案大小的限制
- 使用“InnoDB”储存引擎可以避免违反档案数量的限制
- 如果在档案数量与大小的限制都遇到问题的话，你只好增加硬件和修改作业系统在档案系统上的设定
2 字段资料型态
在建立表格时，你会帮每一个字段指定适合的“资料型态、data type”。正确的选择字段资料型态，除了可以帮你储存正确的资料外，还可以让数据库使用最少的内存与储存空间，这样会让数据库运作的效率更好一些。资料型态主要分为下列三大类：
- 数值：任何包含正、负号的整数与小数资料；另外还有位元(bit)的数值资料，它使用二进制来表示一个数字。
- 字串：包含non-binary与binary两种字串值，non-binary字串值是一些使用字符集与collation的字符(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”。非二进位制就是储存一般文字的字串，会有特定的字符集与collation；二进位制使用字节储存资料，不包含字符集与collation，所以大多用来储存图片或音乐这类资料。“非二进位制、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”的字串都会包含特定的字符集与collation，所以可以用来储存各种不同国家的文字。不同的字符集会占用不同的储存空间，以下列的“cmdev.nonbinarytable2”表格来说：
| 字段名称 | 型态 | 字符集 |
|---|---|---|
| s | VARCHAR(6) | latin1 |
| s2 | VARCHAR(6) | big5 |
| s3 | VARCHAR(6) | utf8 |
上列的表格中，三个字段分别设定为“latin1”、“big5”与“utf8”字符集，你可以查询MySQL数据库支援的字符集特性，“MAXLEN”字段是关于储存空间的资讯：
使用在“LENGTH”函式来查询储存在这个表格中的字串资料，就可以很明显的看出不同的字符集，在储存字符时使用的储存空间：
“LENGTH”函式会传回字串资料实际的储存长度(byte)；如果你要查询字串的字符数量的话，就要使用“CHAR_LENGTH”函式：
字符集会影响字串的储存空间，collation会影响字串排列顺序。以下列的“cmdev.nonbinarytable3”表格来说：
| 字段名称 | 型态 | 字符集 | Collation |
|---|---|---|---|
| s | VARCHAR(6) | latin1 | latin1_general_ci |
| s2 | VARCHAR(6) | latin1 | latin1_general_cs |
上列表格中字段的字符集都指定为“latin1”，不过“s”字段的collation设定为“latin1_general_ci”，表示排序时不区分大小写；“s2”字段设定为“latin1_general_cs”，表示排序时会区分大小写。以下列储存在这个表格中纪录来说：
Collation设定中的“latin1_general_ci”，最后的“ci”表示“case insensitive”，是不分大小写的意思。在这样的设定下，MySQL会把字串“ABC”和“abc”当成是一样的；“latin1_general_cs”，最后的“cs”表示“case sensitive”，是区分大小写的意思。在这样的设定下，MySQL就会把字串“ABC”和“abc”当成是不一样的字串。
是否区分大小写的collation设定会影响排序的结果：
另外一个影响是条件的判断：
“二进位制、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”的字串型态都不可以指定字符集与collation，不过你可以使用它们来储存任何语言的文字，也可以储存类似音乐或图片资料，因为MySQL都是一个一个byte的把资料储存到数据库中；所以在执行查询时的排序和条件设定，都是以使用字节为单位来判断。
2.4 列举与集合
列举(ENUM)与集合(SET)是一种特殊的“非二进位制、non-binary”字串型态，所以它们也可以指定字符集与collation。下列是这两种型态的说明：
| 型态 | 最大个数 | 储存空间 | 说明 |
|---|---|---|---|
| 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’) |
你可以使用一个字串值来管理集合型态字段，在这个字串值中，使用逗号来隔开不同的成员字串：
集合型态字段与列举型态字段同样具有检查资料是否正确的能力：
列举型态字段的成员编号使用简单的连续数字；集合型态字段会比较复杂一些：
了解集合型态字段的成员所代表的数字后，你就可以使用数值来管理储存的资料：
要使用数值来代表多个成员的时候，你只要把所有成员的数字加总起来就可以了：
列举与集合型态都可以设定需要的字符集与collation，以下列的“cmdev.estable”表格来说：
| 字段名称 | 型态 | 字符集 | Collation |
|---|---|---|---|
| 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 |
字符集的设定可以决定可以储存字串资料的编码，而collation的设定会决定字串值是否区分大小写：
如果指定字串值的时候违反collation设定的大小写规则，就会发生错误：
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”型态储存的是格林威治标准时间，所以在修改时区后，查询得到的日期时间资料就会有差异了：
