文档首页
MySQL 8.4 参考手册
8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.1.2 ALTER DATABASE 语句15.1.2 ALTER DATABASE Statement

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 DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the data dictionary. This statement requires the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.

如果省略数据库名称，该语句将应用于默认数据库。在这种情况下，如果没有默认数据库，将会发生错误。If the database name is omitted, the statement applies to the default database. In that case, an error occurs if there is no default database.

对于语句中省略的任何alter_option，数据库将保留其当前的选项值，但更改字符集可能会更改排序规则，反之亦然。For any alter_option omitted from the statement, the database retains its current option value, with the exception that changing the character set may change the collation and vice versa.

字符集和排序规则选项Character Set and Collation Options

CHARACTER SET 选项更改默认数据库字符集。COLLATE 选项更改默认数据库排序规则。有关字符集和排序规则名称的信息，请参阅 第 12 章，字符集、排序规则、Unicode。The CHARACTER SET option changes the default database character set. The COLLATE option changes the default database collation. For information about character set and collation names, see Chapter 12, Character Sets, Collations, Unicode.

要查看可用的字符集和排序规则，请分别使用 SHOW CHARACTER SET 和 SHOW COLLATION 语句。请参见 第 15.7.7.4 节，“SHOW CHARACTER SET 语句”，以及 第 15.7.7.5 节，“SHOW COLLATION 语句”。To see the available character sets and collations, use the SHOW CHARACTER SET and SHOW COLLATION statements, respectively. See Section 15.7.7.4, “SHOW CHARACTER SET Statement”, and Section 15.7.7.5, “SHOW COLLATION Statement”.

在创建时使用数据库默认值的存储例程会将这些默认值作为其定义的一部分。(在存储例程中，如果未明确指定字符集或校对，字符数据类型的变量将使用数据库默认值。参见第 15.1.17 节，“CREATE PROCEDURE 和 CREATE FUNCTION 语句”。) 如果更改数据库的默认字符集或校对，任何要使用新默认值的存储例程都必须被删除并重新创建。A stored routine that uses the database defaults when the routine is created includes those defaults as part of its definition. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly. See Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.) If you change the default character set or collation for a database, any stored routines that are to use the new defaults must be dropped and recreated.

加密选项Encryption Option

ENCRYPTION 选项定义默认的数据库加密，数据库中创建的表将继承该设置。允许的值为 'Y'（启用加密）和 'N'（禁用加密）。The ENCRYPTION option, defines the default database encryption, which is inherited by tables created in the database. The permitted values are 'Y' (encryption enabled) and 'N' (encryption disabled).

mysql 系统模式不能设置为默认加密。其内部现有的表属于通用 mysql 表空间，可能会被加密。information_schema 只包含视图，无法在其中创建任何表。磁盘上没有可以加密的内容。performance_schema 中的所有表都使用 PERFORMANCE_SCHEMA 引擎，该引擎完全驻留在内存中。无法在其中创建其他表。磁盘上没有可以加密的内容。The mysql system schema cannot be set to default encryption. The existing tables within it are part of the general mysql tablespace, which may be encrypted. The information_schema contains only views. It is not possible to create any tables within it. There is nothing on the disk to encrypt. All tables in the performance_schema use the PERFORMANCE_SCHEMA engine, which is purely in-memory. It is not possible to create any other tables in it. There is nothing on the disk to encrypt.

只有新创建的表才会继承默认的数据库加密。对于与数据库关联的现有表，其加密保持不变。如果启用了 table_encryption_privilege_check 系统变量，则需要 TABLE_ENCRYPTION_ADMIN 权限才能指定与 default_table_encryption 系统变量值不同的默认加密设置。有关更多信息，请参见 为模式和通用表空间定义加密默认值。Only newly created tables inherit the default database encryption. For existing tables associated with the database, their encryption remains unchanged. If the table_encryption_privilege_check system variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to specify a default encryption setting that differs from the value of the default_table_encryption system variable. For more information, see Defining an Encryption Default for Schemas and General Tablespaces.

只读选项Read Only Option

READ ONLY 选项控制是否允许修改数据库及其内部的对象。允许的值为 DEFAULT 或 0（非只读）以及 1（只读）。此选项对数据库迁移非常有用，因为启用了 READ ONLY 的数据库可以迁移到另一个 MySQL 实例，而不必担心在操作过程中数据库可能被更改。The READ ONLY option controls whether to permit modification of the database and objects within it. The permitted values are DEFAULT or 0 (not read only) and 1 (read only). This option is useful for database migration because a database for which READ ONLY is enabled can be migrated to another MySQL instance without concern that the database might be changed during the operation.

