Small. Fast. Reliable.
Choose any three.
关于 网站地图 文档 下载 许可 新闻 支持

SQLite里的SQL
SQL As Understood By SQLite

[Top]

核心函数
Core Functions

下面罗列的核心函数默认都是可用的。日期&时间函数 和 聚合函数被分开介绍了。一个应用可以使用sqlite3_create_function()API添加一个C实现的自定义扩展函数到数据库引擎中。
The core functions shown below are available by default. Date & Time functions and aggregate functions are documented separately. An application may define additional functions written in C and added to the database engine using the sqlite3_create_function() API.

abs(X) abs(X)函数返回数字参数X的绝对值。如果X是NULL则Abs(X)返回NULL。如果X是一个字符串或者BLOB，并且无法被转换为数字值则Abs(X)返回0.0。如果X是整数-9223372036854775808，由于没有对等的64位正整数，所以abs(X)会抛出一个整数溢出错误。
The abs(X) function returns the absolute value of the numeric argument X. Abs(X) returns NULL if X is NULL. Abs(X) return 0.0 if X is a string or blob that cannot be converted to a numeric value. If X is the integer -9223372036854775808 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value.
changes() changes()函数返回最近一次INSERT、DELETE或UPDATE语句修改或删除的数据库记录行数，不包含低等触发器中的语句。changes() SQL函数是对sqlite3_changes() C/C++函数的封装，因此计算修改的规则与其一样。
The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.
char(X1,X2,...,XN) char(X1,X2,...,XN)函数返回X1到XN的unicode码所对应的字符。
The char(X1,X2,...,XN) function returns a string composed of characters having the unicode code point values of integers X1 through XN, respectively.
coalesce(X,Y,...) coalesce()返回参数中第一个非NULL参数的拷贝。如果所有参数都是NULL则返回NULL。coalesce()函数必须传入至少两个参数。
The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must have at least 2 arguments.
glob(X,Y) glob(X,Y)函数等同于"Y GLOB X"表达式，注意，参数X和Y在glob()函数中与使用GLOB操作符顺序是相反的。如果使用sqlite3_create_function()接口重载了glob(X,Y) 函数的实现，那么GLOB操作符也会执行新的实现。
The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB operator. If the sqlite3_create_function() interface is used to override the glob(X,Y) function with an alternative implementation then the GLOB operator will invoke the alternative implementation.
ifnull(X,Y) ifnull()返回参数中第一个非NULL的参数拷贝，如果参数都是NULL，则返回NULL。ifnull()函数必须有两个参数。ifnull()函数等价于两个参数的coalesce()函数。
The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.
instr(X,Y) instr(X,Y)函数会在字符串X中寻找第一次出现的字符串Y，并且返回Y字符串之前的字符数量加1，否则，如果X不包含Y则返回0.如果X和Y都是BLOB，instr(X,Y)返回X中第一次出现的Y之前的字节数加1，否则，如果X中不包含Y则返回0.传入的参数X和Y制药不是BLOB和NULL就会被当做字符串。如果instr(X,Y)中X或Y是NULL则返回NULL。
The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X. If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then both are interpreted as strings. If either X or Y are NULL in instr(X,Y) then the result is NULL.
hex(X) hex()函数会将参数转换为BLOB并且将这个BLOB的内容渲染为大写的十六进制字符串返回。
The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob.
last_insert_rowid() last_insert_rowid()函数返回执行last_insert_rowid()的数据库连接上最后一次插入的行的ROWID。last_insert_rowid() SQL函数是sqlite3_last_insert_rowid()C/C++函数的封装。
The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.
length(X) 对于一个字符串参数X， length(X)函数返回X中NUL字符之前的字符（不是字节）数量。由于SQLite字符串中通常不包含NUL字符，所以 length(X)函数通常返回X字符串中字符的总数。如果参数X是一个BLOB值， length(X)返回这个BLOB中的字节数量。如果X是NULL则 length(X)返回NULL。如果X是一个数字那么 length(X)返回X表示为字符串的长度。
For a string value X, the length(X) function returns the number of characters (not bytes) in X prior to the first NUL character. Since SQLite strings do not normally contain NUL characters, the length(X) function will usually return the total number of characters in the string X. For a blob value X, length(X) returns the number of bytes in the blob. If X is NULL then length(X) is NULL. If X is numeric then length(X) returns the length of a string representation of X.
like(X,Y)
like(X,Y,Z)		 like()函数用于实现"Y LIKE X [ESCAPE Z]"表达式。如果使用了可选的ESCAPE子句，那么like()函数则需要三个参数，否则只需要两个参数。注意，参数X和Y在like()函数中和LIKE操作中的顺序是相反的。可以使用 sqlite3_create_function()接口来重载like()函数，从而修改LIKE操作符的实现。当重载like()函数时一定要同时重载两个参数和三个参数的like()函数版本。否则在使用LIKE操作符时，有没有ESCAPE子句会导致调用不同的代码。
The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The sqlite3_create_function() interface can be used to override the like() function and thereby change the operation of the LIKE operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.
likelihood(X,Y) likelihood(X,Y)返回未修改的参数X。likelihood(X,Y) 中的Y参数必须是一个0.0到1.0之间的浮点数（包含0.0和1.0）。likelihood(X,Y)函数没有任何的操作，所以代码生成优化器使其在运行时不消耗CPU周期（在调用sqlite3_step()过程中）。likelihood(X,Y)函数的目的是为查询规划器提供一个参数X为true的概率的参考值，Y表示表示X为true的可能概率。unlikely(X)函数等价于likelihood(X,0.0625)。
The likelihood(X,Y) function returns argument X unchanged. The value Y in likelihood(X,Y) must be a floating point constant between 0.0 and 1.0, inclusive. The likelihood(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles during run-time (that is, during calls to sqlite3_step()). The purpose of the likelihood(X,Y) function is to provide a hint to the query planner that the argument X is a boolean that is true with a probability of approximately Y. The unlikely(X) function is short-hand for likelihood(X,0.0625).
load_extension(X)
load_extension(X,Y)		 load_extension(X,Y)函数加载一个SQLite 扩展，X为共享库名，Y为入口点。load_extension()总是返回一个NULL结果。如果忽略Y参数，则使用默认入口点名。如果load_extension()函数加载扩展失败或者初始化错误则会抛出一个异常。
The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y. The result of load_extension() is always a NULL. If Y is omitted then the default entry point name is used. The load_extension() function raises an exception if the extension fails to load or initialize correctly.

当扩展模块试图修改或删除一个SQL函数或排序器时load_extension()会返回失败。扩展模块可以添加新的函数或排序器，但是不能修改或删除已有的函数和排序器，这是因为这些函数和排序器会被当前运行SQL语句以外的其它地方使用。如果想加载一个可以修改或删除函数和排序器的扩展模块，请使用sqlite3_load_extension() C-语言 API。
The load_extension() function will fail if the extension attempts to modify or delete an SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API.

出于安全原因，默认是关闭扩展模块加载的，必须先调用sqlite3_enable_load_extension()来启用该方法。
For security reasons, extension loaded is turned off by default and must be enabled by a prior call to sqlite3_enable_load_extension().

lower(X) lower(X)函数返回一个X中所有ASCII字符全转换为小写字符的字符串拷贝。默认的内置lower()函数只能对ASCII字符有效。想要对其他非ASCCI字符做转换需要加载ICU扩展模块。
The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.
ltrim(X)
ltrim(X,Y)		 ltrim(X,Y)函数去除参数X中最左边所有出现在Y中的字符。如果忽略Y参数，ltrim(X,Y)会去除X左边的所有空格。最后返回去除后的结果。
The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X.
max(X,Y,...) 不定参数函数max()返回参数中的最大值，如果所有参数都是NULL，则返回NULL。不定参数函数max()从左往右搜索所有参数，找到第一个定义了排序器的参数，然后使用这个排序器处理所有的字符串比较。如果max()中所有参数都没有定义排序函数，那么使用BINARY排序函数。注意，当max()包含两个以上参数时，则是一个普通函数，如果只有一个参数，那么则是一个聚合函数。
The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL. The multi-argument max() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to max() define a collating function, then the BINARY collating function is used. Note that max() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.
min(X,Y,...) 不定参数函数min()返回参数中的最小值。不定参数函数min()从左往右搜索所有参数，找到第一个定义了排序器的参数，然后使用这个排序器处理所有的字符串比较。如果max()中所有参数都没有定义排序函数，那么使用BINARY排序函数。注意，当min()包含两个以上参数时，则是一个普通函数，如果只有一个参数，那么则是一个聚合函数。
The multi-argument min() function returns the argument with the minimum value. The multi-argument min() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to min() define a collating function, then the BINARY collating function is used. Note that min() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.
nullif(X,Y) nullif(X,Y)函数当两个参数不同时返回第一个参数，两个参数相同时返回NULL。nullif(X,Y)函数从左往右搜索所有参数，找到第一个定义了排序器的参数，然后使用这个排序器处理所有的字符串比较。如果nullif()的两个参数都没有定义排序函数，则使用BINARY来排序。
The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to nullif() defines a collating function then the BINARY is used.
printf(FORMAT,...) printf(FORMAT,...) SQL函数与C语言函数sqlite3_mprintf()和标准C库中的printf()函数一样。第一个参数是一个格式化字符串，用户指定如果使用后续的参数值来构成输出字符串。如果缺少了FORMAT参数，或者传入了NULL，那么会返回NULL。%n格式会被静默的忽略，并且不会消耗参数。%p格式是%X的别名。%z格式可以与%s互换。如果参数列表中的参数过少，缺失的参数会被用NULL代替，如果是数字格式会转换为0或0.0 ，如果是%s格式则输出一个空字符串。
The printf(FORMAT,...) SQL function works like the sqlite3_mprintf() C-language function and the printf() function from the standard C library. The first argument is a format string that specifies how to construct the output string using values taken from subsequent arguments. If the FORMAT argument is missing or NULL then the result is NULL. The %n format is silently ignored and does not consume an argument. The %p format is an alias for %X. The %z format is interchangeable with %s. If there are too few arguments in the argument list, missing arguments are assumed to have a NULL value, which is translated into 0 or 0.0 for numeric formats or an empty string for %s.
quote(X) quote(X)函数返回参数的适于插入其它SQL语句中的值。字符串会被添加单引号，在内部的引号前会加入逃逸符号。 BLOB被编码为十六进制文本。包含NUL字符的Sting无法被表示为SQL中的字符串，因此，字符串会截取第一个NUL之前的部分。
The quote(X) function returns the text of an SQL literal which is the value of its argument suitable for inclusion into an SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. Strings with embedded NUL characters cannot be represented as string literals in SQL and hence the returned string literal is truncated prior to the first NUL.
random() random()函数返回一个-9223372036854775808 到 +9223372036854775807之间的伪随机数。
The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
randomblob(N) randomblob(N)函数返回一个伪随机生成的N字节的blob。如果N小于1，则返回一个1字节的随机blob。
The randomblob(N) function return an N-byte blob containing pseudo-random bytes. If N is less than 1 then a 1-byte random blob is returned.

提示：应用中可以使用该函数和hex()与lower()一起来生成全球唯一的标示符。例如：
Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower() like this:

hex(randomblob(16))

lower(hex(randomblob(16)))
replace(X,Y,Z) replace(X,Y,Z)函数返回一个将字符串X中所有出现的字符串Y替换为字符串Z的结果。使用BINARY排序器来进行比较操作。如果参数Y是一个空字符串，则直接返回未修改的字符串X。如果Z不是最初的字符串，则会在处理之前将其转换为UTF-8字符串。
The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.
round(X)
round(X,Y)		 round(X,Y) 函数返回一个浮点数X依照四舍五入保留小数点右边Y位数的结果。如果忽略Y参数则将Y看做0。
The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted, it is assumed to be 0.
rtrim(X)
rtrim(X,Y)		 rtrim(X,Y)函数去除参数X中最右边所有在Y中出现的字符，如果忽略Y参数，rtrim(X,Y)会去除X右边的所有空格。最后返回去除后的结果。
The rtrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the right side of X. If the Y argument is omitted, rtrim(X) removes spaces from the right side of X.
soundex(X) soundex(X)函数返回参数X探测出的编码字符串。如果X是NULL或者包含非ASCII字符则返回字符串"?000"。这个函数在SQLite中默认是忽略的。只有在编译SQLite时使用SQLITE_SOUNDEX编译选项才可以使用。
The soundex(X) function returns a string that is the soundex encoding of the string X. The string "?000" is returned if the argument is NULL or contains no ASCII alphabetic characters. This function is omitted from SQLite by default. It is only available if the SQLITE_SOUNDEX compile-time option is used when SQLite is built.
sqlite_compileoption_get(N) sqlite_compileoption_get()函数是对sqlite3_compileoption_get() C/C++函数的封装。本函数返回SQLite编译期的第N个编译参数。如果N超过参数范围则返回NULL。参见compile_options pragma。
The sqlite_compileoption_get() SQL function is a wrapper around the sqlite3_compileoption_get() C/C++ function. This routine returns the N-th compile-time option used to build SQLite or NULL if N is out of range. See also the compile_options pragma.
sqlite_compileoption_used(X) sqlite_compileoption_used()函数是对sqlite3_compileoption_used() C/C++接口的封装。当参数X是在编译期使用了的编译选项名字时，该函数返回true(1)，否则返回false(0)。
The sqlite_compileoption_used() SQL function is a wrapper around the sqlite3_compileoption_used() C/C++ function. When the argument X to sqlite_compileoption_used(X) is a string which is the name of a compile-time option, this routine returns true (1) or false (0) depending on whether or not that option was used during the build.
sqlite_source_id() sqlite_source_id()函数返回在编译SQLite库时源码版本号的标示符字符串。sqlite_source_id()返回的字符串以源码被检测的时间和日期开始，后面跟一个唯一标识源码树的SHA1哈希字符串。这个函数是对sqlite3_sourceid()C接口的封装。
The sqlite_source_id() function returns a string that identifies the specific version of the source code that was used to build the SQLite library. The string returned by sqlite_source_id() begins with the date and time that the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. This function is an SQL wrapper around the sqlite3_sourceid() C interface.
sqlite_version() sqlite_version()函数返回正在运行的SQLite库的版本号字符串。该函数是sqlite3_libversion()C接口的封装。
The sqlite_version() function returns the version string for the SQLite library that is running. This function is an SQL wrapper around the sqlite3_libversion() C-interface.
substr(X,Y,Z)
substr(X,Y)		 substr(X,Y,Z)函数返回输入字符串X中第Y个字符开始长度为Z个字符的字串。如果不提供参数Z，那么substr(X,Y)返回字符串X中从第Y个字符一直到末尾的字串。X最左边的字符序号为1。如果参数Y是一个负数那么，字串的第一个字符就从X的右边开始数，而不是左边。如果参数Z是负数，那么返回第Y个字符之前abs(Z)个字符组成的字串。如果X是一个字符串，那么字符序号是按照实际的UTF-8字符来计算的，如果X是一个BLOB，那么序号是按照字节来计算的。
The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.
total_changes() total_changes()函数返回当前数据库连接从打开至今所有INSERT、UPDATE和DELETE操作修改的行数。该函数是对sqlite3_total_changes()C/C++接口的封装。
The total_changes() function returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened. This function is a wrapper around the sqlite3_total_changes() C/C++ interface.
trim(X)
trim(X,Y)		 trim(X,Y)函数会移除X两端所有在Y中出现的字符，如果没有提供参数Y，则移除X两端所有的空格，最后返回移除后的结果。
The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X.
typeof(X) typeof(X) 函数返回一个表示表达式X数据类型的字符串："null"、 "integer"、 "real"、 "text"或 "blob"。
The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real", "text", or "blob".
unlikely(X) unlikely(X)函数会原封不动的返回参数X。unlikely(X)函数没有任何操作，所以代码生成优化会使该函数在运行时不消耗CPU周期（在调用sqlite3_step()期间）。unlikely(X)函数的目的是为了给查询计划器提供一个参数X通常不是true的暗示。unlikely(X)等同于likelihood(X, 0.0625)。
The unlikely(X) function returns the argument X unchanged. The unlikely(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles at run-time (that is, during calls to sqlite3_step()). The purpose of the unlikely(X) function is to provide a hint to the query planner that the argument X is a boolean value that is usually not true. The unlikely(X) function is equivalent to likelihood(X, 0.0625).
unicode(X) unicode(X)函数返回字符串X中第一个字符对应的unicode值。如果unicode(X)的参数不是一个字符串，那么结果是未定义的。
The unicode(X) function returns the numeric unicode code point corresponding to the first character of the string X. If the argument to unicode(X) is not a string then the result is undefined.
upper(X) upper(X)函数返回一个X中所有小写ASCII字符全转换为大写字符的字符串拷贝。
The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to their upper-case equivalent.
zeroblob(N) zeroblob(N)函数返回一个N字节的全是0x00的BLOB。SQLite能非常高效的管理这些zeroblob。zeroblob可以用于为一个BLOB预分配空间，以便后续使用增量 BLOB I/O来写入内容。这个SQL函数是使用C/C++接口中的sqlite3_result_zeroblob()来实现的。
The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O. This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface.