首頁猿問如何在Rails的WHERE子句中...

如何在Rails的WHERE子句中使用ANY而不是IN？

SQL Server

皈依舞 2019-11-02 13:39:33

我曾經(jīng)有過這樣的查詢：MyModel.where(id: ids)生成sql查詢，如：SELECT "my_models".* FROM "my_models"WHERE "my_models"."id" IN (1, 28, 7, 8, 12)現(xiàn)在，我想將其更改為ANY而不是IN。我創(chuàng)建了這個：MyModel.where("id = ANY(VALUES(#{ids.join '),('}))"現(xiàn)在，當我使用空數(shù)組時ids = []，出現(xiàn)以下錯誤：MyModel Load (53.0ms) SELECT "my_models".* FROM "my_models" WHERE (id = ANY(VALUES()))ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"Position: 75: SELECT "social_messages".* FROM "social_messages" WHERE (id = ANY(VALUES())) from arjdbc/jdbc/RubyJdbcConnection.java:838:in `execute_query'

查看完整描述

3 回答

白板的微信

TA貢獻1883條經(jīng)驗獲得超3個贊

IN表達式有兩種變體：

expression IN (subquery)

expression IN (value [, ...])

同樣，ANY構(gòu)造的兩個變體：

expression operator ANY (subquery)

expression operator ANY (array expression)

子查詢適用于任何一種技術(shù)，但是對于每種技術(shù)的第二種形式，它都IN希望有一個值列表（如標準SQL中所定義），而= ANY希望有一個數(shù)組。

使用哪個？

ANY是后來的一種更通用的加法，它可以與任何返回布爾值的二進制運算符結(jié)合使用。IN燒成的特例ANY。實際上，它的第二種形式是在內(nèi)部重寫的：

IN用= ANY

NOT IN重寫<> ALL

檢查EXPLAIN輸出是否有任何查詢以供自己查看。這證明了兩件事：

IN永遠不會比= ANY。

= ANY 不會大大加快。

選擇應該由更容易提供的內(nèi)容決定：一個值列表或一個數(shù)組（可能作為數(shù)組文字-一個值）。

如果您要傳遞的ID仍然來自數(shù)據(jù)庫內(nèi)部，則直接選擇它們（子查詢）或?qū)⒃幢砑傻讲樵冎蠮OIN（例如@mu commented）會更加有效。

要通過一個長長的清單從客戶價值并獲得最佳的性能，使用一個數(shù)組，unnest()并加入，或使用提供它作為表表達式VALUES（如@PinnyM評論）。但是請注意，a JOIN可以或不可以在提供的數(shù)組/集中保留可能的重復項。更多：IN= ANY

使用大IN優(yōu)化Postgres查詢

在存在NULL值的情況下，NOT IN通常是錯誤的選擇，而且NOT EXISTS是正確的（而且速度也更快）：

選擇其他表中不存在的行

的語法 = ANY

對于數(shù)組表達式，Postgres接受：

形式的數(shù)組構(gòu)造函數(shù)（數(shù)組是從Postgres一側(cè)的值列表構(gòu)造的）：ARRAY[1,2,3]

或形式的數(shù)組文字'{1,2,3}'。

為了避免無效的類型轉(zhuǎn)換，可以顯式轉(zhuǎn)換：

ARRAY[1,2,3]::numeric[]

'{1,2,3}'::bigint[]

有關：

PostgreSQL：將數(shù)組傳遞給過程的問題

如何將自定義類型數(shù)組傳遞給Postgres函數(shù)

或者，您可以創(chuàng)建一個帶VARIADIC參數(shù)的Postgres函數(shù)，該函數(shù)接受單個參數(shù)并從中形成一個數(shù)組：

在單個參數(shù)中傳遞多個值

如何從Ruby傳遞數(shù)組？

假設id是integer：

MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})

反對回復 2019-11-02

慕虎7371278

TA貢獻1802條經(jīng)驗獲得超4個贊

您的解決方案工作完美，除了當我使用空數(shù)組時，我得到：ActiveRecord :: StatementInvalid（ActiveRecord :: JDBCError：org.postgresql.util.PSQLException：錯誤：運算符不存在：integer = text提示：沒有運算符與給定名稱匹配，并且參數(shù)類型，您可能需要添加顯式類型強制轉(zhuǎn)換

反對回復 2019-11-02