MySQL创建用户报错求助：@符号及BY关键字报错问题

Hey there! Let's break down why you're hitting those syntax errors and get your secure login user set up correctly.

Why Your Original Queries Failed #

1. Using double quotes (") around identifiers:
   By default, MySQL treats double quotes as string delimiters, not identifier wrappers. So when you wrote "sec_user"@'localhost', the database tried to parse "sec_user" as a string followed by @'localhost'—which is invalid syntax, hence the error at the @ symbol.

2. Removing all quotes entirely:
   When you stripped all quotes, your query became CREATE USER sec_user@localhost IDENTIFIED BY '...';. MySQL interpreted sec_user@localhost as a single username (instead of splitting it into the username sec_user and host localhost). This messed up the syntax flow, leading to the error at the BY keyword.

The Correct Query #

Use backticks (`) to wrap identifiers (usernames, database names) — this is MySQL's standard way to handle identifiers without needing to change any settings. Here's the fixed code:

CREATE USER `sec_user`@'localhost' IDENTIFIED BY 'eKcGZr59zAa2BEWU';
GRANT SELECT, INSERT, UPDATE ON `secure_login`.* TO `sec_user`@'localhost';

Quick Verification #

After running these commands, you can confirm the user was created successfully with this check:

SELECT user, host FROM mysql.user WHERE user = 'sec_user';

内容的提问来源于stack exchange，提问作者Chris