使用 NDB 集群，在一个 mysqld 服务器上将数据库设为只读会同步到同一集群中的其他 mysqld 服务器，从而使数据库在所有 mysqld 服务器上都变为只读。With NDB Cluster, making a database read only on one mysqld server is synchronized to other mysqld servers in the same cluster, so that the database becomes read only on all mysqld servers.

如果启用了 READ ONLY 选项，它会显示在 INFORMATION_SCHEMA SCHEMATA_EXTENSIONS 表中。参见 第 28.3.32 节，“INFORMATION_SCHEMA SCHEMATA_EXTENSIONS 表”。The READ ONLY option, if enabled, is displayed in the INFORMATION_SCHEMA SCHEMATA_EXTENSIONS table. See Section 28.3.32, “The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table”.

以下系统模式无法启用 READ ONLY 选项：mysql、information_schema、performance_schema。The READ ONLY option cannot be enabled for these system schemas: mysql, information_schema, performance_schema.

在 ALTER DATABASE 语句中，READ ONLY 选项与其自身的其他实例以及其他选项的交互方式如下：In ALTER DATABASE statements, the READ ONLY option interacts with other instances of itself and with other options as follows:

  • 如果多个 READ ONLY 实例发生冲突（例如，READ ONLY = 1 READ ONLY = 0），将会发生错误。An error occurs if multiple instances of READ ONLY conflict (for example, READ ONLY = 1 READ ONLY = 0).

  • 即使对于只读数据库，只包含（不冲突的）ALTER DATABASE 选项的 READ ONLY 语句也是允许的。An ALTER DATABASE statement that contains only (nonconflicting) READ ONLY options is permitted even for a read-only database.

  • 如果数据库在执行语句之前或之后的只读状态允许修改，则允许将（不冲突的）READ ONLY选项与其他选项混合使用。如果在执行语句之前和之后的只读状态都禁止更改，则会发生错误。A mix of (nonconflicting) READ ONLY options with other options is permitted if the read-only state of the database either before or after the statement permits modifications. If the read-only state both before and after prohibits changes, an error occurs.

    无论数据库是否只读，此语句都会成功：This statement succeeds whether or not the database is read only:

    ALTER DATABASE mydb READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;

    如果数据库不是只读，此语句会成功，但如果数据库已经是只读的，则会失败：This statement succeeds if the database is not read only, but fails if it is already read only:

    ALTER DATABASE mydb READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;

启用 READ ONLY 会影响数据库的所有用户，但以下例外不受只读检查限制：Enabling READ ONLY affects all users of the database, with these exceptions that are not subject to read-only checks:

  • 由服务器在服务器初始化、重启、升级或复制过程中执行的语句。Statements executed by the server as part of server initialization, restart, upgrade, or replication.

  • 在服务器启动时，由系统变量 init_file 指定名称的文件中的语句。Statements in a file named at server startup by the init_file system variable.

  • TEMPORARY 表；即使在只读数据库中，也可以创建、修改、删除和写入 TEMPORARY 表。TEMPORARY tables; it is possible to create, alter, drop, and write to TEMPORARY tables in a read-only database.

  • NDB 集群非 SQL 插入和更新。NDB Cluster non-SQL inserts and updates.

