在数据库表中存储整数或字符串之间的差异 [英] Difference between storing integer or string in database table

问题描述

我关心的是性能，工程和可读性。假设我有一个博客，每个帖子的状态：published（4），pending review（2），draft（1）。建议在 status 列中存储这些信息？

I'm concern about performance, engineering and readability. Let's say I have a blog, and every post has its status: published (4), pending review (2), draft (1). What is the recommended to store these information in the status column?

status        <======= storing status as string
========
pending
published
draft

status        <======= storing status as integer
========
2
4
1

此外，如果我们应该存储整数，应该避免存储运行整数： 1，2，3，4，5 ，而不是存储^ 2整数： 2，4，8，16，32 ？

Also, if we should store integer, should we refrain from storing running integer: 1, 2, 3, 4, 5, as opposed to storing a ^2 integer: 2, 4, 8, 16, 32?

非常感谢。

推荐答案

我认为你最好的赌注是更快的性能，更少的存储空间和可读性是使用CHAR（1） - （p）发布，挂起（r）eview和（d）raft。您可以使用CHECK约束或外键引用验证数据。

I think your best bet for faster performance, less storage space, and readability is to use CHAR(1)--(p)ublished, pending (r)eview, and (d)raft. You can validate that data with either a CHECK constraint or a foreign key reference.

CHAR（1）比整数占用的空间少得多。它是直接可读的人类，所以它不需要一个连接来了解它。由于它既小又立即可读，因此即使在数千万行的表上，也能获得比整数连接更快的检索。

CHAR(1) takes substantially less space than an integer. It's directly readable by humans, so it doesn't need a join to understand it. Since it's both smaller and immediately readable, you'll get faster retrieval than a join on an integer even on a table of tens of millions of rows.

这篇关于在数据库表中存储整数或字符串之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！