Database Administration Statements

最新推荐文章于 2024-06-02 08:09:32 发布

举杯酌酒望明月

最新推荐文章于 2024-06-02 08:09:32 发布

阅读量141

点赞数

分类专栏： # sql语句

本文链接：https://blog.csdn.net/wangchaofan1992/article/details/103364975

版权

sql语句专栏收录该内容

5 篇文章 0 订阅

订阅专栏

帐户管理

ALTER USER 语法

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'

user:
    (see Section6.2.4, "Specifying Account Names")

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

ALTER USER语句修改了MySQL帐户。它允许为现有帐户修改身份验证，SSL / TLS，资源限制和密码管理属性。它也可以用于锁定和解锁帐户。

要使用ALTER USER，您必须具有全局CREATE USER特权或mysql系统数据库的UPDATE特权。当启用了 read_only系统变量，ALTER USER还需要SUPER特权。

默认情况下，如果您尝试修改不存在的用户，则会发生错误。如果IF EXISTS给出了该子句，则该语句将为每个不存在的命名用户生成警告，而不是错误。

重要

在某些情况下，ALTER USER可能会记录在服务器日志中或客户端的历史记录文件中，例如 ~/.mysql_history，这意味着具有对该信息的读取访问权限的任何人都可以读取明文密码。

ALTER USER概述

对于每个受影响的帐户，ALTER USER修改mysql.user系统表中的相应行以反映该语句中指定的属性。未指定的属性保留其当前值。

帐户名的主机名部分（如果省略）默认为'%'。也可以指定 CURRENT_USER或 CURRENT_USER()引用与当前会话关联的帐户。

仅对于一种语法，可以使用以下USER()功能指定帐户：

ALTER USER USER() IDENTIFIED BY 'auth_string';

此语法可以更改您自己的密码，而无需按字面指定您的帐户

对于允许auth_option value 遵循user value 的更改用户语法，auth_option指示帐户如何通过指定帐户身份验证插件，凭据(for example，密码)或两者来进行身份验证。每个auth_option value 仅适用于紧接其前面的帐户。

例如：更改帐户密码并使其过期。因此，用户必须使用指定的密码连接，并在下一次连接时选择一个新密码：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

示例：修改帐户以使用sha256_password身份验证插件和给定密码。要求每 180 天选择一个新密码：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH sha256_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY;

例如：锁定或解锁帐户：

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

Example：要求帐户使用 SSL 进行连接，并建立每小时 20 个连接的限制：

ALTER USER 'jeffrey'@'localhost'
  REQUIRE SSL WITH MAX_CONNECTIONS_PER_HOUR 20;

示例：更改多个帐户，指定一些 per-account properties 和一些 global properties：

ALTER USER
  'jeffrey'@'localhost' IDENTIFIED BY 'new_password',
  'jeanne'@'localhost'
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 2;

jeffrey之后的IDENTIFIED BY value 仅适用于其前一个帐户，因此仅将'jeffrey_new_password'的密码更改为'jeffrey_new_password'。对于jeanne，没有 per-account value(因此保持密码不变)。

其余属性全局应用于语句中命名的所有帐户，因此对于两个帐户：

使用SSL需要连接。
该帐户最多可用于两个同时连接。

CREATE USER语句

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section6.2.4, "Specifying Account Names")

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

CREATE USER语句创建新的 MySQL 帐户。它允许为新帐户建立身份验证，SSL/TLS，资源限制和密码管理属性，并控制帐户最初是被锁定还是未锁定。

要使用CREATE USER，您必须具有全局CREATE USER特权或 mysql系统数据库的INSERT特权。启用read_only系统变量时，创建用户还需要超权限。

如果您尝试创建已存在的帐户，则会发生错误。如果给出了IF NOT EXISTS子句，则该语句会为已存在的每个已命名帐户生成警告，而不是错误。

重要
在某些情况下，创建用户可能会记录在服务器日志中或 client 端的历史文件中，例如~/.mysql_history，这意味着任何对该信息具有读取权限的人都可以读取明文密码。

创建用户概述

对于每个帐户，创建用户在mysql.user system table 中创建一个新行。帐户行反映了语句中指定的 properties。未指定的 properties 设置为其默认值：

身份验证：由default_authentication_plugin系统变量定义的身份验证插件和空凭据
SSL/TLS：NONE
资源限制：无限制
密码管理：PASSWORD EXPIRE DEFAULT
帐户锁定：ACCOUNT UNLOCK

首次创建帐户时没有权限。要分配权限，请使用GRANT语句。

每个帐户 name 使用第 6.2.4 节，“指定帐户名称”中描述的格式。例如：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

帐户 name 的 host name 部分，如果省略，则默认为'%'。

每个user value 命名一个帐户后面可能会有一个可选的auth_option value，指示帐户如何进行身份验证。这些值可以指定帐户身份验证插件和凭据(用于 example，密码)。每个auth_option value 仅适用于紧接其前面的帐户。

遵循user规范，该语句可能包含 SSL/TLS，resource-limit，password-management 和锁定 properties 的选项。所有这些选项都是 global，并且适用于语句中指定的所有帐户。

Example：创建一个使用默认身份验证插件和给定密码的帐户。标记密码已过期，以便用户必须在第一次连接到服务器时选择一个新密码：

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

示例：创建一个使用sha256_password身份验证插件和给定密码的帐户。要求每 180 天选择一个新密码：

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH sha256_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY;

示例：创建多个帐户，指定一些 per-account properties 和一些 global properties：

CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH sha256_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  ACCOUNT LOCK;

每个auth_option value(在这种情况下为IDENTIFIED WITH ... BY)仅适用于紧接其前面的帐户，因此每个帐户使用紧随其后的身份验证插件和密码。

其余的 properties 全局适用于语句中指定的所有帐户，因此对于这两个帐户：

必须使用有效的 X.509 证书进行连接。
每小时最多允许 60 个查询。
该帐户最初是锁定的，因此它实际上是占位符，在管理员解锁之前无法使用。

创建用户身份验证选项

帐户 name 后面可能跟一个auth_option身份验证选项，该选项指定帐户身份验证插件，凭据或两者：

auth_plugin命名一个身份验证插件。插件 name 可以是带引号的 string 文字或不带引号的 name。插件名称存储在mysql.user system table 的plugin列中。

对于未指定身份验证插件的auth_option语法，默认插件由default_authentication_plugin系统变量的 value 指示。有关每个插件的说明，请参阅第 6.4.1 节，“身份验证插件”。

凭证存储在mysql.user system table 中。 'auth_string' value 分别指定帐户凭据，作为明文(未加密)string 或以与帐户关联的身份验证插件所期望的格式进行散列：
对于使用BY 'auth_string'的语法，string 是明文，并传递给身份验证插件以进行可能的散列。插件返回的结果存储在mysql.user table 中。插件可以使用指定的 value，在这种情况下不会发生散列。
对于使用AS 'auth_string'的语法，假定 string 已经采用认证插件所需的格式，并按原样存储在mysql.user table 中。如果插件需要散列 value，则必须以适合插件的格式对 value 进行散列处理，否则插件将无法使用 value，并且不会对 client 连接进行正确的身份验证。
如果身份验证插件不执行身份验证 string 的散列，则BY 'auth_string'和AS 'auth_string'子句具有相同的效果：身份验证 string 按原样存储在mysql.user system table 中。

创建用户允许这些auth_option语法：

IDENTIFIED BY 'auth_string'

将帐户身份验证插件设置为默认插件，将明文'auth_string' value 传递给插件以进行可能的散列，并将结果存储在mysql.user system table 中的帐户行中。

