Rails 3 MySQL 2报告了一个看似有效的SQL语法的错误

I am trying to use the following bit of code to help in seeding my database. I need to add data continually over development and do not want to have to completely reseed data every time I add something new to the seeds.rb file. So I added the following function to insert the data if it doesn't already exist.

我试图使用以下代码来帮助我的数据库播种。我需要在开发过程中不断添加数据，并且不希望每次向seeds.rb文件添加新内容时都必须完全重新设置数据。所以我添加了以下函数来插入数据（如果它尚不存在）。

def AddSetting(group, name, value, desc)
    Admin::Setting.create({group: group, name: name, value: value, description: desc}) unless Admin::Setting.find_by_sql("SELECT * FROM admin_settings WHERE group = '#{group}' AND name = '#{name}';").exists?
end

AddSetting('google', 'analytics_id', '', 'The ID of your Google Analytics account.')
AddSetting('general', 'page_title', '', '')
AddSetting('general', 'tag_line', '', '')

This function is included in the db/seeds.rb file. Is this the right way to do this?

此函数包含在db / seeds.rb文件中。这是正确的方法吗？

However I am getting the following error when I try to run it through rake.

但是，当我尝试通过rake运行它时，我收到以下错误。

rake aborted!
Mysql2::Error: 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 'group = 'google' AND name = 'analytics_id'' at line 1: SELECT * FROM admin_settings WHERE group = 'google' AND name = 'analytics_id';

Tasks: TOP => db:seed
(See full trace by running task with --trace)

Process finished with exit code 1

What is confusing me is that I am generating correct SQL as far as I can tell. In fact my code generates the SQL and I pass that to the find_by_sql function for the model, Rails itself can't be changing the SQL, or is it?

令我困惑的是，据我所知，我正在生成正确的SQL。实际上我的代码生成了SQL，我将它传递给了模型的find_by_sql函数，Rails本身无法更改SQL，或者是它？

SELECT * FROM admin_settings WHERE group = 'google' AND name = 'analytics_id';

I've written a lot of SQL over the years and I've looked through similar questions here. Maybe I've missed something, but I cannot see it.

多年来我写了很多SQL，我在这里看了类似的问题。也许我错过了什么，但我看不到它。

2 个解决方案

#1

"group" is a keyword so you can't use it as-is as an identifier, you have to quote it with backticks (for MySQL at least):

“group”是一个关键字，所以你不能将它原样用作标识符，你必须用反引号引用它（至少对于MySQL）：

SELECT *
FROM admin_settings
WHERE `group` = 'google'
  AND name    = 'analytics_id'

Any SQL that Rails/ActiveRecord generates will use the quoted version of the column name so I'd guess that you're generating some SQL (or just a snippet of SQL for the WHERE clause) and neglecting to quote the column names.

Rails / ActiveRecord生成的任何SQL都将使用列名的引用版本，因此我猜你正在生成一些SQL（或者仅仅是WHERE子句的一小段SQL），而忽略了引用列名。

I'd recommend against using group as a column name, use something else so that you don't have to worry about sprinkling backticks all over the place in your code.

我建议不要使用group作为列名，使用其他内容，这样您就不必担心在代码中的所有地方都会使用反引号。

#2

group is an invalid field name if left unquoted, as it is a SQL keyword. To fix, surround it with backticks in your find_by_sql query, so your DB doesn't attempt to interpret it as the GROUP keyword.

如果不加引号，则group是无效的字段名称，因为它是SQL关键字。要修复，请在find_by_sql查询中使用反引号将其包围，这样您的数据库就不会尝试将其解释为GROUP关键字。

#1