前端与 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);
    };
};