前端與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){n tx.executeSql(n"create table if not exists order_data(order_id primary key, format_city, lat, lng, price, create_time)", [], null, nfunction(tx, err){n throw(`execute sql failed: ${err.code} ${err.message}`);n });n});n

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

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

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

現在往這張表插入數據。

（4）插入數據

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

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

然後執行插入：

tx.executeSql(`ninsert into order_data nvalues(${order.orderId}, ${order.format_city}, n ${order.lat}, ${order.lng}, ${order.price}, ${date})`);n

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

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

（5）主鍵唯一約束

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

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

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

作用是創建一張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 daten

結果如下圖所示：

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, 7n

得到：

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

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

如果返回空的結果集，說明這個orderId不存在。

上面是在控制台執行，在代碼裡面怎麼獲取結果呢，如下圖所示：

某些欄位可能會被重複查詢，如order_id，format_city，如果對這些欄位做一個索引，那麼可以提高查詢的效率。

（8）建立索引

由於order_id是主鍵，自動會有索引，其它欄位需要手動創建一個索引，如對format_city添加一個索引可執行：

create index if not exists index_format_city on order_data(format_city)n

為什麼創建索引可以提高查詢效率呢？因為如果沒建索引要找到某個欄位等於某個值的數據，需要遍歷所有的數據條項，查找複雜度為O(N)，而建立索引一般是使用二叉查找樹或者它的變種，查找複雜度變成O(logN)，mysql是使用的B+樹。有興趣的可繼續查找資料。

另外字元串可使用哈希變成數字，字元串索引要比數字低效很多。

使用索引的代價是增加存儲空間，降低插入修改的效率。所以索引不能建太多，如果查詢的次數要明顯高於修改那麼建立索引是好的，相反如果某個欄位需要被頻繁修改，那可能不太適合建立索引。

5. 關係型資料庫的優缺點

（1）優點

?SQL支持非常複雜的查詢，可以聯表查詢、使用正則表達式查詢、嵌套查詢，還可以寫一個獨立的SQL腳本。

上面的案例，?如果不使用SQL，那兩個查詢自己寫代碼篩選數據也可以實現，但是會比較麻煩，特別是數據量比較大的時候，如果演算法寫得不好，就容易有性能問題。而使用DB數據的查詢性能就交給DB。它還是非同步的，不會有堵塞頁面的情況。

（2）缺點

一般來說，存在以下缺點：?

• 不方便橫向擴展，例如給資料庫表添加一個欄位，如果數據量達到億級，那麼這個操作的複雜性將會是非常可觀的。?
• ?海量數據用SQL聯表查詢，性能將會非常差。
• ?關係型資料庫為了保持事務的一致性特點，難以應對高並發

（3）Web SQL被deprecated

在w3c的文檔上，可以看到：

?This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path.

大意是說WebSQL現有的實現是基於現成的第三方SQLite，但是我們需要獨立的實現。火狐也不打算支持。也就是說主要原因是web sql太過於依賴SQLite，或許W3C可能會在以後重新制訂一套標準。

雖然已經不建議使用了，但是上面還是花了很多篇幅介紹web sql，主要是因為SQL是通用的，我的主要目的並不是要向讀者介紹web sql的API，怎麼使用web sql，而是給讀者介紹一些SQL的核心概念，如怎麼建表，怎麼插入數據，畢竟SQL是通用的，就算再過個幾十年它也很難會過時。

接下來再介紹第二種資料庫非關係型資料庫

6. 非關係型資料庫

非關係型資料庫根據它的存儲特點，常用的有：

（1）key-value型，如Redis/IndexedDB，value可以為任意數據類型，如下圖所示：

（2）json/document型，?如MongoDB，value按照一定的格式，可對value的欄位做索引，IndexedDB也支持，如下圖所示：

非關係型資料庫也叫NoSQL資料庫。