IDENTIFIED WITH auth_plugin

将帐户身份验证插件设置为auth_plugin，将凭据清除为空 string，并将结果存储在mysql.user system table 中的帐户行中。

IDENTIFIED WITH auth_plugin BY 'auth_string'

将帐户身份验证插件设置为auth_plugin，将明文'auth_string' value 传递给插件以进行可能的散列，并将结果存储在mysql.user system table 中的帐户行中。

IDENTIFIED WITH auth_plugin AS 'auth_string'

将帐户身份验证插件设置为auth_plugin，将'auth_string' value 存储为mysql.user帐户行中的'auth_string' value。如果插件需要散列 string，则假定 string 已经以插件所需的格式进行了散列。

IDENTIFIED BY PASSWORD 'auth_string'

将帐户身份验证插件设置为默认插件，然后_存储mysql.user value，如mysql.user帐户行中所示。如果插件需要散列 string，则假定 string 已经以插件所需的格式进行了散列。

注意
不推荐使用IDENTIFIED BY PASSWORD语法，将在以后的 MySQL 版本中删除。

DROP USER 语法

DROP USER [IF EXISTS] user [, user] ...

DROP USER语句删除一个或多个 MySQL 帐户及其权限。它从所有授权表中删除该帐户的权限行。

要使用DROP USER，您必须具有 global 创建用户特权或mysql系统数据库的删除特权。启用read_only系统变量时，DROP USER还需要超权限。

如果您尝试删除不存在的帐户，则会发生错误。如果给出了IF EXISTS子句，则该语句会为每个不存在的命名用户生成警告，而不是错误。

每个帐户 name 使用第 6.2.4 节，“指定帐户名称”中描述的格式。例如：

DROP USER 'jeffrey'@'localhost';

帐户 name 的 host name 部分，如果省略，则默认为'%'。

重要
DROP USER不会自动关闭任何打开的用户会话。相反，在 event 中删除了具有 open session 的用户，该语句在该用户的 session 关闭之前不会生效。关闭 session 后，将删除该用户，并且该用户下次尝试 log 将失败。这是设计使然。

DROP USER不会自动删除或使旧用户在其中创建的数据库或对象无效。这包括DEFINER属性名称为已删除用户的存储程序或视图。如果尝试在定义程序安全性上下文中执行，则尝试访问此类对象可能会产生错误。

GRANT 语法

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user:
    (see Section6.2.4, "Specifying Account Names")

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

tls_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

GRANT语句授予 MySQL 用户帐户权限。 GRANT语句有几个方面，在以下主题下描述：

GRANT 概述

该GRANT语句将特权授予MySQL用户帐户。

要使用GRANT，您必须具有 GRANT OPTION特权，并且必须具有要授予的特权。启用read_only系统变量时，GRANT还需要超权限。

REVOKE语句与GRANT管理员相关，并使管理员能够删除帐户特权。请参见第13.7.1.6节“ REVOKE语句”。

每个帐户 name 使用第 6.2.4 节，“指定帐户名称”中描述的格式。例如：

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

帐户的主机名部分（如果省略）默认为 '%'。

通常，数据库管理员首先用于 CREATE USER创建帐户并定义其非特权特征，例如其密码，是否使用安全连接以及对服务器资源的访问限制，然后再用于 GRANT定义其特权。 ALTER USER可用于更改现有帐户的非特权特征。例如：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

注意

此处显示的示例包括 no IDENTIFIED子句。假设您在 account-creation time 时使用创建用户建立密码以避免创建不安全的帐户。

注意
如果GRANT语句中指定的帐户尚不存在，则GRANT可以在后面讨论NO_AUTO_CREATE_USER SQL 模式的条件下创建它。也可以使用GRANT来指定非特权帐户特征，例如它是否使用安全连接以及对服务器资源的访问限制。

但是，从 MySQL 5.7.6 开始，不推荐使用GRANT来创建帐户或定义非特权。而是使用CREATE USER或ALTER USER执行这些任务。

从MySQL程序开始，GRANT在成功执行时以Query OK, 0 rows affected响应。要确定操作产生的特权，请使用SHOW GRANTS

GRANT支持最多60个字符的主机名。用户名最多可以包含32个字符。数据库，表，列和例程的名称最多可以包含64个字符。

警告

不要尝试通过更改mysql.user 系统表来更改用户名的允许长度。这样做会导致无法预料的行为，甚至可能使用户无法登录到MySQL服务器。mysql除非通过第2.11节“升级MySQL”中描述的过程，否则不要以任何方式改变系统数据库中表的结构。

Object 引用指南

GRANT 语句中的几个对象都需要加引号，尽管在许多情况下引号是可选的：帐户，数据库，表，列和例程名称。例如，如果帐户名中的 user_name或 host_name值作为未加引号的标识符是合法的，则无需使用引号。然而，引号是必要指定 user_name包含特殊字符（例如字符串-），或一个 host_name包含字符串的特殊字符或通配符，如% （例如'test-user'@'%.com'）。分别引用用户名和主机名。

要指定引用的值：

引用数据库，表，列和例程名称作为标识符。
用用户名和主机名加引号作为标识符或字符串。
用密码引用字符串。

GRANT在授予数据库级别特权的语句中指定数据库名称时，允许使用_和%通配符(例如GRANT ... ON db_name.*) 。要使用_字符作为数据库 name 的一部分，请在GRANT语句中将其指定为\_，以防止用户能够访问与通配符模式匹配的其他数据库(例如，GRANT ... ON foo_bar .* TO ...)

如果数据库 name 不用于在数据库 level 上授予权限，但是作为用于向其他 object(例如 table 或GRANT ... ON db_name.tbl_name)授予权限的限定符，则将通配符视为普通字符。

MySQL 支持的权限

以下 table 总结了可以为GRANT和 REVOKE statements 指定的允许的priv_type特权类型，以及可以授予每个特权的级别。有关每个特权的更多信息，请参见第6.2.2节“ MySQL提供的特权”。

Privilege	Meaning and Grantable Levels
`ALL [PRIVILEGES]`	Grant all privileges at specified access level except `GRANT OPTION` and `PROXY`.
`ALTER`	Enable use of `ALTER TABLE`. Levels: Global, database, table.
`ALTER ROUTINE`	Enable stored routines to be altered or dropped. Levels: Global, database, routine.
`CREATE`	Enable database and table creation. Levels: Global, database, table.
`CREATE ROUTINE`	Enable stored routine creation. Levels: Global, database.
`CREATE TABLESPACE`	Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
`CREATE TEMPORARY TABLES`	Enable use of `CREATE TEMPORARY TABLE`. Levels: Global, database.
`CREATE USER`	Enable use of `CREATE USER`, `DROP USER`, `RENAME USER`, and `REVOKE ALL PRIVILEGES`. Level: Global.
`CREATE VIEW`	Enable views to be created or altered. Levels: Global, database, table.
`DELETE`	Enable use of `DELETE`. Level: Global, database, table.
`DROP`	Enable databases, tables, and views to be dropped. Levels: Global, database, table.
`EVENT`	Enable use of events for the Event Scheduler. Levels: Global, database.
`EXECUTE`	Enable the user to execute stored routines. Levels: Global, database, routine.
`FILE`	Enable the user to cause the server to read or write files. Level: Global.
`GRANT OPTION`	Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy.
`INDEX`	Enable indexes to be created or dropped. Levels: Global, database, table.
`INSERT`	Enable use of `INSERT`. Levels: Global, database, table, column.
`LOCK TABLES`	Enable use of `LOCK TABLES` on tables for which you have the `SELECT` privilege. Levels: Global, database.
`PROCESS`	Enable the user to see all processes with `SHOW PROCESSLIST`. Level: Global.
`PROXY`	Enable user proxying. Level: From user to user.
`REFERENCES`	Enable foreign key creation. Levels: Global, database, table, column.
`RELOAD`	Enable use of `FLUSH` operations. Level: Global.
`REPLICATION CLIENT`	Enable the user to ask where master or slave servers are. Level: Global.
`REPLICATION SLAVE`	Enable replication slaves to read binary log events from the master. Level: Global.
`SELECT`	Enable use of `SELECT`. Levels: Global, database, table, column.
`SHOW DATABASES`	Enable `SHOW DATABASES` to show all databases. Level: Global.
`SHOW VIEW`	Enable use of `SHOW CREATE VIEW`. Levels: Global, database, table.
`SHUTDOWN`	Enable use of mysqladmin shutdown. Level: Global.
`SUPER`	Enable use of other administrative operations such as `CHANGE MASTER TO`, `KILL`, `PURGE BINARY LOGS`, `SET GLOBAL`, and mysqladmin debug command. Level: Global.
`TRIGGER`	Enable trigger operations. Levels: Global, database, table.
`UPDATE`	Enable use of `UPDATE`. Levels: Global, database, table, column.
`USAGE`	Synonym for “no privileges”

