int转换为cstring_PostgreSQL 隐式类型转换探秘
個人簡介
何小棟, 從事產(chǎn)品研發(fā)和架構(gòu)設(shè)計(jì)工作,對Oracle、PostgreSQL有深入研究,ITPUB數(shù)據(jù)庫版塊資深版主。現(xiàn)就職于廣州云圖數(shù)據(jù)技術(shù)有限公司,系統(tǒng)架構(gòu)師,博客:http://blog.itpub.net/6906/
摘要
本文通過與Oracle隱式類型轉(zhuǎn)換的比較簡要介紹了PostgreSQL中的隱式類型轉(zhuǎn)換,包括Oracle隱式類型轉(zhuǎn)換的機(jī)制、PostgreSQL隱式類型轉(zhuǎn)換的機(jī)制和原理。
一、緣起
在進(jìn)入正題前,我們先看下面一個案例,在psql中執(zhí)行以下SQL:
-- 創(chuàng)建表
testdb=# create table t_cast (id int);
CREATE TABLE
--?插入數(shù)據(jù)
testdb=# insert into t_cast values(1),(2),(3);
INSERT 0 3
--?查詢數(shù)據(jù)
testdb=# select * from t_cast where id = '1';
?id
----
??1
(1 row)
testdb=# select * from t_cast where id = '1'::text;
psql: ERROR: ?operator does not exist: integer = text
LINE 1: select * from t_cast where id = '1'::text;
??????????????????????????????????????^
HINT: ?No operator matches the given name and argument types. You might need to add explicit type casts.
數(shù)據(jù)表t_cast中的id列類型為int,執(zhí)行第一條SQL沒有出錯,第二條SQL則出錯,錯誤信息為沒有相匹配的operator,給出的HINT為添加顯式類型轉(zhuǎn)換。對于熟悉Oracle數(shù)據(jù)庫的同學(xué)來說,不禁會產(chǎn)生疑問:’1’和’1’::text不都是字符類型嗎?兩者不是一回事?而同樣的SQL,在Oracle中則不會報錯,處理機(jī)制跟PostgreSQL有什么不同??
二、Oracle隱式類型轉(zhuǎn)換機(jī)制
上面列出的幾個問題暫時擱置,我們先來看看同樣的SQL在Oracle中的執(zhí)行結(jié)果:
TEST-orcl@DESKTOP-V430TU3>create table t_cast (id int) tablespace users;
Table created.
TEST-orcl@DESKTOP-V430TU3>insert into t_cast values(1);
1 row created.
TEST-orcl@DESKTOP-V430TU3>insert into t_cast values(2);
1 row created.
TEST-orcl@DESKTOP-V430TU3>insert into t_cast values(3);
1 row created.
TEST-orcl@DESKTOP-V430TU3>select * from t_cast where id = '1';
????????ID
----------
?????????1
TEST-orcl@DESKTOP-V430TU3>select * from t_cast where id = cast('1' as varchar2(2));
????????ID
----------
?????????1
在Oracle中不會出錯,查閱Oracle文檔[1],發(fā)現(xiàn)Oracle使用數(shù)據(jù)類型優(yōu)先級來判定隱式數(shù)據(jù)類型轉(zhuǎn)換的先后順序,優(yōu)先級由高到低順序如下:
1.?Datetime和interval數(shù)據(jù)類型
2.?BINARY_DOUBLE
3.?BINARY_FLOAT
4.?NUMBER
5.?字符數(shù)據(jù)類型
6.?其他內(nèi)置數(shù)據(jù)類型
以上例來說,NUMBER與字符數(shù)據(jù)類型進(jìn)行等值比較,那么字符類型會自動隱式轉(zhuǎn)換為NUMBER進(jìn)行比較,而不是把NUMBER轉(zhuǎn)換為字符類型,因?yàn)镹UMBER優(yōu)先級高于字符類型。
關(guān)于Oracle的隱式轉(zhuǎn)換機(jī)制,根據(jù)Oracle文檔[1],其轉(zhuǎn)換規(guī)則如下:
1.?在INSERT/UPDATE操作中,轉(zhuǎn)換數(shù)據(jù)值為相應(yīng)的列數(shù)據(jù)類型
2.?在SELECT FROM操作中,轉(zhuǎn)換列數(shù)據(jù)類型為目標(biāo)類型
3. 在操作數(shù)值型數(shù)據(jù)時,調(diào)整為最大可用的精度&刻度。在這種情況下,這些操作產(chǎn)生的數(shù)據(jù)類型可能與基表中的數(shù)據(jù)類型不同
4.?在比較字符型和數(shù)值型數(shù)據(jù)時,轉(zhuǎn)換字符型為數(shù)值型
5.?在字符型/數(shù)值型數(shù)據(jù)與浮點(diǎn)數(shù)之間轉(zhuǎn)換時可能不精確,因?yàn)樽址愋秃蚇UMBER使用十進(jìn)制精度,而浮點(diǎn)數(shù)使用二進(jìn)制精度
6.?轉(zhuǎn)換CLOB為字符類型(如VARCHAR2),或者轉(zhuǎn)換BLOB轉(zhuǎn)換為RAW時,如果需要轉(zhuǎn)換的數(shù)據(jù)大小比目標(biāo)數(shù)據(jù)類型可表示的要大,則返回錯誤
7.?TIMESTAMP轉(zhuǎn)換為DATE時,時間戳中秒的小數(shù)部分將被截斷(較老的版本會四舍五入)
8.?BINARY_FLOAT轉(zhuǎn)換為BINARY_DOUBLE時不會丟失精度
9.?BINARY_DOUBLE轉(zhuǎn)換為BINARY_FLOAT會丟失精度(如使用超過float更大的位數(shù)來表示精度)
10.?在比較字符型和日期型數(shù)據(jù)時,轉(zhuǎn)換字符型為日期型
11.?輸入的參數(shù)與函數(shù)或者操作符參數(shù)不匹配時,轉(zhuǎn)換為相應(yīng)的數(shù)據(jù)類型
12.?在賦值時,把等號右邊的數(shù)據(jù)轉(zhuǎn)換為左邊目標(biāo)數(shù)據(jù)類型
13.?在字符串拼接操作時,轉(zhuǎn)換非字符類型為字符類型
14.?在對字符/非字符數(shù)據(jù)類型進(jìn)行算術(shù)運(yùn)算/比較時,根據(jù)需要會將所有字符類型轉(zhuǎn)換為數(shù)值/日期/Rowid
15. 大多數(shù)的SQL函數(shù)可接受CLOB類型作為參數(shù),這時候會執(zhí)行CLOB和字符類型之間的轉(zhuǎn)換。如果CLOB大小超過4000Byte,則只獲取CLOB中的4000Byte
16.?在RAW/LONG RAW和字符類型之間相互轉(zhuǎn)換時,二進(jìn)制數(shù)據(jù)會被表示為十六進(jìn)制的格式,一個十六進(jìn)制字符表示RAW數(shù)據(jù)中的4位
17. 在CHAR和VARCHAR2以及NCHAR和NVARCHAR2之間比較時可能需要不同的字符集。這種情況下默認(rèn)的轉(zhuǎn)換方向是從數(shù)據(jù)庫字符集轉(zhuǎn)換為國家字符集
更詳細(xì)的信息請參考Oracle文檔。
三、PostgreSQL隱式類型轉(zhuǎn)換機(jī)制
這一小節(jié)通過回答先前提到幾個問題來嘗試解析PostgreSQL的隱式類型轉(zhuǎn)換機(jī)制。
’1’和’1’::text是一回事嗎?
在SQL語句中,’1’和’1’::text是一回事嗎?從實(shí)際執(zhí)行結(jié)果來看,顯然不是一回事,否則使用后者就不會出錯了。
一般來說,在PostgreSQL中,SQL語句在執(zhí)行前需要經(jīng)過三個步驟:詞法分析、語法分析和語義分析。在詞法分析階段,SQL語句中的’1’會被視為string literal(字符串文字),注意是string literal不是string value(字符串值)!string literal意思是在單引號或雙引號中的一串字符,在PostgreSQL中如果string literal沒有指定類型,那么該string literal的數(shù)據(jù)類型會被認(rèn)為是unknown,如指定類型則為指定的數(shù)據(jù)類型。例如:
select * from t_cast where id = '1';
未指定類型,‘1’類型為unknown
select * from t_cast where id = '1'::text;
指定了數(shù)據(jù)類型,1’的類型為text
int與text類型比較,為何會報錯?
int與text類型比較,為何會報錯?實(shí)際上,PostgreSQL根據(jù)系統(tǒng)目錄中的定義來確定類型D1和類型D2能否執(zhí)行某個操作O。
在示例中,‘=’為二元操作符,左操作數(shù)類型為int,右操作數(shù)類型為text,在pg_operator系統(tǒng)目錄中,不存在該operator和相應(yīng)操作數(shù)類型的定義:
testdb=# select oprname,oprleft::regtype,oprright::regtype,oprcode from pg_operator where oprname='=' and oprleft::regtype='int'::regtype;
?oprname | oprleft | oprright | oprcode
---------+---------+----------+---------
?= ??????| integer | bigint ??| int48eq
?= ??????| integer | integer ?| int4eq
?= ??????| integer | smallint | int42eq
(3 rows)
testdb=# select oprname,oprleft::regtype,oprright::regtype,oprcode from pg_operator where oprname='=' and oprright::regtype='text'::regtype;
?oprname | oprleft | oprright | ?oprcode ??
---------+---------+----------+------------
?= ??????| text ???| text ????| texteq
?= ??????| name ???| text ????| nameeqtext
(2 rows)
而且int和text并沒有在pg_cast系統(tǒng)目錄中定義為可相互轉(zhuǎn)換:
testdb=# select castsource::regtype,casttarget::regtype,castfunc from pg_cast where castsource::regtype='integer'::regtype;
?castsource | ???casttarget ???| castfunc
------------+------------------+----------
?integer ???| bigint ??????????| ?????481
?integer ???| smallint ????????| ?????314
?integer ???| real ????????????| ?????318
?integer ???| double precision | ?????316
?integer ???| numeric ?????????| ????1740
?integer ???| money ???????????| ????3811
?integer ???| boolean ?????????| ????2557
?integer ???| oid ?????????????| ???????0
?integer ???| regproc ?????????| ???????0
?integer ???| regprocedure ????| ???????0
?integer ???| regoper ?????????| ???????0
?integer ???| regoperator ?????| ???????0
?integer ???| regclass ????????| ???????0
?integer ???| regtype ?????????| ???????0
?integer ???| regconfig ???????| ???????0
?integer ???| regdictionary ???| ???????0
?integer ???| regrole ?????????| ???????0
?integer ???| regnamespace ????| ???????0
?integer ???| "char" ??????????| ??????78
?integer ???| bit ?????????????| ????1683
(20 rows)
testdb=# select castsource::regtype,casttarget::regtype,castfunc from pg_cast where castsource::regtype='text'::regtype;
?castsource | ???casttarget ????| castfunc
------------+-------------------+----------
?text ??????| regclass ?????????| ????1079
?text ??????| character ????????| ???????0
?text ??????| character varying | ???????0
?text ??????| "char" ???????????| ?????944
?text ??????| name ?????????????| ?????407
?text ??????| xml ??????????????| ????2896
(6 rows)
既沒有定義operator也沒有定義數(shù)據(jù)類型cast,因此int與text等值比較會報錯。
如何確定轉(zhuǎn)換方向?
接下來的一個問題是int類型與unknown類型以及int與text比較,是int轉(zhuǎn)換為text類型還是text類型轉(zhuǎn)換為int類型?是否有優(yōu)先級之分?
第一個問題,int類型與unknown類型比較的轉(zhuǎn)換方向。在PostgreSQL中,不單是int類型,包括其他確定的數(shù)據(jù)類型,如存在滿足條件的operator,unknown都會轉(zhuǎn)換為確定的數(shù)據(jù)類型進(jìn)行比較,轉(zhuǎn)換失敗則報錯。
例如:
testdb=# select * from t_cast where id = '1.1';
psql: ERROR: ?invalid input syntax for type integer: "1.1"
LINE 1: select * from t_cast where id = '1.1';
????????????????????????????????????????^
id為int類型,‘1.1’為unknown類型,PostgreSQL會嘗試吧‘1.1’轉(zhuǎn)換為整型與id進(jìn)行比較,轉(zhuǎn)換不了則失敗報錯。值得一提的是,報錯信息提示語法錯誤,也就是說PostgreSQL在語法分析階段已發(fā)現(xiàn)錯誤而不是在執(zhí)行階段才發(fā)現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換錯誤。
第二個問題,int與text比較時的轉(zhuǎn)換方向。剛才已看到,在原生的PostgreSQL中沒有定義int與text的轉(zhuǎn)換,但可通過create cast命令自定義類型轉(zhuǎn)換:
testdb=# create cast(integer as text) with inout as implicit;
CREATE CAST
testdb=# create cast(text as integer) with inout as implicit;
CREATE CAST
我們同時定義了int和text的相互轉(zhuǎn)換,那到底使用哪一個CAST呢?
testdb=# explain select * from t_cast where id = '1'::text;
psql: ERROR: ?operator is not unique: integer = text
LINE 1: explain select * from t_cast where id = '1'::text;
??????????????????????????????????????????????^
HINT: ?Could not choose a best candidate operator. You might need to add explicit type casts.
PostgreSQL給出的答案是無法處理,無法確定使用哪一個候選而報錯。如果我們需要實(shí)現(xiàn)Oracle兼容性(轉(zhuǎn)換為NUMBER比較),那么保留text到int的轉(zhuǎn)換即可:
testdb=# drop cast(integer as text);
DROP CAST
testdb=# explain select * from t_cast where id = '1'::text;
???????????????????????QUERY PLAN ??????????????????????
--------------------------------------------------------
?Seq Scan on t_cast ?(cost=0.00..41.88 rows=13 width=4)
???Filter: (id = 1)
(2 rows)
除了定義cast來實(shí)現(xiàn)int和text的比較外,還可以通過自定義operator來實(shí)現(xiàn),這一部分不在本文的討論范圍,暫且略過。
PostgreSQL的隱式類型轉(zhuǎn)換在官方文檔有詳細(xì)說明[2],這里不再詳細(xì)展開。
四、PostgreSQL隱式類型轉(zhuǎn)換原理
下面從源碼(PostgreSQL 12 beta1)上來對隱式類型轉(zhuǎn)換的原理作一簡要解析,包括使用的數(shù)據(jù)結(jié)構(gòu)FormData_pg_cast以及實(shí)現(xiàn)函數(shù)make_op等。
數(shù)據(jù)結(jié)構(gòu)
FormData_pg_cast結(jié)構(gòu)體定義了pg_cast中數(shù)據(jù)的結(jié)構(gòu)
/* ----------------
?* ???pg_cast definition. ?cpp turns this into
?* ???typedef struct FormData_pg_cast
?* ----------------
?*/
CATALOG(pg_cast,2605,CastRelationId)
{
??Oid ????oid; ?????/* oid */
??/* source datatype for cast */
??Oid ????castsource BKI_LOOKUP(pg_type);//源類型
??/* destination datatype for cast */
??Oid ????casttarget BKI_LOOKUP(pg_type);//目標(biāo)類型
??/* cast function; 0 = binary coercible */
??Oid ????castfunc BKI_LOOKUP(pg_proc);//轉(zhuǎn)換函數(shù)
??/* contexts in which cast can be used */
??char ???castcontext;//上下文,i-表達(dá)式,a-賦值,e-顯式類型轉(zhuǎn)換
??/* cast method */
//f-通過castfunc定義的函數(shù)轉(zhuǎn)換,b-二元運(yùn)算符中的轉(zhuǎn)換,i-函數(shù)參數(shù)轉(zhuǎn)換
??char ???castmethod;
} FormData_pg_cast;
/* ----------------
?* ???Form_pg_cast corresponds to a pointer to a tuple with
?* ???the format of pg_cast relation.
?* ----------------
?*/
typedef FormData_pg_cast *Form_pg_cast;
make_op
該函數(shù)通過變換操作符表達(dá)式以及執(zhí)行類型轉(zhuǎn)換以確保操作數(shù)類型兼容。
相關(guān)代碼如下:
Expr *
make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree,
????Node *last_srf, int location)
{
…
?/* otherwise, binary operator */
????//二元操作符
????ltypeId = exprType(ltree);
????rtypeId = exprType(rtree);
????tup = oper(pstate, opname, ltypeId, rtypeId, false, location);
…
????/* otherwise, binary operator */
//二元操作符
????args = list_make2(ltree, rtree);
????actual_arg_types[0] = ltypeId;
????actual_arg_types[1] = rtypeId;
????declared_arg_types[0] = opform->oprleft;//左操作數(shù)
????declared_arg_types[1] = opform->oprright;//右操作數(shù)
????nargs = 2;
…
//調(diào)用函數(shù)make_fn_arguments實(shí)現(xiàn)參數(shù)類型轉(zhuǎn)換
??make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);
…
make_fn_arguments
調(diào)用coerce_type函數(shù)實(shí)現(xiàn)轉(zhuǎn)換
void
make_fn_arguments(ParseState *pstate,
??????????List *fargs,
??????????Oid *actual_arg_types,
??????????Oid *declared_arg_types)
{
????????…
//執(zhí)行轉(zhuǎn)換
????????node = coerce_type(pstate,//?ParseState結(jié)構(gòu)體
???????????????????node,//節(jié)點(diǎn)(實(shí)際類型為List?*)
???????????????????actual_arg_types[i],//實(shí)際操作符
???????????????????declared_arg_types[i],//聲明操作符?
-1,//?targetTypeMod
???????????????????COERCION_IMPLICIT,//?CoercionContext
???????????????????COERCE_IMPLICIT_CAST,//?CoercionForm
???????????????????-1);//?location
…
coerce_type
該函數(shù)判斷類型是為unknown,如為unknown則轉(zhuǎn)換為另一確定操作數(shù)的數(shù)據(jù)類型,最終的轉(zhuǎn)換實(shí)現(xiàn)在stringTypeDatum中
Node *
coerce_type(ParseState *pstate, Node *node,
??????Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
??????CoercionContext ccontext, CoercionForm cformat, int location)
{
…
??if (inputTypeId == UNKNOWNOID && IsA(node, Const))
??{
//--------- 輸入類型為unknown并且是常量
…
Const ???*con = (Const *) node;//常量
Const ???*newcon = makeNode(Const);//轉(zhuǎn)換后的常量
…
???/*
?????* We assume here that UNKNOWN's internal representation is the same
?????* as CSTRING.
?????* 內(nèi)部表示跟CSTRING一樣
?????*/
????if (!con->constisnull)
??????newcon->constvalue = stringTypeDatum(baseType,
?????????????????????????DatumGetCString(con->constvalue),
?????????????????????????inputTypeMod);//調(diào)用DatumGetCString函數(shù)
????else
??????newcon->constvalue = stringTypeDatum(baseType,
?????????????????????????NULL,
?????????????????????????inputTypeMod);//NULL值
…
stringTypeDatum
stringTypeDatum調(diào)用了int4in/?pg_strtoint32函數(shù)對string?literal進(jìn)行解析
/*
?* ???int4in ?????- converts "num" to int4
?*/
Datum
int4in(PG_FUNCTION_ARGS)
{
??char ????*num = PG_GETARG_CSTRING(0);
??PG_RETURN_INT32(pg_strtoint32(num));
}
/*
?* Convert input string to a signed 32 bit integer.
?*
?* Allows any number of leading or trailing whitespace characters. Will throw
?* ereport() upon bad input format or overflow.
?*
?* NB: Accumulate input as a negative number, to deal with two's complement
?* representation of the most negative number, which can't be represented as a
?* positive number.
?*/
int32
pg_strtoint32(const char *s)
{
??const char *ptr = s;
??int32 ??tmp = 0;
??bool ???neg = false;
??/* skip leading spaces */
??while (likely(*ptr) && isspace((unsigned char) *ptr))
????ptr++;
??/* handle sign */
??if (*ptr == '-')
??{
????ptr++;
????neg = true;
??}
??else if (*ptr == '+')
????ptr++;
??/* require at least one digit */
??if (unlikely(!isdigit((unsigned char) *ptr)))
????goto invalid_syntax;
??/* process digits */
??while (*ptr && isdigit((unsigned char) *ptr))//如'123',-1->-12->-123
??{
//獲取數(shù)字
????int8 ???digit = (*ptr++ - '0');//’0’到’9’ASCII值 – ‘0’得到數(shù)字
????if (unlikely(pg_mul_s32_overflow(tmp, 10, &tmp)) ||//tmp*10
??????unlikely(pg_sub_s32_overflow(tmp, digit, &tmp)))//tmp - digit
??????goto out_of_range;
??}
??/* allow trailing whitespace, but not other trailing chars */
??while (*ptr != '\0' && isspace((unsigned char) *ptr))
????ptr++;
??if (unlikely(*ptr != '\0'))
????goto invalid_syntax;
??if (!neg)
??{
????/* could fail if input is most negative number */
????if (unlikely(tmp == PG_INT32_MIN))
??????goto out_of_range;
????tmp = -tmp;//取反,-123->123
??}
??return tmp;
out_of_range://越界
??ereport(ERROR,
??????(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
???????errmsg("value \"%s\" is out of range for type %s",
??????????s, "integer")));
invalid_syntax://非法的語法
??ereport(ERROR,
??????(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
???????errmsg("invalid input syntax for type %s: \"%s\"",
??????????"integer", s)));
??return 0; ????/* keep compiler quiet */
}
PostgreSQL相對于Oracle最大的優(yōu)勢在于可以通過閱讀源碼了解數(shù)據(jù)庫的底層實(shí)現(xiàn)原理,真正的做到知其然知其所以然。
五、參考資料
1、Oracle?Data Type Comparison Rules
https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF30027
2、PostgreSQL Type Conversion
https://www.postgresql.org/docs/11/typeconv-overview.html
PostgreSQL中文社區(qū)歡迎廣大技術(shù)人員投稿
投稿郵箱:press@postgres.cn
總結(jié)
以上是生活随笔為你收集整理的int转换为cstring_PostgreSQL 隐式类型转换探秘的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 奥密克戎变异株BA.4/5在美国失控:3
- 下一篇: 算法- 分治算法(实现汉诺塔)