syntax to use near ‘identified by “password“‘ at line 1

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 ‘identified by “password”’ at line 1

在MySQL中对外开启远程访问时报错原语句

grant all privileges on *.* to 'root'@'%' identified by "password";
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 'identified by "password"' at line 1

原因：我的版本是8，所以我拆成了两句

use mysql
select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

create user 'root'@'%' identified by 'password';
grant all privileges on *.* to 'root'@'%';

select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| user             | %         | // 复制的时候没注意粘贴成user了
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

然后就可以愉快的连接数据库了。至于另一个root暂时就先不管了。