MySQL :: MySQL 8.4 Reference Manual :: 15.1.2 ALTER DATABASE Statement

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / ALTER DATABASE Statement

15.1.2 更改数据库语句

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的数据库可以在操作过程中不担心被修改。

在 NDB 集群中，将一个mysqld服务器设置为只读，使得该数据库在同一集群中的所有mysqld服务器上都变成只读。

如果启用了READ ONLY选项，它将在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），将发生错误。
ALTER 数据库 语句，只包含非冲突的READ ONLY 选项，即使是只读数据库，也允许执行。
非冲突的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 表。
NDB 集群非 SQL 插入和更新操作。

启用READ ONLY 除了上述列举的操作外，禁止对数据库及其对象（包括定义、数据和元数据）的写操作。以下是受影响的 SQL 语句和操作：

数据库本身：
- CREATE 数据库
- 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
- 在外键级联关系中，如果子表位于只读数据库，父表的更新和删除操作将被拒绝，即使子表本身不受影响。
- 对于像CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=...这样一个MERGE表：
  - 将数据插入到MERGE表(INSERT into s1.t)失败，如果至少有一个s1、s2、s3是只读的，插入操作将被拒绝，即使实际上将数据插入到可写表中。
  - 删除MERGE表(DROP TABLE s1.t)成功，只要s1不是只读的。允许删除指向只读数据库的MERGE表。

ALTER DATABASE 语句直到所有已经访问了要被修改的数据库对象的并发事务都提交后才继续执行。反之，一个写事务访问要被修改的数据库在并发ALTER DATABASE时，直到ALTER DATABASE 执行完成后才继续执行。

如果使用克隆插件将本地或远程数据目录克隆到clone，clone中的数据库保持源数据目录中读取状态。读取状态不影响克隆过程。如果不想在clone中保留相同的数据库读取状态，必须在克隆完成后，使用ALTER DATABASE操作来更改clone的选项。

从捐赠者到受益者克隆时，如果受益者的用户数据库是只读，克隆将失败并显示错误信息。可以在数据库变为可写后重新尝试克隆。

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语句在dump输出中：

在dump文件中，只读数据库的CREATE DATABASE语句包含注释的READ ONLY选项。
可以按照通常的方式恢复dump文件，但是因为服务器忽略了注释的READ ONLY选项，恢复后的数据库不只读。如果要使数据库在恢复后仍然只读，必须手动执行ALTER DATABASE。

假设mydb是只读的，然后dump它如下：

$> mysqldump --databases mydb > mydb.sql

恢复操作后，如果mydb仍然要只读，必须执行ALTER DATABASE：

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

MySQL企业备份工具不受这个问题的影响。它像其他数据库一样备份和恢复只读数据库，但是在恢复时如果备份时启用了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