前端與 SQL 全面梳理

本篇將介紹前端本地儲存裡的Web SQL和IndexedDB，通過一個案例介紹SQL的一些概念。

1. 地圖報表的案例

現在要做一個地圖報表，如下圖所示：

將所有的訂單資料做一個圖表展示，左邊的地圖展示每個city的成單情況，右邊的圖形，展示最近7天的成單情況。由於後端的資料需要前端做一些解析，如向谷歌請求每個city的經緯度，所以後端給前端原始的訂單資料，前端進行格式化和歸類展示。另外把原始資料直接放前端，前端處理起來可以比較靈活，想怎麼展示就怎麼展示，不用每次展示方式變的時候都需要找後端新加介面。

但是資料放在前端管理，相應地就會引入一個問題——如何高效地儲存和使用這些資料。最起碼處理起來不要讓頁面卡了。

2. cookie和localStorage

cookie的資料量比較小，瀏覽器限制最大隻能為4k，而localStorage和sessionStorage適合於小資料量的儲存，firefox和Chrome限制最大儲存為5Mb，如下火狐的config：

localStorage是存放在一個本地檔案裡面，在筆者的Mac上是放在：

/Users/yincheng/Library/Application Support/Google/Chrome/Default/Local Storage/ http_www.test.com.localstorage

用文字編輯器開啟這個二進位制檔案，可以看到本地儲存的內容：

可以參照控制檯的輸出：

如果一個網站要用掉5Mb硬碟空間，那麼開啟過一百個網頁就得花500Mb的空間，所以本地儲存localStorage的空間限制得比較小。

另外，可以看到localStorage是以字串的方式儲存的，存之前要先JSON.stringify變成字串，取的時候需要用JSON.parse恢復成相應的格式。localStorage適合於比較簡單的資料存放和管理。

3. 管理複雜資料

後端給我這樣的JSON資料：

