mysql - 在 mysql 服务器版本中更新 mysql 用户密码的语法错误：8.0.21 MySQL Community Server - GPL

Question

我正在尝试更新用户 == admin 的密码，我的 SQL 版本是Server version: 8.0.21 MySQL Community Server - GPL

+------------------+
| user             |
+------------------+
| admin            |
| admin            |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
+------------------+

update user set authentication_string=PASSWORD('password@1234') where user='admin';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('password@1234') where user='admin'' at line 1

mysql> UPDATE user 
    -> SET authentication_string = PASSWORD('password@1234')
    -> WHERE user = 'admin' AND 
    ->       host = 'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('password@1234')
WHERE user = 'admin' AND 
      host = 'localhost'' at line 2

我已经尝试了上述两种方法，并用密码代替了 authentication_string。无论如何它应该是 authentication_string 因为用户表只包含这个列

标签： mysql

Answer 1

“用户”是保留关键字。你需要用反引号括起来：

UPDATE `user`
SET `authentication_string`=PASSWORD('password@1234')
WHERE `user`='admin';