触发器与表关联。要创建或删除触发器，您必须具有表的权限，而不是触发器的TRIGGER权限。

在GRANT语句中， ALL [PRIVILEGES]or PROXY 特权必须自己命名，不能与其他特权一起指定。 ALL [PRIVILEGES]代表除GRANT OPTION和 PROXY特权外，在授予特权的级别上可用的所有特权。

USAGE可以指定创建一个没有特权的用户，或者在不更改其现有特权的情况下为帐户指定 REQUIREor WITH子句。（但是，不赞成使用GRANT定义非特权特征。)

MySQL 帐户信息存储在mysql系统数据库的表中。有关其他详细信息，请参阅第 6.2 节，“访问控制和帐户管理”，其中广泛讨论了mysql系统数据库和访问控制系统。

可以在多个级别上授予特权，具体取决于该ON子句使用的语法。对于 REVOKE，相同的 ON语法指定要删除的特权。

对于 global，database，table 和例程级别， GRANT ALL仅分配您授予的 level 中存在的权限。例如，GRANT ALL ON db_name.*是 database-level 语句，因此它不会授予任何 global-only 特权，例如 FILE。授予 ALL 不会分配 GRANT OPTION or PROXY权限。

当以下 object 是 table，存储的 function 或存储过程时，object_type子句(如果存在)应指定为TABLE，FUNCTION或PROCEDURE。

用户为数据库，table，列或例程保存的权限是作为每个权限级别(包括 global level)的帐户权限的逻辑 OR而相加形成的。由于在较低的 level 下缺少该特权，因此无法拒绝在更高的 level 上授予的特权。 For example，此语句全局授予SELECT and INSERT 权限：

GRANT SELECT, INSERT ON *.* TO u1;

全局授予的权限适用于所有数据库，表和列，即使未在任何较低级别授予。

如果您对一个用户使用 table，列或例程权限，则服务器会检查所有用户的 table，列和例程权限，这会使 MySQL 慢一点。同样，如果限制任何用户的查询，更新或连接数，则服务器必须监视这些值。

MySQL 使您可以授予不存在的数据库或表的权限。对于表，要授予的权限必须包含 CREATE权限。此行为是设计使然，旨在使数据库管理员能够为稍后 time 创建的数据库或表准备用户帐户和权限。

重要
删除数据库或 table 时，MySQL 不会自动撤消任何权限。但是，如果删除例程，则会撤消为该例程授予的任何 routine-level 特权。

帐户名和密码

GRANT语句中的user value 表示该语句适用的 MySQL 帐户。为了适应从任意主机向用户授予权限，MySQL 支持以'user_name'@'host_name'格式指定user value。

可以在主机名中指定通配符。例如'user_name'@'%.example.com'，适用于example.com域中的任何主机的user_name，'user_name'@'198.51.100.%', 适用于C类子网198.51.100中的任何主机的user_name。

简单形式'user_name'是'user_name'@'%'的同义词。

MySQL 不支持用户名中的通配符。要引用匿名用户，请使用以下GRANT语句指定一个用户名为空的帐户：

GRANT ALL ON test.* TO ''@'localhost' ...;

在这种情况下，将从本地主机使用匿名用户正确密码连接的任何用户，都将被授予访问权限，并具有与匿名用户帐户相关联的特权。

警告
如果允许本地匿名用户连接到 MySQL 服务器，则还应将所有本地用户的权限授予'user_name'@'localhost'。否则，当命名用户尝试从本地计算机 log 登录到 MySQL 服务器时，将使用mysql.user system table 中localhost的匿名用户帐户。

要确定此问题是否适用于您，请执行以下查询，该列表将列出任何匿名用户：

SELECT Host, User FROM mysql.user WHERE User='';

要避免上述问题，请使用以下语句删除本地匿名用户帐户：

DROP USER ''@'localhost';

对于GRANT允许auth_option值跟随 user值的语法，auth_option以IDENTIFIED开头，并通过指定帐户身份验证插件，凭据(例如密码)或两者来指示帐户如何进行身份验证。或两者来指示帐户如何进行身份验证。 auth_option子句的语法与创建用户语句的语法相同。

注意
从 MySQL 5.7.6 开始，不推荐使用GRANT来定义帐户身份验证特征。。而是使用CREATE USER或ALTER USER建立或更改身份验证特征。这个GRANT功能将在未来的 MySQL 版本中删除。

如果IDENTIFIED存在，并且您具有全局授予特权（GRANT OPTION），则指定的任何密码都将成为该帐户的新密码，即使该帐户存在并且已经具有密码。不使用IDENTIFIED，帐户密码保持不变。

Global Privileges

全局特权是管理性的，或适用于给定服务器上的所有数据库。要分配全局特权，请使用 ON *.*语法：

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

CREATE TABLESPACE， CREATE USER， FILE， PROCESS， RELOAD， REPLICATION CLIENT， REPLICATION SLAVE， SHOW DATABASES， SHUTDOWN，和 SUPER特权是管理权限，，只能在全球范围内授予。

在全局级别授予GRANT OPTION 的任何全局特权都适用于所有全局特权。

MySQL stores global privileges in the mysql.user system table.

Database Privileges

数据库权限适用于给定数据库中的所有 objects。要分配 database-level 权限，请使用ON db_name.*语法：

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

如果使用ON *语法(而不是ON *.*)，则会在数据库 level 中为默认数据库分配权限。如果没有默认数据库，则会发生错误。

可以在数据库 level 中指定 CREATE, DROP, EVENT, GRANT OPTION, LOCK TABLES, and REFERENCES 权限。Table 或例程特权也可以在数据库 level 中指定，在这种情况下，它们适用于数据库中的所有表或例程。

MySQL stores database privileges in the mysql.db system table.

Table 权限

Table 权限适用于给定 table 中的所有列。要分配 table-level 权限，请使用ON db_name.tbl_name语法：

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

如果指定tbl_name而不是db_name.tbl_name，则该语句将应用于默认数据库中的tbl_name。如果没有默认数据库，则会发生错误。

table level 上允许的priv_type值为 ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT，OPTION, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

Table-level 特权适用于基表和视图。它们不适用于使用 CREATE TEMPORARY TABLE创建的表，即使 table 名称为 match。

列权限

列特权适用于给定表中的单个列。在列级别要授予的每个特权都必须在括号后加上一个或多个列。

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

