c# - C# 中 MySQL 查询出错？

Question

我的 MySQL 查询收到意外错误，任何人都可以帮助我找出原因吗？

错误消息:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON users.id = user_auth_tickets.user_id WHERE user_auth_tickets.auth_ticket = 'N' at line 1

查询:

dbConnection.SetQuery("SELECT users.id,users.username,users.rank,users.motto,users.look,users.gender,users.last_online,users.credits,users.activity_points,users.home_room,users.block_newfriends,users.hide_online,users.hide_inroom,users.vip,users.account_created,users.vip_points,users.machine_id,users.volume,users.chat_preference,users.focus_preference,users.pets_muted,users.bots_muted,users.advertising_report_blocked,users.last_change,users.gotw_points,users.ignore_invites,users.time_muted,users.allow_gifts,users.friend_bar_state,users.disable_forced_effects,users.allow_mimic,users.rank_vip " +
  "FROM users" +
  "JOIN user_auth_tickets " +
  "ON users.id = user_auth_tickets.user_id " +
  "WHERE user_auth_tickets.auth_ticket = @sso AND last_ip = @lastIp " +
  "LIMIT 1"
);

Answer 1

您的查询中有两个 WHERE 子句。看起来您的初始查询可能较短，您确实正确放置了 WHERE 子句，但后来决定JOIN 附加表并假设附加条件。此时，您需要重新构造查询，根据下面引用中的语法层次结构，WHERE 位于表引用之后。

Per the MySQL 5.6 Reference Manual - SELECT Syntax

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

删除第一个 WHERE，并将 last_ip = @lastIp 附加到下面的 WHERE。

例如:

dbConnection.SetQuery("SELECT users.id,users.username,users.rank,users.motto,users.look,users.gender,users.last_online,users.credits,users.activity_points,users.home_room,users.block_newfriends,users.hide_online,users.hide_inroom,users.vip,users.account_created,users.vip_points,users.machine_id,users.volume,users.chat_preference,users.focus_preference,users.pets_muted,users.bots_muted,users.advertising_report_blocked,users.last_change,users.gotw_points,users.ignore_invites,users.time_muted,users.allow_gifts,users.friend_bar_state,users.disable_forced_effects,users.allow_mimic,users.rank_vip " +
"FROM users " +
"JOIN user_auth_ticket ON users.id = user_auth_ticket.user_id " +
"WHERE user_auth_ticket.auth_ticket = @sso AND last_ip = @lastIp " +
"LIMIT 1"
);