ntextåtextä¸æ ·ç¨æ¥ä¿å大éçæåæ°æ®ï¼ä¸è¿textç¨ååèä¿åæ°æ® ï¼ntextåºå®ç¨ååèä¿åæ°æ®. ntextä¿åçæ¯Uncodeçå符 ï¼
ntextæ¯æè·¨è¯è¨å¹³å°ã
ntextï¼
å¯åé¿åº¦ Unicode æ°æ®çæ大é¿åº¦ä¸º 230 -
1 (1,073,741,823) 个å符ãåå¨å¤§å°æ¯æè¾å
¥å符个æ°ç两åï¼ä»¥åè为åä½ï¼ãntext å¨ SQL-92 ä¸çåä¹è¯æ¯ national
textã
ntextä¸åæ°æ®æ¯æååèåç ï¼æ¾ç¤ºä¸äºNTEXTä½ æ¢ä¸ä¸recordsetæå¼æ¹å¼å°±è¡äº
textï¼
æå¡å¨ä»£ç 页ä¸çå¯åé¿åº¦é Unicode æ°æ®çæ大é¿åº¦ä¸º 231-1 (2,147,483,647)
个å符ãå½æå¡å¨ä»£ç 页使ç¨ååèå符æ¶ï¼åå¨éä»æ¯ 2,147,483,647 åèãåå¨å¤§å°å¯è½å°äº 2,147,483,647 åèï¼åå³äºå符串ï¼ã
charãvarcharãtextåncharãnvarcharãntextçåºå«
1ãCHARãCHARåå¨å®é¿æ°æ®å¾æ¹ä¾¿ï¼CHARå段ä¸çç´¢å¼æç级é«ï¼æ¯å¦å®ä¹char(10)ï¼é£ä¹ä¸è®ºä½ åå¨çæ°æ®æ¯å¦è¾¾å°äº10个åèï¼é½è¦å å»10个åèç空é´,ä¸è¶³çèªå¨ç¨ç©ºæ ¼å¡«å
ã
2ãVARCHARãåå¨åé¿æ°æ®ï¼ä½åå¨æç没æCHARé«ãå¦æä¸ä¸ªå段å¯è½çå¼æ¯ä¸åºå®é¿åº¦çï¼æ们åªç¥éå®ä¸å¯è½è¶
è¿10个å符ï¼æå®å®ä¹ä¸º
VARCHAR(10)æ¯æåç®çãVARCHARç±»åçå®é
é¿åº¦æ¯å®çå¼çå®é
é¿åº¦+1ã为ä»ä¹â+1âå¢ï¼è¿ä¸ä¸ªåèç¨äºä¿åå®é
使ç¨äºå¤å¤§çé¿åº¦ãä»ç©ºé´ä¸èèï¼ç¨varcharåéï¼ä»æçä¸èèï¼ç¨charåéï¼å
³é®æ¯æ ¹æ®å®é
æ
åµæ¾å°æè¡¡ç¹ã
3ãTEXTãtextåå¨å¯åé¿åº¦çéUnicodeæ°æ®ï¼æ大é¿åº¦ä¸º2^31-1(2,147,483,647)个å符ã
4ã
NCHARãNVARCHARãNTEXTãè¿ä¸ç§ä»ååä¸çæ¯åé¢ä¸ç§å¤äºä¸ªâNâãå®è¡¨ç¤ºåå¨çæ¯Unicodeæ°æ®ç±»åçå符ãæ们ç¥éå符ä¸ï¼è±
æå符åªéè¦ä¸ä¸ªåèåå¨å°±è¶³å¤äºï¼ä½æ±åä¼å¤ï¼éè¦ä¸¤ä¸ªåèåå¨ï¼è±æä¸æ±ååæ¶åå¨æ¶å®¹æé ææ··ä¹±ï¼Unicodeå符éå°±æ¯ä¸ºäºè§£å³å符éè¿ç§ä¸å
¼
容çé®é¢è产ççï¼å®ææçå符é½ç¨ä¸¤ä¸ªåè表示ï¼å³è±æå符ä¹æ¯ç¨ä¸¤ä¸ªåè表示ãncharãnvarcharçé¿åº¦æ¯å¨1å°4000ä¹é´ãå
charãvarcharæ¯è¾èµ·æ¥ï¼ncharãnvarcharåæå¤åå¨4000个å符ï¼ä¸è®ºæ¯è±æè¿æ¯æ±åï¼ècharãvarcharæå¤è½åå¨
8000个è±æï¼4000个æ±åãå¯ä»¥çåºä½¿ç¨ncharãnvarcharæ°æ®ç±»åæ¶ä¸ç¨æ
å¿è¾å
¥çå符æ¯è±æè¿æ¯æ±åï¼è¾ä¸ºæ¹ä¾¿ï¼ä½å¨åå¨è±ææ¶æ°é
ä¸æäºæ失ã
对äºä»ä¹æ¶åç¨varcharånvarchar没æ说ä¸å®ç.
ä¹å°±æ¯è¯´ä¸ä¸ªæ±åæ¢å¯ä»¥åå¨varcharä¸,ä¹å¯ä»¥åå¨nvarcharä¸.
é£ä¹å¯¹äºæ±åæè
Unicode
æ°æ®å°åºåå¨varcharånvarcharæä»ä¹åºå«å¢?
ä¸é¢ä¾å说æä¸ä¸:ä¸ä¸ªæ±åå varchar(2),åªå nvarchar(1),
èåæ¯åªå varchar(1),é£ä¹å¨æ°æ®åºå段æ±é¿åº¦çæ¶å,ç¨varcharä½ å°±ä¸ä¸å®ç¥éå®ç¡®åçç¥éå®å°åºæå 个å,å¦æç¨nvarchar,
é£ä¹æ±åä¹æ¯nvarchar(1),åæ¯ä¹æ¯nvarchar(1),é£ä¹å·²ç»å¾ææ¾äº.
åºå«2:varcharçæ£ç´¢å¿«äºnvarchar,è½ç¶æ¯è¿æ ·ä½å¾®è½¯ä¸ä¸ä¸ªçæ¬å°ç»ä¸nvarchar,å¬è¯´ç
管ç ntextãtext å image
æ°æ®
Microsoft? SQL Server? ç ntextãtext å image æ°æ®ç±»åå¨å个å¼ä¸å¯ä»¥å
å«é常大çæ°æ®éï¼æ大å¯
ãã è¾¾
2 GBï¼ãå个æ°æ®å¼é常æ¯åºç¨ç¨åºå¨ä¸ä¸ªæ¥éª¤ä¸è½å¤æ£ç´¢ç大ï¼æäºå¼å¯è½è¿ä¼å¤§äºå®¢æ·ç«¯çå¯ç¨èæå
åãå æ¤ï¼
ãã
å¨æ£ç´¢è¿äºå¼æ¶ï¼é常éè¦ä¸äºç¹æ®çæ¥éª¤ã
ãã
ãã å¦æ ntextãtext å image æ°æ®å¼ä¸è¶
è¿ Unicode
串ãå符串æäºè¿å¶ä¸²çé¿åº¦ï¼åå«ä¸º 4,000 个å符ã8,000 个å
ãã 符å 8,000 个åèï¼ï¼å°±å¯ä»¥å¨ SELECTãUPDATE å
INSERT è¯å¥ä¸å¼ç¨å®ä»¬ï¼å
¶å¼ç¨æ¹å¼ä¸è¾å°çæ°æ®ç±»åç¸åãä¾
ãã å¦ï¼å
å«çå¼ç ntext åå¯ä»¥å¨ SELECT è¯å¥çéæ©å表ä¸å¼ç¨ï¼è¿ä¸
nvarchar åçå¼ç¨æ¹å¼ç¸åãå¼ç¨æ¶å¿
é¡»éµå®ä¸
ãã äºéå¶ï¼ä¾å¦ä¸è½å¨ WHERE åå¥ä¸ç´æ¥å¼ç¨ ntextãtext æ image
åãè¿äºåå¯ä»¥ä½ä¸ºè¿åå
¶å®æ°æ®ç±»åï¼ä¾å¦
ãã ISNULLãSUBSTRING æ PATINDEXï¼çæ个å½æ°çåæ°å
å«å¨ WHERE
åå¥ä¸ï¼ä¹å¯ä»¥å
å«å¨ IS NULLãIS NOT NULL æ
ãã LIKE 表达å¼ä¸ã
ãã
ãã å¤çè¾å¤§çæ°æ®å¼
ãã
ä½æ¯ï¼å¦æ ntextãtext å image æ°æ®å¼è¾å¤§ï¼åå¿
é¡»éåå¤çãTransact-SQL åæ°æ®åº API åå
å«ä½¿åºç¨ç¨åºå¯ä»¥
ãã
éåå¤ç ntextãtext å image æ°æ®çå½æ°ã
ãã
ãã æ°æ®åº API æç
§ä¸ç§éç¨ç模å¼å¤çé¿ ntextãtext å
image åï¼
ãã
ãã è¥è¦è¯»åä¸ä¸ªé¿åï¼åºç¨ç¨åºåªéå¨éæ©å表ä¸å
å« ntextãtext æ image
åï¼å¹¶å°è¯¥åç»å®å°ä¸ä¸ªç¨åºåéï¼è¯¥åé
ãã åºè¶³ä»¥å®¹çº³éå½çæ°æ®åãç¶åï¼åºç¨ç¨åºå°±å¯ä»¥æ§è¡è¯¥è¯å¥ï¼å¹¶ä½¿ç¨ API
å½æ°ææ¹æ³å°æ°æ®éåæ£ç´¢å°ç»å®çåéä¸ã
ãã
ãã
ãã è¥è¦åå
¥ä¸ä¸ªé¿åï¼åºç¨ç¨åºå¯ä½¿ç¨åæ°æ è®° (?) å¨ç¸åºä½ç½®ä»£æ¿
ntextãtext æ image åä¸çå¼ï¼ä»¥æ§è¡ INSERT
ãã æ UPDATE è¯å¥ãåæ°æ è®°ï¼å¯¹ ADO
èè¨å为åæ°ï¼è¢«ç»å®å°ä¸ä¸ªè¶³ä»¥å®¹çº³æ°æ®åçç¨åºåéä¸ãåºç¨ç¨åºè¿å
¥å¾ªç¯ï¼å¨
ãã 循ç¯ä¸å
å°ä¸ä¸ç»æ°æ®ç§»å°ç»å®çåéä¸ï¼ç¶åè°ç¨ API
å½æ°ææ¹æ³åå
¥æ°æ®åãè¿ä¸è¿ç¨å°åå¤è¿è¡ï¼ç´å°æ´ä¸ªæ°æ®å¼
ãã åéå®æ¯ã
ãã ä½¿ç¨ text in row
ãã å¨ Microsoft
SQL Server 2000 ä¸ï¼ç¨æ·å¯ä»¥å¨è¡¨ä¸å¯ç¨ text in row é项ï¼ä»¥ä½¿è¯¥è¡¨è½å¤å¨å
¶æ°æ®è¡ä¸åå¨ textã
ãã ntext æ
image æ°æ®ã
ãã
ãã è¥è¦å¯ç¨è¯¥é项ï¼è¯·æ§è¡ sp_tableoption åå¨è¿ç¨ï¼å° text in row æå®ä¸ºé项åå¹¶å° on
æå®ä¸ºé项å¼ãBLOBï¼äºè¿
ãã å¶å¤§å¯¹è±¡ï¼textãntext æ image æ°æ®ï¼è¡ä¸å¯ä»¥åå¨çé»è®¤æ大大å°ä¸º 256 åèï¼ä½æ¯å¼çèå´å¯ä»¥ä»
24 å° 7000ã
ãã è¥è¦æå®é»è®¤å¼ä»¥å¤çæ大大å°ï¼è¯·æå®è¯¥èå´å
çæ´æ°ä½ä¸ºé项å¼ã
ãã
ãã å¦æåºç¨ä¸åæ¡ä»¶ï¼åå°
textãntext æ image å符串åå¨å¨æ°æ®è¡ä¸ï¼
ãã
ãã å¯ç¨ text in rowã
ãã
ãã
ãã
å符串çé¿åº¦æ¯ @OptionValue ææå®çéå¶ç
ãã
ãã
ãã æ°æ®è¡ä¸æ足å¤çå¯ç¨ç©ºé´ã
ãã å½ BLOB
å符串åå¨å¨æ°æ®è¡ä¸æ¶ï¼è¯»åååå
¥ textãntext æ image å符串å¯ä»¥ä¸è¯»åæåå
¥å符串åäºè¿å¶å符串
ãã ä¸æ ·å¿«ãSQL Server
ä¸å¿
访é®åç¬ç页以读åæåå
¥ BLOB å符串ã
ãã
ãã å¦æ textãntext æ image
å符串æ¯è¡ä¸ææå®çéå¶æå¯ç¨ç©ºé´å¤§ï¼åå°æéåå¨å¨è¯¥è¡ä¸ãå¨è¡ä¸åå¨ BLOB å符
ãã 串çæ¡ä»¶ä»ç¶éç¨ï¼ä½æ¯ï¼æ°æ®è¡ä¸å¿
é¡»æ足å¤ç空é´å®¹çº³æéã
ãã
ãã æå
³æ´å¤ä¿¡æ¯ï¼è¯·åè§ sp_tableoptionã
ãã
ãã 使ç¨ææ¬æé
ãã å¦ææªæå® text in
row é项ï¼textãntext æ image å符串å°åå¨å¨æ°æ®è¡å¤ï¼åªæè¿äºå符串çææ¬æéé©»çå¨æ°
ãã
æ®è¡ä¸ãææ¬æéæåç±å
é¨æéçæçæ çæ ¹èç¹ï¼èè¿äºå
é¨æéæ å°å°å®é
åå¨ï¼textãntext æ image æ°æ®çï¼
ãã å符串段ç页ã
ãã
ãã SQL Server 2000 ä¸çè¡ææ¬æéä¸ SQL Server æ©æçæ¬ä¸çææ¬æéä¸åãè¡ææ¬æéçè¡ä¸ºå°±è±¡ BLOB
æ°æ®çæ件
ãã å¥æï¼æ©æçææ¬æéåè½å象 BLOB æ°æ®çå°åãå æ¤ï¼å¨ä½¿ç¨è¡ææ¬æéæ¶ï¼è¯·è®°ä½ä¸åç¹æ§ï¼
ãã
ãã
ãã
ãã éè¦ è½ç¶æ¸¸æ ä¸å
许æè¡ææ¬ï¼ä½å´ä¸å
许æè¡ææ¬æéãå¦æå°è¯å£°æå
å«è¡ææ¬æéç游æ ï¼SQL Server å°è¿åé
ãã
误信æ¯ï¼8654ã16ã1ã"A cursor plan could not be generated for the given statement
because it contains
ãã textptr(inrow lob)."ã1033ï¼ã
ãã
ãã æ°å
ãã
对äºæ¯ä¸ªæ°æ®åºï¼æ¯ä¸ªäºå¡æå¤å
许 1024 个活å¨è¡ææ¬æéã
ãã
ãã éå®
ãã å½ç¨æ·è·åæ´»å¨ææ¬æéæ¶ï¼SQL Server
2000 å¨ç¬¬ä¸ä¸ªç¨æ·æ§å¶ææ¬æéæ¶éå®æ°æ®è¡ï¼å¹¶ç¡®ä¿æ²¡æå
¶ä»ç¨æ·ä¿®æ¹æå
ãã é¤è¯¥è¡ãéå¨ææ¬æéå为æ ææ¶è¢«éæ¾ãè¥è¦ä½¿ææ¬æéæ æï¼è¯·ä½¿ç¨
sp_invalidate_textptrã
ãã
ãã å½äºå¡çé离级å«æ¯æªæ交读æè
æ°æ®åºä¸º"åªè¯»"模å¼æ¶ï¼ææ¬æéä¸è½ç¨äºæ´æ° BLOB
å¼ã
ãã
ãã å½æ°æ®åºä¸º"åç¨æ·"模å¼æ¶ï¼SQL Server 2000 ä¸éå®æ°æ®è¡ã
ãã
ãã
为举ä¾è¯´æï¼ç»åºä¸é¢ç表ï¼
ãã
ãã CREATE TABLE t1 (c1 int, c2 text)
ãã EXEC
sp_tableoption 't1', 'text in row', 'on'
ãã INSERT t1 VALUES ('1', 'a')
ãã
ãã ä¸é¢çäºå¡å°ä¼æåï¼
ãã
ãã INSERT t1 VALUES ('1','This is text.')
ãã SET
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
ãã GO
ãã BEGIN TRAN
ãã
DECLARE @ptr varbinary(16)
ãã SELECT @ptr = textptr(c2)
ãã FROM t1
ãã
WHERE c1 = 1
ãã READTEXT t1.c2 @ptr 0 5
ãã COMMIT TRAN
ãã GO
ãã
ãã ä¸é¢çäºå¡å°ä¼å¤±è´¥ï¼
ãã
ãã SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED
ãã GO
ãã BEGIN TRAN
ãã DECLARE @ptr varbinary(16)
ãã
SELECT @ptr = textptr(c2)
ãã FROM t1
ãã WHERE c1 = 1
ãã WRITETEXT t1.c2
@ptr 'xx'
ãã COMMIT TRAN
ãã GO
ãã
ãã æç»æ¶é´
ãã
è¡ææ¬æéä»
å¨äºå¡å
ææãæ交äºå¡æ¶ï¼ææ¬æéå为æ æã
ãã
ãã å¨æ个äºå¡å
ï¼å½åçä¸åä»»ä¸æä½æ¶ï¼è¡ææ¬æéå¯è½æ æï¼
ãã
ãã ä¼è¯ç»æã
ãã
ãã
ãã å é¤è¯¥äºå¡ä¸çæ°æ®è¡ãï¼å
¶å®äºå¡æ æ³å é¤æ°æ®è¡ï¼å 为该è¡å
å«éãï¼
ãã
ãã
ãã ææ¬æéæå¨ç表çæ¶æå·²æ´æ¹ã使ææ¬æéæ æçæ¶ææ´æ¹æä½å
æ¬ï¼å建æé¤å»èéç´¢å¼ï¼æ¹åæé¤å»è¡¨ï¼æªæ表ï¼
ãã éè¿
sp_tableoption æ´æ¹ text in row é项ï¼ä»¥åæ§è¡ sp_indexoptionã
ãã 使ç¨åé¢ç示ä¾ï¼ä¸åèæ¬å¨ SQL
Server æ©æçæ¬ä¸ææï¼ä½å¨ SQL Server 2000 ä¸å°çæé误ã
ãã
ãã DECLARE @ptrval
varbinary(16)
ãã PRINT 'get error here'
ãã SELECT @ptrval =
TEXTPTR(c2)
ãã FROM t1
ãã WHERE c1 = 1
ãã READTEXT t1.c2 @ptrval 0
1
ãã
ãã å¨ SQL Server 2000 ä¸ï¼å¿
é¡»å¨äºå¡å
使ç¨è¡ææ¬æéï¼
ãã
ãã BEGIN TRAN
ãã
DECLARE @ptrval varbinary(16)
ãã SELECT @ptrval = TEXTPTR(c2)
ãã FROM
t1
ãã WHERE c1 = 1
ãã READTEXT t1.c2 @ptrval 0 1
ãã COMMIT
ãã
ãã
NULL ææ¬
ãã å¯ä»¥å¨ç± INSERT çæç NULL ææ¬ä¸è·å¾è¡ææ¬æéãèå¨ä»¥åï¼åªæå° BLOB æ´æ°ä¸º NULL
åæè½è·å¾ææ¬æéã
ãã
ãã ä¾å¦ï¼ä¸å代ç å¨ SQL Server 7.0 ä¸æ æï¼ä½å¨ SQL Server 2000
ä¸ææã
ãã
ãã SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ãã GO
ãã
INSERT INTO t1 VALUES (4, NULL)
ãã BEGIN TRAN
ãã DECLARE @ptrval
VARBINARY(16)
ãã SELECT @ptrval = TEXTPTR(c2)
ãã FROM t1
ãã WHERE c1 =
4
ãã WRITETEXT t1.c2 @ptrval 'x4'
ãã COMMIT
ãã
ãã å¨ SQL Server 7.0
ä¸ï¼å¿
é¡»æ§è¡ä¸åæä½ï¼
ãã
ãã INSERT INTO t1 VALUES (4, NULL)
ãã UPDATE t1
ãã
SET c2 = NULL
ãã WHERE c1 = 4
ãã DECLARE @ptrval VARBINARY(16)
ãã
SELECT @ptrval = TEXTPTR(c2)
ãã FROM t1
ãã WHERE c1 = 4
ãã WRITETEXT
t1.c2 @ptrval 'x4'
ãã
ãã ä¸è¡¨æ±æ»å·®å«ã
ãã
ãã å·®å« è¡ææ¬æé éè¡ææ¬æé
ãã æ°å
对äºæ¯ä¸ªæ°æ®åºï¼æ¯ä¸ªäºå¡æå¤å
许 1024 个活å¨è¡ææ¬æéã æ éå¶ã
ãã éå® å°æ°æ®è¡ä¸ç´ S éå®å°æéå为æ æ为æ¢ã
ãã
å½äºå¡ä¸º"æªæ交读"ææ°æ®åºä¸º"åç¨æ·"æ"åªè¯»"模å¼æ¶ä¸è·åéã
ãã ä¸éå®æ°æ®è¡ã
ãã æç»æ¶é´
äºå¡æä¼è¯ç»æãå é¤è¡ææ´æ¹è¡¨çæ¶ææ¶å为æ æã å é¤è¡æ¶å为æ æã
ãã NULL ææ¬ æå
¥ NULL ææ¬åå¯ç«å³è·åã åªææ´æ°åæè½è·åã
ãã
ãã
ãã éè¿æ°æ®åº API ä½¿ç¨ ntextãtext å image æ°æ®
ãã è¿ä¸é¨åæ¦è¿°æ°æ®åº API å¤ç
ntextãtext å image æ°æ®çæ¹å¼ï¼
ãã
ãã ADO
ãã ADO å¯ä»¥å° ntextãtext æ image
åæåæ°æ å°ä¸º Field æ Parameter 对象ãä½¿ç¨ GetChunk æ¹æ³éåæ£ç´¢æ°æ®ï¼ä½¿
ãã ç¨ AppendChunk
æ¹æ³éååæ°æ®ãæå
³æ´å¤ä¿¡æ¯ï¼è¯·åè§ç®¡ç Long æ°æ®ç±»åã
ãã
ãã OLE DB
ãã OLE DB 使ç¨
ISequentialStream æ¥å£æ¯æ ntextãtext å image æ°æ®ç±»åãISequentialStream::Read
æ¹æ³éå读å
ãã é¿æ°æ®ï¼ISequentialStream::Write æ¹æ³å°é¿æ°æ®éååå
¥æ°æ®åºãæå
³æ´å¤ä¿¡æ¯ï¼è¯·åè§ BLOB å OLE
对象ã
ãã
ãã ODBC
ãã ODBC å
·æä¸ç§ç§°ä¸º"æ§è¡ä¸çæ°æ®"çåè½ï¼å¯ç¨äºå¤çé¿æ°æ®ç ODBC
æ°æ®ç±»åï¼SQL_WLONGVARCHAR (ntext)ã
ãã SQL_LONGVARCHAR (text) å SQL_LONGVARBINARY
(image)ãè¿äºæ°æ®ç±»å被ç»å®å°æ个ç¨åºåéä¸ãè¿æ ·ä¸æ¥ï¼å°±å¯ä»¥è°
ãã ç¨ SQLGetData éåæ£ç´¢é¿æ°æ®ï¼è°ç¨ SQLPutData
éååéé¿æ°æ®ãæå
³æ´å¤ä¿¡æ¯ï¼è¯·åè§ç®¡ç text å image åã
ãã
ãã DB-Library
ãã DB-Library
åºç¨ç¨åºä¹æ¯å° ntextãtext å image åç»å®å°ç¨åºåéä¸ãDB-Library å½æ° dbtxtptr ç¨äºè·åæåæ°
ãã
æ®åºä¸é¿ååºç°ä½ç½®çæéï¼dbreadtext åç¨æ¥éå读åé¿æ°æ®ãdbwritetextãdbupdatetext å dbmoretext
ä¹ç±»çå½
ãã æ°ç¨äºéååå
¥é¿æ°æ®ã
ãã
ãã
ãã
ãã 说æ ä¸æ¯æä½¿ç¨ DB-Library 访é®è¡ææ¬
温馨提示:答案为网友推荐,仅供参考