sqlite3 varchar 与“like"匹配但不是“=" [英] sqlite3 varchar matching with "like" but not "="

问题描述

使用 Rails 3.1 和 sqlite3 开发、测试环境.

在迁移中添加了一个新表:

create_table :api_keys 做 |t|t.string :api_keyt.integer :user_idt.时间戳结尾

这会生成一个具有以下架构的表:

create_table "api_keys", :force =>真的做 |t|t.string "api_key"t.integer "user_id"t.datetime "created_at"t.datetime "updated_at"结尾

在 ActiveRecord 模型中:

before_create :fill_api_key私人的def fill_api_keyself.api_key = SecureRandom.hex(30)结尾

ActiveRecord 的动态查找器方法 find_by_api_key(api_key) 不起作用(返回 nil).同:

ApiKey.where({:api_key => 'something'}).first

在 sqlite3 中，我执行以下操作:

插入 api_keys(id, api_key) 值 (-1, '12345');

如果我现在运行一个选择:

select api_keys.* from api_keys where api_keys.api_key = '12345';

会找到记录.

如果我运行未过滤的选择，则会显示从我的应用程序创建的预先存在的数据:

select api_keys.* from api_keys;

如果我尝试通过将来自这些预先存在的记录之一的长十六进制字符串粘贴到我的查询中来查找预先存在的记录:

select api_keys.* from api_keys where api_keys.api_key = 'long hex string';

然后它不返回任何结果.如果我试试这个:

select api_keys.* from api_keys where api_keys.api_key like 'long hex string';

然后我得到一个匹配.

我在 api_keys.api_key 上创建了一个索引，但没有效果.

此问题影响我的应用程序中的另一个模型，该模型使用 Digest::SHA1::hexdigest 生成类似的随机十六进制数字字符串.

詹姆斯

解决方案

好的，我想我已经弄清楚了.问题不在于这是 Rails 3.1，而在于您可能已经从 Ruby 1.8.7 迁移到 Ruby 1.9.2.

在 Ruby 1.9 中，所有字符串现在都已编码.默认情况下，所有字符串都应该是 UTF-8，但是，SecureRandom.hex(30) 返回 ASCII-8BIT 的编码.>

您可以使用以下命令在 sqlite3 中确认这一点:.dump api_keys，您可能会看到 api_key 字段如下所示:

INSERT INTO "api_keys" VALUES(1,X'376433356530[...]',1);INSERT INTO "api_keys" VALUES(1,'1234567890[...]',1);

第一个是SecureRandom生成的api_key.第二个是通过输入控制台创建的.X 表示该字段被编码为 blob，而不是字符串.

要解决此问题，请将您的 fill_api_key 更改为:

self.api_key = SecureRandom.hex(30).force_encoding('UTF-8')

我刚刚很开心，所以希望它能帮到你.

这里有一些关于 1.9 中 String 更改的很好的细节:http://blog.grayproductions.net/articles/ruby_19s_string

Using Rails 3.1 and sqlite3 for development, test environments.

Added a new table in a migration:

create_table :api_keys do |t|
  t.string :api_key
  t.integer :user_id
  t.timestamps
end

This produces a table with the following schema:

create_table "api_keys", :force => true do |t|
  t.string   "api_key"
  t.integer  "user_id"
  t.datetime "created_at"
  t.datetime "updated_at"
end

In ActiveRecord model:

before_create :fill_api_key

private

def fill_api_key
  self.api_key = SecureRandom.hex(30)
end

ActiveRecord's dynamic finder method find_by_api_key(api_key) does not work (returns nil). Same with:

ApiKey.where({:api_key => 'something'}).first

In sqlite3, I do the following:

insert into api_keys (id, api_key) values (-1, '12345');

If I now run a select:

select api_keys.* from api_keys where api_keys.api_key = '12345';

the record will be found.

Pre-existing data created from my app is displayed if I run an unfiltered select:

select api_keys.* from api_keys;

If I try to find a pre-existing record by pasting into my query a long hex string from one of those pre-existing records:

select api_keys.* from api_keys where api_keys.api_key = 'long hex string';

then it returns no results. If I try this instead:

select api_keys.* from api_keys where api_keys.api_key like 'long hex string';

Then I get a match.

I have created an index on api_keys.api_key but that had no effect.

This problem affects one other model in my application that produces a similar string of random hex digits using Digest::SHA1::hexdigest instead.

James

解决方案

OK, I think I've figured it out. The problem isn't that this is Rails 3.1, it's that you've likely moved from Ruby 1.8.7 to Ruby 1.9.2.

In Ruby 1.9, all strings are now encoded. By default, all strings should be UTF-8, however, SecureRandom.hex(30) returns an encoding of ASCII-8BIT.

You can confirm this in sqlite3 by using this command: .dump api_keys and you'll probably see that the api_key field looks something like this:

INSERT INTO "api_keys" VALUES(1,X'376433356530[...]',1);    
INSERT INTO "api_keys" VALUES(1,'1234567890[...]',1);

The first one is the api_key generated by SecureRandom. The second is the one created by typing into the console. The X indicates the field is encoded as a blob, not as a string.

To get around this, change your fill_api_key to this:

self.api_key = SecureRandom.hex(30).force_encoding('UTF-8')

I just got bit big time by this, so hopefully it helps you out.

There are some good details about the changes to String in 1.9 here: http://blog.grayproductions.net/articles/ruby_19s_string

这篇关于sqlite3 varchar 与“like"匹配但不是“="的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！