MySQL :: MySQL 8.4 參考手冊 :: 15.1.2 ALTER DATABASE 陳述式

版本 8.4

8.0 日語

MySQL 8.4 參考手冊 / ... / ALTER DATABASE 語句

15.1.2 ALTER DATABASE 語句

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
  | READ ONLY [=] {DEFAULT | 0 | 1}
}

ALTER DATABASE 讓您能夠變更資料庫的整體特性。這些特性儲存在資料字典中。此語句需要資料庫上的 ALTER 權限。ALTER SCHEMA 是 ALTER DATABASE 的同義詞。

如果省略資料庫名稱，則該語句會應用於預設資料庫。在這種情況下，如果沒有預設資料庫，則會發生錯誤。

對於從語句中省略的任何 alter_option，資料庫會保留其目前的選項值，但變更字元集可能會變更校對規則，反之亦然。

字元集和校對規則選項

CHARACTER SET 選項會變更預設資料庫字元集。COLLATE 選項會變更預設資料庫校對規則。如需字元集和校對規則名稱的相關資訊，請參閱第 12 章，字元集、校對規則、Unicode。

若要查看可用的字元集和校對規則，請分別使用 SHOW CHARACTER SET 和 SHOW COLLATION 語句。請參閱第 15.7.7.4 節，「SHOW CHARACTER SET 語句」和第 15.7.7.5 節，「SHOW COLLATION 語句」。

當建立常式時，使用資料庫預設值的儲存常式會將這些預設值納入其定義的一部分。（在儲存常式中，具有字元資料類型的變數如果未明確指定字元集或校對規則，則會使用資料庫預設值。請參閱第 15.1.17 節，「CREATE PROCEDURE 和 CREATE FUNCTION 語句」。）如果您變更資料庫的預設字元集或校對規則，則任何要使用新預設值的儲存常式都必須捨棄並重新建立。

加密選項

ENCRYPTION 選項會定義預設資料庫加密，資料庫中建立的資料表會繼承此加密。允許的值為 'Y' (啟用加密) 和 'N' (停用加密)。

無法將 mysql 系統結構描述設定為預設加密。其中的現有資料表是整體 mysql 資料表空間的一部分，可以加密。information_schema 只包含檢視。無法在其中建立任何資料表。磁碟上沒有任何需要加密的內容。performance_schema 中的所有資料表都使用 PERFORMANCE_SCHEMA 引擎，此引擎純粹在記憶體中運作。無法在其中建立任何其他資料表。磁碟上沒有任何需要加密的內容。

只有新建立的資料表才會繼承預設資料庫加密。對於與資料庫關聯的現有資料表，其加密會保持不變。如果啟用 table_encryption_privilege_check 系統變數，則需要 TABLE_ENCRYPTION_ADMIN 權限才能指定與 default_table_encryption 系統變數的值不同的預設加密設定。如需詳細資訊，請參閱定義結構描述和一般資料表空間的加密預設值。

唯讀選項

READ ONLY 選項會控制是否允許修改資料庫及其中的物件。允許的值為 DEFAULT 或 0 (非唯讀) 和 1 (唯讀)。此選項對於資料庫移轉很有用，因為啟用 READ ONLY 的資料庫可以移轉至另一個 MySQL 執行個體，而不必擔心資料庫在作業期間可能會變更。

對於 NDB Cluster，在一個 mysqld 伺服器上將資料庫設為唯讀會同步到同一個叢集中的其他 mysqld 伺服器，因此資料庫會在所有 mysqld 伺服器上變成唯讀。

如果啟用 READ ONLY 選項，則此選項會顯示在 INFORMATION_SCHEMA SCHEMATA_EXTENSIONS 資料表中。請參閱第 28.3.32 節，「INFORMATION_SCHEMA SCHEMATA_EXTENSIONS 資料表」。

無法為下列系統結構描述啟用 READ ONLY 選項：mysql、information_schema、performance_schema。

