可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I've tried using google to answer this seemingly simple question, but to my surprise, it didn't help.
I have code in my rails application currently using the 'prepare' method with the mysql gem. On switching to mysql2, this breaks with the error:
undefined method `prepare' for #<Mysql2::Client::0.......
So I tried looking for a version of the 'prepare' method but this search has been unsuccessful so far. Can anyone help me out with this?
Edit: If this isn't possible, could anyone let me know if there's a way to simply parameterize my queries with something in the mysql2 library?
回答1:
The mysql2
gem now supports prepared statements according to the documentation.
The syntax is as follows:
statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)
This was added with a merged pull request in June 2015.
回答2:
UPDATE
As Ryan Rapp pointed out correctly, mysql2 now supports prepared statements. Following snippet is extracted from the readme:
statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)
statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")
Thanks Ryan!
Original Post
I found no such function either; neither in source nor in the documentation.
Maybe the following snippet is a helpful replacement for your needs? (found in the documentation of mysql2 gem):
escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")
回答3:
I swapped out to use https://github.com/tmtm/ruby-mysql instead of mysql2. I'm surprised this isn't a bigger deal-breaker for people using the mysql2 gem. I guess people who dig this deep into writing SQL have swapped to Postgresql?
In case others are having trouble with gem install ruby-mysql
followed by require "mysql"
where you get a Ruby error like 'read_eof_packet': packet is not EOF (Mysql::ProtocolError)
the trick is to gem uninstall ruby-mysql
and instead gem install ruby-mysql-ext
(or use gem 'ruby-mysql-ext'
in your Gemfile) which will swap out the Ruby implementation which isn't yet Ruby 2.0 compatible (or at least, didn't work for me) for simple C bindings.
To be clear, if you do require 'mysql'
while both ruby-mysql-ext
and ruby-mysql
are installed, it will load the Ruby version. There may be a way to require within a specific gem, but I didn't have time to look it up.
回答4:
Yes, mysql2 adapter doesn't support binding till the current Rails 4.0. I'm being surprised! You could tell this by the code snip from ~/.rvm/gems/ruby-2.1.1/gems/activerecord-4.1.1/lib/active_record/connection_adapters/mysql2_adapter.rb
def exec_query(sql, name = 'SQL', binds = [])
result = execute(sql, name)
ActiveRecord::Result.new(result.fields, result.to_a)
end
alias exec_without_stmt exec_query
# Returns an ActiveRecord::Result instance.
def select(sql, name = nil, binds = [])
exec_query(sql, name)
end
also this is helpful for you understanding:
(in ~/.rvm/gems/ruby-2.1.1/gems/activerecord-4.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb)
# Returns an ActiveRecord::Result instance.
def select_all(arel, name = nil, binds = [])
if arel.is_a?(Relation)
relation = arel
arel = relation.arel
if !binds || binds.empty?
binds = relation.bind_values
end
end
select(to_sql(arel, binds), name, binds)
end
That's it! And I guess I may turn to Postgres!!
回答5:
I'm also surprised that the prepare method is missing. Surely in a common ActiveRecord and Mysql2 setup, ActiveRecord must be escaping the strings rather then using libmysql, which I find a tiny bit worrying.
In the meantime, you can use https://github.com/brianmario/mysql2/tree/stmt
回答6:
Rails and the MySQL Active Record adaptor does not have any support for prepared statements as far as I know:
http://patshaughnessy.net/2011/10/22/show-some-love-for-prepared-statements-in-rails-3-1
This is because they actually don't have any use in speeding statements up and can actually slow things down due to MySQL's lack of query planning.
回答7:
You may also use mysql2-cs-bind gem:
https://github.com/tagomoris/mysql2-cs-bind