SQL语句与数据模式（Select Mode）

在InterSystems IRIS和InterSystems Caché 里，是否您遇到过执行一个SQL Insert/Update语句，明明给的是正确的日期值，但被告知“值‘2022-01-01’ 校验失败”的类似情况，并感到困惑？

如果有，那么您需要了解一下InterSystems IRIS和InterSystems Caché保存和显示数据的模式。

一 数据模式

InterSystems IRIS和InterSystems Caché里，数据有3种模式，称之为SELECT MODE：
逻辑模式：这是数据被保存到InterSystems IRIS和InterSystems Cache'时的格式。例如，%Date类型的数据，在数据库里被保存为一个整数，即从1840年12月31号到这个日期的天数，而不是YYYY-MM-DD的格式。

ODBC模式：这是ODBC对数据定义的格式。在这个模式下，%Date类型的数据就会显示为YYYY-MM-DD的格式。

显示模式：这是数据在InterSystems IRIS和InterSystems Caché里默认的显示格式。例如在美国语言环境下，%Date的默认显示格式是DD/MM/YYYY格式。

InterSystems IRIS和InterSystems Caché只会使用逻辑模式保存数据，但可以以任何模式显示数据。

 

二 数据类型与数据模式

 

大多数数据类型在3种数据模式下的格式是一样的，例如%String、%Integer等。但有些情况下，数据在不同数据模式下的格式是不一样的：

• 时间、日期类型的数据
  • InterSystems提供多种时间、日期数据类型，包括%Date、%Time、%PosixTime、%TimeStamp 和%MV.Date。除了%TimeStamp，这些时间日期数据类型的3种数据模式下的格式都是不同的。
• 列表类型的数据
  • 作为一个多模型数据库，可以指定数据为列表类型（%ListOfDataTypes)。列表类型数据的在不同数据模式下的格式也是不同的。例如一个定义为list of %String的属性/字段，中文数据以显示模式和ODBC模式看，数据显示是没问题的，但逻辑模式下看似乱码（其实并非乱码）。
• 指定了VALUELIST 和 DISPLAYLIST的数据
  • 例如在建立患者模型时，我们指定其性别数据类型为%String，但通过VALUELIST限定它的值只可以是0、1、2、9，通过DISPLAYLIST设置这4个值的显示值为“未知的性别"、 "男性"、"女性"、"未说明的性别"。这样，数据被保存到数据库时，保存的是0、1、2、9，而显示值和ODBC值都是中文说明。
• 空字符串和空字符流数据
  • 在逻辑模式下，空字符串和 空BLOB 由不可显示的字符 $CHAR(0) 表示。在显示模式下，它们由一个空字符串 ("") 表示。

 

三 SQL与数据模式

InterSystems提供很多种SQL操作的方式，而其当使用SQL操作数据的时候，很多操作和数据模式有关，例如数据排序。

SQL操作的方式

您是如何使用SQL的？是通过ODBC/JDBC，还是在Object Script里通过嵌入式SQL或动态SQL，抑或是通过Terminal或管理门户的SQL操作页面？

1. ODBC/JDBC

通过ODBC/JDBC连接到InterSystems数据库时，问题很简单：无论是插入或更新数据，还是查询数据，作为值、查询条件的数据和返回的数据显示结果都是按ODBC格式的。也就是说，这种情况下，您不需要关心数据模式。

2. 嵌入式SQL

如果您在使用嵌入式SQL（&SQL）操作数据，那么就需要留心数据模式了。可以通过#SQLCompile Select这个预处理器指令设置需要的数据模式，例如设置为ODBC模式：

ClassMethod Test(pDocNo = "Doc123456")
{
    #SQLCompile Select=ODBC
    &SQL(Insert into Test.Table(
    DocumentNo,
    DOB,
    name_Value)
    values(
    :pDocNo,
    '2001-10-10',
    '预防接种史描述')
    )
}

