如何遍历 MySQL 结果集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10836805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I iterate through a MySQL result set?
提问by Only Bolivian Here
Here is the code I'm using:
这是我正在使用的代码：
# Run the query against the database defined in .yml file.
# This is a Mysql::result object - http://www.tmtm.org/en/mysql/ruby/
@results = ActiveRecord::Base.connection.execute(@sql_query)
In my View, here's what I do to see the values:
在我的视图中，这是我为查看值所做的工作：
<pre><%= debug @results %></pre>
Outputs: #<Mysql2::Result:0x007f31849a1fc0>
<% @results.each do |val| %>
<%= val %>
<% end %>
Outputs: ["asdfasdf", 23, "qwefqwef"] ["sdfgdsf", 23, "asdfasdfasdf"]
So imagine I query something like select * from Person, and that returns a result set such as:
所以想象我查询类似的东西select * from Person，并返回一个结果集，例如：
ID Name Age
1 Sergio 22
2 Lazlow 28
3 Zeus 47
How can I iterate through each value and output it?
如何遍历每个值并输出它？
The documentation here is not useful because I have tried methods that supposedly exist, but the interpreter gives me an error saying that those methods don't exist. Am I using the wrong documentation?
此处的文档没有用，因为我尝试了应该存在的方法，但是解释器给了我一个错误，说这些方法不存在。我是否使用了错误的文档？
http://www.tmtm.org/en/mysql/ruby/
http://www.tmtm.org/en/mysql/ruby/
Thanks!
谢谢！
回答by Josnidhin
If you are using mysql2 gem then you should be getting the mysql2 result object and according to the docs you should be able to do the following
如果您使用的是 mysql2 gem，那么您应该获得 mysql2 结果对象，根据文档，您应该能够执行以下操作
results.each do |row|
# conveniently, row is a hash
# the keys are the fields, as you'd expect
# the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
# Here's an otter: http://farm1.static.flickr.com/130/398077070_b8795d0ef3_b.jpg
end
Checkout the documentation here
在此处查看文档
So in you case you can do the following
因此，在您的情况下，您可以执行以下操作
<% @results.each do |val| %>
<%= "#{val['id']}, #{val['name']}, #{val['age']}" %>
<% end %>
Edit: you seem to be referring to the wrong doc check the Mysql2 gems doc.
编辑：您似乎指的是错误的文档，请检查 Mysql2 gems 文档。
回答by hajpoj
You could try using ActiveRecord::Base.connection.exec_queryinstead of ActiveRecord::Base.connection.executewhich returns a ActiveRecord::Result(available in rails 3.1+)
您可以尝试使用ActiveRecord::Base.connection.exec_query而不是ActiveRecord::Base.connection.execute返回一个ActiveRecord::Result（在rails 3.1+中可用）
Then you can access it in various ways like .rows, .each, or .to_hash
然后您可以通过各种方式访问它，例如.rows, .each, 或.to_hash
From the docs:
从文档：
result = ActiveRecord::Base.connection.exec_query('SELECT id, title, body FROM posts')
result # => #<ActiveRecord::Result:0xdeadbeef>
# Get the column names of the result:
result.columns
# => ["id", "title", "body"]
# Get the record values of the result:
result.rows
# => [[1, "title_1", "body_1"],
[2, "title_2", "body_2"],
...
]
# Get an array of hashes representing the result (column => value):
result.to_hash
# => [{"id" => 1, "title" => "title_1", "body" => "body_1"},
{"id" => 2, "title" => "title_2", "body" => "body_2"},
...
]
# ActiveRecord::Result also includes Enumerable.
result.each do |row|
puts row['title'] + " " + row['body']
end
回答by Arun Jain
Look for @results.fields for column header.
查找列标题的@results.fields。
Example: @results = [[1, "Sergio", 22],[2, "Lazlow", 28],[3, "Zeus", 47]]
示例：@results = [[1, "Sergio", 22],[2, "Lazlow", 28],[3, "Zeus", 47]]
@results.fields do |f|
puts "#{f}\t" # Column names
end
puts "\n"
@results.each do |rows| # Iterate through each row
rows.each do |col| # Iterate through each column of the row
puts "#{col}\t"
end
puts "\n"
end
Hope it is helpful.
希望它有帮助。
回答by coderz
Use :as => :hash:
使用:as => :hash：
raw = ActiveRecord::Base.connection.execute(sql)
raw.each(:as => :hash) do |row|
puts row.inspect # row is hash
end