在 ALTER DATABASE 語句中，READ ONLY 選項會與其自身的其他執行個體和其他選項互動，如下所示

如果多個 READ ONLY 執行個體發生衝突 (例如，READ ONLY = 1 READ ONLY = 0)，則會發生錯誤。
即使對於唯讀資料庫，也允許包含僅 (不衝突的) READ ONLY 選項的 ALTER DATABASE 語句。
如果資料庫在語句之前或之後的唯讀狀態允許修改，則允許將 (不衝突的) READ ONLY 選項與其他選項混合使用。如果語句之前和之後的唯讀狀態都禁止變更，則會發生錯誤。
無論資料庫是否為唯讀，此語句都會成功
```
ALTER DATABASE mydb READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;
```
如果資料庫不是唯讀，此語句會成功，但如果資料庫已為唯讀，則會失敗
```
ALTER DATABASE mydb READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;
```

啟用 READ ONLY 會影響資料庫的所有使用者，但下列例外情況不受唯讀檢查的限制

伺服器在伺服器初始化、重新啟動、升級或複寫時執行的語句。
由 init_file 系統變數在伺服器啟動時命名的檔案中的語句。
TEMPORARY 資料表；可以在唯讀資料庫中建立、變更、捨棄和寫入 TEMPORARY 資料表。
NDB Cluster 非 SQL 插入和更新。

除了剛才列出的例外作業外，啟用 READ ONLY 會禁止對資料庫及其物件 (包括定義、資料和中繼資料) 執行寫入作業。下列清單詳細說明受影響的 SQL 語句和作業

資料庫本身
- CREATE DATABASE
- ALTER DATABASE (變更 READ ONLY 選項除外)
- DROP DATABASE
檢視
- CREATE VIEW
- ALTER VIEW
- DROP VIEW
- 從呼叫具有副作用的函式的檢視中選取。
- 更新可更新的檢視。
- 如果影響唯讀資料庫中檢視的中繼資料 (例如，讓檢視有效或無效)，則會拒絕在可寫入資料庫中建立或捨棄物件的語句。
儲存常式
- CREATE PROCEDURE
- DROP PROCEDURE
- CALL (具有副作用的程序)
- CREATE FUNCTION
- DROP FUNCTION
- SELECT (具有副作用的函式)
- 對於程序和函式，唯讀檢查會遵循預先鎖定行為。對於 CALL 語句，唯讀檢查會以每個語句為基礎執行，因此如果某些有條件執行的語句寫入唯讀資料庫，而該語句實際上並未執行，則呼叫仍會成功。另一方面，對於在 SELECT 中呼叫的函式，函式主體的執行會以預先鎖定模式發生。只要函式中的某些語句寫入唯讀資料庫，函式的執行就會失敗並顯示錯誤，無論該語句是否實際執行。
觸發程序
- CREATE TRIGGER
- DROP TRIGGER
- 觸發程序呼叫。
事件
- CREATE EVENT
- ALTER EVENT
- DROP EVENT
- 事件執行
  - 在資料庫中執行事件會失敗，因為這會變更上次執行時間戳記，這是儲存在資料字典中的事件中繼資料。事件執行失敗也會導致事件排程器停止。
  - 如果事件寫入唯讀資料庫中的物件，則事件的執行會失敗並顯示錯誤，但事件排程器不會停止。
資料表
- CREATE TABLE
- ALTER TABLE
- CREATE INDEX
- DROP INDEX
- RENAME TABLE
- TRUNCATE TABLE
- DROP TABLE
- DELETE
- INSERT
- IMPORT TABLE
- LOAD DATA
- LOAD XML
- REPLACE
- UPDATE
- 對於子資料表位於唯讀資料庫中的串聯外部索引鍵，即使子資料表未直接受到影響，也會拒絕對父項的更新和刪除。
- 對於 MERGE 資料表 (例如 CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=...)，適用下列行為
  - 如果至少 s1、s2、s3 其中之一為唯讀，則無論插入方法為何，插入到 MERGE 資料表 (INSERT into s1.t) 都會失敗。即使實際上最終會插入到可寫入資料表中，也會拒絕插入。
  - 只要 s1 不是唯讀的，刪除 MERGE 表格 (DROP TABLE s1.t) 就會成功。允許刪除參考唯讀資料庫的 MERGE 表格。

