SQL - 带前导零的数字数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7207231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Numeric data type with leading zeros
提问by P.Brian.Mackey
I need to store Medicare APC codes.I believe the format requires 4 numbers. Leading zeros are relevant. Is there any way to store this data type with verification? How should I store this data (varchar(4), int)?
我需要存储Medicare APC 代码。我相信格式需要 4 个数字。前导零是相关的。有没有办法通过验证来存储这种数据类型？我应该如何存储这些数据 (varchar(4), int)？
回答by ecarden
This kind of issue, storing zero leading numbers that need to be treated as Numeric values on some scenarios (i.e. sorting) and as textual values in others (i.e. addresses) is always a pain and there is no one answer that is best for all users. At my company we have a database that stores numbers as text for codes (not Medicare APC codes) and we must pad them with zero's so they will sort properly when used in an order operation.
这种问题，将零前导数字存储在某些场景（即排序）中需要被视为数值，而在其他场景中需要被视为文本值（即地址）总是很痛苦，并且没有一个对所有用户来说都是最好的答案. 在我的公司，我们有一个将数字存储为代码文本（不是医疗保险 APC 代码）的数据库，我们必须用零填充它们，以便在订单操作中使用它们时可以正确排序。
Do not use a numeric data type for this because the item is not a true number but textual data that uses numeric characters. You will not be performing any calculations or aggregates on the codes and so the only benefit to storing them as a number would be to ensure proper sorting of the codes and that can be done with the code stored as text by padding it with zeros where needed. If you sue a numeric data type then any time the code is combined with other textual values you will have to explicitly convert it to CHAR/VARCHAR or let SQL Server do it since implicit conversions should always be avoided that means a lot of extra work for you and the query processor any time the code is used.
不要为此使用数字数据类型，因为该项目不是真正的数字，而是使用数字字符的文本数据。您不会对代码执行任何计算或聚合，因此将它们存储为数字的唯一好处是确保代码的正确排序，并且可以通过在需要的地方用零填充以文本形式存储的代码来完成. 如果您起诉数字数据类型，那么任何时候代码与其他文本值组合时，您都必须将其显式转换为 CHAR/VARCHAR 或让 SQL Server 执行此操作，因为应始终避免隐式转换，这意味着需要大量额外工作您和查询处理器在任何时候使用代码。
Assuming you decide to go with a textual data type the question then is should you use VARCHAR or CHAR and while many who have posted say VARCHAR I would suggest you go with CHAR set to a length of 4. WHY?
假设您决定使用文本数据类型，那么问题是您应该使用 VARCHAR 还是 CHAR，虽然许多发布的人都说 VARCHAR 我建议您将 CHAR 设置为 4 的长度。为什么？
The VARCHAR data type is for textual data in which the size (the length or number of characters) is unknown in advance. For this Medicare code we know the length will always be at least 4 and possibly no more than 4 for the foreseeable future. SQL Server handles the storage of the data differently between CHAR and VARCHAR. SQL Server's BOL (Books On Line) says :
VARCHAR 数据类型用于大小（长度或字符数）事先未知的文本数据。对于此 Medicare 代码，我们知道在可预见的未来，长度将始终至少为 4，并且可能不超过 4。SQL Server 以不同的方式处理 CHAR 和 VARCHAR 之间的数据存储。SQL Server 的 BOL（在线书籍）说：
Use CHAR when the size of the column data entries are consistent
列数据条目大小一致时使用CHAR
Use VARCHAR when the size of the column data varies considerably.
当列数据的大小变化很大时使用 VARCHAR。
I can't say for certain this is true for SQL Server 2008 and up but for earlier versions, the use of a VARCHAR data type carries an extra overhead of 1 byte per row of data per column in a table that has a VARCHAR data type. If the data stored is always the same size and in your scenario it sounds like it is then this extra byte is a waste.
我不能肯定这对于 SQL Server 2008 及更高版本是正确的，但对于早期版本，使用 VARCHAR 数据类型会在具有 VARCHAR 数据类型的表中每列每行数据带来 1 字节的额外开销. 如果存储的数据总是相同的大小，并且在您的场景中听起来是这样，那么这个额外的字节就是浪费。
In the end it's up to you as to whether you like CHAR or VARCHAR better but definitely don't use a numeric data type to store a fixed length code.
最后，您更喜欢 CHAR 还是 VARCHAR 取决于您，但绝对不要使用数字数据类型来存储固定长度的代码。
回答by SLaks
That's not numeric data; it's textual data that happens to contain digits.
那不是数字数据；它是恰好包含数字的文本数据。
Use a VARCHAR.
使用一个VARCHAR.
回答by Sparky
I agree, using
我同意，使用
CHAR(4)
for the check constraint use
对于检查约束使用
check( APC_ODE LIKE '[0-9][0-9][0-9][0-9]' )
This will force a 4 digit number only to be accepted...
这将强制只接受 4 位数字...
回答by Lukas Eder
varchar(4)
optionally, you can still add a check constraint to ensure the data is numeric with leading zeros. This example will throw exceptions in Oracle. In other RDBMS, you could use regular expression checks:
或者，您仍然可以添加检查约束以确保数据是带有前导零的数字。此示例将在 Oracle 中引发异常。在其他 RDBMS 中，您可以使用正则表达式检查：
alter table X add constraint C
check (cast(APC_CODE as int) = cast(APC_CODE as int))
回答by Arun
If you are certain that the APC codes will always be numeric (that is if it wouldn't change in the near future), a better way would be to leave the database column as is, and handle the formatting (to include leading zeros) at places where you use this field values.
如果您确定 APC 代码将始终是数字（也就是说，如果它在不久的将来不会改变），更好的方法是保持数据库列不变，并处理格式（包括前导零）在您使用此字段值的地方。
回答by Muflix
in TSQL
在 TSQL 中
check( isnumeric(APC_ODE) = 1)
回答by JNK
If you need leading 0s, then you must use a varcharor other string data type.
如果需要前导 0，则必须使用 avarchar或其他字符串数据类型。
There are ways to format the output for leading 0s without compromising your actual data.
有多种方法可以在不影响实际数据的情况下将输出格式化为前导 0。
回答by a1ex07
CHAR(4)seems more appropriate to me (if I understood you right, and the code is always 4 digits).
CHAR(4)对我来说似乎更合适（如果我理解正确的话，代码总是 4 位数字）。
回答by Mike Dinescu
What you want to use is a VARCHAR data type with a CHECK constraint, using LIKE with a pattern to check for numeric values.
您要使用的是带有 CHECK 约束的 VARCHAR 数据类型，使用带有模式的 LIKE 来检查数值。
