本篇將介紹前端本地儲存裡的Web SQL和IndexedDB，通過一個案例介紹SQL的一些概念。
1. 地圖報表的案例
現在要做一個地圖報表，如下圖所示：
將所有的訂單資料做一個圖表展示，左邊的地圖展示每個city的成單情況，右邊的圖形，展示最近7天的成單情況。由於後端的資料需要前端做一些解析，如向谷歌請求每個city的經緯度，所以後端給前端原始的訂單資料，前端進行格式化和歸類展示。另外把原始資料直接放前端，前端處理起來可以比較靈活，想怎麼展示就怎麼展示，不用每次展示方式變的時候都需要找後端新加介面。
但是資料放在前端管理，相應地就會引入一個問題——如何高效地儲存和使用這些資料。最起碼處理起來不要讓頁面卡了。
2. cookie和localStorage
cookie的資料量比較小，瀏覽器限制最大隻能為4k，而localStorage和sessionStorage適合於小資料量的儲存，firefox和Chrome限制最大儲存為5Mb，如下火狐的config：
localStorage是存放在一個本地檔案裡面，在筆者的Mac上是放在：
|
1 |
/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資料：
|
1 2 3 4 5 6 7 |
[ {“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} ] |
我用這些資料去請求它們的經緯度。
這些資料的量比較大，有成百上千甚至幾萬條資料，資料需要複雜的查詢，需要支援：
- 訂單按日期分類和排序
- 訂單按照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的提示：
如果不允許，瀏覽器將會拋異常：
|
1 |
QuotaExceededError (DOM Exception 22): The quota has been exceeded. |
這樣就建立了一個資料庫叫order_test，返回了一個db物件，使用這個db物件建立一張表
（3）建立表
如下程式碼所示：
|
1 2 3 4 5 6 7 |
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）插入資料
準備好原始資料和對資料做一些處理，如下所示：
|
1 2 3 4 5 6 7 |
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); |
然後執行插入：
|
1 2 3 4 |
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是否存在，因為資料需要動態更新，例如每兩個小時更新一次，如果有新資料需要去查詢格式化的地址以及經緯度。而每次請求都是拉取全部資料，因此需要找出哪些是新資料。可以執行：
|
1 |
select order_id from order_data where order_id = ${order.orderId} |
IndexedDB是本地儲存的第三種方式，它是非關係型資料庫。它的建立資料庫、建表、插入資料等操作如下程式碼如下，這裡不進行拆分講解，具體API細節讀者可查MDN等相關文件。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
//建立和開啟一個資料庫 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 |
執行完之後就有了一張order_data的表，如下所示：
現在要查詢某個orderId的資料，可執行以下程式碼：
|
1 2 3 4 5 6 7 8 9 |
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) }; } |
結果如下圖所示：
怎麼查詢value欄位裡面的資料呢？如要查詢state為CA的訂單，那麼給state這個欄位新增一個索引就可以查詢 了，如下所示：
這裡就可以知道，為什麼要叫IndexedDB或者索引資料庫了，因為它主要是通過建立索引進行查詢的。
上面只返回了一個結果，但是一般需要獲取全部的結果，就得使用遊標cursor，如下程式碼所示：
列印結果如下：
IndexedDB還支援插入json格式不一樣的資料，如下程式碼：
|
1 2 3 4 5 6 7 8 9 10 11 |
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); }; }; |
結果如下圖所示：
（2）非關係型資料庫的橫向擴充套件
上面說關係型資料庫不利於橫向擴充套件，而在一般的非關係型資料庫裡面，每個資料儲存的型別都可以不一樣，即每個key對應的value的json欄位格式可以不一致，所以不存在新增欄位的問題，而相同型別的欄位可以建立索引，提高查詢效率。
NoSQL做不了複雜查詢，如上面的案例要按照日期/city歸類的話，需要自己開啟一個遊標迴圈做處理。所以我選擇用Web SQL主要是這個原因。
（3）相容性
WebSQL相容性如下caniuse所示：
主要是IE和火狐不支援，而IndexedDB的相容性會好很多：
8. 資料庫與Promise
資料庫的查詢，新增等都是非同步操作，有時候你可能需要先發個請求獲取資料，然後插入資料，重複N次之後，再查詢資料。例如我需要先一條條地向谷歌伺服器解析地址，再插入資料庫，然後再做查詢。在查詢資料之前需要保證資料已經都全部寫到資料庫裡面了，可以用Promise解決，在保證效率的同時達到目的。如下程式碼所示：
9. SQL隱碼攻擊
談SQL一般會離不開SQL隱碼攻擊的話題，什麼是SQL隱碼攻擊呢？
假設有個表單，支援使用者查詢自己在某個地方的訂單，如下圖所示：
所寫的SQL語句是這樣的：
select * from order_data where user_id = 514694887070560 and state = ‘${userData.state}’
userId根據使用者的登陸資訊可以知道，而state則使用使用者傳來的資料，那麼就變成了一道填(song)空(ming)題，如下圖所示：
正常的查詢如下圖所示：
現在進行指令碼注入，如我要查一下所有使用者的訂單情況，如下所示：
select * from order_data where user_id = 514694887070560 and state = ‘CA’ union select * from order_data where ”=’‘;
加粗的字就是我在空格里面填入的東西，它就會拼成一句合法的SQL語句——查詢order_data表的所有資料，結果如下：
由於資料庫是放在遠端伺服器，我怎麼知道你這張表叫做order_data呢？這就需要猜，根據一般的命名習慣，如果order_data不對，那麼對方服務將會返回出錯，那就再換一個，如order/orders等，不斷地猜，一般可以在較少次數內猜中。
我還猜測有張使用者表，存放著使用者的密碼，要查一下某個人的密碼，執行以下SQL語句：
select * from order_data where user_id = 514694887070560 and state = ‘CA’ union select order_id, order_data.user_id, price, address, user.password as city, zipcode, state, format_city, date, lat, lng from order_data join user on user.user_id = order_data.user_id and ”=”;
結果如下：
第二個city就是那個使用者的密碼，如果資料庫是明文儲存密碼，那就更便利了。
還可以再做一些增刪改的操作，這個就比查詢其它使用者資訊更危險了。那怎麼防止SQL隱碼攻擊呢？
如果欄位型別是數字，則沒有注入的風險，而如果欄位是字串則存在。需要把字串裡面的引號進行轉義把它變成查詢的內容，在引號裡面是使用連在一起的兩個引號表示一個引號。
更常見的是底層框架先把sql語句編譯好，傳進來的字串只能做為內容查詢，這種通常是最安全的，就是有時候不太靈活，特別是查詢條件比較多樣時，如果一個條件就寫一句sql還是挺煩的，並且條件還可以組合。
10. 分散式資料庫
如果網站日訪問量太大，一個資料庫服務很可能會扛不住，需要搞幾臺相同的資料庫伺服器分擔壓力，但是要保證這幾個資料庫資料一致性。這個有很多解決方案，最簡單的如mysql的replication：
假設線上有3個資料庫，使用者的一個操作寫到了其中的一個資料庫裡面，這個庫就叫主庫master，其它兩個庫叫從庫slave，主庫會把新資料遠端複製到另外兩個從庫。
11. 資料庫備份
談到資料庫離不開另外一個話題——備份，備份很重要，假設你的網站某一天被攻擊了，一夜之間幾十萬個使用者的資料沒了，要是找不回來，或者寫了十年的部落格全沒了，就真的得一夜白頭了。例如筆者會不對期地對自己的部落格網站做備份：
用wordpress和db的備份檔案，可以在一個小時之內從0恢復整個部落格網站。
備份mysql資料庫可以執行mysqldump的命令，以root使用者的身份：
mysqldump order > order.bak.mysql –u root –p
就可以把order這個資料庫備份起來，恢復的時候只需執行：
mysql -u root -p
就可以把order這個資料庫導進來。
綜合以上，本文談到了本地儲存的三種方式：
- localStorage/sessionStorage
- Web SQL
- IndexedDB
並比較了它們的特點。還談了下DB結合Promise做一些操作和SQL隱碼攻擊等。
最主要是分析了關係型資料庫和非關係型資料庫的特點，關係型資料庫是一名老將，而非關係型隨著大資料的產生應運而生，但它又不侷限於在大資料上使用。html5也增加了這兩種型別的資料庫，為做Web Application做好準備。雖然Web SQL很早前被deprecated，但是隻要你不用支援IE和Firefox還是可以用的，它的好處是查詢比較方便，而IndexedDB儲存比較靈活，查詢不方便。說不定在不久的將來會有一種全新的web關係型資料庫出現。現在很多網站都使用IndexedDB儲存它們的資料。
所以可以兩者嘗試學習和使用一下，一方面為做那種資料驅動型別的網頁提供便利，另一方面可以對資料庫的概念有所瞭解，知道後端是如何建表如何查詢資料返回給你的。