MySQL :: MySQL 9.0 参考手册 :: 15.1.2 ALTER DATABASE 语句

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 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，它使用 SHOW CREATE DATABASE 来在转储输出中生成 CREATE DATABASE 语句

在转储文件中，只读数据库的 CREATE DATABASE 语句包含已注释的 READ ONLY 选项。
转储文件可以照常恢复，但由于服务器会忽略已注释的 READ ONLY 选项，因此恢复的数据库不是只读的。如果数据库在恢复后要设置为只读，则必须手动执行 ALTER DATABASE 来实现这一点。

假设 mydb 是只读的，并且您按如下方式转储它：

$> mysqldump --databases mydb > mydb.sql

如果 mydb 应该仍然是只读的，那么稍后的还原操作必须在 ALTER DATABASE 之后执行。

$> 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;
```

上一个首页上级下一个