老虎SQL問題答案之漏洞

老虎者，壇主也，專數學，善計算，眾人皆稱能。

某日示考題，然觀其解，現微隙二三。以文記之。

 

 

模仿古文太累了，還是說普通話吧。

老虎在論壇裡面發了一篇文章，原文如下：

資料庫課，開始的部分講sql，講到create table和約束，在虎吧上給他們留了一道思考題，是從那本著名的SQL Puzzles and Answers裡找的一道謎題，在原書上的解答其實是有錯誤的

請建立一新表，包含三個列，全部都是varchar2(5)資料型別

要求給這個表上加上合適的約束，使

第一個列只能輸入“不含字母的字串”（就是全部由數字或特殊符號等組成），第二個列只能輸入“包含字母的字串”，第三個列只能輸入“全部是字母的字串”

寫出相關的create table語句

結果很快有一同學就解答出來了，並且把原書的錯誤也糾正了

隨後給出了問題的答案：

這是他給的原始答案

create table x (a varchar2(5) check (upper(a)=a and lower(a)=a), b varchar2(5) check (not upper(b)=b or not lower(b)=b), c varchar2(5) check (length(translate(c,' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))=0));

這個答案還存在一些瑕疵
1 每個列上還要給not null約束，他後來自己也很快發現這個問題並且指出糾正
2 第一個check其實可以簡化為upper(a)=lower(a)，第二個check可以簡化為upper(b)<>lower(b)
3 第三個check使用的translate函式是神來之筆，原書上這個地方是錯的。但translate的通用性不好，最好有通用性更好的辦法

對於老虎的這個答案，顯然還存在很多問題。根據第三個問題的答案分析，老虎這裡的字母指的是英文字母，否則第三個問題的答案就是不完整的。

那麼下面就按照英文字母來理解題中所提到的字母，當然字母代表英文字母也是絕大部分情況下正常的理解。

其實第一題和第二題的答案思路是一樣的，透過UPPER和LOWER函式是否相等，來判斷一個字元是否英文字元。這種方法是很不嚴謹的：

SQL> SELECT UPPER(CHR(42660)), LOWER(CHR(42660)) FROM DUAL;

UP LO
-- --
Δ δ

這是一個最簡單的例子，要想知道不滿足這個條件的結果有多少，可以執行下面的PL/SQL程式碼：

SQL> SET SERVEROUT ON SIZE 1000000
SQL> BEGIN
  2   FOR I IN 1..65535 LOOP
  3    IF UPPER(CHR(I)) != LOWER(CHR(I)) AND LENGTH(CHR(I)) = 1 THEN
  4     DBMS_OUTPUT.PUT_LINE(I || ':' || CHR(I));
  5    END IF;
  6   END LOOP;
  7  END;
  8  /

當然這裡面大部分的結果是一個英文字母加上一個無法顯示的字元，但是既然可能存在這種現象，作為標準答案就可以考慮。何況，還有上面例子給出的希臘字元在內的多種國家的字母。

憑經驗或主管判斷利用UPPER和LOWER來判斷是否是英文字元的方法存在很多的不確定性，除非像上面一樣驗證過所有的字元UPPER和LOWER的輸出結果。

其實對於這種問題，可以利用LTRIM、REPLACE、TRANSLATE等函式來判斷，當然對於高版本也可以使用正規表示式來進行判斷。所有上面這些方法的共同點是在函式中明確指出了所有的可能性，比如利用LTRIM的方法：LTRIM(UPPER(C), ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’) IS NULL。