ALTER DATABASE 陳述式會封鎖，直到所有已存取正在變更的資料庫中物件的並行交易都已提交。相反地，在並行 ALTER DATABASE 中存取正在變更的資料庫中物件的寫入交易，會封鎖直到 ALTER DATABASE 已提交。

如果使用 Clone 外掛程式來複製本機或遠端資料目錄，則複製中的資料庫會保留其在來源資料目錄中的唯讀狀態。唯讀狀態不影響複製過程本身。如果不想在複製中有相同的資料庫唯讀狀態，則必須在複製過程完成後，使用複製上的 ALTER DATABASE 操作來明確變更此選項。

當從捐贈者複製到接收者時，如果接收者具有唯讀的使用者資料庫，則複製會失敗並顯示錯誤訊息。在將資料庫設為可寫入後，可以重試複製。

READ ONLY 允許用於 ALTER DATABASE，但不允許用於 CREATE DATABASE。但是，對於唯讀資料庫，SHOW CREATE DATABASE 產生的陳述式會在註解中包含 READ ONLY=1，以指示其唯讀狀態。

mysql> ALTER DATABASE mydb READ ONLY = 1;
mysql> SHOW CREATE DATABASE mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb`
                 /*!40100 DEFAULT CHARACTER SET utf8mb4
                          COLLATE utf8mb4_0900_ai_ci */
                 /*!80016 DEFAULT ENCRYPTION='N' */
                 /* READ ONLY = 1 */

如果伺服器執行包含此類註解的 CREATE DATABASE 陳述式，則伺服器會忽略該註解，並且不會處理 READ ONLY 選項。這對於 mysqldump 有影響，mysqldump 使用 SHOW CREATE DATABASE 在傾印輸出中產生 CREATE DATABASE 陳述式。

在傾印檔案中，唯讀資料庫的 CREATE DATABASE 陳述式包含註解的 READ ONLY 選項。
傾印檔案可以照常還原，但是由於伺服器會忽略註解的 READ ONLY 選項，因此還原的資料庫不是唯讀的。如果要還原資料庫後設為唯讀，則必須手動執行 ALTER DATABASE 來使其成為唯讀。

假設 mydb 是唯讀的，並且您按如下方式傾印它

$> mysqldump --databases mydb > mydb.sql

稍後還原操作必須接著使用 ALTER DATABASE，如果 mydb 仍然應該是唯讀的。

$> mysql
mysql> SOURCE mydb.sql;
mysql> ALTER DATABASE mydb READ ONLY = 1;

MySQL Enterprise Backup 不受此問題的影響。它可以備份和還原唯讀資料庫，就像其他任何資料庫一樣，但是如果在備份時啟用了 READ ONLY 選項，則會在還原時啟用該選項。

ALTER DATABASE 會寫入二進制日誌，因此在複製來源伺服器上對 READ ONLY 選項的變更也會影響複本。為了防止這種情況發生，在執行 ALTER DATABASE 陳述式之前，必須停用二進制日誌。例如，為了準備在不影響複本的情況下遷移資料庫，請執行以下操作

在單一工作階段中，停用二進制日誌並為資料庫啟用 READ ONLY
```
mysql> SET sql_log_bin = OFF;
mysql> ALTER DATABASE mydb READ ONLY = 1;
```

傾印資料庫，例如使用 mysqldump

$> mysqldump --databases mydb > mydb.sql

在單一工作階段中，停用二進制日誌並為資料庫停用 READ ONLY
```
mysql> SET sql_log_bin = OFF;
mysql> ALTER DATABASE mydb READ ONLY = 0;
```

PREV HOME UP NEXT