Mysql 简明教程

MySQL - Change Password

MySQL 为每个用户提供一个帐户，该帐户使用用户名和密码进行身份验证。MySQL 中的默认帐户是 root，没有密码（然而，可以使用语句为 root 设置密码）。类似地，其他用户定义的帐户可以使用 SQL 语句显式设置密码，或者可以使用 MySQL 系统自动生成密码。

MySQL provides an account to each user which is authenticated with a username and a password. The default account in MySQL is a root with no password (One can however set a password to the root using a statement). Similarly, other user-defined accounts can have passwords set explicitly using an SQL statement or can have it system generated by MySQL.

MySQL Change User Password

与任何其他经过身份验证的帐户一样，MySQL 有一项规定，可以更改用户密码。但必须确保当前没有应用程序正在被用户使用。如果在不断开应用程序的情况下重置了密码，应用程序将无法再通过此用户连接到服务器。

Just like any other authenticated accounts, MySQL has a provision to change the user password. But one must make sure that there is currently no application being used by the user. If the password is reset without disconnecting the application, the application cannot connect to the server through this user again.

我们可以使用以下三个 SQL 语句更改 MySQL 用户帐户的密码：

We can change the password for a MySQL user account using the following three SQL statements −

  1. UPDATE statement

  2. SET PASSWORD statement

  3. ALTER USER statement

The UPDATE Statement

在 MySQL 中更改用户密码的最基本方法是使用 UPDATE 语句。此语句用于更新帐户详细信息，包括“root”帐户的帐户密码。但是，一旦使用此语句完成修改，就必须使用 FLUSH PRIVILEGES 语句从 MySQL 数据库的授权表重新加载权限。

The most basic way to change a user’s password in MySQL is by using the UPDATE statement. This statement is used to update account details, including the account password, from the 'root' account. But, once the modifications are done using this statement, you must use the FLUSH PRIVILEGES statement to reload privileges from the grant table of the MySQL database.

Syntax

以下是使用 UPDATE 语句更改密码的语法：

Following is the syntax to change password using the UPDATE statement −

UPDATE mysql.user
SET authentication_string = PASSWORD(password_string)
WHERE User = user_name AND
      Host = host_name
FLUSH PRIVILEGES;

Example

以下示例演示如何使用 UPDATE 语句更改用户帐户的密码。首先，我们创建一个用户帐户“sample”，密码为 '123456'：

Following example demonstrates how to change the password of a user account using the UPDATE statement. Firstly, we are creating a user account "sample" with a password '123456' −

CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

现在，可以使用以下查询验证用户列表：

Now, you can verify the list of users using the following query −

SELECT User FROM mysql.user;

表将显示如下 -

The table will be displayed as shown below −

如果你有 MySQL 版本 5.7.6 及更高版本，则可以使用以下查询直接修改 mysql.user 表：

If you have the MySQL version 5.7.6 and later, you can directly modify the mysql.user table with the following query −

UPDATE user
SET authentication_string = PASSWORD('xxxxxx')
WHERE User = 'sample' AND Host = 'localhost';

执行上面的代码后，我们得到以下输出： -

After executing the above code, we get the following output −

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

对用户帐户进行更改后，你需要使用 FLUSH PRIVILEGES 语句立即应用这些更改：

After making changes to user accounts, you need to use the FLUSH PRIVILEGES statement to apply these changes immediately −

FLUSH PRIVILEGES;

获得的输出如下所示 −

The output obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

The SET PASSWORD statement

SET PASSWORD 语句用于为 MySQL 帐户设置密码。它包含一个“密码验证”子句，该子句让系统知道需要用另一个密码替换当前用户密码。

The SET PASSWORD statement is used to set a password for a MySQL account. It contains a "password-verification" clause which lets the system know that the current user password needs to be replaced by another.

Syntax

以下是 SET PASSWORD 语句的语法：

Following is the syntax for the SET PASSWORD statement −

SET PASSWORD FOR username@localhost = password_string;

你还可以不使用 FOR 子句使用 SET PASSWORD 更改密码。但是，要使用此语法，你必须已经登录到你想要更改其密码的用户帐户：

You can also change the password using SET PASSWORD without using the FOR clause. To use this syntax however, you must already be logged in on the user account you wish to change the password of −

SET PASSWORD = password_string;

Example

现在，使用 SET PASSWORD 语句，我们将密码更改为 'hello'：

Now, using the SET PASSWORD statement, we are changing the password to 'hello' −

SET PASSWORD = 'hello';

Output

以下是上面代码的输出： -

Following is the output of the above code −

Query OK, 0 rows affected (0.01 sec)

The ALTER USER Statement

要更改 MySQL 用户帐户的任何内容（包括更改密码），ALTER USER 语句比 SET PASSWORD 语句更可取。此语句不是单独使用，而是后面跟着 IDENTIFIED BY 子句来验证新密码。

To alter anything regarding a user account in MySQL, including changing passwords, ALTER USER statement is more preferable than SET PASSWORD statement. This statement is not used alone, instead is followed by the IDENTIFIED BY clause to authenticate the new password.

请注意，用户必须连接到 MySQL 服务器才能使此语句起作用。

Note that the user must be connected to the MySQL server for this statement to work.

Syntax

以下是使用 ALTER USER 语句更改密码的语法：

Following is the syntax to change the password using the ALTER USER statement −

ALTER USER username IDENTIFIED BY 'password';

Example

这里，我们使用下面给出的 ALTER USER 查询，将 sample@localhost 的密码改为“000000” −

Here, we are changing the password of the sample@localhost account to '000000' using the ALTER USER query given below −

ALTER USER sample@localhost IDENTIFIED BY '000000';

Output

以下代码的输出如下：

Output of the above code is shown below −

Query OK, 0 rows affected (0.01 sec)

密码现已更改。要验证，使用新密码再次登录到示例帐户 −

The password is now changed. To verify, log in to the sample account again using the new password −

C:\Windows\System32> mysql -u sample -p
Enter password: ******

mysql>

Changing User password Using a Client Program

除了使用 MySQL 查询更改 MySQL 中的用户密码外，我们还可以使用 Node.js、PHP、Java 和 Python 等客户端程序实现相同的结果。

Besides using MySQL queries to change the user password in MySQL, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

以下是语法 −

Following are the syntaxes −

Example

以下是在 MySQL 中使用客户端程序更改用户密码 −

Following are the client programs to change the user password in MySQL −