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 enables you to change the overall characteristics of a database. ALTER DATABASE使您能够更改数据库的总体特征。These characteristics are stored in the data dictionary. 这些特征存储在数据字典中。This statement requires the 此语句需要数据库的ALTER privilege on the database. ALTER权限。ALTER SCHEMA is a synonym for ALTER DATABASE.ALTER SCHEMA是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.在这种情况下，如果没有默认数据库，则会发生错误。
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.alter_option，数据库将保留其当前选项值，但更改字符集可能会更改排序规则，反之亦然。
The CHARACTER SET option changes the default database character set. CHARACTER SET选项更改默认的数据库字符集。The COLLATE option changes the default database collation. COLLATE选项更改默认的数据库排序规则。For information about character set and collation names, see Chapter 10, Character Sets, Collations, Unicode.有关字符集和排序规则名称的信息，请参阅第10章，字符集，排序规则，Unicode。
To see the available character sets and collations, use the 要查看可用的字符集和排序规则，请分别使用SHOW CHARACTER SET and SHOW COLLATION statements, respectively. SHOW CHARACTER SET语句和SHOW COLLATION语句。See Section 13.7.7.3, “SHOW CHARACTER SET Statement”, and Section 13.7.7.4, “SHOW COLLATION Statement”.请参阅第13.7.7.3节，“显示字符集语句”和第13.7.7.4节，“显示排序规则语句”。
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 13.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.) 参见第13.1.17节，“创建过程和创建函数语句”。）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.如果更改数据库的默认字符集或排序规则，则必须删除并重新创建要使用新默认值的所有存储例程。
The MySQL 8.0.16中引入的ENCRYPTION option, introduced in MySQL 8.0.16, defines the default database encryption, which is inherited by tables created in the database. ENCRYPTION选项定义了默认的数据库加密，由数据库中创建的表继承。The permitted values are 允许的值为'Y' (encryption enabled) and 'N' (encryption disabled). 'Y'（启用加密）和'N'（禁用加密）。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. table_encryption_privilege_check系统变量，则需要TABLE_ENCRYPTION_ADMIN权限来指定与默认default_table_encryption系统变量的值不同的默认加密设置。For more information, see Defining an Encryption Default for Schemas and General Tablespaces.有关更多信息，请参见为模式和常规表空间定义加密默认值。
The MySQL 8.0.22中引入的READ ONLY option, introduced in MySQL 8.0.22, controls whether to permit modification of the database and objects within it. READ ONLY控制是否允许修改数据库和其中的对象。The permitted values are 允许的值为DEFAULT or 0 (not read only) and 1 (read only). DEFAULT或0（非只读）和1（只读）。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.READ ONLY的数据库可以迁移到另一个MySQL实例，而不必担心在操作过程中数据库可能会更改。
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.使用NDB Cluster，使一个mysqld服务器上的数据库成为只读的，这将与同一集群中的其他mysqld服务器同步，从而使数据库在所有mysqld服务器上成为只读的。
The READ ONLY option, if enabled, is displayed in the INFORMATION_SCHEMA SCHEMATA_EXTENSIONS table. READ ONLY选项（如果启用）将显示在INFORMATION_SCHEMA SCHEMATA_EXTENSIONS表中。See Section 26.3.32, “The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table”.请参阅第26.3.32节，“INFORMATION_SCHEMA SCHEMATA_EXTENSIONS表”。
The 不能为以下系统架构启用READ ONLY option cannot be enabled for these system schemas: mysql, information_schema, performance_schema.READ ONLY选项：mysql、information_schema、performance_schema。
In 在ALTER DATABASE statements, the READ ONLY option interacts with other instances of itself and with other options as follows:ALTER DATABASE语句中，READ ONLY选项与自身的其他实例以及其他选项交互，如下所示：
An error occurs if multiple instances of 如果多个READ ONLY conflict (for example, READ ONLY = 1 READ ONLY = 0).READ ONLY实例发生冲突（例如，READ ONLY=1 READ ONLY=0），则会发生错误。
An 即使对于只读数据库，也允许使用仅包含（非冲突）ALTER DATABASE statement that contains only (nonconflicting) READ ONLY options is permitted even for a read-only database.READ ONLY选项的ALTER DATABASE语句。
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. READ ONLY状态允许修改，则允许将（非冲突的）只读选项与其他选项混合使用。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;
Enabling 启用READ ONLY affects all users of the database, with these exceptions that are not subject to read-only checks:READ ONLY会影响数据库的所有用户，但以下例外情况不受只读检查的影响：
Statements executed by the server as part of server initialization, restart, upgrade, or replication.作为服务器初始化、重新启动、升级或复制的一部分由服务器执行的语句。
Statements in a file named at server startup by the 在服务器启动时由init_file system variable.init_file系统变量命名的文件中的语句。
TEMPORARY tables; it is possible to create, alter, drop, and write to TEMPORARY tables in a read-only database.TEMPORARY表；可以在只读数据库中创建、更改、删除和写入TEMPORARY表。
NDB Cluster non-SQL inserts and updates.NDB群集非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. READ ONLY将禁止对数据库及其对象（包括其定义、数据和元数据）执行写操作。The following list details affected SQL statements and operations:以下列表详细说明了受影响的SQL语句和操作：
The database itself:数据库本身：
ALTER DATABASE (except to change the （更改READ ONLY option)READ ONLY选项除外）
Views:视图：
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:存储例程：
CALL (of procedures with side effects)（指有副作用的程序）
SELECT (of functions with side effects)（指有副作用的功能）
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. CALL语句，只读检查是在每个语句的基础上进行的，因此，如果某些有条件执行的语句写入只读数据库时没有实际执行，则调用仍然成功。On the other hand, for a function called within a 另一方面，对于在SELECT, execution of the function body happens in prelocked mode. SELECT中调用的函数，函数体的执行是在预锁定模式下进行的。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:触发器：
Trigger invocation.触发调用。
Events:事件：
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:表：
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.对于子表位于只读数据库中的级联外键，即使子表不受直接影响，也会拒绝父表上的更新和删除。
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:MERGE表，如CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=...应用以下行为：
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. s1、s2、s3中至少有一个是只读的，则插入MERGE（INSERT into s1.t）失败，无论用了哪种插入方法。The insert is refused even if it would actually end up in a writable table.插入被拒绝，即使它实际上会在一个可写表中结束。
Dropping the 只要MERGE table (DROP TABLE s1.t) succeeds as long as s1 is not read only. s1不是只读的，删除MERGE表（DROP table s1.t）就会成功。It is permitted to drop a 允许删除引用只读数据库的MERGE table that refers to a read-only database.MERGE表。
An ALTER DATABASE statement blocks until all concurrent transactions that have already accessed an object in the database being altered have committed. ALTER DATABASE语句阻塞，直到已访问被更改数据库中对象的所有并发事务都已提交。Conversely, a write transaction accessing an object in a database being altered in a concurrent 相反，在并发ALTER DATABASE blocks until the ALTER DATABASE has committed.ALTER DATABASE中访问正在更改的数据库中的对象的写事务会阻塞，直到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.ALTER DATABASE操作显式更改克隆的选项。
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 is permitted for ALTER DATABASE, but not for CREATE DATABASE. ALTER DATABASE允许READ ONLY，但CREATE DATABASE不允许READ ONLY。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: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 */
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. CREATE DATABASE语句，则服务器将忽略该注释，并且不会处理READ ONLY选项。This has implications for mysqldump and mysqlpump, which use 这对mysqldump和mysqlpump有影响，它们使用SHOW CREATE DATABASE to produce CREATE DATABASE statements in dump output:SHOW CREATE DATABASE在转储输出中生成CREATE DATABASE语句：
In a dump file, the 在转储文件中，只读数据库的CREATE DATABASE statement for a read-only database contains the commented READ ONLY option.CREATE DATABASE语句包含注释READ ONLY选项。
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. READ ONLY选项，所以恢复的数据库不是只读的。If the database is to be read ony after being restored, you must execute 如果要在还原后重新读取数据库，则必须手动执行ALTER DATABASE manually to make it so.ALTER DATABASE以使其恢复。
Suppose that 假设mydb is read only and you dump it as follows:mydb是只读的，您将其转储如下：
shell> mysqldump --databases mydb > mydb.sql
A restore operation later must be followed by 如果ALTER DATABASE if mydb should still be read only:mydb仍应为只读，则以后的还原操作后必须执行ALTER DATABASE：
shell>mysqlmysql>SOURCE mydb.sql;mysql>ALTER DATABASE mydb READ ONLY = 1;
MySQL Enterprise Backup is not subject to this issue. MySQL企业备份不受此问题的影响。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.READ ONLY选项，则在还原时启用该选项。
ALTER DATABASE is written to the binary log, so a change to the READ ONLY option on a replication source server also affects replicas. ALTER DATABASE写入二进制日志，因此对复制源服务器上的READ ONLY选项的更改也会影响副本。To prevent this from happening, binary logging must be disabled prior to execution of the 为了防止这种情况发生，必须在执行ALTER DATABASE statement. ALTER DATABASE语句之前禁用二进制日志记录。For example, to prepare for migrating a database without affecting replicas, perform these operations:例如，要准备迁移数据库而不影响副本，请执行以下操作：
Within a single session, disable binary logging and enable 在单个会话中，对数据库禁用二进制日志记录并启用READ ONLY for the database:READ ONLY：
mysql>SET sql_log_bin = OFF;mysql>ALTER DATABASE mydb READ ONLY = 1;
Dump the database, for example, with mysqldump or mysqlpump:例如，使用mysqldump或mysqlpump转储数据库：
shell> mysqldump --databases mydb > mydb.sqlWithin a single session, disable binary logging and disable 在单个会话中，对数据库禁用二进制日志记录和READ ONLY for the database:READ ONLY：
mysql>SET sql_log_bin = OFF;mysql>ALTER DATABASE mydb READ ONLY = 0;