❌

普通视图

发现新文章，点击刷新页面。
昨天以前首页

手贱惹的祸

✇我的天
作者 xrspook
2024年9月24日 08:45

当年今日

VBA可以对普通的Excel文件用SQL查询，虽然我已经用的是超级表，理论上单元格的数据格式是一致的，但实际上这不是一个标准化的数据库，我没有办法在一开始的时候就对每个数据进行声明，所以在数据处理过程中就会出现这样那样说不准为什么的事情。

星期一的下午我跟往常一样做了些普通操作，最后当我用VBA生成一个汇总数据的时候发现弹出一个“标准表达式中数据类型不匹配”的警告。看到这个东西，我的第一反应是肯定是获取的数据里面有一些不规范的东西，比如说某一列通常是数字的，但是却出现了文字，但实际上我翻查了全部源数据都没有发现这个玩意。没办法之下，我只能做一个脚手架，一个一个的去排除，最后发现问题出在where里。where里有一个“性质某某某”的限定条件，问题就出现在那个地方，只要把这一句删掉，VBA是可以正常运行的，至于结果对不对不知道，反正能运行，然后我又回到了这一条的上一条结果，发现where里面的那个条件是没有问题的，所以这到底是什么问题呢？

然后我又想起，在进行普通操作的时候，我好像在某列数值的单元格里发现了一个汉字，说不准为什么那里会有一个汉字，但一个汉字足以影响那个单元格的类型。为什么我深有这种体会，因为如果某一列全部都是数字那么 VBA+ADO+SQL通常都会默认那是一个数值，但只要那一列里面有一个文字，所有东西都会被识别为文本。对数字进行聚合是理所当然没有问题的，但如果对文本进行这种操作，我不敢想象会出现什么东西。当然了，把文本作为分组条件，一点问题都不会有。

我感觉自己的VBA程序是健壮的，因为我已经用了他一年多了，之前从来没有出现过这种问题。有段时间经常会出现这样那样说不准什么问题的问题，但是过了一段时间之后，那些问题又自动消失了，所以我更相信那是有段时间windows更新导致某些框架不稳定导致了那段时间的意外。除非我对源数据做了更改，又或者是出现了某些意外，否则不会报这样的错误。

接着我又记起周一下午某个基本操作的时候，我好像发现在那个超级表的下面有一个“她”字。那个东西在不连续的单元格里，不是超级表的一部分，但关键是如果我用SQL获取数据，那肯定也会被纳入其中。看到那个莫名其妙的“她”字以后我已经把那删掉了，所以我看到的那个原始数据表格没有问题，但只是看上去没有问题。

折腾了好长时间未果，之后，我不得不重新翻出前一天晚上的源数据。然后手动把周一更新过的东西全部都贴回去。再去用VBA汇总，一切正常。在贴回去之前我首先用VBA测试了一下汇总没有问题，然后我才开始贴，贴了之后也没有问题。如果这个东西没有问题，也就是周一之前这个表格是没有问题的，但不知道周一进行了什么操作，导致了问题的出现。倒退以后重新贴数据没有问题，的确这个汇总也算保住了，但是我却一直放心不下，找不出问题的原因下一次依然会手忙脚乱。

吃过晚饭后我重新翻出有有问题的那个源数据。我的猜测是，因为数值列里面出现了一个文本，虽然我已经把文本删掉了，但是那个文本已经影响了那列单元格的类型，最终导致VBA弹出错误提示，虽然那个错误提示并不是出现在VBA调试发现的那一列。我的做法是在不修改VBA的前提下，把有问题源数据超级表下面的所有行全部删除，然后保存，再次运行vba，源数据通过了，可以正常运行。通过这样的操作，就能排除错误，非常有可能意味着我上面的猜测是对的。然后，我故意在数据列超级表外的单元格写一个字，然后保存，VBA汇总挂了。我把那个字删除，保存，VBA依然挂。但是当我把写过字的那一行删除，VBA汇总好了。这再一次验证了我的猜想。

这种事情该如何避免呢？首先不要手误，不要乱填。手误乱填这种事过去那么多年都从来没有发生过，为什么就发生了呢？到底是我的问题，还是另外一个人的问题？如果要避免这个事情，最好我在SQL引用源数据的时候就直接就限定为超级表范围，而不要把超级表所在的所有列都含进去。无论是哪一点，都是可以实现的。限定超级表的范围不太难，但关键是人手贱的这个行为，这一次出现在某个不知道为什么的单元格，下一次如果覆盖掉超级表的一个老数据呢？要避免这种人的失误非常难，但是人为什么会犯这种弱智到极点的失误呢？而且是犯了还毫不知情。

但总算这一次，我找出了可能的原因。

升级VBA抓取方案

✇我的天
作者 xrspook
2024年9月14日 19:26

当年今日

无论是SQL方案还是数据透视表方案，我都是用4个类似的脚本，一款做出来了以后，调整部分的内容，生成其它三款，所以一开始的时候抓取数据我有4个宏，其实里面的内容大多相同。汇总数据，我也有4个宏，SQL有4个，数据透视表也有4个。因为SQL跟数据透视表作用是相同的，所以它们分别搭配4个抓取数据的宏各自组成两个文件。就实现功能来说，这两个文件从形成的那一刻起，已经可以起作用，但是我能不能更进一步呢？

数据抓取用的是最基本的VBA，就只是把数据区域选了一下，然后去掉表头，最后搭配我想要的表头输出。我没有在那个地方就进行筛选，因为之前已经说过，VBA自带的AutoFilter功能不太好用。一开始我没想过要用数据透视表，如果到了SQL，一句where可以把正向的反向的或的且的，想怎么加就怎么加，只是一句话的事情而已。既然在一开始数据抓取单元格层面那么难做筛选，那么我到SQL里做筛选就可以了。后来，因为我又做了数据透视表方案，数据透视表方案可以对字段进行筛选，但关键是如果我抓取了那个数据里面不含有我要排除的内容，又或者我抓取的数据全部字段都要被排除掉，无论是哪一款，都会出错，所以如果用同样的抓取方式，到了数据透视表的那个宏里面，我就需要进行复杂的循环和判断。嵌套一层又一层的公式，再加一层又一层的判断。虽然也能实现我想要的东西，但那样做很麻烦。在做出数据透视表方案的那天晚上，我就在想，在一开始的抓取的宏里面，我能不能直接把筛选这个步骤给做了呢？

要在数组里面进行数据筛选，想想都知道肯定可以实现，但是我想到需要嵌套那些数字就觉得很烦，所以我就翻出了多年以前我用来合并某些数据表的宏。在那里我发现自己用的是把全部数据都粘贴到一起，然后做一个行的删除。当时做的行删除很简单，只要匹配一个字段就行了，我现在的行删除，需要匹配的字段可能会有很多，所以我就把那些需要删除的字段都先放在一个数组里面，然后再利用之前那一天从网上抄回来的那招Application.match方案。在一句if里面就能实现查找某个元素在不在某个数组里,如果在的话就把行删掉。之前写的那个宏的确就这么简单，因为需要删掉的那个部分隔好多行才会出现一次。现在我需要处理的那些数据，说不准我需要删掉的那些行是隔一些才出现，还是下一个就又得删掉，所以在删掉之后，我又赶紧做了一个减法操作，让程序重新测试那一行到底需不需要删除。

在一开始数据抓取阶段，我就把数据范围确定了，把需要例外的数据全部删除，所以后面的SQL跟数据透视表我就可以轻松地直接进行操作，尤其是数据透视表，操作变得简单明了。

因为一开始我不是大神，我只能摸着石头过河，逐渐的磨练自己。

VBA里搞数据透视表

✇我的天
作者 xrspook
2024年9月13日 08:17

当年今日

因为我知道我要实现的那个功能，SQL可以做，数据透视表也可以做。就写代码的熟练程度来说，SQL我肯定更熟悉，VBA的数据透视表有很多参数，我搞不懂到底是什么，反正要实现那个功能，通常是录制一个宏，然后看着办，有需要的数据保留下来，不需要的数据直接删掉。录制的宏通常都很啰嗦，里面有非常多没有必要存在的东西。在不了解数据透视表在VBA里的参数的前提下，先进行一个录制显然是比较靠谱的步骤。但有些东西靠录制是录制不出来的，比如某些字段我需要进行筛选，我只知道有些东西是不能出现的，但我没办法确定可以出现的是什么，所以那一堆不能出现的东西都是反参数。在录制宏的时候，你只能看到什么就反选什么，但关键是这个数据源跟那个数据源的那些参数是不一样的。不一样我只能设定一堆反参数，只要它们是其中一个，就不能显示，但实际上这样的反参数让VBA的数据透视默认不出现你就得兜一个大圈，套上好几层公式实现。最终，在我调试的时候发现的确那些嵌套的公式能把那些反参数都排除在外，但如果数据源里所有数据都是反参数的一部分，那么就会报错，于是我又得在外面加一层捕捉错误的判断。真的是非常折腾。为什么之前我考虑的是SQL而不是数据透视表，反参数是其中一个点，另外一个点是排序。在SQL里，使用自定义序列排序是非常简单的事情，但是数据透视表的字段该如何排序呢？兜了一大圈我发现也就只能真的在Excel里面增加自定义序列，但如果我想用即弃呢，于是还得出了一招VBA先增加自定义序列，排序完以后再把自定义序列删除掉。这个操作在ExcelHome的教程里有，他们教的是在VBA里面，对单元格或者数组排序，不是针对数据透视表的，但实际上原理一样。

这个增自定义序列和减自定义序列到底是怎么确认呢？原来Excel还会对自定义序列给一个序号，所以在增自定义序列的时候，你就得把这个序号记下来，在减的时候把那个序号写上去。我不知道为什么其他人说数据透视表的自定义序列好像怎么排都不是自己想要的效果，但就我个人的经验来说，只要你在Excel里增加了自定义序列，当你刷新数据，默认对某个已经自定义过的字段进行升序，那就是你定义的那个顺序，不需要在设置里面搞一大通。但实际上我也搞不懂，手动设置里很麻烦的东西到底是什么。为什么数据透视表里面的排序就不能像普通表格排序那样那么的直观。我要以什么字段排序、以什么标准排序是系统默认的还是自定义序列。当然，数据透视表里还得考虑一个问题，就是有可能是套叠了多个汇总条件的，如果你自定义的是最后一层条件，首先限制你的是前面的那些所有条件，所以你想最后的那个自定义序列完全按照你的想法排列，你只能把它放到最前面。数据透视表跟SQL比起来，我感觉运行速度会慢一点，可能因为我里面判断设置的东西比较多，所以需要闪那么一下才能结束，但是SQL虽然我已经有意识地关注结尾这个问题，但测试频繁以后，SQL是会出现一些莫名其妙的事故，当你把所有东西关掉再打开就正常了。相比之下，数据透视表不会这么神经。

