MySQL：错误 1142 (42000) 和错误 1064 (42000)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7743392/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: ERROR 1142 (42000) and ERROR 1064 (42000)
提问by cProg
I'm using windows 7. I've downloaded mysql-5.5.16-win32.zipand installed it. I started MySQL server successfully, but I get this error:
我使用的是 Windows 7。我已经下载mysql-5.5.16-win32.zip并安装了它。我成功启动了 MySQL 服务器，但出现此错误：
C:\Program Files\Mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)
mysql> select user, host, password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user
'
mysql> mysql -u root -p
-> select user, host, password from mysql.user;
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 'mysql
-u root -p
select user, host, password from mysql.user' at line 1
mysql> mysql -u root -p root
-> select user, host, password from mysql.user;
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 'mysql
-u root -p root
select user, host, password from mysql.user' at line 1
mysql>
How can set the required privileges, from Windows, to the mysql table users?
如何设置所需的权限，从 Windows 到 mysql 表用户？
回答by Eric Leschinski
The mysql commandline error message:
mysql 命令行错误信息：
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
Means you are logged into mysql with a default null user with just about zero privileges.
意味着您使用默认的空用户登录 mysql，权限几乎为零。
From the command line, run mysql as you did before:
C:\Users\Charity>mysqlWhich brings you to a mysql prompt, run the command
select user();mysql> select user(); +----------------+ | user() | +----------------+ | ODBC@localhost | +----------------+ 1 row in set (0.00 sec)That output means you are connecting not as a user, but as some kind of non-user API connector. ODBC is not in the
mysql.userstable. It is the user that connects to MySQL when you don't specify a user.Run another command:
mysql> select user from mysql.user; ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user' mysql>We didn't connect as any user so it's telling us access denied.
Run these commands:
C:\Users\Charity>mysql -u this_is_not_a_user mysql> select user(); +------------------------------+ | user() | +------------------------------+ | this_is_not_a_user@localhost | +------------------------------+ 1 row in set (0.00 sec) mysql>So we've logged in as another non-user of our definition. Would you expect this user we pulled out of a hat to have any privileges at all? No.
Stop fooling around and login as root:
C:\Users\Charity>mysql -u root -p Enter password: ********** mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>Now you are root, you can see everything.
Look at the mysql.user table:
mysql> select user, host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | | linux | | root | linux | | | localhost | | pma | localhost | | root | localhost | +------+-----------+ 5 rows in set (0.00 sec)You might want to create more users so you can let people use the database without giving them the rights to do anything to your database. Keep the barbarians out at the moat.
You don't want to be logging in as root all the time. So create a new user. Go to phpmyadmin, login to phpadmin as root. Click the "users" tab and create a new user. A new dialog shows, fill in the username, password and permissions. Then you can login as that user:
C:\Users\Charity>mysql -u el -p Enter password: ********** mysql> select user(); +----------------+ | user() | +----------------+ | el@localhost | +----------------+ 1 row in set (0.00 sec) mysql>This user can do everything that you granted in the permissions defined in step 7.
从命令行，像以前一样运行 mysql：
C:\Users\Charity>mysql这将带您进入 mysql 提示符，运行命令
select user();mysql> select user(); +----------------+ | user() | +----------------+ | ODBC@localhost | +----------------+ 1 row in set (0.00 sec)该输出意味着您不是作为用户进行连接，而是作为某种非用户 API 连接器进行连接。ODBC 不在
mysql.users表中。当您不指定用户时，它是连接到 MySQL 的用户。运行另一个命令：
mysql> select user from mysql.user; ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user' mysql>我们没有以任何用户身份连接，所以它告诉我们访问被拒绝。
运行这些命令：
C:\Users\Charity>mysql -u this_is_not_a_user mysql> select user(); +------------------------------+ | user() | +------------------------------+ | this_is_not_a_user@localhost | +------------------------------+ 1 row in set (0.00 sec) mysql>因此，我们已作为我们定义的另一个非用户登录。您是否希望我们摘下帽子的这个用户拥有任何特权？不。
停止鬼混并以root身份登录：
C:\Users\Charity>mysql -u root -p Enter password: ********** mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>现在你是 root，你可以看到一切。
查看mysql.user表：
mysql> select user, host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | | linux | | root | linux | | | localhost | | pma | localhost | | root | localhost | +------+-----------+ 5 rows in set (0.00 sec)您可能希望创建更多用户，这样您就可以让人们使用数据库，而无需授予他们对您的数据库执行任何操作的权限。让野蛮人远离护城河。
您不想一直以 root 身份登录。所以创建一个新用户。转到 phpmyadmin，以 root 身份登录 phpadmin。单击“用户”选项卡并创建一个新用户。出现一个新对话框，填写用户名、密码和权限。然后您可以以该用户身份登录：
C:\Users\Charity>mysql -u el -p Enter password: ********** mysql> select user(); +----------------+ | user() | +----------------+ | el@localhost | +----------------+ 1 row in set (0.00 sec) mysql>此用户可以执行您在步骤 7 中定义的权限中授予的所有操作。
回答by John Flatness
The command mysql -u root -pis supposed to be run at the Windows command line. When you see the prompt
该命令mysql -u root -p应该在 Windows 命令行上运行。当你看到提示
mysql>
This means you're currently using the MySQL command line client (you opened it when you first ran mysqlon the first line of your sample).
这意味着您当前正在使用 MySQL 命令行客户端（在您第一次运行mysql示例的第一行时打开它）。
What you need to do is run the command quit, so you're back at the Windows command prompt, and thenrun
您需要做的是运行该命令quit，以便您返回到 Windows 命令提示符，然后运行
mysql -u root -p
This will start the MySQL client again, but will prompt you for your password to log you in as the user root.
这将再次启动 MySQL 客户端，但会提示您输入密码以用户身份登录root。
回答by user3304639
If you are a beginner, you might not have to type -p just type:
如果您是初学者，您可能不必输入 -p 只需输入：
mysql -u root
