MySQL赋权报错：’the right syntax to use near ‘identified by ‘password‘ with grant option‘

MySQL版本8.0.21，在给新用户授权时，执行如下语句：

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

报错如下：

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' with grant option'

原因分析 ：高版本的MySQL把将创建账户和赋予权限分开了。

解决方法：分开执行。

#创建账户
create user 'root'@'%' identified by '123456';

#赋予权限
grant all privileges on *.* to 'root'@'%' with grant option;

#刷新
flush privileges;