可允许priv_type用于列（即，当你使用一个数值 column_list条款）是 INSERT， REFERENCES， SELECT，和 UPDATE。

MySQL将列特权存储在 mysql.columns_priv系统表中。

Stored Routine Privileges

在ALTER ROUTINE， CREATE ROUTINE， EXECUTE，和 GRANT OPTION权限适用于存储例程（过程和函数）。可以在全局和数据库级别授予它们。除 CREATE ROUTINE之外，可以在例程级别为单个例程授予这些特权。

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

可允许priv_type在常规水平值ALTER ROUTINE，EXECUTE和 GRANT OPTION。 CREATE ROUTINE不是例程级别的特权，因为您必须具有全局或数据库级别的特权才能首先创建例程。

MySQL在mysql.procs_priv系统表中存储例程级别的特权。

MySQL and Standard SQL Versions of GRANT

The biggest differences between the MySQL and standard SQL versions of GRANT are:

MySQL associates privileges with the combination of a host name and user name and not with only a user name.
Standard SQL does not have global or database-level privileges, nor does it support all the privilege types that MySQL supports.
MySQL does not support the standard SQL UNDER privilege.
Standard SQL privileges are structured in a hierarchical manner. If you remove a user, all privileges the user has been granted are revoked. This is also true in MySQL if you use DROP USER. See Section 13.7.1.3, “DROP USER Statement”.
In standard SQL, when you drop a table, all privileges for the table are revoked. In standard SQL, when you revoke a privilege, all privileges that were granted based on that privilege are also revoked. In MySQL, privileges can be dropped with DROP USER or REVOKE statements.
In MySQL, it is possible to have the INSERT privilege for only some of the columns in a table. In this case, you can still execute INSERT statements on the table, provided that you insert values only for those columns for which you have the INSERT privilege. The omitted columns are set to their implicit default values if strict SQL mode is not enabled. In strict mode, the statement is rejected if any of the omitted columns have no default value. (Standard SQL requires you to have the INSERT privilege on all columns.) For information about strict SQL mode and implicit default values, see Section 5.1.10, “Server SQL Modes”, and Section 11.7, “Data Type Default Values”.

RENAME USER 语法

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

RENAME USER 语句重命名现有的 MySQL 帐户。对于不存在的旧帐户或已存在的新帐户，会出错。

To use RENAME USER, you must have the global CREATE USER privilege, 或mysql系统数据库的UPDATE特权。 When the read_only system variable is enabled, RENAME USER additionally requires the SUPER privilege.

每个帐户 name 使用第 6.2.4 节，“指定帐户名称”中描述的格式。例如：

RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';

帐户 name 的 host name 部分，如果省略，则默认为'%'。

RENAME USER使旧用户拥有的权限成为新用户持有的权限。但是， RENAME USER不会自动删除或使旧用户创建的数据库或其中的 objects 无效。这包括DEFINER属性为旧用户命名的存储程序或视图。如果在 definer security context 中执行，尝试访问这些 object 可能会产生错误。

REVOKE 语法

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

REVOKE语句使系统管理员可以撤消 MySQL 帐户的权限。

有关权限存在的级别，允许的priv_type，priv_level和object_type值以及指定用户和密码的语法的详细信息，请参阅第 13.7.1.4 节，“GRANT 语法”。

When the read_only system variable is enabled, REVOKE requires the SUPER privilege in addition to any other required privileges described in the following discussion.

每个帐户 name 使用第 6.2.4 节，“指定帐户名称”中描述的格式。例如：

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

帐户 name 的 host name 部分，如果省略，则默认为'%'。

要使用第一个 REVOKE语法，您必须具有 GRANT OPTION权限，并且必须具有要撤消的权限。

要撤消所有权限，请使用第二种语法，该语法将删除指定用户或用户的所有 global，database，table，column 和 routine 权限：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

要使用此 REVOKE语法，您必须具有 global CREATE USER 特权或mysql系统数据库的UPDATE特权。

必须存在要撤消特权的用户帐户，但是当前不需要授予要撤销的特权。

REVOKE删除权限，但不从mysql.user system table 中删除行。要完全删除用户帐户，请使用DROP USER。

如果授予表包含包含大小写混合的数据库名或表名的特权行，并且lower_casetablenames系统变量设置为非零 value，则不能使用 REVOKE来撤消这些特权。有必要直接操纵授权表。

SET PASSWORD 语法

SET PASSWORD [FOR user] = password_option

password_option: {
    'auth_string'
  | PASSWORD('auth_string')
}

该SET PASSWORD语句为MySQL用户帐户分配密码。 'auth_string'代表明文（未加密）密码。

注意
SET PASSWORD ... = PASSWORD('auth_string') 自MySQL 5.7.6起不推荐使用该语法，并且在将来的MySQL版本中将删除该语法。

SET PASSWORD ... = 'auth_string'语法不会被启用，但是 ALTER USER语法是更改账户的首选，包括修改密码。
ALTER USER user IDENTIFIED BY 'auth_string';

重要

在某些情况下，SET PASSWORD可能会记录在服务器日志中或客户端的历史记录文件中，例如 ~/.mysql_history，这意味着具有对该信息的读取访问权限的任何人都可以读取明文密码。

使用FOR user子句，语句设置指定帐户的密码，该密码必须存在：

如果没有FOR user子句，则语句设置当前用户的密码：

如果没有FOR user子句，则语句设置当前用户的密码：

SET PASSWORD = 'auth_string';

使用非匿名帐户连接到服务器的任何客户端都可以更改该帐户的密码。（特别是，您可以更改自己的密码。）要查看服务器将您用作身份验证的帐户，请调用以下 CURRENT_USER()功能：

SELECT CURRENT_USER();

如果给出FOR user子句，则帐户 name 使用第 6.2.4 节，“指定帐户名称”中描述的格式。例如：

SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';

帐户 name 的 host name 部分，如果省略，则默认为'%'。

设置指定帐户的密码(带有FOR子句)需要mysql系统数据库的UPDATE权限。为自己设置密码(对于没有FOR子句的非匿名帐户)不需要特殊权限。启用read_only系统变量时，除了任何其他所需权限外，设置密码还需要超权限。

密码可以通过以下方式指定：

使用没有PASSWORD()的 string

SET PASSWORD FOR 'jeffrey'@'localhost' = 'password';

SET PASSWORD将字符串解释为明文字符串，并将其传递给与该帐户关联的身份验证插件，并将该插件返回的结果存储在mysql.user系统表的帐户行中。（为插件提供了将值散列为所需的加密格式的机会。插件可以使用指定的值，在这种情况下，不会发生散列。）

使用该PASSWORD()函数（自MySQL 5.7.6起不推荐使用）

SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');

PASSWORD()参数是明文(未加密)密码。 PASSWORD()哈希密码并返回加密密码 string 以存储在mysql.user system table 的帐户行中。

该PASSWORD()函数使用由old_passwords系统变量值的值确定的哈希方法对密码进行哈希处理。确保 old_passwords该值具有与该帐户关联的身份验证插件所需的哈希方法相对应的值。例如，如果帐户使用 mysql_native_password插件，则该 old_passwords值必须为0：

SET old_passwords = 0;
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');

如果该old_passwords值与身份验证插件所需的值不同，则插件PASSWORD()将无法使用所返回的哈希密码值，并且不会进行客户端连接的正确身份验证。

The following table shows, for each password hashing method, the permitted value of old_passwords and which authentication plugins use the hashing method.

Password Hashing Method	old_passwords Value	Associated Authentication Plugin
MySQL 4.1 native hashing	0	`mysql_native_password`
SHA-256 hashing	2	`sha256_password`

