MySQL 超新手入門(9)表格與索引 by Michael | CodeData
top

MySQL 超新手入門(9)表格與索引

分享:

專欄作者新書出版:Android App程式開發剖析 第三版(適用Android 8 Oreo與Android Studio 3)

MySQL 超新手入門(8)儲存引擎與資料型態 << 前情

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)設定,這裡會先討論關於儲存引擎、字元集和collation的屬性設定。如果你在建立表格的時侯,沒有指定這些屬性,MySQL會使用伺服器預設的儲存引擎作為表格的儲存引擎,字元集與collation會使用資料庫預設的設定。

你可以針對表格的需求,設定它使用的儲存引擎、字元集與collation:

mysql_09_snap_06

下列的敘述在建立「addressbook」表格的時候,使用「ENGINE」、「CHARCTER SET」和「COLLATE」設定表格自己使用的儲存引擎、字元集與collation:

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

在建立表格時指定字元集與collation會有一些不同的組合。如果只有指定字元集,MySQL會使用你指定字元集的預設collation:

mysql_09_snap_11

如果只有使用「COLLATE」指定collation,MySQL會使用你指定collation所屬的字元集:

mysql_09_snap_12

註:建立表格的時候,不管你有沒有指定,表格都會有字元集與collation的設定。在這個表格中的「非二進位制、non-binary」字串型態欄位,還有「ENUM」與「SET」型態欄位,都會使用表格預設的字元集與collation。

1.2 字串欄位屬性

如果一個欄位的型態是字串的話,你還可以依照需求加入字串型態的欄位屬性(column attributes)。「非二進位制、non-binary」字串可以額外設定字元集與collation:

mysql_09_snap_13

每一個表格都會有一個預設的字元集與collation設定,如果沒有指定欄位的字元集與collation,就會使用預設的設定:

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使用查詢結果的欄位名稱與型態來建立新的表格
  • 如果沒有指定儲存引擎、字元集或collation的話,建立的新表格使用資料庫預設的儲存引擎、字元集與collation
  • 查詢表格中,欄位的索引與「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) 表格使用的collation

執行下列的查詢敘述就可以查詢表格詳細的資訊:

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

 

後續 >> MySQL 超新手入門(10)子查詢

分享:
按讚!加入 CodeData Facebook 粉絲群

相關文章

留言

留言請先。還沒帳號註冊也可以使用FacebookGoogle+登錄留言

Pharos04/29

非常感谢Michael老师慷慨赐教!请问在CREAT TABLE travelautoincr的例子中,为什么一个表中可以有多个primary key? 紧接着在INSERT INTO travelautoincr的例子中,为什么primary key的数值可以重复?

Michael, Fan Zhang08/05

1.6节中最后的例子应该是“(like tablename)” 而不是“{like table name}”?

kevin7001108/18

請教一下 我照你的步驟做到了
update tstable2 set temp=32 where area='south';
但是缺出現
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
是哪邊錯誤呢?
在前面很多用到update都是出現類似情形?
謝謝您>

mythnc09/11

1.4 通用欄位屬性的第一張圖,
primary key一個表格只能有一個,感覺意思有點不清楚
mysql能允許多個欄位為primary key

黃小天07/26

新版的MySQL好像允許create時使用多個current_timestamp了:
create table A(
created timestamp default current_time,
updated timestamp on update current_time,
temp int not null
)
在insert的時候直接使用default就行了:
insert A (created, temp) values (default, 20)

熱門論壇文章

熱門技術文章