文章
· 二月 18, 2022 阅读大约需 6 分钟

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子句中进行模式分析的谓语,包括%INLISTLIKE%MATCHES%PATTERN%STARTSWITH[ (包含操作符) 和 ] (跟随操作符),这些模式分析操作也是用逻辑值进行比较,但不能用SQL函数进行其他模式到逻辑模式的转换。

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

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

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

3. 排序操作

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

讨论 (0)1
登录或注册以继续