database 数据库中的整数与字符串

Integer vs String in database

提问by Josh Hunt

When defining datatypes in a database, I have always had a problem with choosing whether to use integers or strings to store certain 'numerical' data.

在数据库中定义数据类型时，我总是在选择使用整数还是字符串来存储某些“数字”数据时遇到问题。

Say I am building YetAnotherAddressBookand there is a post code field. Provided that post codes are alwaysa 4 digit number, which data type do I store it as? Integer or string? Technically it is an integer, but I'm not doing any sort of calculations on it, I'm just spitting it out into a table. Would your opinion change if I want to sort the table by post code?

说我建立然而，另一个地址簿，有一个职位代码字段。如果邮政编码始终是 4 位数字，我应该将其存储为哪种数据类型？整数还是字符串？从技术上讲，它是一个整数，但我没有对其进行任何类型的计算，我只是将它吐到表格中。如果我想按邮政编码对表格进行排序，您的意见会改变吗？

Now, I'm not stupid. I do recognize a valid need for integers, such as page views and unique users or logged in users and guest users. But what about for storing how many files are in a torrent? Integer or string?

现在，我不傻。我确实认识到对整数的有效需求，例如页面浏览量和唯一用户或登录用户和访客用户。但是如何存储一个 torrent 中有多少文件呢？整数还是字符串？

回答by myplacedk

In my country, post-codes are also always 4 digits. But the first digit can be zero.

在我的国家，邮政编码也总是 4 位数字。但第一个数字可以为零。

If you store "0700" as an integer, you can get a lot of problems:

如果将“0700”存储为整数，则会遇到很多问题：

• It may be read as an octal value
• If it is read correctly as a decimal value, it gets turned into "700"
• When you get the value "700", you must remember to add the zero
• I you don't add the zero, later on, how will you know if "700" is "0700", or someone mistyped "7100"?

• 它可以读作八进制值
• 如果它被正确读取为十进制值，它会变成“700”
• 当你得到值“700”时，你一定要记得加上零
• 我你不加零，以后你怎么知道“700”是“0700”，还是有人打错了“7100”？

Technically, our post codes is actually strings, even if it is always 4 digits.

从技术上讲，我们的邮政编码实际上是字符串，即使它始终是 4 位数字。

You can store them as integers, to save space. But remember this is a simple DB-trick, and be careful about leading zeroes.

您可以将它们存储为整数，以节省空间。但请记住，这是一个简单的 DB 技巧，并注意前导零。

But what about for storing how many files are in a torrent? Integer or string?

但是如何存储一个 torrent 中有多少文件呢？整数还是字符串？

That's clearly an integer.

这显然是一个整数。

回答by TheTXI

I always use the following rule:

我总是使用以下规则：

If you plan on performing mathematical calculations on it (adding/subtracting/etc) make it an integer or other numerical data type.

如果您计划对其进行数学计算（加/减/等），请将其设为整数或其他数值数据类型。

If you do not plan on performing any types of mathematical calculations on the field, store it as a string.

如果您不打算对字段执行任何类型的数学计算，请将其存储为字符串。

In the instance of Zip codes, you should never have a time where you need to add to a zip code, or subtract, or multiply two zip codes together. Mathematical functions generally are not used on ZIP codes because they are used as identifiers and not quantities. Therefore you should store your zip code as a string datatype

在邮政编码的例子中，您永远不应该有时间需要添加到邮政编码，或者将两个邮政编码相减或相乘。数学函数通常不用于邮政编码，因为它们用作标识符而不是数量。因此，您应该将邮政编码存储为字符串数据类型

回答by Enrico Murru

in my opinion for postal codes you have to use strings, because you can have postal codes that stards with zeros (09100) and if you use integers it would be 9100: sorting is not a problem, because there is still an alphabetical order ('09100' comes before '09101'). For Storing file numbers I would expect an interger, so you don't have any problem in incresing / decreasing its number. So integer vs strings depends upon the use you make!

