SQL 只是 CRUD？

作者： RogueJin 原文来源：https://tidb.net/blog/af270392

【是否原创】是

【首发渠道】TiDB 社区

【首发渠道链接】

【目录】

【正文】

这是一篇关于程序员对 SQL 认知的文章，如果你只是好奇 SQL 和 CRUD 是啥，可以直接滑到文末，顺便点个赞 :slight_smile:

不知道你是不是注意到这么一个现象，在逛各大论坛的时候，大家问的最多的问题是怎么读取数据，怎么存储数据，也就是大家常说的 CRUD 操作，这里打个比方，假如一个数据库比作一个冰箱，把食物放进冰箱，这种操作可以认为是 CRUD 操作中的插入操作；打开冰箱看看有什么食物是查询操作；原材料加工成菜肴再放回冰箱是更新操作；菜肴吃完不再把空盘子放回冰箱是删除操作。程序员如果跟数据库打交道，是不是大部分工作都是在对数据进行拿进拿出的操作？但是 SQL 的初衷真的只是 CRUD 操作嘛？

1986 年，一个叫 ANSI 的标准化组织发表了一些列针对 SQL 的 ISO 标准文档，2019 年最新一版 SQL:2019 已经对 SQL 内容增加到了 15 个部分，这么多内容都讲的啥？对我的工作有啥帮助？是不是每一部分都要看懂才能用数据库？

这里先放一下这些问题，我们先讲一下为啥会突发奇想去看 SQL 标准，重新理解 SQL 内部设计。

故事要从 2017 年讲起，当初由于内部项目对分布式数据的需求，我们开始尝试改造开源分布式数据库 TiDB，这就给了我们一个机会，从数据库内部理解数据存储和数据获取的运行逻辑。这个改造不光只是修改几行代码，我们需要从 TiDB 支持的 MySQL 协议和查询关键字，改变成 PostgreSQL 协议和查询关键字，具体的经过可以查看下面这篇文章。

TiDB for PostgreSQL—牛刀小试 - 知乎 (zhihu.com)

修改 SQL 语句关键字时，就要涉及修改 SQL Parser 模块，这些模块处在接受 SQL 语句之后的流程中，也就是下图绿色方块部分。

这部分代码其实是一个独立的代码库

pingcap/parser: A MySQL Compatible SQL Parser (github.com)

我们在这个库的基础上，重新加入 PostgreSQL 语句和关键词的解析，代码地址和说明可以参考下面的链接。

DigitalChinaOpenSource/DCParser (github.com)

TiDB Parser 模块的简单解读与改造方法 - 技术

TiDB 源码学习笔记：Parser 模块 - 知乎 (zhihu.com)

但这里又出现了一个新的问题，pingcap 下的 parser 是 MySQL 兼容的解析器，如果基于这个项目修改的解析器一定多多少少包含 MySQL 语法的影子，那我们是不是能完全摆脱 MySQL，做出一个纯粹的 pg 语法 parser 呢？

我们打开 parser.y 的 yacc 文件，可以在文件开头注释中看到这样的说明。

另外结合，pingcap 源码阅读系列文章，TiDB 源码阅读系列文章（五）TiDB SQL Parser 的实现 | PingCAP

最初的 parser.y 是从 BNF 文件转化生成的。可以这么理解，由于 SQL 语句解析过于庞大，如果全部手工编写会非常耗时，所以通过工具，先把标准的 SQL BNF 文件转换成 Yacc 文件，再修改这个文件实现 MySQL 适用的解析器，等于找了个事半功倍的工具。

BNF 是 Backus-Naur Form 的首字母缩写，Backus 代表 John Backus，Naur 代表 Peter Naur，这两个人于上世纪 50 年代分别使用数学符号的方式来描述特定语言，最后形成统一的规范，我们就称这种规范为 BNF。这里举一个简单的例子：

<table_expression> ::= <from_clause>[ <where_clause> ][ <group_by_clause> ][ <having_clause> ]

这个表达式的意思是，在 SQL 语句中的 table 表达式部分， 必须有 from 语句，可以有 where 语句，也可以有 group by 语句，还有可以有 having 语句，所以我们可以得知，这里的中括号代表不是强制需要的语句部分，而这些对应的语句部分需要去看相应的语句定义部分。

所以我们知道找到对应的 SQL BNF 文件，就能生成 yacc 文件，就是 tidb parser 最初的样子，也就是 SQL 标准所定义的样子。BNF 可以从 Ron 的代码库中找到

ronsavage/SQL: BNF Grammars for SQL-92, SQL-99 and SQL-2003 (github.com)

至此，我们其实已经可以从 SQL 标准 BNF 开始搭建 PG 语法兼容的 Parser 了，不过为了搞清楚每一个 SQL 标准更新到底有哪些不同，应不应该从最新的标准开始我们的工作，我们需要进一步查找 SQL 标准相关资料。其实也就回到了文章最初的问题，这些标准都讲了啥？对我的工作而言有没有必要了解？

我们从维基百科上找到了这么一张表格，表格内标注了每一个 SQL 标准发布年份和主要更新内容。

SQL-92 是最重要的版本，这个版本里定义了大家最常用的数据功能，包括：

• DATE，TIME，NVARCHAR…等类型

