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 集群，将一个 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 集群非 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 提交之前一直阻塞。

如果使用 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，它使用 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;
```