备忘：MySQL创建用户中的坑

问题

MySQL数据库作业中需要创建多用户，并对其赋予相应的权限。于是我索性用create user语句创建了一个名为lijinlong的用户，如下：

create user lijinlong@"%" identified by "password";
flush privileges;

@后面使用通配符是因为想要这个账户在任何主机都可以登录。创建好账户之后退出数据库，重新用lijinlong的账号登录，发现就算密码输入对了仍然是Access denied。

$ mysql -u lijinlong -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'lijinlong'@'localhost' (using password: YES)

难道是lijinlong账户没有相应的权限，于是用root账户给账户lijinlong赋予了所有权限。

grant all privileges on *.* to lijinlong@"%";

重新登录，依然是Access denied。

解决

百思不得其解，于是去翻MySQL的用户手册，看到如下一段话。

The ‘finley’@’localhost’ account is necessary if there is an anonymous-user account for localhost. Without the ‘finley’@’localhost’ account, that anonymous-user account takes precedence when finley connects from the local host and finley is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the ‘finley’@’%’ account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

传送门：http://dev.mysql.com/doc/refman/5.6/en/adding-users.html

原来是由于新建的账户host为”%”，原生的匿名账户比新建的lijinlong账户更加具体，所以lijinlong会被视为匿名账户。

grant all privileges on *.* to lijinlong@"localhost";

成功解决问题。
还有一种解决办法是直接删除掉无用的匿名账户。