表维护语句

ANALYZE TABLE 语法

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE TABLE执行键分布分析并存储一个或多个命名表的分布。对于MyISAM表，此语句等效于使用myisamchk --analyze。

该声明要求SELECT 和INSERT对表的权限。

ANALYZE TABLE适用于InnoDB，NDB和MyISAM表。它不适用于视图。

ANALYZE TABLE支持分区表，您可以ALTER TABLE ... ANALYZE PARTITION用来分析一个或多个分区；

在分析期间，table 被锁定为InnoDB和MyISAM的读锁定。

CHECK TABLE 语法

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

CHECK TABLE检查一个或多个表是否有错误。对于MyISAM表，键统计信息也会更新。CHECK TABLE还可以检查视图是否存在问题，例如视图定义中引用的表不再存在。

要检查表，您必须具有一些特权。

CHECK TABLE适用于 InnoDB， MyISAM， ARCHIVE，和 CSV表格。

CHECKSUM TABLE 语法

CHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED]

CHECKSUM TABLE报告表内容的校验和。您可以使用此语句来验证备份，回滚或旨在将数据恢复为已知状态的其他操作前后的内容是否相同。

该语句需要SELECT该表的特权。

视图不支持该语句。如果CHECKSUM TABLE针对视图运行，则该Checksum值始终为 NULL，并返回警告。

对于不存在的表，CHECKSUM TABLE返回NULL并生成警告。

在校验和操作期间，table 被锁定为InnoDB和MyISAM的读锁定。

OPTIMIZE TABLE 语法

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE重新组织 table 数据和相关索引数据的物理存储，以减少访问 table 时的存储空间并提高 I/O 效率。对每个 table 所做的确切更改取决于该 table 使用的存储引擎。

在这些情况下使用OPTIMIZE TABLE，具体取决于 table 的类型：

在具有自己的.ibd 文件的InnoDB table 上执行实质 insert，更新或删除操作后，因为它是在启用了innodb_file_pertable选项的情况下创建的。重新组织 table 和索引，并且可以回收磁盘空间以供操作系统使用。
对表中作为FULLTEXT 索引一部分的列进行实质性的插入，更新或删除操作之后InnoDB。首先设置配置选项 innodb_optimize_fulltext_only=1 。为了将索引维护期保持在合理的时间，请设置 innodb_ft_num_word_optimize 选项以指定要在搜索索引中更新的单词数，并运行一系列OPTIMIZE TABLE语句，直到完全更新搜索索引。
删除MyISAM或ARCHIVE table 的大部分内容，或者对 variable-length 行(具有VARCHAR，VARBINARY，BLOB或文本列的表)进行MyISAM或ARCHIVE table 的许多更改。删除的行在链表中维护，随后的插入操作重用旧的行位置。您可以使用OPTIMIZE TABLE来回收未使用的空间并对数据文件进行碎片整理。在对 table 进行大量更改后，此语句还可以改善使用 table 的 statements 的 performance，有时甚至是显着的。

此语句要求SELECT 和INSERT对表的权限。

OPTIMIZE TABLE适用于InnoDB，MyISAM 数据和ARCHIVE表。 OPTIMIZE TABLE内存NDB表的动态列也受支持。它不适用于内存表中的固定宽度列，也不适用于磁盘数据表。可以使用--ndb-optimization-delay调整OPTIMIZE在 NDB Cluster 表上的_per_，它控制 time处理批处理之间等待的 time 长度。

REPAIR TABLE 语法

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE修复可能已损坏的 table，仅适用于某些存储引擎。

此语句需要 table 的选择和插入权限。

尽管通常不必运行 REPAIR TABLE，但是如果发生灾难，此语句很可能从MyISAM表中取回所有数据。如果您的表经常损坏，请尝试找出原因，以消除使用REPAIR TABLE的需要。

REPAIR TABLE检查表以查看是否需要升级。如果是这样，它将按照与相同的规则执行升级 CHECK TABLE ... FOR UPGRADE。

重要

在执行表修复操作之前，请备份表；在某些情况下，该操作可能会导致数据丢失。可能的原因包括但不限于文件系统错误。
如果服务器在REPAIR TABLE操作过程中崩溃，则在重新启动服务器后必须立即对表执行另一条REPAIR TABLE语句，然后再对其执行任何其他操作。在最坏的情况下，您可能有一个新的干净索引文件，而没有有关数据文件的信息，然后执行的下一个操作可能会覆盖数据文件。这是不太可能但可能的情况，强调了首先进行备份的价值。
如果主服务器上的表损坏并在其上运行REPAIR TABLE，则对原始表所做的任何更改都不会传播到从属服务器。

REPAIR TABLE 存储引擎和分区支持

REPAIR TABLE工程 MyISAM， ARCHIVE和 CSV表。对于 MyISAM表，默认情况下它与myisamchk --recover tbl_name具有相同的效果。该语句不适用于视图。

REPAIR TABLE支持分区表。但是，该USE_FRM 选项不能与分区表上的该语句一起使用。

您可以ALTER TABLE ... REPAIR PARTITION用来修复一个或多个分区。

插件和用户定义的函数语句

用户定义函数的CREATE FUNCTION语法

CREATE [AGGREGATE] FUNCTION function_name
    RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

user-defined function(UDF)是一种使用新的 function 扩展 MySQL 的方法，该函数类似于本机(built-in)MySQL 函数，例如ABS()或CONCAT()。

function_name是应在 SQL statements 中用于调用 function 的 name。 RETURNS子句指示 function 的 return value 的类型。 DECIMAL是RETURNS之后的合法值，但当前DECIMAL函数 return string 值，应该像STRING函数一样编写。

shared_library_name是共享 library 文件的基本 name，其中包含实现 function 的 code。该文件必须位于插件目录中。该目录由plugin_dir系统变量的 value 给出。

要创建 function，您必须具有mysql系统数据库的插入特权。这是必要的，因为CREATE FUNCTION向mysql.func system table 添加了一行，用于记录 function 的 name，type 和 shared library name。

active function 是已加载CREATE FUNCTION但未DROP FUNCTION删除的函数。每次服务器启动时都会重新加载所有活动函数，除非您使用该 mysqld--skip-grant-tables选项启动。在这种情况下，将跳过UDF初始化，并且UDF不可用。

有关编写用户定义函数的说明，请参见第28.4.2节“添加用户定义函数”。为了使UDF机制正常工作，必须使用C或C ++（或可以使用C调用约定的另一种语言）编写函数，您的操作系统必须支持动态加载，并且必须动态（而非静态）编译 mysqld。

AGGREGATE function 的功能与本机 MySQL 聚合(摘要)function 完全相同，例如SUM或COUNT()。

注意

要升级与UDF关联的共享库，请发出一条 DROP FUNCTION语句，升级共享库，然后发出一条 CREATE FUNCTION语句。如果先升级共享库然后使用 DROP FUNCTION，则服务器可能会崩溃。

DROP FUNCTION 语法

DROP FUNCTION function_name

该语句删除名为function_name的 user-defined function(UDF)。

要删除 function，您必须具有mysql系统数据库的删除特权。这是因为DROP FUNCTION从记录 function 的 name，type 和 shared library name 的mysql.func system table 中删除了一行。

INSTALL PLUGIN 语法

INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

此语句安装服务器插件。它需要mysql.plugin system table 的插入特权。

plugin_name是库文件中包含的插件描述符结构中定义的插件名称（请参见第28.2.4.2节“插件数据结构”）。插件名称不区分大小写。为了获得最大的兼容性，插件名称应限于ASCII字母，数字和下划线，因为它们用于C源文件，shell命令行，M4和Bourne shell脚本以及SQL环境中。

