SET DEFAULT 設定預設值

1. NOT NULL DEFAULT 設定資料表 tbl 欄位 bi 不可是空的且指定預設值為 100，出現以下錯誤。

   MariaDB root@(none):dyw> alter table tbl modify bi bigint not null default 100;
   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@(none):dyw> update tbl set bi=10;
   You're about to run a destructive command.
   Do you want to proceed? (y/n): y
   Your call!
   Query OK, 4 rows affected
   Time: 0.011s
   

3. 再次設定資料表 tbl 欄位 bi 不可是空的且指定預設值為 100。

   MariaDB root@(none):dyw> alter table tbl modify bi bigint not null default 100;
   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.055s
   

4. 再查看資料表 tbl 的欄位格式。

   MariaDB root@(none):dyw> desc 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.026s
   

5. ALTER SET DEFAULT 變更資料表 tbl 欄位 bi 的預設值為 50。

   MariaDB root@(none):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.012s
   

6. 再查看資料表 tbl 的欄位格式。

   MariaDB root@(none):dyw> desc 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@(none):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.008s
   

8. 再查看資料表 tbl 的欄位格式。

   MariaDB root@(none):dyw> desc 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.022s