next up previous contents
Next: RENAME 重新命名 Up: ALTER 語法 Previous: DROP 刪除   Contents   DYWANG_HOME

SET DEFAULT 設定預設值

  1. NOT NULL DEFAULT 設定資料表 tbl 欄位 bi 不可是空的且指定預設值為 100,出現以下錯誤。
    mariadb root@localhost:dyw> alter table tbl modify bi bigint not null default 10
                                0;                                                  
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    (1265, "Data truncated for column 'bi' at row 1")
    
  2. 原因為資料表 tbl,有紀錄的此欄位為空值,必須先將所有紀錄的 bi 欄位填入資料,此例中將所有紀錄的 bi 欄位設定為 10。
    mariadb root@localhost:dyw> update tbl set bi=10;                               
    Query OK, 4 rows affected
    Time: 0.006s
    
  3. 再次設定資料表 tbl 欄位 bi 不可是空的且指定預設值為 100。
    mariadb root@localhost:dyw> alter table tbl modify bi bigint not null default 10
                                0;                                                  
    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.071s
    
  4. 再查看資料表 tbl 的欄位格式。
    mariadb root@localhost:dyw> show columns from tbl;                              
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | bi              | bigint(20)   | NO   |     | 100     |                |
    | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
    | title           | varchar(100) | NO   |     | <null>  |                |
    | author          | varchar(40)  | NO   |     | <null>  |                |
    | submission_date | date         | YES  |     | <null>  |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    5 rows in set
    Time: 0.027s
    
  5. ALTER SET DEFAULT 變更資料表 tbl 欄位 bi 的預設值為 50。
    mariadb root@localhost:dyw> alter table tbl alter bi set default 50;            
    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.003s
    
  6. 再查看資料表 tbl 的欄位格式。
    mariadb root@localhost:dyw> show columns from tbl;                              
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | bi              | bigint(20)   | NO   |     | 50      |                |
    | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
    | title           | varchar(100) | NO   |     | <null>  |                |
    | author          | varchar(40)  | NO   |     | <null>  |                |
    | submission_date | date         | YES  |     | <null>  |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    5 rows in set
    Time: 0.022s
    
  7. ALTER DROP DEFAULT 變更資料表 tbl 欄位 bi 的預設值。
    mariadb root@localhost:dyw> alter table tbl alter bi drop default;              
    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.007s
    
  8. 再查看資料表 tbl 的欄位格式。
    mariadb root@localhost:dyw> show columns from tbl;                              
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | bi              | bigint(20)   | NO   |     | <null>  |                |
    | id              | int(11)      | NO   | PRI | <null>  | auto_increment |
    | title           | varchar(100) | NO   |     | <null>  |                |
    | author          | varchar(40)  | NO   |     | <null>  |                |
    | submission_date | date         | YES  |     | <null>  |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    5 rows in set
    Time: 0.027s
    



De-Yu Wang 2020-04-07