shared_library_name是包含插件代码的共享库的名称。该名称包括文件扩展名（例如， libmyplugin.so， libmyplugin.dll，或 libmyplugin.dylib）。

共享 library 必须位于插件目录(由plugin_dir系统变量命名的目录)中。 library 必须位于插件目录本身，而不是在子目录中。默认情况下，plugin_dir是pkglibdir configuration 变量指定的目录下的plugin目录，但可以通过在服务器启动时设置plugin_dir的 value 来更改它。例如，在my.cnf 文件中设置其值：

[mysqld]
plugin_dir=/path/to/plugin/directory

如果的值plugin_dir是相对路径名，则将其视为相对于MySQL基本目录（basedir系统变量的值）。

INSTALL PLUGIN加载并初始化插件代码以使插件可用。插件通过执行其初始化函数进行初始化，该初始化函数处理该插件使用前必须执行的所有设置。当服务器关闭时，它将为每个已加载的插件执行反初始化功能，以便该插件有机会执行任何最终清理。

INSTALL PLUGIN还通过在mysql.plugin系统表中添加指示插件名称和库文件名的行来注册插件。服务器启动时，服务器将加载并初始化mysql.plugin中列出的所有插件。

这意味着一个插件仅INSTALL PLUGIN安装一次，而不是每次服务器启动时都安装一次。如果使用该--skip-grant-tables选项启动服务器，则不会在启动时加载插件。

一个插件库可以包含多个插件。对于每个要安装的文件，请使用单独的INSTALL PLUGIN语句。每个语句命名一个不同的插件，但是它们都指定相同的库名。

INSTALL PLUGIN使服务器读取选项（my.cnf）文件，就像在服务器启动期间一样。这使插件可以从那些文件中选取所有相关选项。甚至可以在加载插件之前将插件选项添加到选项文件中（如果使用了 loose前缀）。也可以卸载插件，然后编辑my.cnf并重新安装该插件。以这种方式重新启动插件可以使其具有新的选项值，而无需重新启动服务器。

有关在服务器启动时控制单个插件 loading 的选项，请参阅第 5.5.1 节，“安装和卸载插件”。如果在给出--skip-grant-tables选项时需要为单个服务器启动加载插件(这告诉服务器不要读取系统表)，请使用--plugin-load选项。

要删除插件，请使用以下UNINSTALL PLUGIN语句。

要查看插件安装了哪些，使用 SHOW PLUGINS的语句或查询INFORMATION_SCHEMA的 PLUGINS表。

如果重新编译插件库并需要重新安装，则可以使用以下两种方法之一：

使用UNINSTALL PLUGIN卸载所有插件库，在插件目录中安装新的插件 library 文件，然后使用 INSTALL PLUGIN安装 library 中的所有插件。此过程的优点是可以在不停止服务器的情况下使用它。但是，如果插件库包含许多插件，则必须发出许多INSTALL PLUGINand UNINSTALL PLUGIN语句。
停止服务器，在plugin目录中安装新的插件库文件，然后重新启动服务器。

UNINSTALL PLUGIN 语法

UNINSTALL PLUGIN plugin_name

此语句删除已安装的服务器插件。它需要mysql.plugin system table 的删除特权。 UNINSTALL PLUGIN是NSTALL PLUGIN的补充I。

plugin_name必须是mysql.plugin table 中列出的某个插件的 name。服务器执行插件的反初始化功能，并从mysql.plugin系统表中删除该插件的行，以便随后的服务器重新启动不会加载和初始化该插件。 UNINSTALL PLUGIN不会删除插件的共享库文件。

如果任何使用它的 table 打开，则无法卸载插件。

删除插件对关联表的使用有影响。例如，如果将全文分析器插件与FULLTEXT表上的索引相关联，则卸载该插件会使表无法使用。尝试访问该表都会导致错误。该表甚至无法打开，因此您不能删除使用该插件的索引。这意味着卸载插件是需要小心的事情，除非您不在乎表内容。如果要卸载插件而不打算在以后重新安装它，并且您关心表的内容，则应使用mysqldump转储表，并从转储的创建 TABLE语句中删除WITH PARSER子句，以便稍后可以重新加载 table。如果您不关心该表，DROP TABLE即使缺少与该表关联的任何插件也可以使用。

SET 语法

该SET 声明有几种形式。与特定服务器功能不相关的那些表格的说明出现在本节的小节中：

SET varname = value使您可以为影响服务器或 clients 操作的变量赋值。

SET CHARACTER SET and SET NAMES为与服务器当前连接关联的字符集和排序规则变量赋值。

变量赋值的 SET 语法

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

SET 变量分配的语法使您可以将值分配给影响服务器或客户端操作的不同类型的变量：

用户定义的变量。
存储过程和函数参数，以及存储程序局部变量。
系统变量。

分配变量值的 SET 语句不会写入二进制 log，因此在复制方案中，它仅影响执行它的 host。要影响所有复制主机，请在每个 host 上执行该语句。

以下各节介绍 SET 设置变量的语法。他们使用= 赋值运算符，但是也允许:= 赋值运算符。

User-Defined 变量分配

用户定义的变量在会话内本地创建，并且仅在该会话的上下文中存在；见第 9.4 节，“User-Defined 变量”。

user-defined 变量写为@var_name，并按如下方式分配表达式 value：

SET @var_name = expr;

例子：

SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);

正如那些语句所证明的那样，expr的范围可以从简单(文字 value)到更复杂(由标量子查询返回的 value)。

Performance Schema user_variables_by_thread table 包含有关 user-defined 变量的信息。

参数和局部变量赋值

SET 适用于在其中定义它们的存储对象的上下文中的参数和局部变量。以下过程使用increment过程参数和counter局部变量：

CREATE PROCEDURE p(increment INT)
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 10 DO
    -- ... do work ...
    SET counter = counter + increment;
  END WHILE;
END;

系统变量分配

MySQL服务器维护配置其操作的系统变量。系统变量可以具有影响整个服务器操作的全局值，影响当前 session 的 session value，或两者。许多系统变量是动态的，可以在运行时使用该SET 语句更改以影响当前服务器实例的操作。（要使全局系统变量设置永久生效，以便将其应用于服务器重启时，还应在选项文件中进行设置。）

如果更改会话系统变量，则该值将在会话内保持有效，直到将变量更改为其他值或会话结束为止。该更改对其他会话没有影响。如果更改全局系统变量，则该值将被记住并用于初始化新会话的会话值，直到将变量更改为其他值或服务器退出。该更改对访问全局值的所有客户端可见。但是，更改仅影响更改后连接的客户端的相应会话值。全局变量更改不会影响任何当前客户端会话的会话值（甚至不会发生 global value 更改发生的 session）

注意
设置 global 系统变量 value 始终需要特殊权限。设置 session 系统变量 value 通常不需要特殊权限，可以由任何用户完成，尽管有 exceptions。

要将值分配给全局系统变量，请在变量名称前加上GLOBAL关键字或@@GLOBAL.限定符：

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;

要将 value 分配给 session 系统变量，请在变量 name 之前加上SESSION或LOCAL关键字，使用@@SESSION.，@@LOCAL.或@@限定符，或者不输入任何关键字或不使用修饰符：

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';

客户端可以更改其自己的会话变量，但不能更改任何其他客户端的会话变量。

要将全局系统变量值设置为嵌入式MySQL默认值，或者将会话系统变量设置为当前对应的全局值，请将变量value 设置为DEFAULT。例如，以下两个语句在将的会话值设置max_join_size为当前全局值时是相同的：

