• Tags ,
  •         
  • www.breakyizhan.com
  •    

    1 表格与储存引擎

    表格(table)是资料库中用来储存纪录的基本单位,在建立一个新的资料库以后,你必须为这个资料库建立一些储存资料的表格:

    mysql_08_snap_01

    每一个资料库都会使用一个资料夹,这些资料库资料夹用来储存所有资料库各自需要的档案:

    mysql_08_snap_02

    「Storage engine、储存引擎」是MySQL用来储存资料的技术,为了资料库多样化的应用,你可以在建立表格的时候,依照自己的需求指定一种储存引擎,不同的储存引擎会有不同的资料储存方式与运作的特色。MySQL提供许多储存引擎让你选择,下列是主要的三种储存引擎的简介:

    • MyISAM:MySQL预设的储存引擎,虽然它支援的功能并没有像一般的资料库那么多(例如交易、transaction);不过也因为它比较简单,所以运作的效率相对也比较好
    • InnoDB:这种储存引擎所提供的功能已经跟大型的商用资料库软体一样了,像是交易(transaction)、纪录锁定(row-level locking) 与自动回复(auto-recovery)。
    • MEMORY:这是一个比较特殊的储存引擎,它把资料储存在纪忆体中,所以运作的效率是最快的;不过只要MySQL伺服器关闭后,储存的资料就全部不见了。

    1.1 MyISAM

    「MyISAM」是MySQL预设的储存引擎,「预设」的意思是如果你在建立表格的时候没有指定一种储存引擎,MySQL会帮你建立的新表格指定为「MyISAM」储存引擎。以下列一个使用MyISAM储存引擎的资料库来说,在资料库资料夹中的档案会像这样:

    mysql_08_snap_03

    当你建立一个表格以后,「MyISAM」储存引擎会建立以表格名称为档案名称的三个档案,以「city」表格来说:

    mysql_08_snap_04

    使用「MyISAM」储存引擎的资料库具有「可携性、portable」的特色,你可以很容易的把一个资料库复制到另外一台电脑的MySQL伺服器中:

    mysql_08_snap_05

    注:使用「MyISAM」储存引擎时,MySQL并不会限制一个资料库中可以包含的表格数量。不过一个表格会在档案系统中建立三个档案,如果超过作业系统对于档案数量或容量的限制,你就不能再建立任何新的表格。

    1.2 InnoDB

    MySQL资料库伺服器从3.23.49版本开始把「InnoDB」储存引擎列为正式支援的功能,所以从这个版本开始,MySQL也提供与大型商用资料库软体一样的功能。最主要的功能是支援「交易、transaction」,在比较复杂的资料库应用系统中,很常遇到这样的情况:

    mysql_08_snap_06

    在顺利的情况下,当然不会有任何问题。可是如果发生下列的情况:

    mysql_08_snap_07

    这样的情况是一定要避免,否则资料库中储存的资料就会出现很大的问题了。所以一般的大型商用资料库都会使用交易的功能来处理这样的情况:

    mysql_08_snap_08

    「InnoDB」储存引擎除了提供许多功能外,与「MyISAM」储存引擎最大的差异是档案的储存方式:

    mysql_08_snap_09

    「InnoDB」储存引擎实际储存在档案系统中的档案会像这样:

    mysql_08_snap_10

    注:因为使用「InnoDB」储存引擎的表格会使用同一个储存空间,所以不同资料库的表格资料也会储存在一起。「InnoDB」储存引擎限制在这个共用的储存空间中不能超过两百万个表格。

    1.3 MEMORY

    「MEMORY」储存引擎与其它储存引擎有一个主要的差异,就是它会把纪录与索引资料储存在记忆体中。所以使用「MEMORY」储存引擎的表格,不论在查询或维护资料时的效率都是很好的。在档案系统中储存的档案只有「frm」档,也就是储存表格结构资讯的档案:

    mysql_08_snap_11

    注:因为「MEMORY」储存引擎会把纪录与索引资料储存在记忆体中,所以只要MySQL伺服器关闭、重新启动、当机,所有使用「MEMORY」储存引擎的表格资料都会全部消失,只剩下表格结构;它也不适合储存大量资料的表格,会耗用太多记忆体

    1.4 储存引擎与作业系统

    虽然MySQL资料库是一个独立运作的软体,不过它还是得安装在某一个作业系统中,例如Windows或Linux。而由作业系统控制的档案系统可能会有许多限制,例如档案的数量和档案的大小。如果MySQL资料库软体在建立或使用资料库档案的时候,超过作业系统的限制,就会发生错误。

    如果以支援的功能来决定储存引擎的话,那就会比较明确。如果要以作业系统的限制来决定储存引擎的话,你可以参考下列的作法:

    • 使用「MyISAM」储存引擎可以避免违反档案大小的限制
    • 使用「InnoDB」储存引擎可以避免违反档案数量的限制
    • 如果在档案数量与大小的限制都遇到问题的话,你只好增加硬体和修改作业系统在档案系统上的设定

    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」型态来说:

    mysql_08_snap_12

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

    mysql_08_snap_13

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

    mysql_08_snap_14

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

    型态 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」都可以依照自己的需要设定长度与位数:

    mysql_08_snap_15

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

    • 不可以超过最大长度
    • 小数位数不可以超过长度
    • 长度与小数位数一样的时候,表示只可以储存小数,例如「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_08_snap_16

    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_08_snap_17

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

    mysql_08_snap_18

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

    mysql_08_snap_19

    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」:

    mysql_08_snap_20

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

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

    mysql_08_snap_21

    2.2 位元

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

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

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

    mysql_08_snap_22

    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」字串型态,它们在储存字串资料的时候会不太一样:

    mysql_08_snap_23

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

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

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

    mysql_08_snap_24

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

    mysql_08_snap_25

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

    mysql_08_snap_26

    字元集会影响字串的储存空间,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」,表示排序时会区分大小写。以下列储存在这个表格中纪录来说:

    mysql_08_snap_27

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

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

    mysql_08_snap_28

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

    mysql_08_snap_29

    「二进位制、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的数字:

    mysql_08_snap_30

    所有「二进位制、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」表格来说:

    mysql_08_snap_31

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

    mysql_08_snap_32

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

    mysql_08_snap_33

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

    mysql_08_snap_34

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

    mysql_08_snap_35

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

    mysql_08_snap_36

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

    mysql_08_snap_37

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

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

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

    mysql_08_snap_38

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

    mysql_08_snap_39

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

    mysql_08_snap_40

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

    mysql_08_snap_41

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

    mysql_08_snap_42

    列举与集合型态都可以设定需要的字元集与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的设定会决定字串值是否区分大小写:

    mysql_08_snap_43

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

    mysql_08_snap_44

    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」。所以要注意下列的情况:

    mysql_08_snap_45

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

    mysql_08_snap_46

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

    mysql_08_snap_47

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

    mysql_08_snap_48

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

    mysql_08_snap_49

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

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

    mysql_08_snap_50

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

    mysql_08_snap_51

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

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

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

    mysql_08_snap_60

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

    mysql_08_snap_61

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

    mysql_08_snap_62

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

    mysql_08_snap_55

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

    mysql_08_snap_56

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

    mysql_08_snap_57

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

    mysql_08_snap_58

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

    mysql_08_snap_59

     
    转载请保留页面地址:https://www.breakyizhan.com/sql/5923.html