在我看来，对于邮政编码，您必须使用字符串，因为您可以使用以零开头的邮政编码 (09100)，如果您使用整数，它将是 9100：排序不是问题，因为仍然存在字母顺序 (' 09100' 出现在 '09101' 之前）。对于存储文件编号，我希望有一个整数，因此您在增加/减少其编号时没有任何问题。所以整数还是字符串取决于你的用途！

回答by Andrew Hare

This is a question of semantics. You are trying to decide the appropriate datatype for storage which can be a tricky question. The best rule of thumb is to store your data as integers if you will need to use the data as an integer.

这是一个语义问题。您正在尝试为存储决定合适的数据类型，这可能是一个棘手的问题。如果您需要将数据用作整数，最好的经验法则是将数据存储为整数。

In other words, since you will never be using a postal code as a number it does not make sense to store it as one. It doesn't matter what the data lookslike, it matters what it is. Is a postal code a number? No, it's a string of characters that just happens to be made up of wholly numeric characters. Therefore a postal code is best stored as a string.

换句话说，由于您永远不会将邮政编码用作数字，因此将其存储为一个是没有意义的。数据看起来如何并不重要，重要的是它是什么。邮政编码是数字吗？不，它是一串恰好由全数字字符组成的字符串。因此，邮政编码最好存储为字符串。

回答by cletus

Well as far as postcodes go, this is a typical UK postcode:

就邮政编码而言，这是一个典型的英国邮政编码：

EC2R 6PK

In university my databases lecturer told me something that has stuck with me and still holds 15+ years later:

在大学里，我的数据库讲师告诉我一些一直困扰着我并且在 15 多年后仍然适用的事情：

If you perform arithmetic on it, store it as a number. Otherwise it's a string.

如果对其进行算术运算，请将其存储为数字。否则它是一个字符串。

Frankly I don't think you can go wrong with that advice.

坦率地说，我认为这个建议不会出错。

Obviously you don't perform arithmetic on postcodes, therefore they're strings.

显然，您不对邮政编码执行算术运算，因此它们是字符串。

回答by gbn

Post code is not a number: it's a code or identifier. The same applies to phone numbers.

邮政编码不是数字：它是代码或标识符。这同样适用于电话号码。

Number of files in a torrent is an integer.

Torrent 中的文件数是一个整数。

Not least, in this case you can create a CHECK CONSTRAINT LIKE '[09][09][09][09]'to keep data correct at the database level.

尤其是，在这种情况下，您可以创建一个CHECK CONSTRAINT LIKE '[09][09][09][09]'以在数据库级别保持数据正确。

回答by Ronald Wildenberg

For a postal code I would choose a string. It is not intrinsically an integer. It is just an identifier for something and it could just as well have been a series of four characters.

对于邮政编码，我会选择一个字符串。它本质上不是整数。它只是某物的标识符，也可以是一系列四个字符。

As for the number of files inside a torrent, that should be an integer.

至于 torrent 中的文件数，应该是一个整数。

回答by Brian Agnew

Is '0000' a postcode ? Is it distinct from '0' ?

“0000”是邮政编码吗？它与 '0' 不同吗？

If it's always a four-digit number, I would always store it as 4 digits, and that would point to keeping it as a string.

如果它总是一个四位数字，我总是将它存储为 4 位数字，这意味着将它保留为一个字符串。

回答by Jim Blizard

I don't use a numerical data type unless I expect to do math on the data. Why risk finding a problem in the future for something that you were "sure" would always be a number that someone decides to put a non-numeric character in.

除非我希望对数据进行数学运算，否则我不使用数字数据类型。为什么要冒着在未来为您“确定”的事情发现问题的风险将始终是某人决定放入非数字字符的数字。

If you aren't going to do math on it make it a string.

如果您不打算对其进行数学运算，请将其设为字符串。

回答by HLGEM

It is also good to remember that not all postal codes in all counrties are numbers only. Just because you don't have any addreses in Canada right now doesn't mean you won't have any. I've always gone by the rule, if you want to do math calculations store it in a numeric type, if it is just a code (postalcodes, phones, SSN, partnumber, etc) then I store it as a string. What you want to avoid is any unnecessary casting of the data into another format every time you call it up (for instance code to add the leading zeros if you store the postal code as a number or code to convert a string to a number for calulations). These can be expensive operations if you need to do them repeatedly, espcially when the tables are large and you end up having to do the conversion in the where clause. It is far better to store the data the way you need to use it.

请记住，并非所有国家/地区的所有邮政编码都只是数字。仅仅因为您现在在加拿大没有任何地址并不意味着您不会有任何地址。我一直遵循规则，如果你想进行数学计算，将它存储在数字类型中，如果它只是一个代码（邮政编码、电话、SSN、部件号等），那么我将它存储为字符串。您想要避免的是每次调用时将数据不必要地转换为另一种格式（例如，如果您将邮政编码存储为数字或将字符串转换为数字以进行计算的代码，则添加前导零的代码）。如果您需要重复执行这些操作，这些操作可能会很昂贵，尤其是当表很大并且您最终不得不在 where 子句中进行转换时。