?NoSQL是Not Only SQL的簡寫，意思為不僅僅是SQL，但其實它和SQL沒什麼關係，只是為了不讓人覺得它太異類。它的特點是存儲比較靈活，但是查找沒有像關係型SQL一樣好用。適用於數據量很大，只需要單表key查詢，一致性不用很高的場景。

7. IndexedDB

（1）IndexedDB的一些概念

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

//創建和打開一個資料庫nvar request = window.indexedDB.open("orders", 7);nvar db = null;nrequest.onsuccess = function(event){n db = event.target.result;n //如果order_data表已經存在，則直接插入數據n if(db.objectStoreNames.contains("order_data")){n var orderStore = db.transaction("order_data", "readwrite").objectStore("order_data");n //insertOrders(orderStore);n }nn};nnrequest.onupgradeneeded = function(event){n db = event.target.result;n //如果order_data表不存在則創建，並插入數據n if(!db.objectStoreNames.contains("order_data")){n var orderStore = db.createObjectStore("order_data", {keyPath: "orderId"});n insertOrders(orderStore);n }n};nnfunction insertOrders(orderStore){n var orders = orderData.data;n for(var i = 0; i < orders.length; i++){n orderStore.add(orders[i]); //add是一個非同步的操作，返回一個IDBRequest，有onsucessn }n}n

執行完之後就有了一張order_data的表，如下所示：

?現在要查詢某個orderId的數據，可執行以下代碼：

function query(orderId){n db.transaction("order_data", "readonly") //IDBTransactionn .objectStore("order_data") //IDBObjectStoren .get(orderId) //IDBRequestn .onsuccess = function(event){n var order = event.target.result;n console.log(order)n };n}n

結果如下圖所示：

怎麼查詢value欄位裡面的數據呢？如要查詢state為CA的訂單，那麼給state這個欄位添加一個索引就可以查詢 了，如下所示：

這裡就可以知道，為什麼要叫IndexedDB或者索引資料庫了，因為它主要是通過創建索引進行查詢的。

上面只返回了一個結果，但是一般需要獲取全部的結果，就得使用游標cursor，如下代碼所示：

列印結果如下：

IndexedDB還支持插入json格式不一樣的數據，如下代碼：

var specilaData = {n orderId: hello, world,n text: "goodbye, world"n};nnvar orderStore = db.transaction("order_data", "readwrite").objectStore("order_data");norderStore.add(specilaData).onsuccess = function(event){n orderStore.get(hello, world).onsuccess = function(event){n console.log(event.target.result);n };n};n

結果如下圖所示：

（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}n

?userId根據用戶的登陸信息可以知道，而state則使用用戶傳來的數據，那麼就變成了一道填(song)空(ming)題，如下圖所示：

正常的查詢如下圖所示：

現在進行腳本注入，如我要查一下所有用戶的訂單情況，如下所示：

select * from order_data where user_id = 514694887070560 and state = CA union select * from order_data where =;n

加粗的字就是我在空格裡面填入的東西，它就會拼成一句合法的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 =;n

結果如下：

第二個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.bak.mysql

就可以把order這個資料庫導進來。

綜合以上，本文談到了本地存儲的三種方式：

1. localStorage/sessionStorage
2. Web SQL
3. IndexedDB

並比較了它們的特點。還談了下DB結合Promise做一些操作和SQL注入等。

最主要是分析了關係型資料庫和非關係型資料庫的特點，關係型資料庫是一名老將，而非關係型隨著大數據的產生應運而生，但它又不局限於在大數據上使用。html5也增加了這兩種類型的資料庫，為做Web Application做好準備。雖然Web SQL很早前被deprecated，但是只要你不用支持IE和Firefox還是可以用的，它的好處是查詢比較方便，而IndexedDB存儲比較靈活，查詢不方便。說不定在不久的將來會有一種全新的web關係型資料庫出現。現在很多網站都使用IndexedDB存儲它們的數據。

所以可以兩者嘗試學習和使用一下，一方面為做那種數據驅動類型的網頁提供便利，另一方面可以對資料庫的概念有所了解，知道後端是如何建表如何查詢數據返回給你的。