3.放棄CHAR吧，在鑄成大錯之前！

標籤：使用   資料   cti   代碼   io   應用   

/*************************************************
三、主題：放棄CHAR吧，在鑄成大錯之前！

CHAR,定長，長度不足時，會在尾部補空格。
VARCHAR,不定長，長度不一定達到最大值。
*************************************************/

=============================================================================
3.1.length，返回字元長度的函數
The LENGTH functionsreturn the length of char. LENGTH calculates length usingcharacters as defined by the input character set.
     --返回以字元為單位的長度.
LENGTHB usesbytes instead of characters.
     --返回以位元組為單位的長度.
LENGTHC usesUnicode complete characters.
     --返回以Unicode完全字元為單位的長度.
LENGTH2 usesUCS2 code points.
     --返回以UCS2代碼點為單位的長度.
LENGTH4 usesUCS4 code points.
    --返回以UCS4代碼點為單位的長度.
=============================================================================
    CREATE TABLE TEST4
    (
      C CHAR(4),
      VC VARCHAR(4),
      NC VARCHAR(4)
    );

    INSERT INTO TEST4 VALUES(‘1‘,‘12‘,‘10‘);
    INSERT INTO TEST4 VALUES(‘3‘,‘3‘,‘210‘);
    INSERT INTO TEST4 VALUES(‘ ‘,‘110‘,‘1300‘);
    INSERT INTO TEST4 VALUES(‘25‘,‘ ‘,‘9‘); 
    INSERT INTO TEST4 VALUES(‘AA‘,‘HE‘,‘50‘);       
=============================================================================
3.2.CHAR 與 VARCHAR 的長度  
    SELECT C,LENGTH(C),VC,LENGTH(VC) FROM TEST4;--查詢結果：其中有C=‘1   ‘，明顯可以看到後面有空格
   
    SELECT C,LENGTH(C),LENGTHB(C),LENGTHC(C),DUMP(C),--DUMP:其中有C=‘1   ‘時，Typ=96 Len=4: 49,32,32,32,後面三個32表示空格
           VC,LENGTH(VC),LENGTHB(VC),LENGTHC(VC),DUMP(VC)--DUMP:明顯的區別，實際的長度，而不是最大長度
       FROM TEST4;

3.3.比較大小(常規字元型)      
   
    SELECT C,DUMP(C)
      FROM TEST4 ORDER BY C;--排序結果：‘ ‘ ,‘1‘,‘25‘,‘3‘,‘AA‘
     
    SELECT VC,DUMP(VC)
      FROM TEST4 ORDER BY VC;--排序結果：‘ ‘,‘110‘,‘12‘,‘3‘,‘HE‘

3.4.數值在排序中的效果比較，重要應用：字元型儲存資料全為數字時。
    SELECT NC,DUMP(NC)
      FROM TEST4
     ORDER BY NC;--排序結果：10,1300,210,50,9     
   --若是想將全為數位字元型進行排序，ORDER BY C+0  或者 ORDER BY TO_NUMBER(NC) 即可
    SELECT NC,DUMP(NC)
      FROM TEST4
     ORDER BY NC+0;--排序結果：9,10,50,210,1300
  
    SELECT NC,DUMP(NC)
      FROM TEST4
     ORDER BY TO_NUMBER(NC);--排序結果：9,10,50,210,1300

3.5.字串的比較
    填補空格的比較規則：若是長度不同，會在短的後面填補空格，等長度一致後比較；
                        自左往右順序比較，直到比較出不一致，或者比較完成。    
   (1)單純的字面值
      SELECT * FROM TEST4 WHERE C=‘1‘;  --能查出結果，正確的
     
   (2)VARCHAR 的值不會填補空格，所以TEST4的 CHAR ‘1   ‘ 與 TEST1的 VARCHAR2 ‘1‘ 不同  
      SELECT * FROM TEST4 WHERE C IN (SELECT NAME FROM TEST1);--0行被選擇，無查詢結果，其實TEST1 裡面有 ‘1‘.
      --原因
      SELECT DUMP(C),DUMP(NAME)
       FROM TEST4，TEST1
      WHERE TEST4.C=‘1‘
        AND TEST1.NAME=‘1‘
     結果：Typ=96 Len=4: 49,32,32,32 ; Typ=1 Len=1: 49.
    
   (3)要想得出正確結果，所以經常有 TRIM,RPAD 函數出現,但是不推薦
      SELECT * FROM TEST4 WHERE TRIM(C) IN (SELECT NAME FROM TEST1);--TEST4 清除空格， 查詢結果有值
      SELECT * FROM TEST4 WHERE C IN (SELECT RPAD(NAME,4) FROM TEST1); --TEST1 填充空格，查詢結果有值

   --若是有CHAR存在，每次比較時，可能就要加上一些字元的函數。
   --CHAR浪費空間，並且可能查詢中連索引都使用不上。
  
3.6.結論：放棄CHAR吧，在鑄成大錯之前！