SET @@SESSION.max_join_size = DEFAULT;
SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;

要显示系统变量名称和值：

使用SHOW VARIABLES 语句；
多个 Performance Schema 表提供系统变量信息。

SET 错误处理

如果 SET语句中的任何变量赋值失败，则整个语句将失败，并且不会更改任何变量。

SET 在此处描述的情况下会产生错误。大多数示例都显示 SET 使用关键字语法的语句（例如 GLOBAL或SESSION），但是对于使用相应修饰符（例如@@GLOBAL.或 @@SESSION.）的语句，其原理也适用。

使用 SET （任何变体）设置只读变量：

mysql> SET GLOBAL version = 'abc';
ERROR 1238 (HY000): Variable 'version' is a read only variable

使用GLOBAL来设置仅具有会话值的变量：

mysql> SET GLOBAL sql_log_bin = ON;
ERROR 1231 (42000): Variable 'sql_log_bin' can't be
set to the value of 'ON'

使用SESSION设置只有 global value 的变量：

mysql> SET SESSION max_connections = 1000;
ERROR 1229 (HY000): Variable 'max_connections' is a
GLOBAL variable and should be set with SET GLOBAL

省略GLOBAL以设置仅具有 global value 的变量：

mysql> SET max_connections = 1000;
ERROR 1229 (HY000): Variable 'max_connections' is a
GLOBAL variable and should be set with SET GLOBAL

@@GLOBAL.，@@SESSION.和@@修饰符仅适用于系统变量。尝试将它们应用于 user-defined 变量，存储过程或 function 参数或存储的程序局部变量时会发生错误。
并非所有系统变量都可以设置为 DEFAULT。在这种情况下，分配 DEFAULT会导致错误。
尝试分配 DEFAULT给用户定义的变量，存储过程或函数参数或存储程序局部变量时发生错误。

多变量分配

一条SET 语句可以包含多个变量分配，以逗号分隔。该语句为用户定义的变量和系统变量分配一个值：

SET @x = 1, SESSION sql_mode = '';

如果在单个语句中设置了多个系统变量，则该语句中的最新GLOBAL或 SESSION关键字用于后续未指定关键字的分配。

多变量分配的示例：

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@GLOBAL.sort_buffer_size = 1000000, @@LOCAL.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

@@GLOBAL.，@@SESSION.和@@修饰符仅适用于紧随其后的系统变量，而不适用于任何剩余的系统变量。此语句将sort_buffer_size global value 设置为 50000，将 session value 设置为 1000000：

SET @@GLOBAL.sort_buffer_size = 50000, sort_buffer_size = 1000000;

表达式中的系统变量引用

要在表达式中引用系统变量的值，请使用@@-modifiers之一。例如，您可以在如下SELECT语句中检索系统变量值

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@sql_mode;

注意
对表达式中的系统变量(带@@而不是@@GLOBAL.或@@SESSION.)的引用为返回会话值，否则返回全局值。这与SET @@var_name = expr不同，SET @@var_name = expr总是指 session value。

SET CHARACTER SET 语法

SET {CHARACTER SET | CHARSET}
    {'charset_name' | DEFAULT}

该语句使用给定的映射关系映射在服务器和当前客户端之间发送的所有字符串。SET CHARACTER SET设置三个会话系统变量： character_set_client和 character_set_results设置为给定的字符集，和 character_set_connection设置为character_set_database的值。

charset_name可以引用或不引用。

可以使用 value DEFAULT恢复默认字符集映射。默认值取决于服务器 configuration。某些字符集不能用作客户端字符集。尝试SET CHARACTER SET与它们一起使用会产生错误。

SET NAMES 语法

SET NAMES {'charset_name'
    [COLLATE 'collation_name'] | DEFAULT}

此语句将三个 session 系统变量character_setclient，character_set_connection和character_set_results设置为给定的字符集。将character_set_connection设置为charset_name，也为charset_name的默认排序规则。

可选COLLATE子句可用于显式指定排序规则。如果提供，则排序规则必须是 charset_name允许的排序规则之一。

charset_name和collation_name可以引用或不引用。

可以使用值 DEFAULT恢复默认映射。默认值取决于服务器配置。

某些字符集不能用作客户端字符集。尝试与SET NAMES它们一起使用会产生错误。

SHOW 语法

SHOW具有许多形式，可提供有关数据库，表，列或有关服务器的状态信息的信息。本节介绍以下内容：

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

如果给定SHOW 语句的语法包括部分，则'pattern'是 string，可以包含 SQL %和_通配符。该模式对于将语句输出限制为匹配值很有用。

一些SHOW语句还接受一个WHERE子句，该子句在指定要显示的行时提供了更大的灵活性。

许多MySQL API（例如PHP）使您可以将SHOW语句返回的结果视为 SELECT；此外，您可以在 SQL 中使用来自INFORMATION_SCHEMA数据库中的表的查询的结果，而使用SHOW statements 的结果则无法轻松完成。

SHOW BINARY LOGS 语法

SHOW BINARY LOGS
SHOW MASTER LOGS

Other Administrative Statements

BINLOG 语法

BINLOG 'str'

BINLOG是内部使用声明。它由mysqlbinlog 程序生成，作为二进制日志文件中某些事件的可打印表示形式。

CACHE INDEX 语法

CACHE INDEX {
      tbl_index_list [, tbl_index_list] ...
    | tbl_name PARTITION (partition_list)
  }
  IN key_cache_name

tbl_index_list:
  tbl_name [{INDEX|KEY} (index_name[, index_name] ...)]

partition_list: {
    partition_name[, partition_name] ...
  | ALL
}

CACHE INDEX语句将 table 索引分配给特定的 key 缓存。它仅适用于MyISAM表，包括分区的MyISAM表。分配索引后，如果需要，可以使用 LOAD INDEX INTO CACHE将它们预加载到缓存中。

以下语句将表t1，t2和t3中的索引分配给名为hot_cache的 key 缓存：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

CACHE INDEX的语法使您可以指定只应将 table 中的特定索引分配给缓存。但是，该实现将表的所有索引分配给高速缓存，因此没有理由指定除 table name 之外的任何内容。

可以通过使用参数设置语句或服务器参数设置设置其大小来创建CACHE INDEX语句中引用的 key 高速缓存。例如：

SET GLOBAL keycache1.key_buffer_size=128*1024;

Key 缓存参数作为结构化系统变量的成员进行访问。

在为其分配索引之前，必须存在 key 缓存，否则会发生错误：

mysql> CACHE INDEX t1 IN non_existent_cache;
ERROR 1284 (HY000): Unknown key cache 'non_existent_cache'

默认情况下，table 索引分配给在服务器启动时创建的主(默认)key 缓存。销毁 key 缓存时，分配给它的所有索引都会重新分配给默认的 key 缓存。

索引分配会全局影响服务器：如果一个 client 为给定缓存分配索引，则无论哪个 client 发出查询，此缓存都将用于涉及索引的所有查询。

分区的MyISAM表支持CACHE INDEX。您可以将一个，多个或所有分区的一个或多个索引分配给给定的 key 缓存。例如，您可以执行以下操作：

CREATE TABLE pt (c1 INT, c2 VARCHAR(50), INDEX i(c1))
    ENGINE=MyISAM
    PARTITION BY HASH(c1)
    PARTITIONS 4;

SET GLOBAL kc_fast.key_buffer_size = 128 * 1024;
SET GLOBAL kc_slow.key_buffer_size = 128 * 1024;

CACHE INDEX pt PARTITION (p0) IN kc_fast;
CACHE INDEX pt PARTITION (p1, p3) IN kc_slow;

