Sqlite v3 的数据类型

When SQLite compares two strings,it uses a collating sequence or collating function (two words for the same thing) to determine which string is greater or if the two strings are equal. SQLite has three built-in collating functions: BINARY,NOCASE,and RTRIM.

• BINARY- Compares string data using memcmp(),regardless of text encoding.
• NOCASE- The same as binary,except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.
• RTRIM- The same as binary,except that trailing space characters are ignored.

An application can register additional collating functions using thesqlite3_create_collation()interface.

6.1 Assigning Collating Sequences from SQL

Every column of every table has an associated collating function. If no collating function is explicitly defined,then the collating function defaults to BINARY. The COLLATE clause of thecolumn definitionis used to define alternative collating functions for a column.

The rules for determining which collating function to use for a binary comparison operator (=,<,>,<=,>=,!=,IS,and IS NOT) are as follows and in the order shown:

1. If either operand has an explicit collating function assignment using the postfixCOLLATE operator,then the explicit collating function is used for comparison,with precedence to the collating function of the left operand.

2. If either operand is a column,then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence,a column name preceded by one or more unary "+" operators is still considered a column name.

3. Otherwise,the BINARY collating function is used for comparison.

An operand of a comparison is considered to have an explicit collating function assignment (rule 1 above) if any subexpression of the operand uses the postfixCOLLATE operator. Thus,if aCOLLATE operatoris used anywhere in a comparision expression,the collating function defined by that operator is used for string comparison regardless of what table columns might be a part of that expression. If two or moreCOLLATE operatorsubexpressions appear anywhere in a comparison,the left most explicit collating function is used regardless of how deeply the COLLATE operators are nested in the expression and regardless of how the expression is parenthesized.

The expression "x BETWEEN y and z" is logically equivalent to two comparisons "x >= y AND x <= z" and works with respect to collating functions as if it were two separate comparisons. The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collating sequence. The collating sequence used for expressions of the form "x IN (y,...)" is the collating sequence of x.

Terms of the ORDER BY clause that is part of aSELECTstatement may be assigned a collating sequence using the

6.2 Collation Sequence Examples

The examples below identify the collating sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required,and no collating sequence used,in the case of numeric,blob or NULL values.

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,a,/* collating sequence BINARY */
    b COLLATE BINARY,/* collating sequence BINARY */
    c COLLATE RTRIM,/* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc  ','abc');
INSERT INTO t1 VALUES(2,'ABC');
INSERT INTO t1 VALUES(3,'abc ','Abc');
INSERT INTO t1 VALUES(4,'ABC','abc');
 
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc',and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c,x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''),x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE,x;
--result 2 4 3 1

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!