用不同的方法实现同样的事情，得出一样的结果，这种感觉很好。在探索这个的过程中，会让我体会到二者的优劣，以后选择的时候，我就可以更有底气地数出1234。

不完整的错

✇我的天
作者 xrspook
2024年9月12日 08:09

当年今日

上一篇说到了数据汇总的问题。这个周一我就是按照上周五设定的那个步骤去做。在做的过程中，几乎没有发现什么问题，但是当我做完所有，一个个表格验证的时候却发现不知道为什么有些表格 SQL抓取的数据不完整，VBA从原始表格筛选、抓取的数据没有问题，但关键是SQL从本地的文件里提取到的那些数据不完成。第一次发现这个问题的时候，我看到的是为什么汇总数不一致。当我把SQL回退到第1步的时候发现第1步获取的数据就已经不完整。明明有50行数据，实际上只能提取到42行，重复多次依然是那个效果，但是偶然当我把文件关掉重开以后又好了。所以这个有时发生，有时不发生，到底是什么情况呢？当我打开VBA文件，一个一个测试的时候，发现前几个还好，后面就会出状况，可能是数据不完整，也可能是弹出一些莫名其妙的错误，但只要你把所有Excel都关掉，再重新打开又没有问题了，但是在测试几个以后，又会出现这样这样那样的状况。用VBA+ADO+SQL整理输出数据我已经实施过很多遍，之前从来没有遇到过这种神奇的状况。最后当我打开VBA脚本，无意之间拉到最后，居然发现cnn没有close，也没有初始化。cnn是个非常牛逼的东西，但是那个玩意也要耗费巨大的资源，在我出现数据状况的时候，我没有观察过我电脑的性能到底如何了，会不会CPU或者内存甚至二者都有点状况了。因为一次又一次的验证数据就意味着我得一次又一次调用cnn，光是打开又不关闭，最后就会出现奇奇怪怪的事情。当我把所有脚本都加上了结尾以后。从头到尾10个表以上的数据，一次性搞完，期间不会出现状况，所以多么神经质的行为才会导致了这种弱智事情呢？以前我倒真的从未试过这样。有过这样的经历以后就让我明白到cnn打开和关闭都必须是一个闭环，在一个宏里就得实现到位。如果某个宏被卡住了，半路停在那里，估计那个cnn是不正常的，当我又再次启动其它，只会让错误不断积累，最终导致崩溃，又或者是得不到我想要的东西。

写程序可以很快，但是调试却非常耗时间。这大概是所有码农都必须面对的事情，但实际上更多的人只顾写，只顾实现，而不考虑全盘，不尽可能地用全面数据测试，最终的结果就是使用的时候出现各种各样的未知情况。我不知道其他人到底是如何调试的，反正我真觉得调试的过程比写脚本更费神，因为要考虑所有的情况，哪怕某些条件可能非常极端，几乎不会碰到，但即便那样，一个健壮的程序应该依然能捕捉到那个错误，然后给出对应的反馈。比如我抓取不到数据了，我就应该弹框告诉人家我抓不到，因为有些操作的抓取数据以后才能进行，所以既然能判断抓不到数据，后面的也就不用继续了。

调试程序是一个很磨人的过程，这个过程重复多了，人自然而然就会向完美靠拢，即便我们一定不能成为完美的那个。

VBA里奇怪的筛选与粘贴

✇我的天
作者 xrspook
2024年9月11日 09:05

当年今日

上周五我尝试纯粹用VBA的方式对某些数据先进行筛选，然后保留在粘贴板，又或者是把那些东西输出。前提是对某些数据进行筛选复制的时候，我首先必须得有个条件。理想很完美，现实很骨感。

VBA本身就自带一个叫做autofilter的函数，那个东西可以对选定的单元格区域进行筛选，你可以正向选择，正向的时候可以多选，可以把需要选择的内容组成一个数组，实现多选。你也可以进行反选，但反选的时候你却不能把反选的条件建立成一个数组进行同样的操作。这些都只是针对一次筛选而言的，如果同一片区域多次叠加筛选，第一次你筛选的是第1列，第二次筛选的是第2列，我感觉如果第三次和第四次你反选的是第2列估计也行，但关键是我需要选择的那个数据是一个作死的不规范有两行甚至三行的标题栏，但是autofilter这个函数默认输出的东西就含有标题栏。本来我的数据范围是不规范的，如果我一开始就把前面三个标题栏给去掉。那么在我进行autofilter的时候，就会从纯粹的数据开始，显然那就不是我想要的东西了。因为哪怕最终筛选的数据是空，也会默认带入那个标题栏。所以你就搞不懂为什么步骤都对，但结果就不对。

autofilter之后要进行一个特殊粘贴，那个东西是只对可见的数据复制。就普通人的思维而言，我复制了可见的数据，那么理论上我就可以算出它有多少行，如果是0我就直接不输出了，如果大于0，那么我就可以输出，但实际上特殊粘贴又不可以用一般的技术判定到底筛选的结果是不是0。虽然也能判断有没有，但需要绕一个圈去实现这种功能，所以VBA为什么有那么神奇的思路呢？筛选可以进行，但是你不能对反向的数据批量进行处理，你也不能把特殊筛选结果直接保存为某个东西。当你觉得你大概可以把可见的部分保存到一个新的区域，然后你就可以去头去尾之类，但实际上当你再去查看那个新区域的时候发现原来是保存了个寂寞而已，那种神经性质跟那个可见区域是一样的。我不知道以前的人到底是怎么忍耐VBA这些奇怪脾性的，因为在接触这个之前我就已经接触过pandas。pandas的数据分为两个，一个是标题，另外一个是数据。输出的时候你可以都输出，你也只可以只输出其中的一部分。在使用pandas的时候，我没有解决过一些我的实际问题，我都是按照书本上的例子进行操作的，所以到底在我使用的过程中会不会也遇到一些像VBA这么神经，明明觉得可以，但实际上又不可能直接实现的事情不知道。

周五的下午，折腾了一番以后，我的目标数据最终可以复制到粘贴板，但是在VBA那个脚本结束之前，我就也得把那粘贴出来，否则当那个结束以后，剪贴板的内容就没有了。为什么会这样呢？不是说当我把数据复制到了剪贴板，而我又把Excel关掉，软件会问我要不要清空剪贴板的数据吗？但显然现在Excel都没关闭，我只是关闭了那个脚本，但我剪贴板却什么也没有了。

周五在回家的路上，我又努力地想了想这个问题。最后决定，我没有必要这么折腾自己获取剪贴板数据。反正全体数据不多，我直接对那个整体数据进行加工处理就行。加工处理的方法是首先从我的目标数据那里通过VBA获取我要的部分，然后输出到指定的位置，接着通过ADO+SQL进行数据处理。准确的来说，就是一个分组聚合、添加汇总以及排序。当然其实最后这个ADO+SQL的操作我也可以换成数据透视表，但如果那样的话，最后的排序我要让那完美的按照我想要的方式，我就只能先在Excel添加一个自定义的序列。无论是SQL还是数据透视表，分类汇总和排序都一定会比在VBA里用数组方便非常多。

思考一个问题的时候，有时可能我们有点过于钻牛角尖了，退一步，可能会有一个更清晰的思路。

office系的SQL为啥不能文本拼接？！

✇我的天
作者 xrspook
2024年8月24日 08:48

当年今日

花了几乎一天的时间去研究什么把Access VBA里的自定义函数移植到Excel的VBA里面。大家都是VBA，大家都是 office家庭的，听上去好像没什么难度，但实际上前人已经碰壁阵亡，确定这是不可能的，我只是在做垂死的挣扎。经过这么多年office的发展，在数据格结构上，会不会只有那么一点改进呢？毕竟即便是在Excel里，如果我用的是VBA+ADO+SQL，实际上我是把数据以数据库形态进行SQL的加工。于是我就想，万一他们的数据格式是一样的，万一Excel已经进化了那么一点点呢。但现实告诉我，虽然都是VBA，虽然都是自定义函数，但是因为他们操作的是SQL，所以出来的效果完全不一样。

SQL的语法结构非常类似，无论你用的是什么类型的数据库，但在一些细节上，大家的处理是有区别的，我觉得Excel里面和Access里SQL最大区别在于因为我在Excel里面SQL用的是ADO的方式，所以这就意味着虽然我写的是SQL的语法，但实际上那是以字符串的名义存在的东西。在Excel VBA的数据格式里，我写的结构化语言全部都是字符串，但是在Access里，在SQL的查询界面里，那个东西不是字符串。我没有认真看某些单词有没有高亮，因为那是特殊字段又或者是保留字段。当我直接把Access VBA里的那个自定义模块挪到Excel VBA里，发现打开记录集的方式根本不一样，语法不一样。因为在Access里本来就是一个数据库，但在Excel VBA的ADO里是通过一些特殊的语句打开那个记录集的。

回到一开始，为什么我得这么折腾呢？因为一直以来我都发现，从来没有一个人能在Excel VBA+ADO+SQL的模式之下在分组聚合的时候把文本以某些字符去重连接成字符串。要实现这个功能，只能最后把结果输出，然后在VBA里通过字典的处理，再把那些合并好的东西与其它东西结合在一起形成一个新的数组，最后往单元格里面输出，而不能像其它SQL查询结果那样直接就在单元格里全部输出。先输出到字典，然后再用字典合数组合并的难易程度跟那个数据最终的查询结果复杂程度有关。在高端的数据库里，文本聚合连接有直接的函数可以做到，比如在MySQL里面直接group_concat就可以做到，在其它专业数据库里，那个函数的名字各有不同，但都能实现同一个效果，就是把字符聚合拼接。在Power Query里，他们没办法在窗口界面让你实现这个，但可以在高级编辑器里面通过text.combine的方式实现这种功能。在Power Pivot里，concatenatex也能实现这种文本的拼接。让人觉得非常无语的是，都到了Microsoft 365时代，Access这个东西依然是office大家族的一部分，但这种肯定有需求的东西居然没有一个官方函数实现，但你又可以通过在模块里用自定义函数的方式达成。Excel的VBA里不能秒生成这种东西，但在函数层面textjoin+unique+filter可以。为什么就不能在Excel VBA支持的SQL里面出现这个文本拼接的官方函数呢？如果他们真觉得没有必要的话，为什么Power Bi的软件就可以实现呢？我不知道Power Bi软件是一开始就能实现，还是后面慢慢进化出来实现的，反正我第1次看到Power Bi相关软件的时候，他们已经能实现了。

一整天的挣扎下来好像没什么进展，但我在这些问题上又仔细思考了一番。

我还是比较喜欢VBA+ADO+SQL

✇我的天
作者 xrspook
2024年8月23日 08:39

