将可搜索数组存储在SQL数据库字段中 [英] Storing searchable Arrays in a SQL-database field

问题描述

当前，我尝试解决以下问题：
我正在使用的文档管理软件可以为其中的文档定义掩码，并且每个掩码都有一个或多个索引字段（类型：字符串）。系统支持以下数据库：

Currently I try to solve the following problem: The document management software I´m working with can define masks for there documents and each mask has one or more indexfields (type:String). The system supports the following databases:

1. Postgres 2. ORACLE 3. Microsoft SQl 4. DB2

现在，如果可以将字符串数组仅存储在sql数据库的一个字段中，以便每个掩码仅占用sql-中的一行，我应该进行研究。表。
还有一个要求是可以通过sql搜索数组。

Now I should do a research if it is possible to store the array of strings in just one field of the sql database so that each mask only takes up one line in the sql-table. One more requirement is that the array is searchable via sql.

结果应该是sql数据库的碎片化程度不如现在，因为每个索引字段都是掩码定义中的一列，并且就性能而言，结果应该相等（或者如果可能更快）。

The result should be that the sql-database isn´t as fragmented as it is now, cause every indexfield is one column in the mask definition and also in regard of performance the result should be equal (or if possible faster).

当前状态是postgres（到目前为止仅使用Double []）和ORACLE（通过ArrayDouble类型）的工作示例，结果良好（没有碎片，并且性能提高了（> 200％））。例子是用java（标准的jdbc连接）编写的。

The current state is that I have working examples for postgres ( but only with Double[] so far ) and ORACLE ( via the ArrayDouble type ) with good results (no fragmentation and with performance improvements (>200%)). The examples are written in java ( standard jdbc connection ).

对于DB2，我找到了一些有关普通数组（和其他两种类型）的文档，但是无法构建一个有效的文档。例如，对于Microsoft SQL，我找不到任何提示，表明它具有内置的数组功能。

For DB2 I found some documentation about ordinary arrays (and two other types) but was not able to build a working example and for Microsoft SQL I was not able to find any hint that it has an array feature built in.

所以我的主要问题是你们是否有解决此问题的经验并且可以就如何解决此问题特别向我提供有关DB2和Microsoft SQL的建议。另外，如果您对Postgres和/或Oracle有很棒的解决方案，请与我分享您的知识。 ：）

So my main question is if you guys have experience with this problem and can give me advice esspecially regarding DB2 and Microsoft SQL in how to tackle this problem. Also if you have an awesome solution for postgres and/or Oracle please share your knowledge with me. :)

在此先感谢：）祝大家星期一星期一过得愉快：）
帕斯卡问候

Thanks in advance :) wish you all a enjoyable monday morning :) Greetings Pascal

推荐答案

使用Oracle，您可以使用 NESTED TABLE s（或 VARRAY s）：

With Oracle, you can store arrays in a column using NESTED TABLEs (or VARRAYs):

SQL Fiddle

Oracle 11g R2架构设置：

CREATE TYPE String_Table IS TABLE OF VARCHAR2(100)
/

CREATE TABLE test (
  id     NUMBER(10,0),
  col1   VARCHAR2(10),
  array1 String_Table
) NESTED TABLE array1 STORE AS test__array1
/

INSERT INTO test ( id, col1, array1 )
  SELECT 1, 'Row1', String_Table( 'A', 'B', 'C' ) FROM DUAL UNION ALL
  SELECT 2, 'Row2', String_Table( 'C', 'D', 'E' ) FROM DUAL
/

查询1 ：然后您可以使用以下收集操作： MEMBER OF 在集合中查找项目；和 MULTISET 运算符（如 SUBMULTISET OF ）来查找包含另一个集合的所有项目的集合。

Query 1: Then you can use collection operations such as: MEMBER OF to find items in a collection; and MULTISET operators like SUBMULTISET OF to find collections containing all items of another collection.

SELECT *
FROM   test
WHERE  'B' MEMBER OF array1
OR     String_Table( 'E', 'C' ) SUBMULTISET OF array1

结果 ：

Results:

| ID | COL1 | ARRAY1 |
|----|------|--------|
|  1 | Row1 |  A,B,C |
|  2 | Row2 |  C,D,E |

如果您使用Java，则可以将Java数组作为 PreparedStatement 或 CallableStatement 。 此处和在这里。

If you are using Java then you can pass Java arrays as bind parameters of a PreparedStatement or CallableStatement. Some examples of this are here and here.

这篇关于将可搜索数组存储在SQL数据库字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！