• Tags ,         
  • 2018-10-10  16:11:44        
  • 7 °C    

    1 建立表格

    在建立好资料库以后,就可以根据储存资料的需求,使用SQL叙述建立所有需要的表格(table)。建立表格的设定非常多,以建立「world.city」表格来说,它的叙述会像这样:

    mysql_09_snap_01

    根据不同的需求,建立表格的语法有好几种,下列是建立表格基本的语法:

    mysql_09_snap_02

    MySQL规定一个表格中至少要有一个栏位,在设定表格中的栏位时,至少要明确的决定栏位的名称与型态,其它的栏位设定都是选择性的,如果有一个以上栏位,要使用逗号隔开:

    mysql_09_snap_03

    使用需要的资料型态,就可以建立一个可以储存亲友通讯录的表格:

    mysql_09_snap_04

    建立表格的时候可以使用「IF NOT EXISTS」选项,预防发生表格已存在的错误:

    mysql_09_snap_05

    1.1 表格属性

    建立表格的时候也可以为表格加入需要的表格属性(table attributes)设定,这里会先讨论关于储存引擎、字元集和collat​​ion的属性设定。如果你在建立表格的时侯,没有指定这些属性,MySQL会使用伺服器预设的储存引擎作为表格的储存引擎,字元集与collat​​ion会使用资料库预设的设定。

    你可以针对表格的需求,设定它使用的储存引擎、字元集与collat​​ion:

    mysql_09_snap_06

    下列的叙述在建立「addressbook」表格的时候,使用「ENGINE」、「CHARCTER SET」和「COLLATE」设定表格自己使用的储存引擎、字元集与collat​​ion:

    mysql_09_snap_07

    注:根据语法的说明,「CHARCTER SET」也可以使用比较简短的「CHARSET」;另外在设定时都可以省略「=」。

    MySQL资料库伺服器支援许多不同应用的储存引擎,你可以使用「SHOW ENGINES」查询:

    mysql_09_snap_08

    在建立表格的时候,如果没有使用「ENGINE」设定储存引擎,那就会使用MySQL资料库伺服器预设的储存引擎。你可以使用下列的方式修改MySQL资料库伺服器预设的储存引擎设定:

    • 修改设定档:MySQL资料库伺服器在启动时会读取一个名称为「my.ini」的设定档,档案中有许多启动资料库伺服器时需要的资讯。其中就包含预设的储存引擎设定,你可以修改这个设定后再重新启动资料库伺服器,让新的设定生效:

    mysql_09_snap_09

    • 设定储存引擎:你也可以使用「SET」叙述设定预设的储存引擎:

    mysql_09_snap_10

    在建立表格时指定字元集与collat​​ion会有一些不同的组合。如果只有指定字元集,MySQL会使用你指定字元集的预设collat​​ion:

    mysql_09_snap_11

    如果只有使用「COLLATE」指定collat​​ion,MySQL会使用你指定collat​​ion所属的字元集:

    mysql_09_snap_12

    注:建立表格的时候,不管你有没有指定,表格都会有字元集与collat​​ion的设定。在这个表格中的「非二进位制、non-binary」字串型态栏位,还有「ENUM」与「SET」型态栏位,都会使用表格预设的字元集与collat​​ion。

    1.2 字串栏位属性

    如果一个栏位的型态是字串的话,你还可以依照需求加入字串型态的栏位属性(column attributes)。「非二进位制、non-binary」字串可以额外设定字元集与collat​​ion:

    mysql_09_snap_13

    每一个表格都会有一个预设的字元集与collat​​ion设定,如果没有指定栏位的字元集与collat​​ion,就会使用预设的设定:

    mysql_09_snap_14

    1.3 数值栏位属性

    数值型态栏位专用的属性设定有「UNSIGNED」、「ZEROFILL」与「AUTO_INCREMENT」:

    mysql_09_snap_15

    注:数值型态栏位设定为「UNSIGNED」与「ZEROFILL」的效果在「第八章、表格与索引、建立表格、数值栏位属性」中已经讨论过;而「AUTO_INCREMENT」的设定与索引有关,所以在这一章后面索引的部份一起讨论。

    1.4 通用栏位属性

    除了字串与数值两种栏位专用的栏位属性设定外,还有许多可以用在所有型态的栏位属性:

    mysql_09_snap_16

    「NOT NULL」栏位属性可以用来禁止某个栏位储存「NULL」值,一般来说,「NULL」值用来表示一个栏位的资料是「不确定」、「未知」或「没有」 。不过有一些栏位并不能出现「NULL」值,不然就会成为一笔很奇怪的纪录了:

    mysql_09_snap_17

    使用「NULL」或「NOT NULL」设定栏位属性后,在查询表格栏位资讯时,是在「Null」栏位用「YES」或「NO」来表示:

    mysql_09_snap_18

    如果一个表格中,有设定为「NOT NULL」的栏位,那就要注意你在新增或修改纪录时指定的资料,不能够违反这些规则:

    mysql_09_snap_19

    使用「DEFAULT」关键字可以设定栏位的预设值,你可以自己指定任何想要的预设值,在新增或修改资料的时候都有可能会使用到栏位的预设值。要特别注意的是MySQL限制你的预设值只能是「一个明确的值」,也就是预设值的设定不可以使用任何函式或运算式。

    如果你没有为栏位使用「DEFAULT」关键字设定预设值,而且也没有设定为「NOT NULL」,MySQL会自动为你加入预设值的设定:

    mysql_09_snap_20

    以通讯录表格来说,如果纪录的地址大部份都是「Taipei」的话,你可以为「address」栏位设定一个预设值:

    mysql_09_snap_22

    使用「DEFAULT」关键字加入预设值的设定以后,就可以在新增或修改资料的时候使用:

    预设值的设定要注意下列的规则:

    • 「BLOB」与「TEXT」栏位型态不可以使用[DEFAULT]关键字指定预设值,其它的栏位型态都可以
    • 不能与其它的栏位设定造成冲突。例如一个设定为「NOT NULL」的栏位,却使用「DEFAULT NULL」设定预设值为「NULL」
    • 指定的预设值要符合栏位型态。例如「DATE」型态栏位使用「DEFAULT 'Hello!'」指定预设值

    注:「UNIQUE KEY」与「PRIMARY KEY」在这一章后面索引的部份一起讨论。

    1.5 TIMESTAMP栏位型态与预设值

    「TIMESTAMP」栏位是日期时间资料的一种,它除了具有「时区、timezone」的特性外,也可以搭配「DEFAULT」和「ON UPDATE」来完成一些比较特殊的需求:

    mysql_09_snap_23

    在表格中使用「TIMESTAMP」型态的栏位时,如果你没有设定它们的栏位属性,MySQL会自动帮你在第一个「TIMESTAMP」栏位加入「NOT NULL」、「DEFAULT」和「ON UPDATE」三个栏位属性的设定。

    • 「NOT NULL」不允许你储存「NULL」值
    • 「DEFAULT CURRENT_TIMESTAMP」设定预设值为目前的日期时间。在所有栏位型态中,只有「TIMESTAMP」可以使用「CURRENT_TIMESTAMP」指定预设值;其它的栏位型态,在指定预设值只能是「一个明确的值」
    • 「ON UPDATE」可以指定在修改纪录的时候,MySQL自动帮你填入的资料

    其它没有设定栏位属性的「TIMESTAMP」栏位,MySQL会帮你加入「NOT NULL」与「DEFAULT」两个栏位属性。

    「DEFAULT CURRENT_TIMESTAMP」栏位属性的效果,在你新增纪录的时候就可以看得出来了:

    mysql_09_snap_24

    而「ON UPDATE CURRENT_TIMESTAMP」栏位属性,会在修改纪录的时候产生效果:

    mysql_09_snap_25

    「TIMESTAMP」栏位型态很适合用来记录资料新增或修改的日期与时间。可是如果在同一笔纪录中,要使用一个栏位记录新增资料的日期与时间,而使用另一个栏位记录修改资料的日期与时间。为了应付这样的需求,你应该会使用下列的栏位定义:

    mysql_09_snap_26

    在一个表格中,MySQL限制「CURRENT_TIMESTAMP」只能在一个栏位出现,所以当有这样的需求出现时,你必须使用MySQL提供给你的特殊设定方式来解决:

    mysql_09_snap_27

    建立好这样的表格以后,看起来虽然怪怪的,不过当你指定「created」栏位的值为「NULL」的时候,MySQL会自动为你填入目前的日期与时间:

    mysql_09_snap_28

    后续在修改资料的时候,就只会在「updated」栏位填入目前的日期与时间:

    mysql_09_snap_29

    1.6 使用其它表格建立一个新表格

    在资料库中建立需要的表格,通常是使用上列讨论的方式,根据自己的需求,建立一个新的表格来储存需要保存的资料;在一些比较特别的情况,你可能会使用一个现有的表格来建立新的表格,这样的需求可以使用下列的语法:

    mysql_09_snap_30

    以「world」资料库中的「city」来说,下列的查询叙述可以传回台湾的城市与人口数:

    mysql_09_snap_31

    如果你想要建立一个新表格,这个表格中的资料就是上列查询的结果,就可以使用这种建立表格的语法:

    mysql_09_snap_32

    使用这种语法建立的新表格,可以省略栏位定义的工作,新表格会使用原有表格的栏位名称与定义,而且在查询叙述中传回的资料,会直接新增到新建立的表格中:

    mysql_09_snap_33

    你也可以在建立新表格的时候,使用栏位定义来设定新表格的栏位型态与其它属性:

    mysql_09_snap_34

    如果需要的话,也可以加入查询叙述中没有的栏位:

    mysql_09_snap_35

    使用这种语法建立表格时有下列几个重点:

    • MySQL使用查询结果的栏位名称与型态来建立新的表格
    • 如果没有指定储存引擎、字元集或collat​​ion的话,建立的新表格使用资料库预设的储存引擎、字元集与collat​​ion
    • 查询表格中,栏位的索引与「AUTO_INCREMENT」设定都会被忽略

    如果只需要借用一个已经存在的表格栏位定义,可是并不需要纪录资料的话,你可以使用下列的语法来建立新表格:

    mysql_09_snap_36

    使用这种语法建立的新表格,并不会新增纪录到新表格中,可是包含索引与「AUTO_INCREMENT」设定都会套用在新表格,除了下列两个例外:

    • 使用「MyISAM」储存引擎时,你可以在建立表格的时候使用「DATA DIRECTORY」与「INDEX DIRECTORY」指定资料与索引档案的资料夹位置;建立的新表格会忽略这些设定,而使用资料库预设的资料夹
    • 栏位的「FOREIGN KEY」与表格的「REFERENCES」属性设定都会被忽略

    1.7 建立暂存表格

    上列讨论的建立表格方式,都可以在建立表格的时候,依照需要加入「TEMPORARY」关键字,指定这个新建立的表格为「用户端暂时存在」的表格:

    mysql_09_snap_37

    「TEMPORARY」表格有下列重点:

    • 「TEMPORARY」表格是每一个用户端专属的表格,用户端离线后,MySQL就会自动删除这些表格
    • 因为「TEMPORARY」表格是用户端专属的表格,其它用户端不能使用,所以不同的用户端,使用同样名称建立「TEMPORARY」表格也没有关系
    • 「TEMPORARY」表格名称可以跟资料库中的表格名称一样,不过在「TEMPORARY」表格存在的时候,资料库中的表格会被隐藏起来
    • 可以使用「ALTER TABLE」修改「TEMPORARY」表格名称,不可以使用「RENAME TABLE」修改「TEMPORARY」表格名称

    2 修改表格

    使用「CREATE TABLE」叙述建立表格以后,如果发现某个栏位或设定打错,或是在使用一阵子以后,发觉表格中有一些设定不太对。在这些情况下,你可以使用「ALTER TABLE」叙述来修改一个表格的结构:

    mysql_09_snap_38

    2.1 增加栏位

    你可以使用下列的修改定义增加一个本来没有的栏位:

    mysql_09_snap_39

    如果你在增加栏位的时候,没有指定新增栏位的位置,MySQL会把这个栏位放在最后一个:

    mysql_09_snap_40

    你可以搭配使用「FIRST」关键字,把新增的栏位放在第一个:

    mysql_09_snap_41

    或是使用「AFTER」关键字,指定新增的栏位要放在哪一个栏位后面:

    mysql_09_snap_42

    如果需要增加多个栏位的话,也可以使用下列的语法一次把需要新增的栏位,全部加到表格中;不过这种语法加入的新栏位,都会放在最后面的位置:

    mysql_09_snap_43

    注:「ALTER TABLE」叙述也可以用来增加索引,在这一章后面索引的部份一起讨论。

    2.2 修改栏位

    如果需要修改栏位的名称、型态、大小范围或其它栏位属性,你可以使用下列两种修改定义来执行修改的工作。「CHANGE」可以修改栏位的名称与定义,「MODIFY」只能修改栏位的定义,不能修改栏位名称:

    mysql_09_snap_44

    以下列使用「CHANGE」关键字修改表格的叙述来说,它将「one」栏位的名称修改为「changecolumn」,型态从「INT」修改为「BIGINT」,而且把修改后的栏位位置放在「two」栏位后面:

    mysql_09_snap_45

    下列使用「MODIFY」关键字修改表格的叙述,它将「two」栏位的型态从「INT」修改为「BIGINT」,而且把修改后的栏位位置放在「three」栏位后面:

    mysql_09_snap_46

    2.3 删除栏位

    如果要删除一个表格中不需要的栏位,可以使用下列的修改定义:

    mysql_09_snap_47

    下列格的叙述会删除「two」栏位:

    mysql_09_snap_48

    2.4 修改表格名称

    如果需要修改表格的名称,你可以使用下列两种叙述,包含在「ALTER TABLE」叙述中使用修改表格名称的修改定义;或是使用「RENAME TABLE」叙述:

    mysql_09_snap_49

    下列两个叙述都可以把「mytable」表格名称修改为「mynewtable」:

    mysql_09_snap_50

    3 删除表格

    你可以使用下列的叙述删除一个不需要的表格:

    mysql_09_snap_51

    注:使用「DROP TABLE」叙述执行删除表格的工作时,MySQL并不会再次跟你确认是否真的要删除,而是真的就直接删除了,表格储存的纪录资料当然也不见了。

    4 索引介绍

    资料库与表格是MySQL资料库的基本元件,依照需求建立好的资料库与表格后,就可以使用它们来为你保存资料。一个设计良好的资料库,不论是资料的正确性,还有后续的维护与查询都比较不会发生问题。除了好好规划与建立资料库与表格外,你还可以利用「索引、index」预防你的资料出现问题,尤其是表格储存非常大量的纪录时,建立适当的索引,可以增加查询与维护资料的效率。

    以「MyISAM」储存引擎来说,资料表的储存的纪录资料,是储存在电脑中的一个档案:

    mysql_09_snap_52

    当你执行一个像这样的查询叙述时:

    mysql_09_snap_53

    资料库要找到你需要查询或维护的纪录,如果没有索引帮助的话,就会从头开始一边读取,一边判断是否有符合条件的资料。你可以为表格建立索引来改善这种比较没有效率的方式:

    mysql_09_snap_54

    建立城市名称的索引档以后,同样执行下列的查询叙述,MySQL会自动使用索引来快速找到你需要的资料:

    mysql_09_snap_55

    注:索引同样可以增加删除或修改的效率。

    索引分为主索引键(primary key)、唯一索引(unique index)与非唯一索引(non-unique index)三种。

    主索引键的应用很常见,而且一个表格通常会有一个,而且只能有一个。在一个表格中,设定为主索引键的栏位值不可以重复,而且不可以储存「NULL」值。因为这样的限制,所以很适合使用在类似编码、代号或身份证字号这类栏位。

    唯一索引也称为「不可重复索引」,在一个表格中,设定为唯一索引的栏位值不可以重复,但是可以储存「NULL」值。这种索引适合用在类似员工资料表格中储存电子邮件帐号的栏位,因为员工不一定有电子邮件帐号,所以允许储存「NULL」值,可以每一个员工的电子邮件帐号都不可以重复。

    上列两种索引都可以预防储存的资料发生重复的问题,也可以增加查询与维护资料的效率。非唯一索引就只是用来增加查询与维护资料效率的索引。设定为非唯一索引的栏位值可以重复,也可以储存「NULL」值。

    5 建立索引

    MySQL提供许多不同的方式让你建立需要的索引。通常在规划一个资料库的时候,会把表格所需要的索引一并规划好,在这样的情况下,你可以把建立索引的定义,加在「CREATE TABLE」叙述中,建立表格的时候就一起把索引建立好;不过也有可能在使用表格一阵子以后,才发觉有建立索引的需求,在这样的情况下,你可以使用「ALTER TABLE」或「CREATE INDEX」建立需要的索引。

    5.1 在建立表格的时候建立索引

    在建立表格的叙述中,你会定义出许多表格所需要的栏位,在栏位的定义中,除了名称、型态与属性,还可以加入「唯一索引」与「主索引键」的定义:

    mysql_09_snap_56

    以下列这个建立储存联络簿的表格来说,你可以使用这样的语法在「id」栏位后面加入「PRIMARY KEY」,指定「id」栏位为主索引键,这表示「id」栏位的值不可以重复,而且不可以储存「NULL」值;另外在「email」栏位加入「UNIQUE KEY」,指定「email」栏位为唯一索引,这表示「email」栏位的值不可以重复:

    mysql_09_snap_57

    下列是另外一种在「CREATE TABLE」叙述中建立索引的语法:

    mysql_09_snap_58

    同样以建立储存联络簿的表格来说,下列两种建立索引语法的效果是一样的:

    mysql_09_snap_59

    如果你要建立一般索引(可以重复的索引),或是要建立包含多个栏位的索引时,就一定要把建立索引的定义加在所有栏位定义后面:

    mysql_09_snap_60

    在建立索引的时候,你可以指定某一个栏位为建立索引的栏位,不过有时候你只想要为一个字串型态栏位的部份资料建立索引,或是指定建立的索引资料,是要依照由小到大,还是由大到小排列。有这样的需求时,你可以依照下列的语法来指定:

    mysql_09_snap_61

    以建立联络簿的表格来说,为地址资料「address」栏位建立索引的时候,如果你希望建立地址前五个字元的索引资料,而且依照由大到小的顺序。下列的叙述就可以建立这样的索引:

    mysql_09_snap_62

    注:只有「CHAR」、「VARCHAR」、「BINARY」与「VARBINARY」型态的栏位可以指定制作索引的长度。「ASC」或「DESC」可以使用在任何型态的栏位。

    如果一个表格使用的储存引擎是「MEMORY」的话,建立索引的时候还可以额外指定索引使用的「演算法、algorithm」。使用其它储存引擎的表格,MySQL会忽略这个设定。索引使用的演算法有「BTREE」与「HASH」两种,你可以使用下列的语法来指定索引使用的演算法:

    mysql_09_snap_63

    预设的「HASH」演算法适合用在主索引键和唯一索引,这种演算法在搜寻不能重复的资料时,效率会比较好;而「BTREE」演算法适合用在可以允许重复资料的一般索引,在搜寻上会比「HASH」有更好的效率。

    注:「FULLTEXT」索引只能用在「CHAR」、「VARCHAR」与「TEXT」型态的栏位,而且表格使用的储存引擎必须是「MyISAM」。「SPATIAL」索引是「SPATIAL」型态栏位专用的,而且表格使用的储存引擎必须是「MyISAM」。这两种索引不会在这里讨论。

    5.2 在修改表格的时候建立索引

    如果你想要为一个已经存在的表格建立索引的话,你可以在修改表格「ALTER TABLE」中建立索引:

    mysql_09_snap_64

    以下列的范例来说,在建立联络簿表格时没有建立索引,你可以使用「ALTER TABLE」叙述建立需要的索引,不过一个「ALTER TABLE」叙述只能建立一个索引:

    mysql_09_snap_67

    5.3 使用「CREATE INDEX」建立索引

    需要为一个已经存在的表格建立索引,除了使用「ALTER TABLE」叙述建立索引外,还可以使用「CREATE INDEX」叙述建立唯一索引与一般索引:

    mysql_09_snap_66

    使用「CREATE INDEX」叙述只能建立唯一索引与一般索引,你还是要使用「ALTER TABLE」叙述建立主索引键:

    mysql_09_snap_67

    为一个已经存在的表格建立索引时,要特别注意主索引键与唯一索引这两种索引。如果这个表格没有任何纪录资料的话,那就不会有问题;可是如果表格中已经有纪录了,而且你想要建立一个主索引键时,有可能会发生下列的错误:

    mysql_09_snap_94

    为一个已经存在、而且已经有纪录的表格建立唯一索引时,也有可能会发生下列的错误:

    mysql_09_snap_95

    6 索引的名称

    在「CREATE TABLE」或是「ALTER TABLE」叙述中建立索引的话,你可以为建立的索引取一个名称:

    mysql_09_snap_68

    如果你在使用上列的语法建立索引的时候没有指定索引名称,MySQL会帮你取一个,索引的名称就是栏位名称,如果是多个栏位的索引,就会使用第一个栏位当作索引名称。

    使用「CREATE INDEX」建立索引的时候,就一定要指定一个索引名称:

    mysql_09_snap_69

    注:在一般的操作中,你并不会用到索引名称;不过在删除索引的时候就会用到。

    7 删除索引

    如果一个已经建立好的索引已经不需要了,为了节省储存的空间,你可以使用下列的语法删除索引:

    mysql_09_snap_70

    下列的叙述使用修改表格「ALTER TABLE」叙述删除不需要的索引:

    mysql_09_snap_71

    你也可以使用下列的「DROP INDEX」叙述删除不需要的索引:

    mysql_09_snap_72

    使用「ALTER TABLE」叙述可以一次删除多个索引,「DROP INDEX」叙述一次只能删除一个索引:

    mysql_09_snap_73

    8 数值栏位型态与AUTO_INCREMENT

    在资料库的应用中,很常会遇到为纪录「编流水号」的需求,如果资料表中的每一笔纪录都需要一个递增的数值编号,你可以选择整数型态的栏位后,再使用「AUTO_INCREMENT」栏位属性:

    mysql_09_snap_74

    如果一个公司想要储存员工开会的资料,你可以在建立开会资料表格的时候,为这个表格定义一个储存开会编号的栏位,这个栏位需要自动递增,而且会为它建立主索引键:

    mysql_09_snap_75

    建立开会资料表格以后,另外建立一个储存参加会议的员工资料表格:

    mysql_09_snap_76

    设定为「AUTO_INCREMENT」的整数栏位,在新增资料的时候可以不用指定数值,MySQL会为你自动编制一个流水号并储存在纪录中;而接着要新增参加这次开会的员工资料到「participate 」表格时,你需要用到MySQL刚才会为你在「meeting」表格中自动编制的流水号,这样的需求可以使用「LAST_INSERT_ID()」函式来取得:

    mysql_09_snap_77

    新增这些开会与参加会议的员工资料后,就可以使用结合查询来查询开会资料了:

    mysql_09_snap_78

    在新增资料时,要让MySQL为你自动编制一个流水号,并储存到纪录中的方式有​​下列几种:

    mysql_09_snap_79

    MySQL是一个可以让多人同时使用的资料库,使用「LAST_INSERT_ID()」函式来取得自动编制的流水号数值,并不会因为不同的用户端同时使用而造成混乱:

    mysql_09_snap_80

    「AUTO_INCREMENT」栏位的一般用法通常是用来储存从「1」开始的流水号,每一笔新增的纪录都会自动加一成为新的编号。可是如果在新增纪录的时候,自己指定「AUTO_INCREMENT」栏位一个数值,就会造成下列的情况:

    mysql_09_snap_81

    「AUTO_INCREMENT」栏位在你删除纪录以后,也不会帮你重新使用已经用过的编号:

    mysql_09_snap_82

    注:使用「TRUNCATE TABLE」叙述删除包含「AUTO_INCREMENT」栏位表格的所有纪录,编号会重新从头开始。

    不要指定值,或是指定「NULL」值给「AUTO_INCREMENT」栏位,都可以让MySQL为你自动编制一个流水号,并储存到纪录中,这两种也是比较好的方式;另外指定「AUTO_INCREMENT」栏位值为「0」的方式也可以,不过会因为MySQL资料库伺服器的环境设定而有不同的效果:

    mysql_09_snap_83

    如果你需要编制的流水号范围是非常大的,你应该选择「AUTO_INCREMENT」栏位的型态为「BIGINT」;MySQL另外提供一个「SERIAL」关键字,让你在定义这种栏位时可以比较方便一些:

    mysql_09_snap_84

    使用「MyISAM」储存引擎的表格,可以使用下列这种比较特殊的「AUTO_INCREMENT」栏位:

    mysql_09_snap_85

    这样的设定同样是请MySQL为你自动编制流水号,不过因为「AUTO_INCREMENT」栏位包含在主索引键中,编制流水号的动作会不太一样:

    mysql_09_snap_86

    注:在上列的范例中,是把「empno,location,counter」设定为主索引键;如果设定为唯一索引的话,也会有一样的效果;设定为一般索引的话,会造成错误。

    使用「AUTO_INCREMENT」栏位属性有下列几个重点:

    • 一个表格只能有一个「AUTO_INCREMENT」栏位,而且要为它建立一个索引,而且通常是建立主索引键或唯一索引,这样可以防止重复的编号;不过MySQL也允许你建立可重复的索引
    • 只有整数型态才可以使用「AUTO_INCREMENT」栏位属性,你可以根据编号大小的需求,选择使用「TINYINT」、「SMALLINT」、「MEDIUMINT」、「INT」或「BIGINT」,而且因为只会使用到正数,所以你可以加入「UNSIGNED」来增加编号的范围
    • 如果编号已经到栏位型态的最大范围,例如一个「SMALLINT」型态,而且是指定为「UNSIGNED」的「AUTO_INCREMENT」栏位,编号已经到「65535」了,如果再执行新增的叙述,就会造成「Duplicate entry '65535′ for key '栏位名称'」的错误

    9 查询表格与索引资讯

    一个资料库在建立许多表格与索引以后,不论是程式开发或是资料库管理人员,都会有查询表格与索引相关资料的需求。例如查询一个表格中有哪些栏位,还有栏位的型态与属性的设定;也可能需要查询某一个表格建立了哪些索引与设定的资讯。

    9.1 表格相关资讯

    想要知道一个资料库中有哪一些表格,可以执行下列的叙述:

    mysql_09_snap_87

    这个叙述可以使用「字串样式」设定表格名称的条件:

    mysql_09_snap_88

    MySQL资料库在启动以后,会有一个很特别的资料库,名称是「information_schema」,这个资料库通常会称为「系统资讯资料库」。这个资料库中有一个表格叫作「TABLES」,它储存所有MySQL资料库中的表格相关资讯,「TABLES」表格有下列主要的栏位:

    栏位名称 型态 说明
    TABLE_SCHEMA varchar(64) 资料库名称
    TABLE_NAME varchar(64) 表格名称
    ENGINE varchar(64) 使用的储存引擎名称
    TABLE_ROWS bigint(21) unsigned 纪录数量
    AUTO_INCREMENT bigint(21) unsigned 如果包含「AUTO_INCREMENT」栏位的话,这个栏位会储存下一个编号
    TABLE_COLLATION varchar(32) 表格使用的collat​​ion

    执行下列的查询叙述就可以查询表格详细的资讯:

    mysql_09_snap_89

    MySQL也提供下列的叙述让你查询一个表格的定义:

    mysql_09_snap_90

    下列的叙述可以查询建立表格的「CREATE TABLE」叙述:

    mysql_09_snap_91

    回传的「Create Table」栏位的内容就是一个建立表格的叙述:

    mysql_09_snap_92

    9.2 索引相关资讯

    MySQL提供「SHOW INDEX」叙述查询一个表格的索引详细资讯,下列是执行这个叙述以后,传回的主要栏位资料:

    栏位名称 说明
    Table 表格名称
    Non_unique 「0」表示不可重复;「1」可以重复
    Key_name 索引名称
    Seq_in_index 单一栏位的索引为「1」;多个栏位的索引表示建立索引的栏位顺序
    Column_name 索引栏位名称
    Sub_part 如果是指定长度的索引,这里会显示长度;不是的话显示「NULL」
    Null 是否允许「NULL」值
    Index_type 索引种类,「BTREE」或「HASH」

    你可以在「SHOW INDEX FROM」后面指定一个表格名称,执行以后就可以查询这个表格所有的索引资讯:

    mysql_09_snap_93

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