除了刚才列出的例外操作之外，启用READ ONLY会禁止对数据库及其对象进行写操作，包括它们的定义、数据和元数据。以下列表详细说明了受影响的 SQL 语句和操作：Other than for the excepted operations just listed, enabling READ ONLY prohibits write operations to the database and its objects, including their definitions, data, and metadata. The following list details affected SQL statements and operations:

  • 数据库本身：The database itself:

  • 视图：Views:

    • CREATE VIEWCREATE VIEW

    • ALTER VIEWALTER VIEW

    • DROP VIEWDROP VIEW

    • 从调用具有副作用的函数的视图中进行选择。Selecting from views that invoke functions with side effects.

    • 更新可更新的视图。Updating updatable views.

    • 如果在可写数据库中创建或删除对象的语句影响只读数据库中视图的元数据（例如，使视图有效或无效），则会被拒绝。Statements that create or drop objects in a writable database are rejected if they affect metadata of a view in a read-only database (for example, by making the view valid or invalid).

  • 存储例程：Stored routines:

    • CREATE PROCEDURECREATE PROCEDURE

    • DROP PROCEDUREDROP PROCEDURE

    • CALL（具有副作用的过程）CALL (of procedures with side effects)

    • CREATE FUNCTIONCREATE FUNCTION

    • DROP FUNCTIONDROP FUNCTION

    • SELECT（具有副作用的函数）SELECT (of functions with side effects)

    • 对于存储过程和函数，只有只读检查遵循预锁定行为。对于 CALL 语句，只读检查是基于每条语句进行的，因此如果某些有条件执行的语句写入只读数据库但实际上未执行，调用仍然成功。另一方面，对于在 SELECT 中调用的函数，函数体的执行发生在预锁定模式下。只要函数中的某条语句写入只读数据库，无论该语句是否实际执行，函数的执行都会以错误告终。For procedures and functions, read-only checks follow prelocking behavior. For CALL statements, read-only checks are done on a per-statement basis, so if some conditionally executed statement writing to a read-only database does not actually execute, the call still succeeds. On the other hand, for a function called within a SELECT, execution of the function body happens in prelocked mode. As long as a some statement within the function writes to a read-only database, execution of the function fails with an error regardless of whether the statement actually executes.

  • 触发器：Triggers:

  • 事件：Events:

    • CREATE EVENTCREATE EVENT

    • ALTER EVENTALTER EVENT

    • DROP EVENTDROP EVENT

    • 事件执行：Event execution:

      • 在数据库中执行事件失败，因为这将更改最后执行时间戳，这是存储在数据字典中的事件元数据。事件执行失败还会导致事件调度程序停止。Executing an event in the database fails because that would change the last-execution timestamp, which is event metadata stored in the data dictionary. Failure of event execution also has the effect of causing the event scheduler to stop.

      • 如果事件向只读数据库中的对象写入数据，事件执行会失败并出现错误，但事件调度程序不会停止。If an event writes to an object in a read-only database, execution of the event fails with an error, but the event scheduler is not stopped.

  • 表：Tables:

    • CREATE TABLECREATE TABLE

    • ALTER TABLEALTER TABLE

    • CREATE INDEXCREATE INDEX

    • DROP INDEXDROP INDEX

    • RENAME TABLERENAME TABLE

    • TRUNCATE TABLETRUNCATE TABLE

    • DROP TABLEDROP TABLE

    • DELETEDELETE

    • INSERTINSERT

    • IMPORT TABLEIMPORT TABLE

    • LOAD DATALOAD DATA

    • LOAD XMLLOAD XML

    • REPLACEREPLACE

    • UPDATEUPDATE

    • 对于级联外键，如果子表在只读数据库中，即使子表没有直接受到影响，对父表的更新和删除也会被拒绝。For cascading foreign keys where the child table is in a read-only database, updates and deletes on the parent are rejected even if the child table is not directly affected.

    • 对于像 MERGE 这样的 CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=... 表，适用以下行为：For a MERGE table such as CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=..., the following behavior applies:

      • 如果 s1、s2、s3 中至少有一个为只读，无论插入方法如何，向 MERGE 表 (INSERT into s1.t) 插入数据都会失败。即使最终数据实际上会写入可写表，插入仍会被拒绝。Inserting into the MERGE table (INSERT into s1.t) fails if at least one of s1, s2, s3 is read only, regardless of insert method. The insert is refused even if it would actually end up in a writable table.

      • 只要 s1 不是只读，删除 MERGE 表 (DROP TABLE s1.t) 就会成功。允许删除引用只读数据库的 MERGE 表。Dropping the MERGE table (DROP TABLE s1.t) succeeds as long as s1 is not read only. It is permitted to drop a MERGE table that refers to a read-only database.

ALTER DATABASE 语句会阻塞，直到所有已经访问被更改数据库中对象的并发事务提交为止。反之，访问正在被并发 ALTER DATABASE 更改的数据库中对象的写事务，会阻塞，直到 ALTER DATABASE 提交为止。An ALTER DATABASE statement blocks until all concurrent transactions that have already accessed an object in the database being altered have committed. Conversely, a write transaction accessing an object in a database being altered in a concurrent ALTER DATABASE blocks until the ALTER DATABASE has committed.

