SQL 表只有一行？

SQL table with a single row?

提问by Jeremy Powell

What is the point (if any) in having a table in a database with only one row?

在只有一行的数据库中拥有一个表有什么意义（如果有的话）？

Note: I'm not talking about the possibility of having only one row in a table, but when a developer deliberately makes a table that is intended to always have exactly one row.

注意：我不是在谈论表格中只有一行的可能性，而是当开发人员故意制作一个旨在始终只有一行的表格时。

Edit:

编辑：

The sales tax example is a good one.

销售税的例子是一个很好的例子。

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one large table; I assume I'm missing something.

我刚刚在一些代码中观察到我正在查看三个不同的表，这些表包含三种不同类型的证书（a la SSL），每个表都只有一行。我不明白为什么不把它做成一张大桌子；我想我错过了一些东西。

采纳答案by Welbog

I've seen something like this when a developer was asked to create a configuration table to store name-value pairs of data that needs to persist without being changed often. He ended up creating a one-row table with a column for each configuration variable. I wouldn't say it's a good idea, but I can certainly see why the developer did it given his instructions. Needless to say it didn't pass review.

当开发人员被要求创建一个配置表来存储需要持久化而不经常更改的名称-值对数据时，我见过类似的事情。他最终创建了一个单行表，每个配置变量都有一列。我不会说这是一个好主意，但我当然可以理解开发人员为什么按照他的指示这样做。不用说它没有通过。

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one row; I assume I'm missing something.

我刚刚在一些代码中观察到我正在查看三个不同的表，这些表包含三种不同类型的证书（a la SSL），每个表都只有一行。我不明白为什么不把它排成一排；我想我错过了一些东西。

This doesn't sound like good design, unless there are some important details you don't know about. If there are three pieces of information that have the same constraints, the same use and the same structure, they should be stored in the same table, 99% of the time. That's a big part of what tables are for fundamentally.

这听起来不像是好的设计，除非有一些你不知道的重要细节。如果有三条信息具有相同的约束、相同的用途和相同的结构，它们应该存储在同一张表中，99% 的时间。这是表的基本用途的很大一部分。

回答by Vinay Sajip

For some things you only need one row - typically system configuration data. For example, "current sales tax rate". This might change in the future and so shouldn't be hardcoded, but you'll typically only ever need one at any given time. This kind of data needs to be in the database so that queries can use it in computations.

对于某些事情，您只需要一行 - 通常是系统配置数据。例如，“当前的销售税率”。这在未来可能会发生变化，因此不应进行硬编码，但您通常在任何给定时间都只需要一个。这种数据需要在数据库中，以便查询可以在计算中使用它。

回答by Aaron Fi

It's not necessarily a bad idea.

这不一定是个坏主意。

What if you had some global state (say, a boolean) that you wanted to store somewhere? And you wanted your stored procedures to easily access this state?

如果你有一些想要存储在某个地方的全局状态（比如一个布尔值）怎么办？您希望您的存储过程轻松访问此状态吗？

You could create a table with a primary key whose value range was limited to exactly one value.

您可以创建一个表，其主键的值范围仅限于一个值。

回答by MAB

Single row is like a singleton class. purpose: to control or manage some other process.

单行就像一个单例类。目的：控制或管理一些其他过程。

Single row table could act as a critical section or as deterministic automaton (kind of dispatcher based on row values)

单行表可以充当临界区或确定性自动机（一种基于行值的调度程序）

Single row is use full in a table COMPANY_DESCRIPTION, to obtain consistent data about that company. Use full on company letters and addressing.

在表 COMPANY_DESCRIPTION 中使用 full 单行，以获取有关该公司的一致数据。在公司信函和地址中使用 full。

Single row is use full to contain an actual value like VAT or Date or Time, and so on.

单行用于包含实际值，如增值税或日期或时间等。

回答by jeje

It can be useful sometime to emulate some features the Database system doesn't provide. I'm thinking of sequences in MySQL for instance.

有时模拟数据库系统不提供的某些功能会很有用。例如，我正在考虑 MySQL 中的序列。

回答by Quassnoi

What is the point (if any) in having a table in a database with only one row?

在只有一行的数据库中拥有一个表有什么意义（如果有的话）？

A relational database stores things as relations: a tuples of data satisfying some relation.

