當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

int转换为cstring_PostgreSQL 隐式类型转换探秘

發(fā)布時間：2023/12/10 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 int转换为cstring_PostgreSQL 隐式类型转换探秘小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

個人簡介

何小棟， 從事產(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)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇：奥密克戎变异株BA.4/5在美国失控：3
下一篇：算法- 分治算法（实现汉诺塔）