#SQLCompile Select可以设置为逻辑模式（Logical）、显示模式（Display）、 ODBC模式（ODBC）或  运行时模式（RuntimeMode）。而RuntimeMode默认为逻辑模式。

3. 动态SQL

如果是使用动态SQL，对于%SQL.Statement，它有一个属性%SelectMode，可以用来设置数据模式。它的可选值为：0 (逻辑模式)、1 (ODBC模式)  、2(显示模式)，0（逻辑模式）是默认值。

例如下面的例子将数据模式设置为逻辑模式：

  SET myquery = 3
  SET myquery(1) = "SELECT {t '12:04:29'} AS time1,"
  SET myquery(2) = "{t '12:4:29'} AS time2,"
  SET myquery(3) = "{t '12:04:29.00000'} AS time3"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=0
  SET tStatus = tStatement.%Prepare(.myquery)
  SET rset = tStatement.%Execute()
  DO rset.%Display()

4. Terminal的SQL shell

当使用Do $System.SQL.Shell()进入Terminal的SQL操作环境时，可以使用SET SELECTMODE命令来查询和设置数据模式。

直接使用SET SELECTMODE命令，返回的是当前数据模式。要设置数据模式，使用SET SELECTMODE=odbc/logic/display，例如SET SELECTMODE=odbc将当前SQL操作环境的数据模式设置为ODBC模式。

注意：SET SELECTMODE=odbc，等号前后不能有空格！

5. 管理门户的SQL操作页面

在管理门户的SQL操作页面中，通过下拉列表来选择当前的数据模式。

例如下面的逻辑模式的数据显示结果：

这是相同的数据在显示模式下的数据显示结果：

6. 在同一个SQL语句中显示不同的数据模式

有可能出于某种原因，你希望在SQL语句中对不同的字段使用不同的数据模式。这时，可以使用SQL函数：%EXTERNAL、%INTERNAL和 %ODBCOUT 来控制输出模式。

%EXTERNAL：按显示模式输出表达式结果
%INTERNAL：按逻辑模式输出表达式结果

%ODBCOUT ：按ODBC模式输出表达式结果

 

例如对于相同的数据，我们采用不同的数据模式输出：

 

那么除了显示和赋值，哪些操作和数据模式相关呢？

和数据模式相关的操作

1. 比较谓语

在where子句中进行比较的谓语，包括=、>、<、BETWEEN和 IN，这些比较谓语操作时，都会用逻辑模式的值进行比较操作，但可以通过SQL函数做其他数据模式到逻辑模式的数据转换。

例如，我们做日期类型的比较，当前的数据模式是ODBC模式，mydate是ODBC模式，比较条件值也应该是ODBC模式，可以这样写：

... WHERE mydate > '2010-01-01'

而如果数据模式是逻辑数据模式，上面的SQL写法将不会得到你想要的结果，因为这时mydate是逻辑模式的值。你可以用SQL函数TO_DATE将ODBC格式的日期值转为逻辑值：

... WHERE mydate>TO_DATE('2010-01-01','YYYY-MM-DD')

2. 模式谓语
在where子句中进行模式分析的谓语，包括%INLIST、 LIKE、 %MATCHES、 %PATTERN、 %STARTSWITH、 [ (包含操作符) 和 ] (跟随操作符)，这些模式分析操作也是用逻辑值进行比较，但不能用SQL函数进行其他模式到逻辑模式的转换。

当然，你可以考虑用模式转换函数将数据转换为字符串进行模式分析，例如下面的SQL使用%ODBCOUT将mydate转为ODBC格式的字符串，并看其模式是否满足以“2010-”开头。

...WHERE %ODBCOut(mydate) %STARTSWITH '2010-'

注意，这时SQL引擎不会使用mydate上的索引，有可能造成性能降低。

3. 排序操作

无论使用哪种数据模式，ORDER BY  都使用逻辑模式的值进行排序。