当年今日

我觉得编程会让人上瘾，尤其是当你实现了自己的目标以后，你就会有很多想法，比如之前我已经做过，而且已经实现了东西，能不能更进一步，再改进一些，让程序跑得更快一点？一开始的时候，只要能实现某个功能就可以了，无论用的是什么方法。在这个初级阶段，我是不会考虑别人到底行不行的，反正我行就可以，但是当自己包里面的工具越来越多以后。到底要选择什么工具，也会变成我一个纠结的地方，虽然有些工具已经很成熟了，肯定能实现我的效果，但是我还会想有没有更快捷的方式呢？

我已经不记得我是什么时候开始认识Power Query了，大概是在office2016的时候吧。那个时候我觉得那个东西可以做文本拼接太厉害了，而且厉害之处就像是跟数据透视表一样，当你的原数据发生了变动，刷新一下结果就出来了，但实际上那只是教程的效果，你完全按照教程这么干，的确能出结果。还记得几年前当我要算某些库存的时候，我用了一些很笨的方法。为了要实现区间日期里面的累计库存我用了一些非常耗费电脑的步骤。本来数据的量就不小，又外加要实现这样的效果，所以真的得算上很长时间才终于得到结果。那个很长时间意味着可能要等5分钟以上，在等待的过程中，我都怀疑自己的电脑是不是死机了。后来我也有算累计库存，但大概我已经不用一开始的那些方法了。我也有试过在VBA里计算累计库存。如果是在其它软件下的SQL里，计算累计数可以有很直接的方法，因为他们有现成的函数可以套用，但是在VBA里面的SQL，貌似至今为止，我尝试成功的也就只能硬着头皮做一个笛卡尔积。如果数据量比较大，那将是一个噩梦。噩梦归噩梦，数据还是能算出来的，如果我只是算一个月的库存，顶多就是几秒钟的事，通常情况下如果业务量不大，一秒就差不多了，但是如果要算一年的数据，那就要跑上几十秒。在VBA层面需要跑几十秒，而如果在PQ里我简直不敢想象得多久。

试过VBA，试过PQ，在PQ里我知道我要什么，它的透视和逆透视功能让我省掉很多麻烦，但这两个便捷功能也会默认带出一些意想不到的反效果，比如默认透视的是来源去向，万一筛选区间只有入没有出，但后续处理又默认有出入，这就会卡住。Excel 的SQL里，透视就是最后一步，所以如果中途要实现这种功能只能通过添加条件字段，手动添加字段的好处是不会有PQ透视法的那种透视不出来后面没法干。就可控程度来说，VBA更容易，能把多个操作在一步里秒杀实现，比如修改某个字段的数据和增加某个字段，我就可以把它们在一步里实现，外加同时搞个什么排序。这些步骤在PQ里面，如果不是高级玩家用嵌套的方式，也就只能一步一步慢慢来。我不知道，PQ里面嵌套一步到位跟一步一步慢慢来到底效率差了多少。估计这会有运行时间的差别，但到底差别了多少，这个我没有研究过，因为我还没到的那种可以混搭在一起，一步到位的水平。处理同样的数据，使用类似的步骤，PQ就是比VBA要慢，我也不知道到底慢在哪里，为什么会那么慢？其实数据量不大，但关键是PQ载入的时候很容易出错，但那个出错到底是什么，没人说得清，因为上一次刷新不行，下一次刷新可能又可以了。在VBA里，除了去年年末的某段时间，我经常出现这样那样的奇怪现象，其它时候基本上行就行，不行就是不行。不会出现同一个数据，同一个宏，前一次可以，后一次不行。在PQ里可能得转上半分钟以上的事情，在VBA里非常有可能0.5秒以内就解决了。以前做字幕的时候，我就知道人的反应时间通常是0.3秒，如果一个VBA脚本只需0.3秒就能结束战斗，对普通人来说，那就是眨眼的事而已。

以前我没想过要这么干，以前想着怎么方便怎么来，但是当VBA有点上瘾了以后，我逐渐的把之前用PQ处理的东西全部都用VBA的方式再整了一遍。出来的效果非常好，干净利落快如闪电。让我觉得舒服的是VBE界面是被我调整过的，调整过VBE的布局和颜色，但是在PQ里，那个小得要死的高级编辑器字体实在让我看得很不舒服，但通常某些高端的功能只能在那里敲代码，所以这就很痛苦。

不把某些事完成，心里总会一直念惦记着，把这些事情都干完了，我就可以好好睡觉。

怎么就null了呢

✇我的天
作者 xrspook
2024年8月16日 08:54

当年今日

周三的下午做了一个决定，周三的晚上可以说10点半开始，我觉得整个人都崩溃了。过了12点去睡觉的时候，我感觉整个人毫无睡意，躺在床上，翻来覆去，无论如何睡不着。所以刚刚躺下，我就又重新打开了微信，发出了一段的吐槽，但即便这样还是不解恨。接下来，我甚至都不知道自己是几点睡着的。满脑子都是工作上吐槽的东西，我感觉周三的晚上我就没怎么睡着过，但居然小米手环说我也有接近一个半小时的深睡时间。我不知道那个是怎么算出来的，但估计也睡着了一些时候，因为当我再次翻来覆去的时候，脑子里的东西好像少了一些，也变化了一些内容。我感觉周四一整天肯定会非常糟糕，但实际上却没有想象中的那么难。早上起床的时候不太糟糕，白天的时候也不太糟糕。周四要解决20级消消乐，搞到中午1点30才终于结束，午睡的时候好像也是迷迷糊糊，没怎么睡着，但起码脑子不会有那种大脑缺氧集中不了精神，做不了事的感觉。之所以这样啊，大概是因为周四早上吐槽完以后，我又进行了一番PQ的奋战，把最后的那个记账凭证部分也搞出来了。

其实前一天，我已经把另一个部分的记账凭证搞出来了。晚上洗澡又或者说睡觉的时候，我在考虑最后的这部分该怎么操作，所以其实整个思路其实都已经有了，最后就只是如何实施而已。最后的那个部分能实现我想要的效果，但是有一个我想不明白，当我添加某个条件列的时候，为什么在某个条件之下，明明我已经设定了数据，但是当我保存出去刷新的时候却没有数据？那个东西不知道为什么会默认把我设定的数据变成null，于是那里莫名其妙空了。第一次遇到的时候，我觉得是不是我手误，但是当我好几次都遇到，尤其是周四早上已经修正了一次，又遇到的时候我就觉得这大概率不是我的问题。但是为什么其它条件就不会出现这种状况，唯独在损耗这个条件判断上就会出现这种问题呢？但不是每个条件判断损耗的时候都会这样。我当然希望这只是因为我没有保存导致的，但不可能每次都是我没有保存出现这种问题，另外一个我觉得不是我问题的原因是如果条件判断最后出的结果我没有填写的话，那个地方应该是空的，当我发现刷新出来的数据少了一些东西进去看的时候，发现那里填写了null，我不可能在那里填写这种东西。Power Query在这个问题上到底有什么毛病呢？

PQ方案出来了以后我发现几乎没有需要文本合并的部分，所以这个方案完全可以用VBA替代。最大的区别我感觉是再用 PQ的时候，我在添加条件选择的时候会很方便，如果要在VBE里面写一大串的SQL代码，如果要体现回车还挺麻烦，但不会车和退格会造成一些编写和阅读上的困难。如果我是在一个数据库软件里写SQL，而不是在VBA里，以标准格式写SQL，没有这种烦恼，回车退格什么的东西都是很标准化的。但关键是要我在VBE里面写SQL，那个SQL就像变成了纯文本一样。因为在这个转化方案里需要用到大量的条件判断。想清楚那些条件判断，结果就出来了。我在PQ里面进行条件判断编写不需要用自定义模式，直接有一个条件判断的界面，在那里很容易就能实现功能。因为那个界面就像一个多条件的筛选框。以前我从来没试过这样，这一次添加条件列帮了我不少忙。

因为人已经恢复了平静，我也就不想继续吐槽了。

分析时序数据：从 InfluxQL 到 SQL 的演变

✇夜天之书
作者 tison
2024年6月5日 08:00

近年来，时序数据的增长是 Data Infra 领域一个不容忽视的趋势。这主要得益于万物互联带来的自然时序数据增长，以及软件应用上云和自身复杂化后的可观测性需求。前者可以认为是对联网设备的可观测性，而可观测性主要就建构在设备或应用不断上报的指标和日志等时序数据上。

分析时序数据的演变史几乎是大数据分析演变史的复现，即一开始都是把数据存在关系型数据库上，使用 SQL 分析；而后由于规模增长的速度超过传统技术增长，经历了一个折衷技术的歧出；最终，用户在 SQL 强大的理论框架和生态支持的影响下，回到解决了规模问题的 SQL 方案上来。

对于时序数据来说，这一歧出造成了大量时序数据库自创方言。其中以 InfluxDB 在 V1 版本创造了 InfluxQL 方言，在 V2 版本创造了 Flux 方言，又在 V3 里开始主推 SQL 的演变过程最为有趣。

查询语言简介

InfluxQL

InfluxQL 是 InfluxDB V1 自创的查询语言，大体上模仿了 SQL 的结构，以下是一些 InfluxQL 查询的示例：

1
2
3
4
5
SELECT * FROM h2o_feet;
SELECT * FROM h2o_feet LIMIT 5;
SELECT COUNT("water_level") FROM h2o_feet;
SELECT "level description","location","water_level" FROM "h2o_feet";
SELECT *::field FROM "h2o_feet";

InfluxDB 设计开发的年代，实现一个数据库的技术远远没有像今天一样有大量人才掌握。因此，尽管 InfluxQL 努力靠近 SQL 的语法，但是在当时，以关系代数为支撑实现完整的 SQL 查询并添加时序扩展是比较困难的。InfluxQL 转而实现了大量专为时序数据分析设计的功能和运算符。例如，所有查询会默认返回时间列并按升序排序，所有查询必须带有 field 列才会返回结果，面向时间线粒度设计的特殊查询语法，等等。

基本上，InfluxQL 就是 InfluxDB 对以数值指标为主的时序数据分析需求的直接翻译。随着 InfluxDB 产品的发展，InfluxQL 还支持连续查询和指定保留策略，以实现某种程度的实时数据处理。

虽然 InfluxQL 在 InfluxDB V2 中也能使用，但是由于 InfluxDB V2 主推 Flux 查询语言，使用 InfluxQL 会面临一系列模型失配导致的额外挑战。

Flux

Flux 是 InfluxDB V2 自创的查询语言。不同于 InfluxQL 模仿 SQL 的语法结构，Flux 的语法应该算作 DataFrame 的流派。Elixir 的开发者大概会对 Flux 的语法感到亲切，以下是 Flux 查询的示例：

1
2
3
4
5
from(bucket: "example-bucket")
    |> range(start: -1d)
    |> filter(fn: (r) => r._measurement == "example-measurement")
    |> mean()
    |> yield(name: "_result")

从设计理念上说，Flux 的目的是要支持各种数据源上的时序数据的联合分析。它允许用户从时序数据库（InfluxDB）、关系型数据库（PostgreSQL 或 MySQL），以及 CSV 文件上获取数据，然后进行分析。例如，可以用 sql.from 或 csv.from 相关的语法从数据源拉取数据，替代上述示例中 from(bucket) 的部分，后接其他分析算子。

Flux 语言只能在 InfluxDB V2 中使用，V1 上不支持，V3 上被弃用。原因想必大家看完上面这个例子也可以想象：学习成本巨高。更不用说没有专业的语言开发者支持，要在扩展语法的同时修复各种设计实现问题，这几乎是不可负担的工程成本。

SQL

SQL 大家耳熟能详了。它的大名是结构化查询语言（Structured Query Language），理论基础是关系代数。

不同于从业务中生长出来的，专为业务场景定制的方言，SQL 有坚实的理论支持。从 E. F. Codd 发表了经典论文 A Relational Model of Data for
Large Shared Data Banks 之后，五十多年来积累在关系型数据库上的研究汗牛充栋。

尽管各家 SQL 数据库都会实现独特的扩展，有时让用户也挺摸不着头脑，但是在关系代数理论的支持下，基本的查询分析能力，每一个 SQL 数据库都能一致实现。如果在十几二十年前，或许 Data Infra 的舆论场还会出现 SQL 已死或者 NoSQL 才是未来的论调。但是在今天，毫无疑问 SQL 作为数据分析的默认选择已经王者归来。几十年来，SQL 不断地被改进和扩展，并经由一系列久经考验的实现推广，在全球范围内得到了广泛采用。

InfluxDB V3 号称实现了 SQL 查询的支持，并在该版本中推荐用户使用 SQL 分析时序数据。GreptimeDB 在技术选型上和 InfluxDB V3 不谋而合，率先自主实现了面向时序数据的 SQL 数据库，并在多个严肃生产环境当中部署使用。

抛开时序查询扩展不谈，在 GreptimeDB 上可以用标准 SQL 执行查询：

1
SELECT idc, AVG(memory_util) FROM system_metrics GROUP BY idc;

SQL 的理论支持帮助新的时序数据库可靠地实现复杂的查询逻辑，以及完成日常数据管理任务。SQL 丰富的生态，也使得新的时序数据库能够快速接入到数据分析的技术栈上。例如，此前制作的输入行为分析示例，就利用 GreptimeDB 支持 MySQL 协议这点，零成本地集成到 Streamlit 上实现了可视化。

时序分析的挑战

SQL

虽然 SQL 有着理论支持强大和分析生态丰富两个核心优势，但是在传统的 SQL 数据库在处理时序数据时仍然会面临一系列的挑战，其中最突出的就是数据规模带来的挑战。

时序数据的价值密度大多数时候非常低。设备上传的信息大部分时候你都不会专门去看，应用上报自己状态健康的数据，也不需要额外留意。因此，存储时间数据的成本效率就至关重要。如何利用新时代的云共享存储降低成本，通过针对时序数据的极致压缩来减少数据本身需要的容量，都是时序数据库需要研究的课题。

此外，如何高效地从大量时序数据中提取关键信息，很多时候确实需要特定的查询扩展来优化。GreptimeDB 支持 RANGE QUERY 以帮助用户分析特定时间窗口下的数据聚合就是一个例子。

Flux

毋庸赘言，学习成本就杀死了这个方言。同样，复述一遍前文的观点，作为一个单一提供商独木难支的方言，其语言本身的健壮性，性能优化能做的投入，以及生态的开发，都面临巨大的挑战，更不用说现在这个唯一提供商还放弃了继续发展 Flux 方言。这下已死勿念了。

InfluxQL

虽然 InfluxQL 查询写起来有些像 SQL 的语法，但是其中细微的区别还是非常让人恼火的。而且，即使努力的 Cosplay SQL 的语法，InfluxQL 从根上还是一个从主要关注指标的时序分析业务需求长出来的方言。它在后续开发和维护成本上的挑战和 Flux 不会有本质的差别。

例如，InfluxQL 不支持 JOIN 查询。虽然你可以写类似 SELECT * FROM "h2o_feet", "h2o_pH" 这样的查询，但是它的含义是分别读出两个 measurement 上的数据（😅）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
> SELECT * FROM "h2o_feet","h2o_pH"

name: h2o_feet
--------------
time                   level description      location       pH   water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica        2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek        8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica        5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica        4.938

name: h2o_pH
------------
time                   level description   location       pH   water_level
2015-08-18T00:00:00Z                       santa_monica   6
2015-08-18T00:00:00Z                       coyote_creek   7
[...]
2015-09-18T21:36:00Z                       santa_monica   8
2015-09-18T21:42:00Z                       santa_monica   7

此外，虽然 InfluxDB V3 在强烈的用户呼声下支持了 InfluxQL 以帮助用户逐步迁移到新版本，但是 InfluxDB V3 主推的还是基于 SQL 的查询方案。换句话说，大胆点判断，InfluxQL 也是一个已死勿念的方言。

注意 InfluxQL 是查询方言，不包括 InfluxDB 行协议（Line Protocol）的部分。InfluxDB 行协议是一个简洁、完整、高效的数据写入接口。它几乎没有再开发和维护的成本，并且通过 Telegraf 的插件生态，能够快速跟一系列数据上报方案做集成。

如何迁移到 SQL 分析

上文提到，InfluxDB V3 仍然支持 InfluxQL 的核心原因是用户需求。诚然，InfluxDB 过去很长一段时间可说是时序数据库的代名词，并且现在仍然是 DB-Engines 上时序数据分类中最高影响力的数据库。因此，不少时序数据分析的用户现有的分析逻辑是用 InfluxQL 写成的。

这里介绍 InfluxQL 跟 SQL 的核心不同，从而说明如何从 InfluxQL 迁移到 SQL 分析。

时间列

应用逻辑迁移当中，最重要的一个区别就是 SQL 对时间列没有特殊的处理，而 InfluxQL 会默认返回时间列，且结果按时间戳升序排列。SQL 查询需要显式指定时间列以在结果集中包含时间戳，也需要手动指定排序逻辑。

数据写入时，InfluxQL 会默认自动用当前时间填充时间列，而 SQL 必须手动指定时间列的值。如果是当前时间，也需要明确写出：

1
2
3
4
-- InfluxQL
INSERT INTO "measurement" (tag, value) VALUES ('my_tag', 42);
-- SQL
INSERT INTO measurement (ts, tag, value) VALUES (NOW(), 'my_tag', 42);

InfluxQL 不支持一个 INSERT 语句插入多列，SQL 数据库通常支持一个 INSERT 语句插入多列：

1
INSERT INTO measurement (ts, tag, value) VALUES (NOW(), 'my_tag_0', 42), (NOW(), 'my_tag_1', 42);

此外，InfluxQL 查询使用 tz() 函数指定查询的时区，而 SQL 通常有其他设定时区的方式。例如，GreptimeDB 支持 MySQL 和 PostgreSQL 设置时区的语法。

时间线

InfluxQL 有一些时间线粒度的查询语法，例如 SLIMIT 和 SOFFSET 等。

SLIMIT 会限制结果集中单个时间列返回数据的数量，例如 SLIMIT 1 意味着每个时间列最多返回一个符合过滤条件的结果。

SQL 不是专为时序数据分析设计的，因此需要一些取巧的手段，例如：

1
SELECT DISTINCT ON (host) * FROM monitor ORDER BY host, ts DESC;

这个查询返回以 host 为标签区分的时间列，每个时间列唯一一个结果：

1
2
3
4
5
6
+-----------+---------------------+------+--------+
| host      | ts                  | cpu  | memory |
+-----------+---------------------+------+--------+
| 127.0.0.1 | 2022-11-03 03:39:58 |  0.5 |    0.2 |
| 127.0.0.2 | 2022-11-03 03:39:58 |  0.2 |    0.3 |
+-----------+---------------------+------+--------+

通常，时序数据库会实现各自的语法扩展或特殊函数来支持时间列粒度的查询。

时间间隔

InfluxQL 的时间间隔语法形如 1d 或 12m 等，SQL 的时间间隔语法有标准：

1
2
INTERVAL '1 DAY'
INTERVAL '1 YEAR 3 HOURS 20 MINUTES'

数据列和标签列

InfluxQL 从模型上就区分了数据列和标签列，只 SELECT 了标签列的查询是查不出数据的。此外，InfluxQL 支持 ::field 和 ::tag 后缀来指定数据列或标签列，并由此支持同名的数据列和标签列。

SQL 标准不区分数据列和标签列，都是普通的一列。不过在具体系统实现上，可能会对概念做一些映射。例如，GreptimeDB 的数据模型就区分了时间列、标签列和数据列，并有对应的映射规则。

GreptimeDB 的数据模型

函数名称

部分函数的名称未必相同。例如，InfluxQL 当中的 MEAN 函数对应 SQL 当中的 AVG 函数。

其他函数，例如 COUNT / SUM / MIN 等等，许多还是相同的。

标识符

InfluxQL 的标识符很多时候需要用双引号括起来，而 SQL 则支持无引号的标识符。

值得注意的是，SQL 的标识符默认是大小写不敏感的，如果需要大小写敏感的标识符，则需要用对应的引号括起来。在 GreptimeDB 当中，默认是用双引号括起。但是在 MySQL 或 PostgreSQL 客户端链接上来的时候，会尊重对应方言的语法。

InfluxQL 标识符引号的部分使用区别示例如下：

InfluxQLSQL
WHERE(“value”) > 42where value_col > 42
GROUP BY “tag”GROUP BY tag_col
SELECT MEAN(“value”) FROM “measurement”SELECT AVG(value_col) FROM measurement

JOIN

InfluxQL 不支持 JOIN 查询，SQL 数据库的一个重要甚至是基础能力就是支持 JOIN 查询：

1
2
3
4
5
6
7
8
-- Select all rows from the system_metrics table and idc_info table where the idc_id matches
SELECT a.* FROM system_metrics a JOIN idc_info b ON a.idc = b.idc_id;

-- Select all rows from the idc_info table and system_metrics table where the idc_id matches, and include null values for idc_info without any matching system_metrics
SELECT a.* FROM idc_info a LEFT JOIN system_metrics b ON a.idc_id = b.idc;

-- Select all rows from the system_metrics table and idc_info table where the idc_id matches, and include null values for idc_info without any matching system_metrics
SELECT b.* FROM system_metrics a RIGHT JOIN idc_info b ON a.idc = b.idc_id;

以上是来自 GreptimeDB JOIN 的示例。目前时序数据库在 JOIN 查询上支持最全的应该是 QuestDB 数据库。

时间范围查询

InfluxQL 的 GROUP BY 语句支持传递一个时间间隔，以按照特定长度的时间窗口来聚合数据。

SQL 没有这样特定的查询能力，最接近的应该是 OVER ... PARTITION BY 的语法，但是这个语法还挺难理解的。

支持 SQL 的时序数据库大多会实现自己的范围查询扩展：

GreptimeDB 的 RANGE QUERY 是其中最强大的。不过其中 ALIGN / RANGE / FILL 的含义和应该出现的位置需要一点点学习成本，我应该近期会写一篇文章来讨论这个场景的需求和 RANGE QUERY 的实现。

持续聚合

InfluxQL 支持持续聚合，这在 SQL 当中是标准的物化视图（Materialized View）的需求，TimescaleDB 就使用了 MATERIALIZED VIEW 的相关语法来实现持续聚合。

不过物化视图在大部分 SQL 数据库中的实现都比较脆弱，目前仍然是一个有待探索的领域。部分时序数据库会实现自己的持续集合方案，例如 GreptimeDB 基于数据流引擎实现了持续聚合。

MySQL 实践

✇anran758's blog
作者 anran758
2022年5月16日 11:25

由于自考的实践考核要求有需要用到 mysql 进行考核，故记录一下在 mac 环境下试手的笔记。

初始环境

首先在 mysql 官网中下载你想要的版本。可以直接下载 dmg 安装包，按照安装指示一步一步安装，并设置 mysql 的密码。

下载完毕后，一般情况下直接通过命令行使用 mysql 命令会找不到对应的命令：

1
2
➜  ~ mysql -v
zsh: command not found: mysql

因此需要对当前的命令行工具配置对应的环境变量，比如笔者使用的是 zsh，则打开 ~/.zshrc 文件添加以下配置:

1
export PATH=${PATH}:/usr/local/mysql/bin/

若使用 bash 的用户同理，直接在 ~/.bashrc 添加相同代码。添加完毕后通过 source 命令重新加载对应的环境变量: source ~/.zshrc

接着就可以在命令行直接使用 mysql 了。输入 mysql -u root -p 登录 mysql，密码是在安装阶段时设置的密码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
➜  ~ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

数据库操作

DATABASE 可以不区分大小写，但只能要么全小写，要么全大写。一般会将这些参数用大写写出。

创建数据库

1
2
3
-- 还可以通过 DEFAULT CHARACTER SET 选项设置默认的编码集
mysql> CREATE DATABASE DANNY_DATABASE;
Query OK, 1 row affected (0.01 sec)

查看现有的数据库

1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW DATABASES;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| DANNY_DATABASE             |
| mysql                      |
| performance_schema         |
| sys                        |
+----------------------------+
6 rows in set (0.00 sec)

切换到指定数据库

1
mysql> USE DANNY_DATABASE

数据库的查看与删除

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
26
27
28
29
30
31
32
-- 创建数据库: 准备稍后移除的数据库
mysql> CREATE DATABASE DANNY_DATABASE_WAIT_DELETE;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| DANNY_DATABASE             |
| DANNY_DATABASE_WAIT_DELETE |
| mysql                      |
| performance_schema         |
| sys                        |
+----------------------------+
6 rows in set (0.00 sec)

-- 删除数据库
mysql> DROP DATABASE DANNY_DATABASE_WAIT_DELETE;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DANNY_DATABASE     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

查看当前使用的数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 未选择的情况下
mysql> SELECT DATABASE();
+----------------+
| DATABASE()     |
+----------------+
| null           |
+----------------+
1 row in set (0.00 sec)

-- 切换指定数据库
use DANNY_DATABASE;

mysql> SELECT DATABASE();
+----------------+
| DATABASE()     |
+----------------+
| danny_database |
+----------------+
1 row in set (0.00 sec)

数据表操作

创建数据表

1
2
3
4
5
6
7
8
9
10
-- 创建名为 customers 的数据表
mysql> CREATE TABLE IF NOT EXISTS customers(
    ->   cust_id INT NOT NULL AUTO_INCREMENT,
    ->   cust_name CHAR(50) NOT NULL,
    ->   cust_sex CHAR(1) NOT NULL DEFAULT 0,
    ->   cust_address CHAR(50) NULL,
    ->   cust_contact CHAR(50) NULL,
    ->   PRIMARY KEY(cust_id)
    -> );
Query OK, 0 rows affected (0.11 sec)

其中 IF NOT EXISTS 参数是可选的，它的意思为若 customers 表不存在则创建它。

查看数据表与表列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查看当前用户在当前数据库中可以访问的数据表
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_danny_database |
+--------------------------+
| customers                |
+--------------------------+
1 rows in set (0.00 sec)

-- 查看指定数据表中列的信息
-- DESC customers;  等价于如下命令
mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | NO   |     | NULL      |                |
| cust_sex     | char(1)     | NO   |     | 0         |                |
| cust_address | char(50)    | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
5 rows in set (0.00 sec)

删除数据表

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
26
27
28
29
30
31
32
-- 添加一个数据表用于演示删除
mysql> CREATE TABLE IF NOT EXISTS customers_1(
    ->   cust_id INT NOT NULL AUTO_INCREMENT,
    ->   cust_name CHAR(50) NOT NULL,
    ->   cust_sex CHAR(1) NOT NULL DEFAULT 0,
    ->   cust_address CHAR(50) NULL,
    ->   cust_contact CHAR(50) NULL,
    ->   PRIMARY KEY(cust_id)
    -> );
Query OK, 0 rows affected (0.11 sec)

-- 查看当前的数据表
mysql> SHOW tables;
+--------------------------+
| Tables_in_danny_database |
+--------------------------+
| customers                |
| customers_1              |
+--------------------------+
2 rows in set (0.00 sec)

-- 删除指定数据表
mysql> DROP TABLES customers_1;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW tables;
+--------------------------+
| Tables_in_danny_database |
+--------------------------+
| customers                |
+--------------------------+
1 row in set (0.00 sec)

数据表添加新列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 插入新列
mysql> alter TABLE customers
    -> ADD COLUMN cust_city char(10) NOT NULL DEFAULT 'guangzhou' AFTER cust_sex;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 确认表列状态
mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | NO   |     | NULL      |                |
| cust_sex     | char(1)     | NO   |     | 0         |                |
| cust_city    | char(10)    | NO   |     | guangzhou |                |
| cust_address | char(50)    | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
6 rows in set (0.00 sec)

数据表修改表列

修改整列: 将列名 cust_sex 修改 sex，并修改默认值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> alter TABLE customers
    -> CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT 'M';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL      |                |
| sex          | char(1)     | YES  |     | M         |                |
| cust_city    | char(10)    | NO   |     | guangzhou |                |
| cust_address | char(50)    | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
6 rows in set (0.00 sec)

仅修改列的类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> ALTER TABLE customers
    -> MODIFY COLUMN cust_address varchar(50);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL      |                |
| sex          | char(1)     | YES  |     | M         |                |
| cust_city    | char(10)    | NO   |     | guangzhou |                |
| cust_address | varchar(50) | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
6 rows in set (0.00 sec)

修改指定列的指定字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> ALTER TABLE customers
    -> ALTER COLUMN cust_city SET DEFAULT 'shenzhen';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+----------+----------------+
| Field        | Type        | Null | Key | Default  | Extra          |
+--------------+-------------+------+-----+----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL     | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL     |                |
| sex          | char(1)     | YES  |     | M        |                |
| cust_city    | char(10)    | NO   |     | shenzhen |                |
| cust_address | varchar(50) | YES  |     | NULL     |                |
| cust_contact | char(50)    | YES  |     | NULL     |                |
+--------------+-------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)

移除数据表列： 移除 cust_contact 数据表项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> ALTER TABLE danny_database.customers
    -> DROP COLUMN cust_contact;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+----------+----------------+
| Field        | Type        | Null | Key | Default  | Extra          |
+--------------+-------------+------+-----+----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL     | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL     |                |
| sex          | char(1)     | YES  |     | M        |                |
| cust_city    | char(10)    | NO   |     | shenzhen |                |
| cust_address | varchar(50) | YES  |     | NULL     |                |
+--------------+-------------+------+-----+----------+----------------+
5 rows in set (0.00 sec)

数据项操作

添加数据

默认情况下在命令行中 mysql 是不能直接插入中文的，这个跟字符集有关。可输入下面命令修改数据库或表的字符集:

1
2
3
4
5
6

-- 设置名为 danny_database 的数据库字符集
ALTER DATABASE danny_database character SET utf8;

-- 设置名为 customers 的数据库表字符集 (Tip: 若数据库已经被设置为 utf8, 则无需再设置表的字符集)
ALTER TABLE customers convert to character SET utf8;

为数据表插入数据，显式设置字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address)
    -> VALUES(901, '张三', DEFAULT, '广州市');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) 
    -> VALUES(0, '李四', DEFAULT, '广州市');
Query OK, 1 row affected (0.01 sec)

mysql> select * from customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
+---------+-----------+------+-----------+--------------+
2 rows in set (0.00 sec)

由于 cust_id 是自增的，因此可以将此字段的值设置为 0 或 NULL 会自动自增。上例 “李四” 的 cust_id 在创建后就被自增为 902。

还可以通过 SET 语句设置部分值：

1
2
mysql> INSERT INTO danny_database.customers SET cust_name='王五', cust_address='武汉市', sex=DEFAULT;
Query OK, 1 row affected (0.00 sec)

查询数据

可通过 SELECT 语句查询数据：

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
|     903 | 王五      | M    | shenzhen  | 武汉市         |
+---------+-----------+------+-----------+--------------+
3 rows in set (0.00 sec)

仅展示指定字段：

1
2
3
4
5
6
7
8
+---------+-----------+------+
| cust_id | cust_name | sex  |
+---------+-----------+------+
|     901 | 张三      | M    |
|     902 | 李四      | M    |
|     903 | 王五      | M    |
+---------+-----------+------+
3 rows in set (0.00 sec)

通过 WHERE 子句设置查询条件，筛选出符合查询条件的数据：

1
2
3
4
5
6
7
8
9
mysql> SELECT cust_id,cust_name,cust_address FROM customers
    -> WHERE cust_address="广州市";
+---------+-----------+--------------+
| cust_id | cust_name | cust_address |
+---------+-----------+--------------+
|     901 | 张三      | 广州市         |
|     902 | 李四      | 广州市         |
+---------+-----------+--------------+
2 rows in set (0.00 sec)

删除数据

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
26
27
28
29
30
-- 添加几项测试数据
mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) 
    -> VALUES(1, 'test1', DEFAULT, '深圳市');
Query OK, 1 row affected (0.02 sec)

mysql> select * from customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|       1 | test1    | M    | shenzhen  | 深圳市         |
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
|     903 | 王五      | M    | shenzhen  | 武汉市         |
+---------+-----------+------+-----------+--------------+
4 rows in set (0.00 sec)

-- 删除表数据
mysql> DELETE FROM customers
    -> WHERE cust_id=1;
Query OK, 1 row affected (0.02 sec)


mysql> select * from customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
|     903 | 王五      | M    | shenzhen  | 武汉市         |
+---------+-----------+------+-----------+--------------+

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 更新数据
mysql> UPDATE customers SET cust_address="深圳市" WHERE cust_name="李四";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市       |
|     902 | 李四      | M    | shenzhen  | 深圳市       |
|     903 | 王五      | M    | shenzhen  | 武汉市       |
+---------+-----------+------+-----------+--------------+
3 rows in set (0.00 sec)

实践

以一个 eShop 的需求为例做个简单的测试吧。

创建 eshop 数据库

在 MySQL 中创建一个名为 eshop 的数据库，选择字符集为 utf8mb4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> CREATE DATABASE IF NOT EXISTS eshop DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DANNY_DATABASE     |
| eshop              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

-- 切换数据库
mysql> use eshop;
Database changed

创建数据表及相关记录

相关表信息如下

表名：用户(t_user)

字段名类型大小
用户ID (id)自增类型
姓名 (user_name)文本50，非空
联系电话 (phone_no)文本20，非空

表名：商品（product）

字段名类型大小
商品ID（id）自增类型
商品名称(product_name)文本50，非空
价格(price)数值类型（整数位9位，小数位2位），非空

表名：购物车 (shopping_cart)

字段名类型大小
用户id（user_id）整数非空，主键，参考用户表主键
商品id(product_id)整数非空，主键，参考商品表主键
商品数量(quantity)整数非空
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
26
27
28
29
30
31
32
33
34
35
-- 用户表
mysql> CREATE TABLE IF NOT EXISTS t_user(
    ->   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `user_name` CHAR(50) NOT NULL,
    ->   `phone_no` CHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)

-- 商品表
mysql> CREATE TABLE IF NOT EXISTS product(
    ->   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `product_name` CHAR(50) NOT NULL,
    ->   `price` DOUBLE(9, 2)
    -> );
Query OK, 0 rows affected (0.06 sec)

-- 购物车
mysql> CREATE TABLE IF NOT EXISTS shopping_cart(
    ->   `user_id` INT NOT NULL,
    ->   `product_id` INT NOT NULL,
    ->   `quantity` INT NOT NULL,
    ->   PRIMARY KEY(`user_id`, `product_id`)
    -> );
Query OK, 0 rows affected (0.05 sec)

-- 查看数据表
mysql> show tables;
+-----------------+
| Tables_in_eshop |
+-----------------+
| product         |
| shopping_cart   |
| t_user          |
+-----------------+
3 rows in set (0.00 sec)

录入用户数据

用户信息

1
2
3
4
1;张三; 13333333333;
2;李四; 13666666666
3;王五; 13888888888
4;赵六; 13999999999

商品信息

1
2
3
1; C++程序设计教程; 45.5
2; 数据结构; 33.7
3; 操作系统; 51

购物车

1
2
3
4
1; 1; 5
1; 2; 3
2; 3; 6
2; 4; 8

录入数据：

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 插入用户表数据
mysql> INSERT INTO t_user
    ->   (id, user_name, phone_no)
    -> VALUES
    ->   (1, '张三', '13333333333'),
    ->   (2, '李四', '13666666666'),
    ->   (3, '王五', '13888888888'),
    ->   (4, '赵六', '13999999999');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+-----------+-------------+
| id | user_name | phone_no    |
+----+-----------+-------------+
|  1 | 张三      | 13333333333 |
|  2 | 李四      | 13666666666 |
|  3 | 王五      | 13888888888 |
|  4 | 赵六      | 13999999999 |
+----+-----------+-------------+
4 rows in set (0.00 sec)

-- 插入「商品信息」
mysql> INSERT INTO product
    ->   (id, product_name, price)
    -> VALUES
    ->   (1, 'C++程序设计教程', 45.5),
    ->   (2, '数据结构', 33.7),
    ->   (3, '操作系统', 51);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM product;
+----+-----------------------+-------+
| id | product_name          | price |
+----+-----------------------+-------+
|  1 | C++程序设计教程       | 45.50 |
|  2 | 数据结构              | 33.70 |
|  3 | 操作系统              | 51.00 |
+----+-----------------------+-------+
3 rows in set (0.00 sec)

-- 插入购物车
mysql> INSERT INTO shopping_cart
    ->   (user_id, product_id, quantity)
    -> VALUES
    ->   (1, 1, 5),
    ->   (1, 2, 3),
    ->   (2, 3, 6),
    ->   (2, 4, 8);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM shopping_cart;
+---------+------------+----------+
| user_id | product_id | quantity |
+---------+------------+----------+
|       1 |          1 |        5 |
|       1 |          2 |        3 |
|       2 |          3 |        6 |
|       2 |          4 |        8 |
+---------+------------+----------+
4 rows in set (0.00 sec)

数据的查询与更新

使用 SQL 语句列出「张三」购买商品清单信息，以购买数量升序排列：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT u.user_name, p.product_name, u.phone_no, p.price, s.quantity FROM t_user u, product p, shopping_cart s
    -> WHERE u.user_name="张三" AND u.id = s.user_id  AND p.id = s.product_id 
    -> ORDER BY quantity asc
    -> LIMIT 100;
+-----------+-----------------------+-------------+-------+----------+
| user_name | product_name          | phone_no    | price | quantity |
+-----------+-----------------------+-------------+-------+----------+
| 张三      | 数据结构              | 13333333333 | 33.70 |        3 |
| 张三      | C++程序设计教程       | 13333333333 | 45.50 |        5 |
+-----------+-----------------------+-------------+-------+----------+
2 rows in set (0.01 sec)

使用 SQL 语句选出李四购买商品的总价：

1
2
3
4
5
6
7
8
9
mysql> SELECT u.user_name, p.product_name, p.price, s.quantity, p.price*s.quantity AS total_price FROM t_user u, product p, shopping_cart s
    -> WHERE u.user_name="李四" AND u.id = s.user_id  AND p.id = s.product_id 
    -> LIMIT 100;
+-----------+--------------+-------+----------+-------------+
| user_name | product_name | price | quantity | total_price |
+-----------+--------------+-------+----------+-------------+
| 李四      | 操作系统     | 51.00 |        6 |      306.00 |
+-----------+--------------+-------+----------+-------------+
1 row in set (0.00 sec)

使用 SQL 语句列出购买数量排前两位的商品名称：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT p.product_name, p.price, s.quantity FROM product p, shopping_cart s
    -> WHERE p.id = s.product_id
    -> ORDER BY quantity desc
    -> LIMIT 2;
+-----------------------+-------+----------+
| product_name          | price | quantity |
+-----------------------+-------+----------+
| 操作系统              | 51.00 |        6 |
| C++程序设计教程       | 45.50 |        5 |
+-----------------------+-------+----------+
2 rows in set (0.00 sec)

忘记密码

若忘记数据库密码后可通过 mysqld_safe 来修改密码：

  1. 在系统偏好设置中关闭 mysql 服务

  2. 打开终端，输入命令:

    1
    2
    ➜  ~ cd /usr/local/mysql/bin
    ➜  ~ sudo su

  3. 命令行变成以 sh-3.2# 开头后继续输入命令:

    1
    2
    3
    4
    sh-3.2# ./mysqld_safe --skip-grant-tables &

    mysqld_safe Logging to '/usr/local/mysql/data/DannydeMBP.err'. 
    mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

  4. 新开个命令行窗口，进入 mysql:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    ➜  ~ /usr/local/mysql/bin/mysql

    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 30
    Server version: 5.7.31

    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>
    mysql> use mysql

    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed

  5. 更新密码

    1
    2
    3
    4
    mysql> update user set authentication_string=password('admin') where Host='localhost' and User='root';

    Query OK, 1 row affected, 1 warning (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 1

  6. 输入 exit 命令退出 mysql，查出 mysqld_safe 进程号并杀掉:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> exit
    Bye

    ➜  ~ ps -ax | grep mysql
      8553 ttys004    0:00.03 /bin/sh ./mysqld_safe --skip-grant-tables
      8623 ttys004    0:00.92 /usr/local/mysql-5.7.31-macos10.14-x86_64/bin/mysqld --basedir=/usr/local/mysql-5.7.31-macos10.14-x86_64 --datadir=/usr/local/mysql-5.7.31-macos10.14-x86_64/data --plugin-dir=/usr/local/mysql-5.7.31-macos10.14-x86_64/lib/plugin --user=mysql --skip-grant-tables --log-error=host-3-187.can.danny1.network.err --pid-file=host-3-187.can.danny1.network.pid

    # 杀掉 mysql 的进程
    ➜  ~ kill -9 8553
    ➜  ~ kill -9 8623

  7. 此时返回系统偏好设置中看到 mysql 被关闭后就算正确退出了。接着继续输入 mysql -u root -p 命令连接数据库，再输入刚才修改的密码即可。

参考资料

NoSQL Revolution

✇夜天之书
作者 tison
2022年6月13日 08:00

从本世纪初谷歌的三篇论文发布以来，数据处理领域在大数据的方向上探索了将近二十年的时间。从三篇论文的开源实现 Apache Hadoop 和 Apache HBase 开始，到打破传统关系型数据库的分布式数据处理系统如雨后春笋般接连诞生，NoSQL 系统回应了移动互联时代的数据爆发式增长的挑战。

诚然，传统的数据库专家对 NoSQL 也有像 MapReduce: A major step backwards 这样的批评，不过 NoSQL 系统本身也在向传统数据处理领域当中被证明有效的特性靠拢，向 Not Only SQL 系统转变。

本文首先从移动互联时代数据增长和数据模型演进带来的实际问题出发，讨论 NoSQL 系统在现在企业数据处理生态当中的定位和价值，然后介绍 NoSQL 系统靠近 Not Only SQL 定位的过程中遇到的硬核诉求，最后分析新时代 NoSQL 的发展方向。

数据量的增长带来的挑战

NoSQL 系统崛起的主要原因就是移动互联时代数据的爆发式增长。

起初，企业经营过程中产生且需要运维的数据并不多，单机数据库应对就绰绰有余。尤其是在摩尔定律尚未失效的硬件主导技术升级的年代，数据量级增长的速度未曾超过硬件升级的速度。关系数据库赢下单机数据库战争以后，几乎每家企业的数据处理生态都被 Oracle 数据库、IBM 的 DB2 数据库和微软的 SQL Server 数据库所占据。

随着移动互联时代的到来，计算机全面进入到民用阶段。几乎人人手持一部甚至多部终端设备，这些设备逐渐占领了每个人生活的绝大部分时间。全域搜索、社交媒体、在线游戏、电商购物、网络直播……提供此类服务的企业所要处理的数据的量级，不再是商业场景下的 B2B 订单、客户关系管理和运维的量级，而是全民参与的 B2C 或 C2C 的用户行为的量级。换句话说，这时企业所要处理的数据，从一部分企业及其行为的量级增长到了全体民众及其行为的量级。

另一方面，硬件的升级也遇到了摩尔定律的瓶颈，硬件的升级不再能够满足用户数量增长的需求。阿里巴巴在 2008 年前后开始的“去 IOE 运动”就是这一趋势的一个注脚。原本，阿里巴巴在应对用户数据快速增长的时候，采取的也是传统的硬件技术升级的手段，采购商业级 Oracle 数据库、特殊定制硬件的 IBM 小型机和 EMC 高级存储设备来支持。然而，一方面受到技术自主可控的驱动，另一方面也是出于企业经营成本控制的要求，阿里巴巴转向了 MySQL 数据库以及后续一系列开源或自研的分布式数据处理系统的解决方案。

当然，单机 MySQL 也无法抗住全网用户每天源源不断产生的行为数据。因此，在阿里巴巴等互联网公司当中就诞生了以分库分表技术为核心的数据库中间件解决方案，即通过分拆业务到不同数据库实例中，同一业务选择分片键分拆到不同数据库实例中，再于业务和数据库实例集群之间设置一个解析查询和转发查询的中间件，来实现以多台廉价计算机和运行其上的 MySQL 数据库，抗下用户行为数据的解决方案。

严格来说，这一方案产生的软件不是 NoSQL 系统。NoSQL 系统的一个重要特征是用户能够像对待单一系统那样与整个 NoSQL 分布式系统交互，而分库分表的数据库中间件往往要求用户知悉底下数据分片的模型，从而针对性的写出不会导致全表扫描的查询。另一方面，即使采用了分库分表的解决方案，系统所能处理的数据量仍然是有限的。目前主流的分库分表方案，最多能够应对 TB 级别的数据。这对于用户账户数据、商户和商品概要数据以及最近一段时间的订单数据或许是足够的，但是对于历史订单数据、商品详情数据、用户历史足迹和社交网络活动记录来说，则远远不够。或者说，即使能够扩容分库分表的数量来支撑更大的数据量级，底下运行的 MySQL 实例产生的开销，也不如 NoSQL 系统底下只是需要一个普通的数据节点更有性价比。

NoSQL 系统当中，除去主打内存缓存的 Redis 以外，诸如 HBase 和支持 Redis 协议的数据持久化 NoSQL 系统 Apache Kvrocks (Incubating) 都能支持 PB 级别甚至以上的数据存储和访问。这得益于从谷歌三篇论文一脉相承的 scale out 策略，藉由简化系统复杂度，以硬件技术的新增长点网络性能抵消单机处理的延时优势。这样，企业当中的数据处理系统可以用增加成本可控的节点数，而非对抗摩尔定律购买价格不支持商用的高端硬件的方式，在延时可接收的范围内应对更大的数据量。

前面提到，NoSQL 系统对于用户来说是一个整体，而分库分表在扩缩容时却未必能够像传统数据库使用体验那样流畅。由于分片键与实例数相关，分库分表分出来多少个库表，这个知识会成为整个系统的一个固有限制。如果想要增加数据库实例，这个过程并非简单地上线新实例就可以开始服务，而是需要整个逻辑数据库在新的库表数下重新分片。我在某司操作过 32 库乘以 128 表到 128 库乘以 128 表的迁移过程，这个迁移的数据同步阶段总共花了两天半的时间，在线上几乎没有感知的情况下以深夜一分钟左右的闪断为代价切换成功。然而，这还是建立在公司有足够强的研发实力支持从头开发一套数据中间件以及数据迁移系统的前提下的开销。而无论是哪种典型的 NoSQL 系统，几乎都支持用户无感知的扩容和缩容动作。

分库分表的数据库中间件实质上操作的是底下不同的数据库实例，传统数据库支持的事务一致性、多表联合操作和存储过程等功能，几乎都受限于实际上数据存在于多个数据库实例的物理限制而无法支持。NoSQL 系统可以认为是在这样的 baseline 上，基于整体考虑设计出一个能够最大化数据处理吞吐和尽可能降低数据延迟，并且尽可能使得用户像对待一个统一系统那样操作的解决方案。

对于定位在支持传统数据库的语义和功能，同时又要满足数据增长需求的 NewSQL 系统，这些系统能够处理的数据量级上限，实际上也没有超过分库分表方案 TB 级别的水平。同时，在数据量超过一定水平时，这些系统会面临严重的功能挑战，例如大事务延迟不可接受，选择 TSO 作为中央授时的系统中心节点不堪重负，或者 Aurora 会提示用户关闭 Binary Log 以保证用户读写的时延。相比起 NoSQL 系统所能处理的数据量级，这些 NewSQL 系统还是不太够看。从它们支持的数据库功能来看，往往与传统数据库也有明显的差别，比如存在微妙差异的事务一致性，不支持存储过程，不支持外键，等等。

数据模型贴近业务的价值

NoSQL 系统崛起的另一个主要原因是打破了关系模型对数据处理领域的垄断。

严格来说，在业务逻辑开发这一块，关系模型并没有统治开发者的心智。虽然不少业务逻辑是写在存储过程或者触发器当中的，这些代码自然深深地被搭上了关系模型的印记，但是尤其在互联网业务开发的领域当中，开发人员并非直接面向数据库编程。在开发人员编写的业务代码到底下的数据库系统中间，经常有一层对象关系映射框架（ORM）的存在。

这就是关系数据库始终绕不过的“对象关系阻抗失配”问题。

现代程序设计语言的主流是面向对象的程序设计，即使并非“一切都是对象”的信徒，大部分语言也都支持数据结构的嵌套。而在关系模型当中，所有的数据都以元组的形式存储，想要表达列表或者嵌套数据结构，要么需要冗余数据，要么需要设置多张表并藉由外键关联来查询。

前者不仅会造成空间的浪费，还会在数据结构趋于复杂，尤其是存在 option 和 either 这样的结构的时候，列的碾平生出非常难以查询和写入的表结构。后者更不必说，原本是同一个逻辑对象的数据，如今散落在多张表上，无论是更新时需要注意的级联变更和完整性约束，还是查询时需要依靠 JOIN 来聚合数据，都是非常麻烦的事情。

反观 Redis 的主要特点之一就是支持丰富的数据结构，例如开发者熟悉的 List/Hash/Set/ZSet 以及方便的 HyperLogLog/GeoHash/Bitmap 等等。对于接受经典数据结构培养的研发人员来说，Redis 这种丰富数据结构上手成本很低，开发者对于基本的数据结构都会使用。反观关系数据库的模型，要在其中实现 List Push/Pop 这样的操作还是有些麻烦。

MongoDB 的数据模型文档将支持灵活的数据模型放在了第一位，Apache Cassandra 的数据模型文档则进一步点明了这种数据模型价值观与关系数据库的不同——如果说关系数据库的数据模型是表驱动的，那么 NoSQL 系统的数据模型就是查询驱动的。

传统的数据库开发流程，往往是由 DBA 或架构师定义出一系列的表及表的模式，藉由关系数据库系统支持的特性和约束来保证数据的完整性和一致性，以这些表及表的模式为基础，上线数据处理系统支持业务需求。如果业务迭代需要引入新的字段或者添加新表支持嵌套数据结构，这些改动都需要送交 DBA 和架构师审批，甚至对于核心数据表的改动，还需要送交研发高管审批。这一过程和认识直到今天仍然没有什么大的改变。基本上，关系数据库在企业当中的定位就是持久化数据资产。

然而，移动互联时代业务的需求有着很强的时效性，需求经常变化，为了应对某个活动需要临时增加某个字段，过后即可废弃。这样的使用场景遇上层层审批的变更流程，必然激发出剧烈的矛盾。NoSQL 系统此时就扮演了一个在企业关键数据资产和业务经常变化且时效性强的需求之间的润滑剂。

一方面，核心数据资产例如用户账户数据、用户信息数据、订单交易数据等等，仍然由关系数据库来支撑运转，保证数据的完整性、一致性和足以应对容灾的持久化，并且借助几十年来发展得相当成熟的数据平台生态进行冷数据归档，以及数据订阅、数据同步等等，作为业务系统的核心数据来源支撑。另一方面，NoSQL 系统存储非核心的经营数据或者衍生、冗余数据，用以支持业务高速迭代的需求。查询驱动的含义就在于此：业务查询是什么样的，底下的数据模式就可以是什么样的。例如使用 Redis 存储用户账户与手机号的对应关系，使用 HBase 存储全国地图上的兴趣点以支持基于位置的用户服务，将业务数据导入 ElasticSearch 当中提供搜索功能，使用 Apache Pulsar 接受采点上报数据。

这种职责分层实践在过去十几年当中不断地被传播和应用，证明了 NoSQL 在企业当中足以赢得自己生存的空间。从数据模型的角度看，贴近业务的数据模型天然适合应对业务的经常性迭代。灵活的数据模式能够快速适应数据模型变更的需求；丰富的数据结构符合开发者的心智模型，能够更快的完成业务代码开发；而对于消息队列、倒排索引系统和图数据库，则是各自领域当中最贴合的建模方式。例如 XLab 分析 GitHub 全域开源协同数据的时候，就自然选择了图数据库来分析人与人、人与项目、项目与项目之间形如社交网络的关系和行为数据。

随着 NoSQL 系统逐渐成熟，尤其是在数据一致性和存储可靠性上面的突破，越来越多的企业也结合自身业务的特性，尝试把核心业务及其数据也假设在 NoSQL 系统上。国外基于 MongoDB 发展出一套 MEAN 应用开发栈，就是这一实践的注脚。虽然业务稳定以后，数据模型变更减少，表驱动的关系数据库能够带来多年积累的软件成熟度和生态繁荣度的优势，但是对于创业公司或者新团队新业务来说，采用 NoSQL 来快速启动自己的业务，并且能够灵活地调整数据模型，或许是个更好的选择。

Not Only SQL 的诉求

NoSQL 系统一开始得名就是因为它的设计理念和数据模型都是反（NO）关系数据库（SQL）的。

这种反叛的极致体现在谷歌的三篇论文当中完全无视数据库领域二三十年的积累，以一种非常土味的方式用廉价机器拼凑起来一个分布式存储系统 GFS 和仅仅支持 MapReduce 这样简单算子的计算引擎。Bigtable 作为初代 NoSQL 引擎，不支持跨行跨表事务，不支持严格的表模式，没有关联查询，没有索引，没有存储过程。

这些“离经叛道”的创举自然引来了数据库大佬们的批评，比如本文开篇引用的 MapReduce: A major step backwards 博客文章。这些批评主要就集中在上面提到的这些“不支持”和“没有”上，以及与数据库生态的不兼容。

一开始，尝到了堆砌大量廉价机器就能解决业务问题甜头的开发者和公司对这些批评自然是不屑一顾的。只是随着业务越长越大，复杂性越来越高，人们面临着数据杂乱无章的失序的风险，以及缺乏传统数据库约束和索引带来的性能退化的痛点，逐渐开始认真考虑数据库领域一直以来的研究的价值。

事务

第一个被提出的议题就是事务，或者说其所代表的数据一致性问题。单机数据库能够保证简写为 ACID 的事务一致性，而分布式系统受到 CAP 理论的限制，往往无法实现单机关系数据库能达到的数据一致性。

关于 CAP 理论的理解，在实际业务取舍的过程中，并不是简单的一致性、可用性和分区容忍性三选二，而是在分布式系统本来就需要能够做到分区容忍，以及业务必须保证服务可用的前提下，看看能够做到多少一致性。当然，有些一致性是以服务短暂不可用或者时延升高为代价的，但是业务绝对不会接受服务一直不可用。

这种情况下首先被提出的解决方案是所谓的 BASE 性质，即基本可用、柔性状态和最终一致，或者我喜欢借用一个说法，叫做啥也不保证。BASE 性质基本已经被扫进历史的垃圾堆里了，不会再有系统标榜自己符合所谓的 BASE 性质。但是它确实提供了数据一致性上的一条基线，即最终一致性。也就是说，对于给定的有限的输入集合，NoSQL 系统当中的数据最终会收敛一个稳定状态，但是这个稳定状态下数据的值是否还有业务意义，不保证。

一般来说，NoSQL 系统在此之上能够做到对自己数据模型下单个数据单元的基本操作是原子性的。比如说，KV NoSQL 系统当中 Put 一个字符串是原子的，不会出现两个 Put 操作的结果是值一部分由第一个操作提供，一部分由第二个操作提供的情况。不过，业务要求显然远远不止这点。对于业务来说，常见的一致性或者叫事务需求，是保证对一行数据的多个操作的原子性，乃至多行数据多个操作的原子性。例如单行数据的 CAS 操作，或者多行数据原子写乃至事务性的读后写的支持。

HBase 和 Bigtable 都支持单行事务，这是因为它们的数据模型里单行数据一定存在单台机器上，保证同一台机器上操作的原子性是比较简单的。大部分系统根据自己物理数据分布的特性，也会向用户保证这类数据存储在同一台机器上的情况下事务能力的支持。

对于跨多台机器的事务支持，则要牵扯到分布式事务的话题。对于 Pulsar 这样数据仅追加的消息系统来说，可以通过批量提交及该操作的幂等性来实现生产消息的事务支持。对于存在删改的系统来说，要么选择放弃隔离性，实现复杂的数据补偿逻辑来支持 Sagas 式的分布式“事务”，要么是采用 Raft 这样的共识算法加上某种形式的两阶段提交算法来支持分布式事务。例如 TiKV 采用了 Raft + Percolator 算法来实现分布式事务，Percolator 本质上还是两阶段提交，但是在生产上会有一系列的优化，并且在某些特定条件满足的情况下可以简化成一阶段提交。

一般来说，启用分布式事务会导致数据吞吐的下降和其他性能影响，因此大部分 NoSQL 系统都提供了用户自己调节数据一致性的选项，来保证只在需要对应级别的数据一致性的情况下，才付出相应的开销。

模式

前文提到，NoSQL 的一个优势是灵活的数据模式能够响应业务的高速迭代。不过，随着业务日渐复杂，开发团队人员更迭，维护 NoSQL 系统上存储的数据的质量就成为了一个难题。

如果所有的数据都是无模式的，或者数据模式没有被良好的记录和检验，那么杂乱无章的数据就可能带来极大的存储空间浪费并阻碍业务开发。

关系数据库和 SQL 当中有专门的数据定义语言（DDL）来描述表模式，通过定义清楚字段的类型和约束来保证数据是结构化的。虽然一旦这种约束过于繁琐和严格，且由于企业流程难于变更时，会影响业务开发的效率，但是清晰的类型约束和唯一性约束是有助于开发人员理解字段的属性和检验业务逻辑正确性的。

这种思路体现在 NoSQL 的演进之路上就是渐进式模式定义。

例如，MongoDB 就支持数据模式校验，Pulsar 也支持定义消息的模式。

再以 Cassandra 为例，虽然一开始它对外暴露的是稀疏列簇式大宽表的接口，但是也逐渐地转向建议用户以 CQL 和 Cassandra 交互，同时也保留直接操作底下稀疏列簇式大宽表的手段。

对于现有系统本身不支持数据模式定义的，也有其他系统来支持。例如 Apache Hive 支持为 Hadoop 上的数据定义模式，Apache Phoenix 支持为 HBase 定义数据模式。

索引

对于直截了当的查询来说，NoSQL 的性能优势是明显的。例如 HBase 上已知 rowkey 查询值，这样的操作是系统设计之初就考虑到的情况，属于舒适区。

然而，随着业务发展逐渐复杂，各种新的查询维度也纷至沓来。例如，不再是以 rowkey 查询值，而是以某一列的值为筛选条件来查询匹配的所有行。比如一个用户表，一开始将用户 ID 作为主键存储，现在要根据用户所在地筛选出所有在某地的用户。由于 HBase 没有索引，这种查询只能扫全表后过滤。可想而知，每次查询都需要遍历全表数据，查询的性能肯定好不到哪去。

关系数据库当中也有一样的问题，MySQL 每一行的主键是固定的，要么是创建表模式时指定，要么由插入行时自动生成的 rowid 取代。关系数据库当中可以针对某张表创建索引。一方面，唯一键索引可以施加键值唯一的约束；另一方面，创建索引通常会在存储系统当中额外创建出一个从索引列到主键的映射。实际以索引列为过滤条件查询的时候，会先从索引映射当中找到对应主键的集合，然后直接挑选出小部分相关行做后续操作。

基本上现在的 NoSQL 系统都会实现一定的索引机制。例如业内前沿的数据湖存储 Apache Hudi 系统，一开始只是一个按照直觉写出的读取 Hadoop 上的文件，应用对给定记录的变更并写回 Hadoop 的 Apache Spark 程序。但是在后来投入生产之后，越来越多的开发人力加入和生产环境对性能无止境的追求，为 Hudi 添加了基于元数据文件的、基于 HBase 外存的，以及在选择 Apache Flink 处理引擎的情况下基于 Flink 内置 State 存储的多种索引方案。

上一节提到的能为 HBase 定义数据模式的 Phoenix 项目，也支持为 HBase 创建索引。

从这一系列 NoSQL 系统的转变来看，索引确实是其走向 Not Only SQL 的一个性能上的硬核需求。

新时代 NoSQL 的发展方向

NoSQL 系统的范畴非常广，具体到每个细分领域面临的业务环境演化和技术需求都不尽相同。

对于整个 NoSQL 生态发展的角度来说，未来的发展方向是发挥在应对大数据量上无需全面兼容传统数据库约束的优势，直面海量数据和全球分布式系统的挑战，并且结合具体业务领域对数据模型的要求，根据对应假设设计出在不同业务场景下最优化的数据处理系统。

对于具体的 NoSQL 系统来说，我想 KV NoSQL 这个细分领域值得关注。字典映射是构建复杂数据结构的基础构建块，无论是应对什么场景特化的 NoSQL 系统，最终映射到持久化存储的数据结构，几乎都是某种 KV 的形式。如果 KV 引擎能够从现在 RocksDB 占据单机引擎半壁江山的状况，发展到有一个分布式 KV NoSQL 系统能够支持其他特化的 NoSQL 系统基础的存储需求，那么这或许会是分布式数据处理系统下一次革命的开端。

对于有望成为这个方向解决方案的系统，它至少能够可选地支持上面提到的 Not Only SQL 所需要的硬核特性，也就是事务、模式和索引。在此基础上，如果能够在低延迟、可扩展性和稳定性上实现突破，比如引擎的创新带来的性能提升，利用云原生时代的基础设施和硬件的迭代支持全球规模的集群管理，工程打磨实现生产可靠的稳定且方便运维的系统，那么这样的一个软件将是有价值的。

对于现有系统来说，实现这样的转型并不容易，但也绝非不可能。例如 Datastax 公司全力投入支持以 Cassandra 为基础的 Astra DB 在云上的应用，HBase 社群也在投入存储上云的开发。对于新系统来说，历史包袱会轻松一些，能够基于现在的情况做针对性的设计。但是在增量市场逐渐萎缩的环境下，做好现有系统 API 的兼容让就是生产系统采用的一个关键考量了。例如，ScyllaDB 采用了 thread-per-code 的线程模型来试图革新 KV NoSQL 的性能，但是在面向用户的接口上选择兼容 Cassandra 的 API 以帮助存量用户平滑过渡。

进一步地，如果某个 KV NoSQL 系统在 KV 领域打开了局面，那么它就可以借助协议层抽象来支持不同场景的数据存储需求。

例如，TiKV 是一个支持分布式事务的 KV NoSQL 系统，Titan 通过实现 Redis 协议层来支持 redis-cli 对 TiKV 系统的访问；TiDB 可以认为是在 TiKV 之上实现了一个支持 SQL 访问的协议层。

总结一下，新时代 KV NoSQL 的发展方向，一方面是需要支持前面提到的 Not Only SQL 的硬核诉求，并且需要和现存的数据处理生态保持良好的兼容性。另一方面，这些系统可以在低延迟、可扩展性和稳定性等方向上寻求突破。最后，如果某个 KV NoSQL 系统足够成熟，那么它可以借助协议层了解 KV 之上具体场景下的数据结构信息，知道用户想要存的是什么数据，从而在复杂场景下允许用户直观的表达自己的业务数据，同时让数据处理系统理解相应场景的的语义，帮用户做场景优化。

MySQL 大批量插入，如何过滤掉重复数据

✇鸟不拉诗
作者 鸟不拉诗
2021年8月10日 11:30

线上库有6个表存在重复数据，其中2个表比较大，一个96万+、一个30万+，因为之前处理过相同的问题，就直接拿来了上次的Python去重脚本，脚本很简单，就是连接数据库，查出来重复数据，循环删除。

emmmm，但是这个效率嘛，实在是太低了，1秒一条，重复数据大约

❌
❌