• 对字符串，时间进行运算

• 定义了 UNION，JOIN 等操作

• 创建临时表

• 事务隔离等级

同时开始出现”数据关系“这个概念，这些功能代表着，从 SQL-92 开始，数据库不再只是简单的数据存储和索引系统，而是可以通过大家熟悉的第一范式、第二范式、第三范式减少数据冗余和建立数据关系，能够对数据库内部数据按照业务需求进行数据转换呈现。

SQL-1999 之后，SQL 开始添加更多超出关系范畴的功能，例如正则表达式，循环查询，数组类型，自定义类型等等，我们举一些简单的例子。

• 数组类型

• 嵌套表

• 复合类型

• 递归查询

数组类型：

我们在系统设计时经常会碰到一些数组类型的对象属性，这些属性如果按照关系型设计，通常会设计成下图，属性表没有主键，查询还需要 join 多张表，是不是觉得很累赘。

如果抛弃外键关系，利用数组类型，可以变成一种非常简单的设计：

From https://www.postgresql.org/docs/9.1/arrays.html

嵌套表：

如果希望对这些数组属性进行表的聚合与筛选操作，就可以利用嵌套表实现，表结构如下：

ALTER TABLE customers_demoADD (cust_address_ntab cust_address_tab_typ,cust_address2_ntab cust_address_tab_typ)NESTED TABLE cust_address_ntab STORE AS cust_address_ntab_storeNESTED TABLE cust_address2_ntab STORE AS cust_address2_ntab_store;

可以通过 MULTISET INTERSECT DISTINCT 对两张嵌套表数据进行筛选，选出相同的数据：

SELECT customer_id, cust_address_ntabMULTISET INTERSECT DISTINCT cust_address2_ntab multiset_intersectFROM customers_demoORDER BY customer_id;
CUSTOMER_ID MULTISET_INTERSECT(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID----------- -----------------------------------------------------------------------------------101 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))102 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))103 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))104 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))105 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('4019 W 3Rd St', '47404', 'Bloomington', 'IN', 'US'))

From https://docs.oracle.com/cd/B28359_01/server.111/b28286/operators006.htm#SQLRF51164

复合类型：

如果我们希望数据关系更紧密，我们可以考虑利用复合类型来构建数据间的关系：

CREATE TYPE inventory_item AS (name text,supplier_id integer,price numeric);
CREATE TABLE on_hand (item inventory_item,count integer);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

From https://www.postgresql.org/docs/current/rowtypes.html

递归查询：

我们在处理有自我关系的数据表时，经常需要处理层级问题，例如下面这个情况，业务希望查找第四层人员中有没有一个叫 Anna 的人，

CRUD boy 通常会有两种做法，

方法一，一次性获取所有数据，在内存中处理，减少数据库链接成本：

all_data = get_all_data_from_database()level_data = get_root_node(all_data)for depth = 2 to 4level_data = get_next_level_data(all_data, level_data)
If has_person(level_data, "Anna")    // found

方法二，一次获取一层数据，减少一次性加载的数据量：

level_data = get_data_from_database(null)  // select * from graph where parent_id = nullfor depth = 2 to 4parent_ids = get_ids(level_data)level_data = get_data_from_database(parent_ids) // select * from graph where parent_id in parent_ids
If has_person(level_data, "Anna")    // found

这两种方式都是传统 CRUD 处理的办法，都有优缺点，数据量少可以用第一种方法，数据量多就只能靠每次读数据库处理了。

但是如果利用 SQL-1999 提供的递归查询功能，可以非常简单的处理这类问题，代码示例如下，

WITH RECURSIVE search_graph(id, parent_id, data, depth) AS (SELECT g.id, g.parent_id, g.data, 1FROM graph gUNION ALLSELECT g.id, g.parent_id, g.data, sg.depth + 1FROM graph g, search_graph sgWHERE g.id = sg.parent_id)SELECT * FROM search_graph where depth = 4 and data = 'anna';

From https://www.postgresql.org/docs/9.1/queries-with.html

之后 SQL 版本又增加了 XML，JSON，Window 函数，Fetch 语句的支持。可以说 SQL 数据之间的关系早已超越了数据库设计范式的定义，甚至开始针对 XML/JSON 等非强关系数据结构进行处理操作，所以对数据表设计要求越来越高，如果数据表设计合理可以减少冗余，提高数据查询效率；反之会带来数据灾难，尤其是在复杂业务数据场景下。

文章结束前打个广告，tidb for pg 项目正在招募志同道合的小伙伴一起推进 tidb pg 生态，有兴趣的小伙伴可以联系我们：dc.opensource@yungoal.com

项目地址：

• https://github.com/DigitalChinaOpenSource/TiDB-for-PostgreSQL
  

• https://github.com/DigitalChinaOpenSource/DCParser/
  

名词解释：

-SQL：Structured Query Language 的缩写，简单来说，通过接近自然语言的方式操作数据的编程语言。比如看看保险箱 (safe) 里有多少 (sum) 钱 (money)，就能用这样 SQL 语句，SELECT SUM(money) FROM safe;

-CRUD：Create, Read, Update, Delete 四种操作的首字母缩写，也是程序员对数据库应用最多的操作