next up previous contents
Next: 組合主鍵 Up: KEY 及 INDEX Previous: Index 簡介   Contents   DYWANG_HOME

Primary key 主鍵

  1. 登入 mariadb。
    [root@kvm8 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe
    mariadb 10.3.11-MariaDB
    mycli 1.20.1
    Chat: https://gitter.im/dbcli/mycli
    Mail: https://groups.google.com/forum/#!forum/mycli-users
    Home: http://mycli.net
    Thanks to the contributor - Lewis Peckover
    
  2. 使用資料庫 dyw。
    mariadb root@localhost:(none)> use dyw;                                         
    You are now connected to database "dyw" as user "root"
    Time: 0.001s
    
  3. 查看資料表。
    mariadb root@localhost:dyw> show tables;                                        
    +---------------+
    | Tables_in_dyw |
    +---------------+
    | dcount        |
    | tbl           |
    +---------------+
    2 rows in set
    Time: 0.022s
    
  4. 查詢資料表 dcount 沒有索引。
    mariadb root@localhost:dyw> show index from dcount\G                            
    
    mariadb root@localhost:dyw>
    
  5. 於資料表 dcount 欄位 author 加入主鍵
    mariadb root@localhost:dyw> alter table dcount add primary key(author);         
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.038s
    
  6. 查詢資料表 dcount 的索引,其中 Key_name: PRIMARY 表示主鍵,Non_qunique: 0 表示不重複。 Collation: How the column is sorted in the index. In MySQL, this can have values “A” (Ascending) or NULL (Not sorted).
    Cardinality: An estimate of the number of unique values in the index.
    mariadb root@localhost:dyw> show index from dcount\G                            
    ***************************[ 1. row ]***************************
    Table         | dcount
    Non_unique    | 0
    Key_name      | PRIMARY
    Seq_in_index  | 1
    Column_name   | author
    Collation     | A
    Cardinality   | 6
    Sub_part      | <null>
    Packed        | <null>
    Null          | 
    Index_type    | BTREE
    Comment       | 
    Index_comment | 
    
    1 row in set
    Time: 0.012s
    
  7. 刪除資料表 dcount 的主鍵,因每個資料表只能有一個主鍵,所以不用指定欄位。
    mariadb root@localhost:dyw> alter table dcount drop primary key;                
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 6 rows affected
    Time: 0.043s
    
  8. 查詢資料表 dcount 沒有索引。
    mariadb root@localhost:dyw> show index from dcount\G                            
    
    mariadb root@localhost:dyw>
    



De-Yu Wang 2020-04-07