如何在Rails 5.2中从mysql查询数组列?

本文介绍了如何在Rails 5.2中从mysql查询数组列?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用gem rails~> 5.2和gem mysql2 >= 0.3.13, < 0.5.

我有一个模型Lawer，它有一个数组列lawer_filed [sic].

I have a model Lawer, which has an array column lawer_filed [sic].

# Model lawer.rb serialize :lawer_field, Array

然后我创建了Lawer，我可以按如下方式获取lawer_field值:

Then I created a Lawer, and I can get the lawer_field value as follows:

=> Lawer.first.lawer_field => ["2", "3", "5"]

现在，我想在使用lawer_field的查询中找到一个Lawer.我试过了:

Now, I want to find one Lawer with a query using lawer_field. I tried:

@lawer = Lawer.where("lawer_field && ARRAY[?]", "2")

出现了这样的错误:

ActiveRecord::StatementInvalid (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 '['2']) LIMIT 11' at line 1: SELECT `lawers`.* FROM `lawers` WHERE (lawer_field && ARRAY['2']) LIMIT 11)

我的SQL语法有一个错误，但是我不知道如何解决它.有人可以帮忙吗?

There is a mistake in my SQL syntax, but I don't how to fix it. Can anyone help?

推荐答案

MySQL与PostgreSQL不同，它不支持数据库中的数组.因此，您需要添加以下行:

MySQL, unlike PostgreSQL, does not support arrays in database. Therefore you needed to add this line:

serialize :lawer_field, Array

这意味着您的数据库中有一个string字段，但是只要ActiveRecord拆包数据库返回的结果时，它就会将它们直接映射到Ruby Array的实例.这意味着您唯一的筛选数据库结果的方法是使用任何MySQL字符串比较功能，LIKE等.

This means that you have a string field in your database, but whenever ActiveRecord is unpacking results returned by the database, it maps them directly to an instance of Ruby Array. What this means is that your only option to filter the results in the database is with any MySQL string comparison functions, LIKE, etc.

您的选择是使用LIKE或执行其他字符串函数(由于您将无法使用索引，因此效果不佳)或构建另一个表，向其中添加has_many关联，并以预期的方式使用MySQL.当然，您也可以迁移到PostgreSQL，但这似乎是最极端的选择.

Your options are to either use LIKE or perform some other String functions (which will not perform well as you will be unable to use indices) or build another table, add a has_many association to it and use MySQL the way it was supposed to be used. You could also, of course, migrate to PostgreSQL, but that seems to be the most extreme option.

编辑:您还可以考虑使用MySQL的 JSON (最近添加).不过这取决于您的MySQL版本.

you could also consider using MySQL`s JSON, which has been added recently. That depends on your version of MySQL though.