关系数据库将事物存储为关系：满足某种关系的数据元组。

Like, this one: "a VATof this many percentis in effect in my country now".

像这一个：“一VAT中这个许多％的是在我的国家效力，现在”。

If only one tuple satisifies this relation, then yes, it will be the only one in the table.

如果只有一个元组满足这种关系，那么是的，它将是表中唯一的元组。

SQLcannot store variables: it can store a set consisting of 1element, this is a one-row table.

SQL不能存储变量：它可以存储由1元素组成的集合，这是一个单行表。

Also, SQLis a set based language, and for some operations you need a fake set of only one row, like, to select a constant expression.

此外，它SQL是一种基于集合的语言，对于某些操作，您需要一个只有一行的假集合，例如，选择一个常量表达式。

You cannot just SELECTout of nothing in Oracle, you need a FROMclause.

你不能只是SELECT无中生有Oracle，你需要一个FROM子句。

Oraclehas a pseudotable, dual, which contains only one row and only one column.

Oracle有一个伪表，dual仅包含一行和一列。

Once, long time ago, it used to have tworows (hence the name dual), but lost its second row somewhere on its way to version 7.

很久以前，它曾经有两行（因此得名dual），但在通往 version 的途中丢失了第二行7。

MySQLhas this pseudotable too, but MySQLis able to do selects without FROMclause. Still, it's useful when you need an empty rowset: SELECT 1 FROM dual WHERE NULL

MySQL也有这个伪表，但MySQL可以在没有FROM子句的情况下进行选择。不过，当您需要一个空行集时，它还是很有用的：SELECT 1 FROM dual WHERE NULL

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one large table; I assume I'm missing something.

我刚刚在一些代码中观察到我正在查看三个不同的表，这些表包含三种不同类型的证书 (a la SSL)，每个表都只有一行。我不明白为什么不把它做成一张大桌子；我想我错过了一些东西。

It may be a kind of "have it all or lose" scenario, when all three certificates are needed at once:

当同时需要所有三个证书时，这可能是一种“要么一无所有”的场景：

SELECT  *
FROM    ssl1
CROSS JOIN
        ssl2
CROSS JOIN
        ssl3

If any if the certificates is missing, the whole query returns nothing.

如果缺少证书，则整个查询不返回任何内容。

回答by PaulG

A table with a single row can be used to store application level settings that are shared across all database users. 'Maximum Allowed Users' for example.

具有单行的表可用于存储在所有数据库用户之间共享的应用程序级别设置。例如“最大允许用户数”。

回答by ProZach

Funny... I asked myself the same question. If you just want to store some simple value and your ONLY method of storage is an SQL server, that's pretty much what you have to do. If I have to do this, I usually end up creating a table with several columns and one row. I've seen a couple commercial products do this as well.

有趣...我问自己同样的问题。如果您只想存储一些简单的值，并且您唯一的存储方法是 SQL 服务器，那么这就是您必须做的。如果我必须这样做，我通常最终会创建一个包含多列和一行的表格。我已经看到一些商业产品也这样做。

回答by NerdFury

If your database is your application, then it probably makes sense for storing configuration data that might be required by stored procedures implementing business logic.

如果您的数据库是您的应用程序，那么存储实现业务逻辑的存储过程可能需要的配置数据可能是有意义的。

If you have an application that could use the file system to store information, then I don't think there is an advantage to using the database over an XML or flat file, except maybe that most developers are now far more well versed in using SQL to store and retrieve data than accessing the file system.

如果您有一个可以使用文件系统存储信息的应用程序，那么我认为使用数据库而不是 XML 或平面文件有什么优势，除非大多数开发人员现在更精通使用 SQL存储和检索数据而不是访问文件系统。

回答by MANCHUCK

I really cannot figure out why this would be the best solution. It seams more efficient to just have some kind of config file that will contain the data that would be in the tables one row. The cost of connecting to the database and querying the one row would be more costly. However if this is going to be some kind of config for the database logic. Then this would make a little bit more sense depending on the type of database you are using.

我真的不明白为什么这会是最好的解决方案。只拥有某种配置文件将包含表中一行的数据看起来更有效。连接到数据库并查询一行的成本会更高。但是，如果这将是数据库逻辑的某种配置。那么这会更有意义，具体取决于您使用的数据库类型。