error: error: error: SQL语法中有错误

I have a vary basic db/seeds.rb, one single line, fetching a sql file:

我有不同的基本db/seed。rb，一行，取sql文件:

ActiveRecord::Base.connection.execute(IO.read("db/load.sql"))

And the content of the load.sql file is this:

以及负载的内容。sql文件是这样的:

BEGIN;
INSERT INTO cities (id, name, created_at, updated_at) VALUES
(1, 'Goleta', now(), now()),
(2, 'Santa Barbara', now(), now()),
(3, 'Montecito', now(), now()),
(4, 'Summerland', now(), now()),
(5, 'Carpinteria', now(), now()),
(6, 'La Conchita', now(), now());
END;

When running the command

在运行命令

rake db:seed

I am getting this error:

我得到了这个错误:

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 'INSERT INTO cities (id, name, created_at, updated_at) VALUES
(1, 'Goleta', now()' at line 3

I am able to execute without any error the same insert statement from the mysql console. What I am doing wrong here?

我可以在没有任何错误的情况下，从mysql控制台执行相同的insert语句。我做错了什么?

Here is a new version for the file load.sql containing only 2 statements and failing after the first one with same error about the syntax as reported at the beginning of this post:

这是文件加载的新版本。sql只包含2个语句，第一个语句后失败，语法错误与本文开头报告的相同:

delete from stores_tacos;
INSERT INTO cities (id, name, created_at, updated_at) VALUES
(1, 'Goleta', now(), now()),
(2, 'Santa Barbara', now(), now()),
(3, 'Montecito', now(), now()),
(4, 'Summerland', now(), now()),
(5, 'Carpinteria', now(), now()),
(6, 'La Conchita', now(), now());

2 个解决方案

#1

Why don't you do this using a hash of values and a good-old ActiveRecord Class? The whole point of ActiveRecord is to protect you from SQL.

为什么不使用一个值散列和一个老旧的ActiveRecord类来实现这一点呢?ActiveRecord的目的就是保护您免受SQL攻击。

#2

Remove BEGIN and END tokens.

删除开始和结束标记。

This tokens are suposed to be inside a stored procedures triggers or events.

这些令牌可以放在存储过程触发器或事件的内部。

BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs (stored procedures and functions, triggers, and events). A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords. statement_list represents a list of one or more statements, each terminated by a semicolon (;) statement delimiter. The statement_list itself is optional, so the empty compound statement (BEGIN END) is legal.

开始……结束语法用于编写复合语句，这些语句可以出现在存储的程序(存储过程和函数、触发器和事件)中。复合语句可以包含多个语句，其中包含开始和结束关键字。statement_list表示一个或多个语句的列表，每个语句以分号(;)语句分隔符结尾。statement_list本身是可选的，所以空复合语句(开始结束)是合法的。

Source: http://dev.mysql.com/doc/refman/5.7/en/begin-end.html

来源:http://dev.mysql.com/doc/refman/5.7/en/begin-end.html

#1