ä¸ varchar,nvarchar,
å个类åé½å±äºåé¿å符类åï¼ varcharåvarchar2çåºå«å¨ä¸åè
æææå符é½å 两åèï¼åè
åªå¯¹æ±ååå
¨è§çå符å 两åèã nvarcharånvarchar2çåºå«åä¸é¢ä¸æ ·ï¼ ä¸ä¸é¢åºå«å¨äºæ¯æ ¹æ®Unicode æ åæè¿è¡çå®ä¹çç±»åï¼é常ç¨äºæ¯æå¤å½è¯è¨ç±»ä¼¼ç³»ç»çå®ä¹ã
1.char
charçé¿åº¦æ¯åºå®çï¼æ¯å¦è¯´ï¼ä½ å®ä¹äºchar(20),å³ä½¿ä½ ä½ æå
¥abcï¼ä¸è¶³äºå个åèï¼æ°æ®åºä¹ä¼å¨abcåé¢èªå¨å ä¸17ä¸ªç©ºæ ¼ï¼ä»¥è¡¥è¶³äºå个åèï¼
charæ¯åºåä¸è±æçï¼ä¸æå¨charä¸å 两个åèï¼èè±æå ä¸ä¸ªï¼æ以char(20)ä½ åªè½å20个åæ¯æ10个æ±åã
charéç¨äºé¿åº¦æ¯è¾åºå®çï¼ä¸è¬ä¸å«ä¸æçæ
åµ
2.varchar/varchar2
varcharæ¯é¿åº¦ä¸åºå®çï¼æ¯å¦è¯´ï¼ä½ å®ä¹äºvarchar(20),å½ä½ æå
¥abcï¼åå¨æ°æ®åºä¸åªå 3个åèã
varcharåæ ·åºåä¸è±æï¼è¿ç¹åcharã
varchar2åºæ¬ä¸çåäºvarcharï¼å®æ¯oracleèªå·±å®ä¹çä¸ä¸ªéå·¥ä¸æ åvarcharï¼ä¸åå¨äºï¼varchar2ç¨null代æ¿varcharç空å符串
varchar/varchar2éç¨äºé¿åº¦ä¸åºå®çï¼ä¸è¬ä¸å«ä¸æçæ
åµ
3.nvarchar/nvarchar2
nvarcharånvarchar2æ¯é¿åº¦ä¸åºå®ç
nvarcharä¸åºåä¸è±æï¼æ¯å¦è¯´ï¼ä½ å®ä¹äºnvarchar(20),ä½ å¯ä»¥åå
¥20个è±æåæ¯/æ±åæä¸è±æç»åï¼è¿ä¸ª20å®ä¹çæ¯å符æ°èä¸æ¯åèæ°
nvarchar2åºæ¬ä¸çåäºnvarcharï¼ä¸åå¨äºnvarchar2ä¸åçè±æåæ¯ä¹å 两个åè
nvarchar/nvarchar2éç¨äºåæ¾ä¸æ
char [ ( n ) ]
åºå®é¿åº¦ï¼é Unicode å符æ°æ®ï¼é¿åº¦ä¸º n 个åèãn çåå¼èå´ä¸º 1 è³ 8,000ï¼åå¨å¤§å°æ¯ n 个åèã
varchar [ ( n | max ) ]
å¯åé¿åº¦ï¼é Unicode å符æ°æ®ãn çåå¼èå´ä¸º 1 è³ 8,000ãmax æ示æ大åå¨å¤§å°æ¯ 2^31-1 个åèãåå¨å¤§å°æ¯è¾å
¥æ°æ®çå®é
é¿åº¦å 2 个åèï¼ç¨äºåæ åå¨çæ°æ®çé¿åº¦ãæè¾å
¥æ°æ®çé¿åº¦å¯ä»¥ä¸º 0 个å符ã
* å¦æåæ°æ®é¡¹ç大å°ä¸è´ï¼åä½¿ç¨ charã
* å¦æåæ°æ®é¡¹ç大å°å·®å¼ç¸å½å¤§ï¼åä½¿ç¨ varcharã
* å¦æåæ°æ®é¡¹å¤§å°ç¸å·®å¾å¤§ï¼èä¸å¤§å°å¯è½è¶
è¿ 8,000 åèï¼è¯·ä½¿ç¨ varchar(max)ã
å¦ææªå¨æ°æ®å®ä¹æåé声æè¯å¥ä¸char æ varchar æ°æ®ç±»åæå® nï¼åé»è®¤é¿åº¦ä¸º 1ãå¦æå¨ä½¿ç¨ CAST å CONVERT å½æ°æ¶char æ varchar æ°æ®ç±»åæªæå® nï¼åé»è®¤é¿åº¦ä¸º 30ã
å½æ§è¡ CREATE TABLE æ ALTER TABLE æ¶ï¼å¦æ SET ANSI_PADDING 为 OFFï¼åå®ä¹ä¸º NULL ç char åå°ä½ä¸º varchar å¤çã
å¦å¤å¸®å©ç解çï¼åªä¾åèï¼è½¬èª
http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197ä¹å¯åç
§å¦ä¹
http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=11.NULLå¼(空å¼)ã
a. charåçNULLå¼å ç¨åå¨ç©ºé´ã
b. varcahråçNULLå¼ä¸å ç¨åå¨ç©ºé´ã
c. æå
¥åæ ·æ°éçNULLå¼ï¼varcharåçæå
¥æçææ¾é«åºcharåã
2.æå
¥æ°æ®
æ 论æå
¥æ°æ®æ¶åçåæ¯å¦å»ºç«ç´¢å¼ï¼charçæçé½ææ¾ä½äºvarcharã
3. æ´æ°æ°æ®
å¦ææ´æ°çåä¸æªå»ºç«ç´¢å¼ï¼åcharçæçä½äºvarcharï¼å·®å¼ä¸å¤§ï¼å»ºç«ç´¢å¼çè¯ï¼æçè¾é«ã
4. ä¿®æ¹ç»æ
a. æ 论å¢å ææ¯å é¤çåçç±»åæ¯charè¿æ¯varcharï¼æä½é½è½è¾å¿«çå®æï¼èä¸æçä¸æ²¡æä»ä¹å·®å¼ã
b. 对äºå¢å åç宽度èè¨ï¼charä¸varcharæé常ææ¾çæçå·®å¼ï¼ä¿®æ¹varcahrååºæ¬ä¸ä¸è±è´¹æ¶é´ï¼èä¿®æ¹charåéè¦è±è´¹å¾é¿çæ¶é´ã
5.æ°æ®æ£ç´¢
æ 论æ¯å¦éè¿ç´¢å¼ï¼varcharç±»åçæ°æ®æ£ç´¢ç¥ä¼äºcharçæ«æã
éæ©charè¿æ¯éæ©varcharç建议
1.éå®äºcharçæ
åµï¼
a. åä¸çåè¡æ°æ®é¿åº¦åºæ¬ä¸è´ï¼é¿åº¦ååä¸è¶
è¿50åèï¼
b. æ°æ®åæ´é¢ç¹ï¼æ°æ®æ£ç´¢çéæ±è¾å°ã
c. åçé¿åº¦ä¸ä¼ååï¼ä¿®æ¹charç±»ååç宽度ç代价æ¯è¾å¤§ã
d. åä¸ä¸ä¼åºç°å¤§éçNULLå¼ã
e. åä¸ä¸éè¦å»ºç«è¿å¤çç´¢å¼ï¼è¿å¤çç´¢å¼å¯¹charåçæ°æ®åæ´å½±åè¾å¤§ã
2.éå®äºvarcharçæ
åµ;
a. åä¸çåè¡æ°æ®çé¿åº¦å·®å¼æ¯è¾å¤§ã
b. åä¸æ°æ®çæ´æ°é常å°ï¼ä½æ¥è¯¢é常é¢ç¹ã
c. åä¸ç»å¸¸æ²¡ææ°æ®ï¼ä¸ºNULLå¼æ为空å¼
nchar [ ( n ) ]
n 个å符çåºå®é¿åº¦ç Unicode å符æ°æ®ãn å¼å¿
é¡»å¨ 1 å° 4,000 ä¹é´ï¼å«ï¼ãåå¨å¤§å°ä¸ºä¸¤å n åèã
nvarchar [ ( n | max ) ]
å¯åé¿åº¦ Unicode å符æ°æ®ãn å¼å¨ 1 å° 4,000 ä¹é´ï¼å«ï¼ãmax æ示æ大åå¨å¤§å°ä¸º 2^31-1 åèãåå¨å¤§å°æ¯æè¾å
¥å符个æ°ç两å + 2 个åèãæè¾å
¥æ°æ®çé¿åº¦å¯ä»¥ä¸º 0 个å符ã
注é
å¦æ没æå¨æ°æ®å®ä¹æåé声æè¯å¥ä¸æå® nï¼åé»è®¤é¿åº¦ä¸º 1ãå¦æ没æä½¿ç¨ CAST å½æ°æå® nï¼åé»è®¤é¿åº¦ä¸º 30ã
å¦æåæ°æ®é¡¹ç大å°å¯è½ç¸åï¼è¯·ä½¿ç¨ ncharã
å¦æåæ°æ®é¡¹ç大å°å¯è½å·®å¼å¾å¤§ï¼è¯·ä½¿ç¨ nvarcharã
sysname æ¯ç³»ç»æä¾çç¨æ·å®ä¹æ°æ®ç±»åï¼é¤äºä¸å¯ä¸ºç©ºå¼å¤ï¼å¨åè½ä¸ä¸ nvarchar(128) ç¸åãsysname ç¨äºå¼ç¨æ°æ®åºå¯¹è±¡åã
ä¸ºä½¿ç¨ nchar æ nvarchar ç对象åé
çæ¯é»è®¤çæ°æ®åºæåºè§åï¼ä½å¯ä½¿ç¨ COLLATE åå¥åé
ç¹å®çæåºè§åã
SET ANSI_PADDING ON æ°¸è¿éç¨äº nchar å nvarcharãSET ANSI_PADDING OFF ä¸éç¨äº nchar æ nvarchar æ°æ®ç±»åã
å¨Oracleä¸CHAR,NCHAR,VARCHAR,VARCHAR2,NVARCHAR2è¿äºç§ç±»åçåºå«
1.CHAR(size)åVARCHAR(size)çåºå«
CHAR为å®é¿çå段ï¼æ大é¿åº¦ä¸º2Kåèï¼
VARCHAR为å¯åé¿çå段ï¼æ大é¿åº¦ä¸º4Kåèï¼
2.CHAR(size)åNCHAR(size)çåºå«
CHARå¦æåæ¾åæ¯æ°åå 1个åèï¼åæ¾GBKç¼ç çæ±ååæ¾2个åèï¼åæ¾UTF-8ç¼ç çæ±åå ç¨3个åèï¼
NCHARæ ¹æ®æéå符éæ¥å®ä¹åæ¾å符çå ç¨åèæ°ï¼ä¸è¬é½ä¸º2个åèåæ¾ä¸ä¸ªå符(ä¸ç®¡å符æè
æ±å)
3.VARCHAR(size)åVARCHAR2(size)çåºå«
å¨ç°å¨ççæ¬ä¸ï¼ä¸¤è
æ¯æ²¡æåºå«çï¼æ大é¿åº¦ä¸º4Kåèï¼æ¨è使ç¨VARCHAR2ï¼
4.VARCHAR2(size)åNVARCHAR2(size)çåºå«
æ大é¿åº¦ä¸º4Kåèï¼åºå«åCHARä¸NCHARçåºå«ï¼ï¼å¦ææ°æ®åºå符éé¿åº¦æ¯2ï¼åNVARCHAR2æ大为2Kï¼
5.å
±åç¹æ§
å½æ§è¡insertçæ¶åï¼æå
¥çå¼ä¸º''ï¼å转åænullï¼å³insert ... values('') <=> insert ... values(null)
æç´¢çæ¡ä»¶é¡»ç¨where xx is null
6.ä¾å
æ¯å¦æä¸ä¸ªæ§å«å段ï¼éé¢åæ¾âç·ï¼å¥³âçå
¶ä¸ä¸ä¸ªå¼ï¼ä¸¤ç§å¸¸ç¨éæ©
CHAR(2) å NCHAR(1)
Oracleä¸çTIMESTAMPç±»å
SQL> create table test_time (col_time timestamp);
表已å建ã
SQL> insert into test_time values (to_timestamp('0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));
å·²å建 1 è¡ã
SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));
å·²å建 1 è¡ã
SQL> insert into test_time values (to_timestamp('9999-12-31 23:59:59.999999', 'syyyy-mm-dd hh24:mi:ss.ff'));
å·²å建 1 è¡ã
SQL> insert into test_time values (to_timestamp('-0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));
å·²å建 1 è¡ã
SQL> insert into test_time values (to_timestamp('-0100-3-4 13:2:3.234015', 'syyyy-mm-dd hh24:mi:ss.ff'));
å·²å建 1 è¡ã
SQL> insert into test_time values (systimestamp);
å·²å建 1 è¡ã
SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));
å·²å建 1 è¡ã
SQL> commit;
æ交å®æã
SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
2 from test_time;
TIME DUMP_TIME
------------------------------ ----------------------------------------------------
0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232
å·²éæ©7è¡ã
ä¸DATEç±»å对æ¯å¯ä»¥åç°ï¼å¯¹äºTIMESTAMPç±»åï¼å¦æä¸å
å«å¾®ç§ä¿¡æ¯æè
å¾®ç§å¼ä¸º0ï¼é£ä¹åå¨ç»æåDATEå®å
¨ç¸åãå½å¾®ç§å¼ä¸º0æ¶ï¼Oracle为äºèç空é´ï¼ä¸ä¼ä¿åå¾®ç§ä¿¡æ¯ã
å¦æ毫ç§å¼ä¸ä¸º0ï¼Oracleæå¾®ç§å¼å½ä½ä¸ä¸ª9ä½æ°çæ°åæ¥ä¿åã
æ¯å¦999999000ï¼ä¿å为59,154,198,24ã234015000ä¿å为13,242,201,24ã
SQL> select to_char(999999000, 'xxxxxxxxxx') from dual;
TO_CHAR(999
-----------
3b9ac618
SQL> select to_number('3b', 'xxx') one, to_number('9a', 'xxx') two,
2 to_number('c6', 'xxx') three, to_number('18', 'xxx') four from dual;
ONE TWO THREE FOUR
---------- ---------- ---------- ----------
59 154 198 24
SQL> select to_char(234015000, 'xxxxxxxx') from dual;
TO_CHAR(2
---------
df2c918
SQL> select to_number('d', 'xxx') one, to_number('f2', 'xxx') two,
2 to_number('c9', 'xxx') three, to_number('18', 'xxx') four from dual;
ONE TWO THREE FOUR
---------- ---------- ---------- ----------
13 242 201 24
å¦å¤ï¼æ³¨æä¸ç¹ï¼ä¸æå®ç²¾åº¦çæ
åµä¸ï¼TIMESTAMPé»è®¤å6ä½ãé¿åº¦è¶
è¿6ä½ï¼ä¼åèäºå
¥å°6ä½ãå¦æå¸æä¿å9ä½çTIMESTAMPï¼å¿
é¡»æç¡®æå®ç²¾åº¦ã
SQL> alter table test_time modify (col_time timestamp(9));
表已æ´æ¹ã
SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));
å·²å建 1 è¡ã
SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
2 from test_time;
TIME DUMP_TIME
------------------------------ ---------------------------------------------------
0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232
2000-01-01 00:00:00.123456789 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,21
å·²éæ©8è¡ã