[

{“orderId”:100314,”userId”:379558604617762,”city”:”ca”,”state”:”ca”,”zipcode”:”91000″,”address”:”11″,”price”:2698.00,”createTime”:1477651308000},

{“orderId”:100821,”userId”:514694887070560,”city”:”San Francisco”,”state”:”CA”,”zipcode”:”94103″,”address”:”251 Rhode Island St #105″,”price”:2182.00,”createTime”:1481104358000}

]

我用這些資料去請求它們的經緯度。

這些資料的量比較大，有成百上千甚至幾萬條資料，資料需要複雜的查詢，需要支援：

1. 訂單按日期分類和排序
2. 訂單按照city分類

如果自己管理JSON資料就會比較麻煩，所以這裡嘗試使用Web SQL來管理這些資料。

4. Web SQL

（1）什麼是SQL

SQL作用在關係型資料庫上面，什麼是關係型資料庫？關係型資料庫是由一張張的二維表組成的，如下圖所示：

那什麼是SQL呢？SQL是一種操作關係型DB的語言，支援建立表，插入表，修改和刪除等等，還提供非常強大的查詢功能。

常見的關係型資料庫廠商有MySQL、SQLite、SQL Server、Oracle，由於MySQL是免費的，所以企業一般用MySQL的居多。

Web SQL是前端的資料庫，它也是本地儲存的一種，使用SQLite實現，SQLite是一種輕量級資料庫，它佔的空間小，支援建立表，插入、修改、刪除表格資料，但是不支援修改表結構，如刪掉一縱列，修改表頭欄位名等。但是可以把整張表刪了。同一個域可以建立多個DB，每個DB有若干張表，如下圖示意：

（2）建立一個DB

如下程式碼所示：

使用openDatabase，傳4個引數，指定資料庫大小，如果指定太大，瀏覽器會提示使用者是否允許使用這麼多空間，如Safari的提示：

如果不允許，瀏覽器將會拋異常：

QuotaExceededError (DOM Exception 22): The quota has been exceeded.

這樣就建立了一個資料庫叫order_test，返回了一個db物件，使用這個db物件建立一張表

（3）建立表

如下程式碼所示：

db.transaction(function(tx){
    tx.executeSql(
"create table if not exists order_data(order_id primary key, format_city, lat, lng, price, create_time)", [], null,  
function(tx, err){
        throw(`execute sql failed: ${err.code} ${err.message}`);
    });
});

傳一個回撥給db.transaction，它會傳一個SQLTransaction的例項，它表示一個事務，然後調executeSql函式，傳四個引數，第一個引數為要執行的SQL語句，第二個引數為選項，第三個為成功回撥函式，第四個為失敗回撥函式，這裡我們拋一個異常，列印失敗的描述。我們執行的SQL語句為：

create table if not exists order_data(order_id primary key, format_city, lat, lng, price, create_time)

意思是建立一張order_data表，它的欄位有6個，第一個order_id為主鍵，主鍵用來標誌這一列，並且不允許有重複的值。

現在往這張表插入資料。

（4）插入資料

準備好原始資料和對資料做一些處理，如下所示：

var order = { 
    orderId: 100314, format_city: "New York, NY, USA", 
    lat: 40.7127837, lng: -74.0059413,
    price: 150, createTime: 1473884040000
};
//把時間戳轉成年月日2017-06-08型別的
var date = dataProcess.getDateStr(order.createTime);

然後執行插入：

tx.executeSql(`
insert into order_data 
values(${order.orderId}, '${order.format_city}', 
          ${order.lat}, ${order.lng}, ${order.price}, '${date}')`);

就可以在瀏覽器控制檯看到剛剛建立的資料庫、表，如下圖所示：

如果把剛剛的那條資料再插入一遍會怎麼樣呢？如重新整理一下頁面，它又重新執行。

（5）主鍵唯一約束

插入一個重複主鍵，這裡為id，executeSql的失敗函式將會執行，如下所示：

所以一般id是自動生成的，mysql可以指定某個整數字段為auto_increment，而web sql對整數字段不指定也是auto_increment，需要在建立的時候指定當前欄位為integer，如下語句：

create table student(id integer primary key auto_increment, age, score);

作用是建立一張student表，它的id是自動自增的，執行insert插入時會自動生成一個id：

insert into student(grade, score) values(5, 88);

這樣插入幾次，得到如下表：

可以看到id由1開始自動增長。經常利用這種自增功能生成使用者的id、訂單的id等等。

上面指定了id為整型，就不能插入一個字串的資料，否則會報錯。而如果沒指定，可以插入數字也可以插入字串，當然同一欄位最好型別要一致。如mysql、SQL Server等資料庫都是強型別的。

這裡有一個細節需要注意，後端的mysql的id一般採用64位的長整型，這個數最大值為一個19位數：

9223372036854775807

而JS的最大整數為一個16位數，大於這個數的值將會是不可靠的，如下圖所示：

因此如果發生這種情況的話，需要讓後端把ID當作字串的方式傳給你。這個我在《為什麼0.1 + 0.2不等於0.3？》這篇文章裡面做過討論。

（6）全部的資料

把所有的資料都插入之後，得到如下表：

然後我們開始做查詢。

（7）Select查詢

a）查出每個城市的單數和，按日期升序。便於地圖按city展示，可以執行以下SQL：

     select format_city as city, count(order_id) as ‘count’, sum(price) as amount from order_data group by format_city order by date

結果如下圖所示：

b）然後再查一下最近7天每一天的單數，用於右邊柱狀圖的展示，執行以下SQL：

select date, count(order_id) as ‘count’, sum(price) as amount from order_data group by date order by date desc limit 0, 7

得到：

c）查詢某個orderId是否存在，因為資料需要動態更新，例如每兩個小時更新一次，如果有新資料需要去查詢格式化的地址以及經緯度。而每次請求都是拉取全部資料，因此需要找出哪些是新資料。可以執行：

select order_id from order_data where order_id = ${order.orderId}

IndexedDB是本地儲存的第三種方式，它是非關係型資料庫。它的建立資料庫、建表、插入資料等操作如下程式碼如下，這裡不進行拆分講解，具體API細節讀者可查MDN等相關文件。

//建立和開啟一個資料庫
var request = window.indexedDB.open("orders", 7);
var db = null;
request.onsuccess = function(event){
    db = event.target.result;
    //如果order_data表已經存在，則直接插入資料
    if(db.objectStoreNames.contains("order_data")){
        var orderStore = db.transaction("order_data", "readwrite").objectStore("order_data");
        //insertOrders(orderStore);
    }

};

request.onupgradeneeded = function(event){
    db = event.target.result;
    //如果order_data表不存在則建立，並插入資料
    if(!db.objectStoreNames.contains("order_data")){
        var orderStore = db.createObjectStore("order_data", {keyPath: "orderId"});
        insertOrders(orderStore);
    }
};

function insertOrders(orderStore){
    var orders = orderData.data;
    for(var i = 0; i < orders.length; i++){
        orderStore.add(orders[i]);          //add是一個非同步的操作，返回一個IDBRequest，有onsucess
    }
}

function query(orderId){
    db.transaction("order_data", "readonly") //IDBTransaction
      .objectStore("order_data")             //IDBObjectStore
      .get(orderId)                          //IDBRequest
      .onsuccess = function(event){
          var order = event.target.result;
          console.log(order)
    };
}

var specilaData = {
    orderId: 'hello, world',
    text: "goodbye, world"
};

var orderStore = db.transaction("order_data", "readwrite").objectStore("order_data");
orderStore.add(specilaData).onsuccess = function(event){
    orderStore.get('hello, world').onsuccess = function(event){
        console.log(event.target.result);
    };
};