MySQL数据库创建用户是报错（ERROR 1396 (HY000): Operation CREATE USER failed）

问题场景：

今天在练习数据基本操作命令的时候发现在创建一个新用户的时候出现报错：ERROR 1396 (HY000): Operation CREATE USER failed for ‘chaochao’@‘127.0.0.1’，检查语句没有任何语法问题

---

问题描述：

在使用create user 命令创建用户的时候系统报错：ERROR 1396 (HY000): Operation CREATE USER failed for ‘chaochao’@‘127.0.0.1’

mysql> select user,host,password from mysql.user;
+--------+-------------------+-------------------------------------------+
| user   | host              | password                                  |
+--------+-------------------+-------------------------------------------+
| root   | localhost         |                                           |
| root   | vlnx136128.rc.com |                                           |
| root   | 127.0.0.1         |                                           |
| root   | ::1               |                                           |
+--------+-------------------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> create user chaochao@'127.0.0.1' identified by 'cc888';
ERROR 1396 (HY000): Operation CREATE USER failed for 'chaochao'@'127.0.0.1'

---

原因分析：

出现问题后往前看执行的sql命令，发现前面曾经创建过一个与chaochao同名的用户，并且使用 delete from 命令对该用户在user表里的记录进行了删除操作，实现与drop user 差不多的作用，但是却给后面新建用户埋下了个大坑

mysql> create user chaochao@'127.0.0.1' identified by 'cc888';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+----------+-------------------+-------------------------------------------+
| user     | host              | password                                  |
+----------+-------------------+-------------------------------------------+
| root     | localhost         |                                           |
| root     | vlnx136128.rc.com |                                           |
| root     | 127.0.0.1         |                                           |
| root     | ::1               |                                           |
| chaochao | 127.0.0.1         | *099EBCAAFC559454E50CE58D22E0FFA7F193C759 |
+----------+-------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> delete from mysql.user where user='chaochao';
Query OK, 1 row affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+--------+-------------------+-------------------------------------------+
| user   | host              | password                                  |
+--------+-------------------+-------------------------------------------+
| root   | localhost         |                                           |
| root   | vlnx136128.rc.com |                                           |
| root   | 127.0.0.1         |                                           |
| root   | ::1               |                                           |
+--------+-------------------+-------------------------------------------+
4 rows in set (0.00 sec)

---

解决方案：

使用 drop user 命令对该用户（chaochao@‘127.0.0.1）进行清理

mysql> drop user chaochao@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

然后再重新执行下创建新用户的命令，发现命令可以成功执行了

mysql> create user chaochao@'127.0.0.1' identified by 'cc888';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+----------+-------------------+-------------------------------------------+
| user     | host              | password                                  |
+----------+-------------------+-------------------------------------------+
| root     | localhost         |                                           |
| root     | vlnx136128.rc.com |                                           |
| root     | 127.0.0.1         |                                           |
| root     | ::1               |                                           |
| chaochao | 127.0.0.1         | *099EBCAAFC559454E50CE58D22E0FFA7F193C759 |
+----------+-------------------+-------------------------------------------+
5 rows in set (0.00 sec)

希望这个分享能帮到和我遇到同样问题的伙伴们，谢谢大家的观看。欢迎伙伴们留言评论吖~