上一组 statements 执行以下操作：

创建一个包含 4 个分区的分区 table;这些分区自动命名为p0，...，p3;此 table 在c1列上有一个名为i的索引。
创建名为kc_fast和kc_slow的 2 个 key 缓存
将分区p0的索引分配给kc_fast key 缓存，将分区p1和p3的索引分配给kc_slow key 缓存;剩余分区的索引(p2)使用服务器的默认 key 缓存。

如果您希望将 table pt中的所有分区的索引分配给名为kc_all的单个 key 缓存，则可以使用以下两个 statements 之一：

CACHE INDEX pt PARTITION (ALL) IN kc_all;

CACHE INDEX pt IN kc_all;

刚显示的两个语句是等效的，发出任何一个都具有完全相同的效果。换句话说，如果您希望将分区 table 的所有分区的索引分配给同一 key 缓存，则PARTITION (ALL)子句是可选的。

将多个分区的索引分配给 key 缓存时，分区不必是连续的，您无需在任何特定的 order 中列出其名称。未明确分配给 key 缓存的任何分区的索引会自动使用服务器默认的 key 缓存。

分区的MyISAM表也支持索引预加载。

FLUSH 语法

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
    flush_option [, flush_option] ...
  | tables_option
}

flush_option: {
    BINARY LOGS
  | DES_KEY_FILE
  | ENGINE LOGS
  | ERROR LOGS
  | GENERAL LOGS
  | HOSTS
  | LOGS
  | PRIVILEGES
  | OPTIMIZER_COSTS
  | QUERY CACHE
  | RELAY LOGS [FOR CHANNEL channel]
  | SLOW LOGS
  | STATUS
  | USER_RESOURCES
}

tables_option: {
    TABLES
  | TABLES tbl_name [, tbl_name] ...
  | TABLES WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... FOR EXPORT
}

FLUSH语句有几种变体形式，用于清除或重新加载各种内部高速缓存，刷新表或获取锁。要执行FLUSH，您必须具有RELOAD权限。特定刷新选项可能需要其他权限，如稍后所述。

注意

无法FLUSH在存储的函数或触发器中发出语句。但是，您可以FLUSH在存储过程中使用，只要不从存储函数或触发器中调用它们即可。

注意

FLUSH LOGS， FLUSH BINARY LOGS， FLUSH TABLES WITH READ LOCK （带或不带表列表）， FLUSH TABLES tbl_name ... FOR EXPORT在任何情况下都不会写入二进制 log，因为如果复制到从站会导致问题。FLUSH语句导致隐式提交。

KILL语句

KILL [CONNECTION | QUERY] processlist_id

每个到mysqld的连接都在一个单独的线程中运行。您可以使用KILL processlist_id语句终止一个线程。

可以从INFORMATION_SCHEMA PROCESSLIST table 的ID列，SHOW PROCESSLIST输出的Id列，和 Performance Schema threads table的PROCESSLIST_ID column确定线程进程列表标识符。CONNECTION_ID() function 返回当前线程的 value。

KILL 允许可选的CONNECTION或QUERY修饰符：

KILL CONNECTION is the same as KILL 没有修饰符：在终止连接正在执行的任何语句后，它终止与给定processlist_id关联的连接。
KILL QUERY终止连接当前正在执行的语句，但保持连接本身不变。

查看哪些线程可以被杀死的能力取决于 PROCESS 特权：

没有 PROCESS，您只能看到自己的线程。
使用 PROCESS，您可以看到所有线程。

杀死线程和 statements 的能力取决于超特权：

如果没有超，则只能删除自己的线程和 statements。
使用超，您可以终止所有线程和 statements。

您还可以使用 mysqladmin processlist 和 mysqladmin kill命令来检查和终止线程。

注意

您不能使用KILL嵌入式MySQL服务器库，因为嵌入式服务器仅在主机应用程序的线程内运行。它不会自己创建任何连接线程。

使用KILL时，将为线程设置特定于线程的kill标志。在大多数情况下，线程死亡可能需要一些时间，因为仅在特定时间间隔检查kill标志：

在SELECT操作过程中，for ORDER BY和GROUP BY loop会在读取一行行之后检查该标志。如果设置了kill标志，该语句将中止。
ALTER TABLE进行表复制的操作会定期对从原始表读取的每几条复制行检查kill标志。如果设置了kill标志，该语句将中止并删除临时表。
该KILL语句返回而无需等待确认，但是kill标志检查会在相当短的时间内中止操作。中止该操作以执行任何必要的清理也需要一些时间。
在UPDATE或 DELETE操作期间，在每次读取块之后以及在每个更新或删除的行之后检查kill标志。如果设置了kill标志，该语句将中止。如果您不使用事务，则更改不会回滚。
GET_LOCK()中止并返回 NULL。
如果线程在表锁处理程序中（状态 Locked：），则表锁将快速中止。
如果线程在写调用中等待可用磁盘空间，则写操作将中止，并显示“ disk full ” 错误消息。

警告

杀死 MyISAM表上的REPAIR TABLE或 OPTIMIZE TABLE操作会导致表损坏且无法使用。除非您再次对其进行优化或修复（不间断），否则对该表的任何读取或写入都会失败。

LOAD INDEX INTO CACHE 语法

LOAD INDEX INTO CACHE
  tbl_index_list [, tbl_index_list] ...

tbl_index_list:
  tbl_name
    [PARTITION (partition_list)]
    [{INDEX|KEY} (index_name[, index_name] ...)]
    [IGNORE LEAVES]

partition_list: {
    partition_name[, partition_name] ...
  | ALL
}

LOAD INDEX INTO CACHE语句将表索引预加载到由显式CACHE INDEX语句为其分配的键高速缓存中，否则加载到默认键高速缓存中

LOAD INDEX INTO CACHE仅适用于MyISAM 表，包括分区MyISAM表。另外，分区表上的索引可以预加载一个，几个或所有分区。

所述IGNORE LEAVES改性剂导致要预装只为索引的非叶结点的块。

分区的MyISAM表也支持IGNORE LEAVES。

以下语句为表t1和t2预加载索引的节点(索引块)：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

此语句从t1预加载所有索引块。它仅从t2预加载非叶节点的块。

RESET 语法

RESET reset_option [, reset_option] ...

reset_option: {
    MASTER
  | QUERY CACHE
  | SLAVE
}

RESET语句用于清除各种服务器操作的状态。您必须具有 RELOAD执行权限 RESET。

RESET充当FLUSH声明的更强版本。

RESET语句导致隐式提交。

以下列表描述了允许的 RESET语句 reset_option值：

RESET MASTER

删除索引文件中列出的所有二进制日志，将二进制 log 索引文件重置为空，并创建一个新的二进制 log 文件。

RESET QUERY CACHE

从查询缓存中删除所有查询结果。

注意
从 MySQL 5.7.20 开始，不推荐使用查询缓存，并在 MySQL 8.0 中删除。弃用包括 RESET QUERY CACHE。

RESET SLAVE

使从属服务器在 master 二进制日志中忘记其复制位置。还通过删除任何现有的 relay log files 并重新开始一个来重置 relay log。

SHUTDOWN 语法

SHUTDOWN

该语句停止 MySQL 服务器。它需要关掉特权。

MySQL 5.7.9 中添加了关掉。它为使用mysqladmin shutdown命令或mysql_shutdown() C API function 提供的相同功能提供 SQL-level 接口。

举杯酌酒望明月

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Database Administration Statements

帐户管理ALTER USER 语法ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH resource_option [resource_option] ...]...
复制链接

扫一扫