如果使用 Clone 插件克隆本地或远程数据目录，则克隆中的数据库会保留源数据目录中的只读状态。只读状态不会影响克隆过程本身。如果不希望在克隆中保留相同的数据库只读状态，则必须在克隆过程完成后，通过对克隆执行 ALTER DATABASE 操作显式更改该选项。If the Clone plugin is used to clone a local or remote data directory, the databases in the clone retain the read-only state they had in the source data directory. The read-only state does not affect the cloning process itself. If it is not desirable to have the same database read-only state in the clone, the option must be changed explicitly for the clone after the cloning process has finished, using ALTER DATABASE operations on the clone.

从捐赠者克隆到接收者时，如果接收者有只读的用户数据库，克隆会失败并出现错误消息。在使数据库可写后可以重试克隆。When cloning from a donor to a recipient, if the recipient has a user database that is read only, cloning fails with an error message. Cloning may be retried after making the database writable.

READ ONLY 允许用于 ALTER DATABASE，但不允许用于 CREATE DATABASE。然而，对于只读数据库，由 SHOW CREATE DATABASE 生成的语句确实在注释中包含 READ ONLY=1，以指示其只读状态：READ ONLY is permitted for ALTER DATABASE, but not for CREATE DATABASE. However, for a read-only database, the statement produced by SHOW CREATE DATABASE does include READ ONLY=1 within a comment to indicate its read-only status:

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语句作为转储输出：If the server executes a CREATE DATABASE statement containing such a comment, the server ignores the comment and the READ ONLY option is not processed. This has implications for mysqldump, which uses SHOW CREATE DATABASE to produce CREATE DATABASE statements in dump output:

  • 在转储文件中，一个只读数据库的 CREATE DATABASE 语句包含被注释掉的 READ ONLY 选项。In a dump file, the CREATE DATABASE statement for a read-only database contains the commented READ ONLY option.

  • 转储文件可以照常恢复，但由于服务器会忽略被注释掉的 READ ONLY 选项，所以恢复后的数据库 不是只读的。如果希望数据库在恢复后为只读状态，必须手动执行 ALTER DATABASE 来设置为只读。The dump file can be restored as usual, but because the server ignores the commented READ ONLY option, the restored database is not read only. If the database is to be read only after being restored, you must execute ALTER DATABASE manually to make it so.

假设 mydb 是只读的，你可以按如下方式进行转储：Suppose that mydb is read only and you dump it as follows:

$> mysqldump --databases mydb > mydb.sql

如果 mydb 仍然应该是只读的，那么恢复操作之后必须执行 ALTER DATABASE：A restore operation later must be followed by ALTER DATABASE if mydb should still be read only:

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

MySQL 企业备份不受此问题影响。它像备份和恢复任何其他数据库一样处理只读数据库，但如果在备份时启用了 READ ONLY 选项，则在恢复时会启用该选项。MySQL Enterprise Backup is not subject to this issue. It backs up and restores a read-only database like any other, but enables the READ ONLY option at restore time if it was enabled at backup time.

ALTER DATABASE 会被写入二进制日志，因此在复制源服务器上更改 READ ONLY 选项也会影响副本。为了防止这种情况发生，必须在执行 ALTER DATABASE 语句之前禁用二进制日志。例如，为了在不影响副本的情况下准备迁移数据库，需要执行以下操作：ALTER DATABASE is written to the binary log, so a change to the READ ONLY option on a replication source server also affects replicas. To prevent this from happening, binary logging must be disabled prior to execution of the ALTER DATABASE statement. For example, to prepare for migrating a database without affecting replicas, perform these operations:

  1. 在单个会话中，禁用二进制日志并为数据库启用 READ ONLY：Within a single session, disable binary logging and enable READ ONLY for the database:

    mysql> SET sql_log_bin = OFF;
mysql> ALTER DATABASE mydb READ ONLY = 1;

  2. 导出数据库，例如使用 mysqldump：Dump the database, for example, with mysqldump:

    $> mysqldump --databases mydb > mydb.sql

  3. 在单个会话中，禁用二进制日志并禁用数据库的READ ONLY：Within a single session, disable binary logging and disable READ ONLY for the database:

    mysql> SET sql_log_bin = OFF;
mysql> ALTER DATABASE mydb READ ONLY = 0;
首页   上一级   下一节
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 261.9Kb
Man Pages (Zip) - 367.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb