SQLite列别名 [英] SQLite column aliasing

问题描述

前提

我最近在 select 语句中遇到了一个错误我的代码。在我意识到发生了什么之后，修复起来很琐碎，但是我有兴趣找到一种方法来确保不再发生类似的错误。

I recently ran into a bug in a select statement in my code. It was fairly trivial to fix after I realized what was going on, but I'm interested in finding a way to make sure a similar bug doesn't happen again.

下面是一个令人讨厌的查询示例：

Here's an example of an offending query:

select
  the,
  quick,
  brown
  fox,
  jumped,
  over,
  the,
  lazy,
  dog
from table_name;

我的原意是：

select
  the,
  quick,
  brown,
  fox,
  jumped,
  over,
  the,
  lazy,
  dog
from table_name;

对于那些没有看到它的人，在棕色后缺少逗号在前者。这将导致该列被别名，因为不需要要求使用 as 关键字。因此，您得到的结果是：

For those who don't see it, a comma is missing after brown in the former. This causes the column to be aliased, because the as keyword is not required. So, what you get in the result is:

  the,
  quick,
  fox,
  jumped,
  over,
  the,
  lazy,
  dog

...在名为 fox 的列中具有所有 brown 的值。对于像上面这样的简短查询，这很容易注意到（特别是当每个列具有非常不同的值时），但是出现在一个相当复杂的查询中，其中大部分是整数列，例如：

...with all the values of brown in a column named fox. This can be noticed pretty easily for a short query like the above (especially when each column has very different values), but where it came up was in a fairly complicated query with mostly integer columns like this:

select
  foo,
  bar,
  baz,
  another_table.quux,
  a1,
  a2,
  a3,
  a4,
  a5,
  a6,
  a7,
  a8,
  a9,
  a10,
  a11,
  a12,
  a13,
  a14,
  a15,
  a16,
  b1,
  b2,
  b3,
  b7,
  b8,
  b9,
  b10,
  b11,
  b12,
  b13,
  b14,
  b18,
  b19,
  b20,
  b21,
  c1,
  c2,
  c3,
  c4,
  c5,
  c6,
  c7,
  c8
from table_name
join another_table on table_name.foo_id = another_table.id
where
  blah = 'blargh'
-- many other things here
;

即使列名更好，其值也非常相似。例如，如果在 b11 之后想念逗号，然后所有 b11 值都称为 b12 ，当我们通过处理管道运行数据时（这取决于结果中的这些列名称），这是非常不幸的。通常，我会从table_name中选择 select * ，但是我们需要的是选择性更高。

Even with better column names, the values are all very similar. If I were to miss a comma after b11 (for example) and then all of the b11 values get called b12, it's pretty unfortunate when we run the data through our processing pipeline (which depends on these column names in the result). Normally, I'd do select * from table_name, but what we needed required us to be a little more selective than that.

问题

我正在寻找一种阻止这种情况再次发生的策略。

What I'm looking for is a strategy to stop this from happening again.

别名列时是否有要求 as 的方法？还是写东西使其出错的技巧？ （例如，在类似C的语言中，我开始将 1 == foo 而不是 foo == 1 编写为当我不小心遗漏了一个等号时，会导致编译错误，使其成为无效的 1 = foo 而不是 foo = 1 。）

Is there a way to require as when aliasing columns? Or a trick of writing things to make it give an error? (For example, in C-like languages, I started writing 1 == foo instead of foo == 1 to cause a compile error when I accidentally left out an equal sign, making it the invalid 1 = foo instead of foo = 1.)

我通常使用 vim ，所以我可以使用 hlsearch 突出显示逗号，以便我可以注意。但是，我不得不经常在其他环境中编写查询，包括一个专有接口，在该接口中我无法轻松地做到这一点。

I use vim normally, so I can use hlsearch to highlight commas just so I can eyeball it. However, I have to write queries in other environments quite often, including a proprietary interface in which I can't do something like this easily.

感谢您的帮助！

推荐答案

我之前做过的一件事是将逗号移动到行首。这样可以带来一些好处。首先，您可以立即查看是否缺少逗号。其次，您可以在末尾添加新列，而不必修改先前的最后一行。

One thing that I've done before is to move the commas to the beginning of the line. This allows some benefits. First, you can instantly see if there are any commas missing. Second, you can add a new column at the end without having to modify the previously last line.

缺少：

select
  the
, quick
, brown
  fox
, jumped
, over
, the
, lazy
, dog
from table_name;

不丢失：

select
  the
, quick
, brown
, fox
, jumped
, over
, the
, lazy
, dog
from table_name;

这篇关于SQLite列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！