限制客户端使用 MySQL 服务器资源的一种方法是将全局 max_user_connections 系统变量设置为非零值。这限制了任意账户可以建立的同时连接数，但不限制客户端在连接后可以执行的操作。此外，设置 max_user_connections 并不启用对单个账户的管理。这两种类型的控制均是 MySQL 管理员关心的。One means of restricting client use of MySQL server resources is to set the global max_user_connections system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. In addition, setting max_user_connections does not enable management of individual accounts. Both types of control are of interest to MySQL administrators.

为了解决此类问题，MySQL 允许对各个账户使用这些服务器资源设置限制：To address such concerns, MySQL permits limits for individual accounts on use of these server resources:

客户端可以发出的任何语句都计入查询限制。只有修改数据库或表的语句才计入更新限制。Any statement that a client can issue counts against the query limit. Only statements that modify databases or tables count against the update limit.

在此上下文中，“账户” 对应于 mysql.user 系统表中的一行。也就是说，连接会根据适用于该连接的 user 表行中的 User 和 Host 值进行评估。例如，账户 'usera'@'%.example.com' 对应于 user 表中的一行，该行的 User 和 Host 值分别为 usera 和 %.example.com，允许 usera 从 example.com 域中的任何主机连接。在这种情况下，服务器会将该行中的资源限制统一应用于 usera 从 example.com 域中任何主机发起的所有连接，因为所有这些连接使用相同的账户。An “account” in this context corresponds to a row in the mysql.user system table. That is, a connection is assessed against the User and Host values in the user table row that applies to the connection. For example, an account 'usera'@'%.example.com' corresponds to a row in the user table that has User and Host values of usera and %.example.com, to permit usera to connect from any host in the example.com domain. In this case, the server applies resource limits in this row collectively to all connections by usera from any host in the example.com domain because all such connections use the same account.

要在创建账户时为账户建立资源限制，请使用CREATE USER语句。要修改现有账户的限制，请使用ALTER USER。提供一个WITH子句来命名每个要限制的资源。每个限制的默认值为零（无限制）。例如，要创建一个只能以有限方式访问customer数据库的新账户，请执行以下语句：To establish resource limits for an account at account-creation time, use the CREATE USER statement. To modify the limits for an existing account, use ALTER USER. Provide a WITH clause that names each resource to be limited. The default value for each limit is zero (no limit). For example, to create a new account that can access the customer database, but only in a limited fashion, issue these statements:

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

在 WITH 子句中，不必命名所有限制类型，但已命名的类型可以按任意顺序出现。每小时限制的值应为表示每小时计数的整数。对于 MAX_USER_CONNECTIONS，其限制是一个整数，表示该账户允许的最大同时连接数。如果此限制设置为零，则由全局 max_user_connections 系统变量值来确定允许的同时连接数。如果 max_user_connections 也为零，则账户没有限制。The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections by the account. If this limit is set to zero, the global max_user_connections system variable value determines the number of simultaneous connections. If max_user_connections is also zero, there is no limit for the account.

要修改现有账户的限制，请使用ALTER USER语句。下列语句将francis的查询限制更改为 100：To modify limits for an existing account, use an ALTER USER statement. The following statement changes the query limit for francis to 100:

mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

该声明仅修改指定的限额值，而账户的其他方面保持不变。The statement modifies only the limit value specified and leaves the account otherwise unchanged.

要移除限制，请将其值设置为零。例如，要移除限制每小时 francis 可以连接的次数，请使用以下语句：To remove a limit, set its value to zero. For example, to remove the limit on how many times per hour francis can connect, use this statement:

mysql> ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

如前所述，账户的同时连接限制由 MAX_USER_CONNECTIONS 限制和 max_user_connections 系统变量确定。假设全局 max_user_connections 值为 10，且三个账户有如下单独的资源限制：As mentioned previously, the simultaneous-connection limit for an account is determined from the MAX_USER_CONNECTIONS limit and the max_user_connections system variable. Suppose that the global max_user_connections value is 10 and three accounts have individual resource limits specified as follows:

ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0;
ALTER USER 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5;
ALTER USER 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;

user1 的连接限制为 10（全局 max_user_connections 值），因为它的 MAX_USER_CONNECTIONS 限制为零。user2 和 user3 的连接限制分别为 5 和 20，因为它们的 MAX_USER_CONNECTIONS 限制非零。user1 has a connection limit of 10 (the global max_user_connections value) because it has a MAX_USER_CONNECTIONS limit of zero. user2 and user3 have connection limits of 5 and 20, respectively, because they have nonzero MAX_USER_CONNECTIONS limits.

服务器在对应账户的 user 表行中存储账户的资源限制。max_questions、max_updates 和 max_connections 列存储每小时的限制，而 max_user_connections 列存储 MAX_USER_CONNECTIONS 限制。（参见 第 8.2.3 节，“授权表”。）The server stores resource limits for an account in the user table row corresponding to the account. The max_questions, max_updates, and max_connections columns store the per-hour limits, and the max_user_connections column stores the MAX_USER_CONNECTIONS limit. (See Section 8.2.3, “Grant Tables”.)

当任何账户对其使用的任何资源设定了非零限制时，就会进行资源使用计数。Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources.

随着服务器的运行，它会统计每个账户使用资源的次数。如果某个账户在过去一小时内达到连接数的限制，服务器会拒绝该账户的进一步连接，直到该小时结束。同样，如果账户在查询或更新次数上达到限制，服务器会拒绝进一步的查询或更新，直到该小时结束。在所有这些情况下，服务器会发出相应的错误信息。As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, the server rejects further connections for the account until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, the server rejects further queries or updates until the hour is up. In all such cases, the server issues appropriate error messages.

资源计数是按账户进行的，而不是按客户端进行的。例如，如果你的账户查询限制为 50，你不能通过同时创建两个客户端连接到服务器将你的限制增加到 100。两个连接上发出的查询会被共同计数。Resource counting occurs per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.

当前每小时资源使用计数可以针对所有账户全局重置，也可以针对特定账户单独重置：The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:

按小时重置的计数器不会影响 MAX_USER_CONNECTIONS 限制。Per-hour counter resets do not affect the MAX_USER_CONNECTIONS limit.

服务器启动时所有计数都从零开始。计数不会在服务器重启后延续。All counts begin at zero when the server starts. Counts do not carry over through server restarts.

对于 MAX_USER_CONNECTIONS 限制，如果帐户目前已经打开了允许的最大连接数，可能会出现一个边缘情况：如果在断开连接后立即进行连接，可能会导致错误（ER_TOO_MANY_USER_CONNECTIONS 或 ER_USER_LIMIT_REACHED），如果服务器在连接发生时尚未完全处理断开连接。当服务器完成断开连接处理后，又可以允许另一个连接。For the MAX_USER_CONNECTIONS limit, an edge case can occur if the account currently has open the maximum number of connections permitted to it: A disconnect followed quickly by a connect can result in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) if the server has not fully processed the disconnect by the time the connect occurs. When the server finishes disconnect processing, another connection is once more permitted.